5 Methods of Generating Code Using Microsoft Tools

March 11th, 2019

Writing code can be tedious, but if you have the tools available, it can drastically improve productivity. Today, I cover five ways to generate code rapidly.

We are all looking for better ways to speed up our development. I've even discussed numerous ways to code at ludicrous speed.

Of course, we always have the commercial approaches of buying software to generate code based on a given technology, but lately, I've been generating code using a number of techniques explained in this post.

While it may seem like overkill to use a code generator, there are times when it's required to generate a large amount of code.

In the list below, I explain certain scenarios why I used and how I implemented each method to generate code...FAST!

1. T-SQL (SQL Server)

And yes, I would say that T-SQL is a language and I use it frequently to generate code.

About two weeks ago, I needed a way to generate a list of insert statements for seed data. This seed data required the primary key for a subset of records (Note: This script works off of the AdventureWorks2012 database for demo purposes).

DECLARE @tablename NVARCHAR(20)

SET @tableName = 'Production.Product'

SELECT TOP 20 
    'INSERT INTO '+@tableName+
        ' SELECT ProductId, Name, ProductNumber, MakeFlag FROM '+@tableName+
        ' WHERE NOT EXISTS(SELECT TOP 1 1 FROM '
        +@tablename+' WHERE ProductId='+CAST(prd.ProductID AS VARCHAR(10))+')' AS Query
FROM [Production].[Product] prd

You can modify this to use multiple tables for various seed/static tables for lookup.

I've seen a lot of dynamic SQL where people generate a number of stored procedures or even CRUD stored procedures.

In a database project (SQL Server project), the ability to dynamically create a stored procedure or custom SQL code is simple enough to pipe code into a Script.PostDeployment.sql for a DACPAC deploy.

This makes your database deployments so much easier when you have a DevOps mentality.

(And yes, I know you can use DTS to simplify this even more).

2. T4 (Text Template Transformation Toolkit)

I've been using T4 templates since Visual Studio 2010 and think it's one of Visual Studio's best-kept secrets.

Some developers don't even know about this handy code generator in the IDE.

The great part about T4 generation is the ability to use C# to...well...create C# (or SQL, XML, or whatever output you want).

You can connect to a database and pull records from the table and create SQL statements, create repository patterns, services...just about anything you can think of for your system.

I mentioned T4 templates back in 2014 with my Ludicrous Speed Series, but they are still going strong and are very easy to use.

To create a T4 template (using Visual Studio 2017),

  1. "Add a new item" to your project
  2. Under Visual C#, select General.
  3. In the middle, select the Text Template item. The filename at the bottom of the dialog box should have a .tt extension.
  4. Enter the name of your T4 template.
  5. Click Add.

In this example below, this connects to a database, pulls back records from tables, builds a .CSV file with a pipe (|) as a field delimiter, and saves the file as a CSV for SQL Server Bulk Insert.

<#@ template language="C#" debug="True" hostspecific="True" #>
<#@ output extension=".sql" #>
<#@ SqlModelDirective processor="SqlModelDirectiveProcessor" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.xml" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<#

    // VARIABLES
    var templateDirectory = Path.GetDirectoryName(this.Host.ResolvePath("."));
    var outputDirectory = Path.Combine(templateDirectory, ""); // Just use current.

    // ConnectionString 
    // Add your connection string here
    var connection = new SqlConnectionStringBuilder(
        "Server=localhost;Initial Catalog=my-database;Persist Security Info=False;");

    // Seed tables to generate a MERGE statement
    var tableList = new List<string>
    {
        "ZipCode"
    };

    var conn = new SqlConnection(connection.ConnectionString);

    foreach (var tableName in tableList)
    {
        var command = string.Format("select * FROM {0}", tableName);
        var comm = new SqlCommand(command, conn);

        conn.Open();

        var reader = comm.ExecuteReader();

        // Get the names of the columns.
        var columns = GetColumnNames(reader);

        // Build record rows.
        while (reader.Read())
        {
            var values = new List<string>();
            for (var i = 0; i < columns.Count; i++)
            {
                var fieldType = reader.GetFieldType(i);

                if (reader.IsDBNull(i))
                {
                    values.Add("NULL");
                    continue;
                }

                switch (fieldType.Name)
                {
                    case "Int32":
                        values.Add(reader.GetValue(reader.GetOrdinal(columns[i])).ToString());
                        break;
                    case "Boolean":
                        values.Add(reader.GetValue(reader.GetOrdinal(columns[i])).ToString() == "True" ? "1" : "0");
                        break;
                    default:
                        values.Add("N'" +
                                   reader.GetValue(reader.GetOrdinal(columns[i]))
                                       .ToString()
                                       .Trim()
                                       .Replace("'", "''")
                                   + "'");
                        break;
                }
            }

            var row = String.Join("|", values);
 
#>
<#= row #>
<#
        }

        conn.Close();

        SaveOutput(Path.Combine(outputDirectory, tableName + ".csv"));
    }
#>
 
<#+

    private List<string> GetColumnNames(SqlDataReader reader, bool addBrackets = false)
    {
        // Get the names of the columns.
        var columns = new List<string>();
        for (var i = 0; i < reader.FieldCount; i++)
        {
            var columnName = reader.GetName(i);
            if (addBrackets)
            {
                columnName = "[" + columnName + "]";
            }
            columns.Add(columnName);
        }

        return columns;
    }

    private void SaveOutput(String fileName)
    {
        Directory.CreateDirectory(Path.GetDirectoryName(fileName));
        File.Delete(fileName);
        File.WriteAllText(fileName, this.GenerationEnvironment.ToString());
        this.GenerationEnvironment.Clear();
    }
#>

The output file is outside the <# #> tags.

This may be a small, basic T4 script, but it can be modified to output any SQL statements you want to build...FAST!

3. MS Excel/Google Sheets

It seems everyone is building systems off of Excel and Google Sheets. It's crazy-scary the amount of systems running on a spreadsheet.

There, I said it.

However, I like to think of it as a tool as opposed to a system, but being a developer, you'd expect me to say that, right?

There are two examples of how I used a spreadsheet as a code generator.

The first scenario dealt with my business partner sending me data in a hierarchical format and he wanted me to add this large spreadsheet into a database.

This post explained how I transformed the data into something useable. As soon as I finished converting the data in the spreadsheet, I'm already in a spreadsheet to calculate values. So I thought why not have it create the INSERT statements for each row in the spreadsheet as well?

Once I finished, I copied the rows into SQL Server Management Studio (SSMS) and ran the SQL statements. Job finished.

The second scenario is actually an ongoing, weekly process I use to create my weekly newsletter, DanylkoWeb Digest (formerly called DanylkoWeb Wire). I use Google Sheets to gather all of the weekly data and it automatically generates content for a MailChimp newsletter every week.

This generates custom HTML based on a consistent format of my newsletter.

Always look for faster ways to build not just code, but content. ;-)

4. Snippets

Do not knock snippets.

I've seen a few developers who really rock the snippets and write code like their fingers are on fire.

The Code Snippet Manager (Under Tools, Ctrl-K, Ctrl-B) gives developers a list of quick-to-use snippets to increase productivity.

For example, in a C# file, type:

ctor

and press Tab. Presto! You have an instant constructor.

So if you use a lot of custom code you type over and over again, I would recommend creating some snippets to match your coding style and rock the snippets.

To activate them, press Ctrl-K, then Ctrl-X to select specific snippets. To go back, press Backspace.

One other tool to use in conjunction with Snippets is Resharper (Review|Smart Template Design Patterns). It gives you a boost of productivity, but does NOT give you wings. ;-)

5. ASP.NET Core Scaffolding (dotnet CLI)

If you work with ASP.NET Core and use the command-line interface (CLI), you know you can create an entire application ready to deploy by typing dotnet new.

But did you know you can create your very own application templates so scaffolding applications occurs quicker with the command line?

Microsoft even takes you through a simple tutorial on how to set up your own custom template for applications.

When done properly, your template should appear in the dotnet list as shown in the screenshot taken from here.

One of the best ways to incorporate this into your company is to prepare templates for Web APIs, General Web Sites, and anything else you feel could be generated where 80% is boilerplate and the remaining is custom-made to the task.

Conclusion

As with every concept, everyone has their way of writing code. Some developers purchase tools to write code, some use the sure-fire method of using their hands, but I think these are probably the best methods to keep in your tool belt for future projects.

Mainly because they are free and I can afford them. ;-)

Did I miss a method? How do you generate large amounts of code? Do you code anymore? Post your comments below and let's discuss.