Creating C# Enums from a SQL Database using T4 Text Templates

A project my team is tasked with rewriting has a very, very normalized database. Everything, literally everything that can be represented in a SQL table is.

This includes things like lookup values, which results in SQL tables that look like this:

Table: tbl_RecordState

Table: tbl_RecordType

There are more than a hundred of these tables in our SQL database, each with their own type codes, names, and descriptions, but generally following the same structure in terms of column names. These values, according to our users, hardly ever change (on the order of one change a year) but they want to keep the ability to change them if necessary.

All of which lead me and my team to this conclusion: the values are not table rows, they are enumerations, and we should treat them as such.

In the C# world, we might have enumerations that look like this:

public enum RecordState
{
    [Display(Name = "New Entry", Description = "The first state a record has; only used when record is brand new.")]
    New,
    
    [Display(Name = "Under Review", Description = "The record is now being reviewed by the company.")]
    Review,
    
    [Display(Name = "Closed", Description = "The record is now closed.")]
    Closed
}

Our goal, then, was to create something that could generate these C# enumerations from the SQL tables. To that end, we decided to use T4 text templates to generate the enums from our database.

What are T4 Text Templates?

T4 text templates are, essentially, code that writes code. Using a specific format and with a certain amount of control logic, they can be used to create whole C# classes. Microsoft's own docs site has this to say:

"In Visual Studio, a T4 text template is a mixture of text blocks and control logic that can generate a text file. The control logic is written as fragments of program code in Visual C# or Visual Basic."

The idea is to use a T4 template to generate each C# enumeration we need to use in our project. Instead of writing hundreds of individual C# classes, we could write a single template, and reuse it to generate the enumerations.

Our Template Needs...

So, we need a T4 template that will do the following things:

1. Call a SQL database and query a specified table for the name, type code, description, and ID.
2. Create a new C# class with the obtained values for the enumeration, ignoring the silly "tbl_" prefixes, and in certain cases using a different name for the enum than the table.
3. Include the Name and Descriptions for the enumeration in the generated code file AND
4. Ensure that both the Enum name and the values are allowed by C#.

That seems like a lot, but it wasn't too bad. In fact, my starting point was another post of mine from nearly five years ago that had a very similar premise: use T4 templates to generate enums from database tables.

The T4 Text Template

From that starting point, we were able to develop the following T4 text template. It's not as complicated as it looks, and it's annotated for your reading pleasure.

EnumGenerator.ttinclude

<#@ template debug="true" hostSpecific="true" #>
<#@ output extension=".generated.cs" #>
<#@ Assembly Name="EnvDTE.dll" #>
<#@ Assembly Name="System.Data" #>
<#@ Assembly Name="System.Core" #>
<#@ import namespace="EnvDTE" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#@ import namespace="System.Linq" #>
<#
    //By default, we use these names for the columns we are querying from.
    string columnId = "id";
    string columnName = "Name";
    string columnDescription = "Description";
    string columnTypeCode = "typeCode";
    string connectionString = "YOUR CONNECTION STRING";
 
    //We need to get the containing project in order to know what project
    //to add the generated C# files to. In our case, we have a dedicated C#
    //project that has nothing except this template and the generated
    //enums in it.
    IServiceProvider serviceProvider = (IServiceProvider)Host;
    DTE dte = (DTE)serviceProvider.GetService(typeof(DTE));
    Project project = dte.Solution.FindProjectItem(Host.TemplateFile).ContainingProject;
#>
using System;
using System.CodeDom.Compiler;
using System.ComponentModel.DataAnnotations;
using System.Linq;
 
namespace MyProject.Enums
{
    /// <summary>
    /// <#= tableName #> auto generated enumeration
    /// </summary>
    [GeneratedCode("TextTemplatingFileGenerator", "10")]
    public enum <#= className #>
    {
<#
    SqlConnection conn = new SqlConnection(connectionString);
    //You may want to add a WHERE or ORDER BY clause to this query.
    string command = string.Format("select {0}, {1}, {2}, {3} from {4}", columnId, columnName, columnDescription, columnTypeCode, tableName);
    SqlCommand comm = new SqlCommand(command, conn);
 
    conn.Open();
 
    SqlDataReader reader = comm.ExecuteReader();
    bool loop = reader.Read();
 
    while(loop)
    {
#>
        [Display(Name = "<#= MakeSafeForString(reader[columnName]) #>", Description = "<#= MakeSafeForString(reader[columnName]) #>")]
        <#= CreateName(reader[columnTypeCode]) #> = <#= reader[columnId] #><# loop = reader.Read(); #><#= loop ? ",\r\n" : string.Empty #>
<#
    }
#>  }
}
<#+

    //This method makes the value safe for a string value by limiting
    //the characters to alphanumeric, parentheses, and hyphens.
    private string MakeSafeForString(object value)
    {
        Regex rx = new Regex(@"[^a-zA-Z0-9 \(\)\/-]");
        return rx.Replace(value.ToString(), "");
    }

    //Creates the name of the enum, using alphanumerics, 
    //parentheses, and underscores.
    //If the first character is a number, 
    //appends a "T" to the beginning of the value
    //and makes that the name.
    private string CreateName(object value)
    {
        Regex rx = new Regex(@"[^a-zA-Z0-9 \(\)\/_]");
        string typeCode = rx.Replace(value.ToString(), "");
        bool isDigit = char.IsDigit(typeCode[0]);
        if(isDigit)
        {
            return "T" + typeCode;
        }
        else
        {
            return FirstLetterToUpperCase(typeCode);
        }
    }

    // Returns the input string with the first 
    // character converted to uppercase
    public string FirstLetterToUpperCase(string s)
    {
        if (string.IsNullOrEmpty(s))
            throw new ArgumentException("There is no first letter");

        char[] a = s.ToCharArray();
        a[0] = char.ToUpper(a[0]);
        return new string(a);
    }
#>

We use this template by creating other files, named with the SQL table name, and specifying the columns to use. For example, here's the template for the RecordState table from earlier.

<# 
	string className = "RecordState";
	string tableName = "tbl_RecordState";
#>
<#@ include file="EnumGenerator.ttinclude" #>

Note that we must define the variables for className and tableName before the include, otherwise we will get a compilation error.

When we run the template (by right-clicking on the RecordState.tt file and clicking "Run Custom Tool") we get the following generated file:

using System;
using System.CodeDom.Compiler;
using System.ComponentModel.DataAnnotations;
using System.Linq;
 
namespace MyProject.Enums
{
    /// <summary>
    /// tbl_RecordState auto generated enumeration
    /// </summary>
    [GeneratedCode("TextTemplatingFileGenerator", "10")]
    public enum RecordState
    {
        [Display(Name = "New Entry", Description = "The first state a record has; only used when record is brand new.")]
        New = 1,

        [Display(Name = "Under Review", Description = "The record is now being reviewed by the company.")]
        Review = 2,

        [Display(Name = "Closed", Description = "The record is now closed.")]
        Closed = 3
    }
}

Sweet! This is a usable enumeration for our C# project! And, even better, it includes the Name and Description, which we can write simple extensions for.

GetDisplayName() Extension

Those extensions turned our to be very useful, so I am including a demo of one of them in this post. The general process is this:

1. For the given Enum value, use Reflection to determine if a [Display] attribute exists on the value.
2. If it does exist, return either the name or the description.
3. If it does not exist, return the name of the value (e.g. the Type Code from the table).

Hence, we get this extension for the Name:

public static class EnumExtensions
{
    public static string GetDisplayName(this Enum en)
    {
        if (en == null)
            return "<none selected>";

        try
        {
            //Use Reflection to get information about this particular value,
            //including if any attributes are on it.
            FieldInfo field = en.GetType().GetField(en.ToString());
                
            //If no attributes is found, return the name of the value.
            if(field == null)
                return en.ToString();

            //If attributes are found, find the [Display] attribute.
            DisplayAttribute[] attributes = (DisplayAttribute[])field.GetCustomAttributes(typeof(DisplayAttribute), false);

            //If a [Display] attribute exists, return the Name value from it.
            if (attributes.Length > 0)
                return attributes[0].Name;
            //Otherwise, return the name of the Enum value.
            else
                return en.ToString();
        }
        catch
        {
            return en.ToString();
        }
    }
}

The corresponding extension for Description is exactly the same, but returns attributes[0].Description instead of attributes[0].Name.

Summary

We can use T4 Text Templates to generate C# enumerations from a SQL database lookup table. The template we used is reusable, and we created more than a hundred C# enums from it. We also created a simple extension to get the [Display] attribute's Name value for a given enum value.

Got questions on how this works? Got a better implementation, or one that improves on something I've done here? I want to hear about it. Share in the comments!

Also, if this post helped you and you'd like to support my work, I'd very much appreciate it if you'd buy me a coffee. Your support means I can keep working on explaining complicated concepts in the most easily-understood manner I can manager. Thanks!

Happy Coding!