Table To Enum Using T4 Template

Most developers have used enums at some point in their development process. They are basically strongly typed compile-time constants and allow you to assign a symbolic name to an integer value, removing the use of magic numbers, which leads to more maintainable code. Most projects tend to have several enums that mirror database lookup tables and these values tend to get out of sync over time and can be tedious to maintain.

In a previous project, I created enums dynamically from a database table using a number of classes in the System.Reflection.Emit namespace and use a separate project to generate an assembly that housed the enums. Though not overly complex, it involved a number of extra steps and I wanted to refactor. I finally got around to refactoring using the Text Template Transformation Toolkit (T4).

T4 is basically Microsoft’s code generation tool that’s built into Visual studio, so I decided to use it to simplify the creation of the various enums I needed. There are dozens of great articles on T4 such as T4 Code Generation – Best Kept Visual Studio Secret, Code Generation and T4 Text Templates, T4 and ASP.NET MVC and Creating your first code generator so I won’t go into detail here, but it’s well worth a few minutes of your time if you aren’t familiar with them.

To start, I created a new Text Template in Visual Studio 2013 and added the assembly and namespace directives that were needed to connect to a SQL database and specified the output file type.

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ output extension=".cs" #>

I then added a code block to set the connection, create a list of tables to generated enums for and the logic to loop through the tables.

<#
// Connection info
ServerName = "(local)";
DatabaseName = "AdventureWorks2012";

// List of entities to create enums for
entities.Add(new Entity {Name="Person.AddressType", TextColumn="Name", ValueColumn="AddressTypeID"});
entities.Add(new Entity {Name="Person.ContactType", TextColumn="Name", ValueColumn="ContactTypeID"});
entities.Add(new Entity {Name="Person.PhoneNumberType", TextColumn="Name", ValueColumn="PhoneNumberTypeID"});

// Add a namespace around the generated enums (could be added directly to the template as well)
WriteLine("namespace AdWorksPerson");
WriteLine("{");

// Generate enums
foreach(var entity in entities)
{
#>
    public enum <#= ParseEntityField(entity.Name) #>
    {
    <#
        CreateEnum(entity);
    #>
    }

<#
}
// Namespace closing brace
WriteLine("}");
#>

And finally, I added a class code block with the fields and methods that will used by the other code blocks in the template.

<#+
// Class Feature Block (#+) avoids repeating common code

private string ServerName;
private string DatabaseName;

private struct Entity
{
    public string Name;
    public string TextColumn;
    public string ValueColumn;
}

private List<Entity> entities = new List<Entity>();

private string CreateConnectionString()
{
    var sb = new SqlConnectionStringBuilder();
    sb.DataSource = ServerName;
    sb.InitialCatalog = DatabaseName;
    sb.IntegratedSecurity = true;

    return sb.ConnectionString;
}

private string ParseEntityField(string name)
{
    // Remove the dot, left bracket, right bracket, space
    // and slash characters from the fieldname.
    var pattern = @"[\.\[\]\s/-]*";
    var regex = new Regex(pattern, RegexOptions.None);
    return regex.Replace(name, string.Empty);
}

private void CreateEnum(Entity e)
{
    using(var con = new SqlConnection(CreateConnectionString()))
    {
        var cmd = con.CreateCommand();
        var sql = string.Format("SELECT {0}, {1} FROM {2} ORDER BY {0}", e.TextColumn, e.ValueColumn, e.Name);
        cmd.CommandText = sql;
        con.Open();

        using(var rdr = cmd.ExecuteReader())
        {
            PushIndent("\t");
            while (rdr.Read())
            {
                var option = string.Format("{0} = {1},", ParseEntityField(rdr[e.TextColumn].ToString()), rdr[e.ValueColumn].ToString());
                WriteLine(option);
            }
            PopIndent();
        }
    }
}
#>

Now, to generate the enums simply save the template or right-click the template name in Solution Explorer and choose Run Custom Tool, and a file (csharp in this case) will be created based on the name of the template and the output directive. The output generated from the template is shown below.

namespace AdventureWorks
{
    public enum PersonAddressType
    {
    Archive = 6,
    Billing = 1,
    Home = 2,
    MainOffice = 3,
    Primary = 4,
    Shipping = 5,
    }

    public enum PersonContactType
    {
    AccountingManager = 1,
    AssistantSalesAgent = 2,
    AssistantSalesRepresentative = 3,
    CoordinatorForeignMarkets = 4,
    ExportAdministrator = 5,
    InternationalMarketingManager = 6,
    MarketingAssistant = 7,
    MarketingManager = 8,
    MarketingRepresentative = 9,
    OrderAdministrator = 10,
    Owner = 11,
    OwnerMarketingAssistant = 12,
    ProductManager = 13,
    PurchasingAgent = 14,
    PurchasingManager = 15,
    RegionalAccountRepresentative = 16,
    SalesAgent = 17,
    SalesAssociate = 18,
    SalesManager = 19,
    SalesRepresentative = 20,
    }

    public enum PersonPhoneNumberType
    {
    Cell = 1,
    Home = 2,
    Work = 3,
    }
}

One of the first question you may have, as I did, when using T4 is how do I access app/web config settings from the template. You cannot do it directly as T4 runs under a different context than that of the project. See T4 templates using own project .config file, T4 templates for Web.config/App.config AppSettings or T4 Template For AppSettings Access In Config Files for information on using config files.

T4 allows a mixture of text and code blocks and can generate any type of text file such as classes, web pages, stored procedures, etc. These types of boilerplate code are often needed repeatedly and code generation can be used to reduce time and the chance of errors.

Tags: , ,

Comments are closed.