Generate enum from a database lookup table using T4

This is something rather common. You're building an application that uses database storage in the background. If your database isn't completely trivial and you're not fatally in love with magic values/numbers, then you probably also use lookup tables to gain referential integrity when it comes to certain types, codes and similar data. But to follow the DRY software development principle we want to use these values defined in database on upper layers as well without manually writing any additional code. Because as mentioned magic values are evil regardless of where they're used.

Database lookup table

A typical lookup table would be some entity status when it has more than two values. So a simple bit field (ie. IsActive) can't be used. Consider this schema of two related tables:

   1:  create table dbo.StatusType

   2:  (

   3:      StatusTypeID int identity not null

   4:          primary key,

   5:      Name nvarchar(100) not null

   6:          unique,

   7:      Description nvarchar(250) null

   8:  )

   9:  go

  10:   

  11:  create table dbo.Process

  12:  (

  13:      ProcessID int identity not null

  14:          primary key,

  15:      Name nvarchar(100) not null

  16:          unique,

  17:      Status int not null

  18:          references dbo.StatusType(StatusTypeID)

  19:  )

  20:  go

The first one being a referential integrity lookup table with pre-populated values that don't change over application's lifetime. It defines a limited set of values similar to:

• Pending
• Active
• Completed
• Deferred
• Error
• etc.

The thing is that these statuses are usually also used by higher level layers of the application and not just in the database itself.

C# lookup table equivalent

The usual C# equivalent of a lookup table is an enumeration:

   1:  public enum StatusType

   2:  {

   3:      Pending,

   4:      Active,

   5:      Completed,

   6:      Deferred,

   7:      Error

   8:  }

After we have written this enumeration we can easily refer to its values in our code and convert them to numbers when needed. But by writing this enumeration manually we face several risks:

• values may be in different order in the database compared to enumeration which leads to incorrect values when converted to and from numbers (setting enumeration values' actual values explicitly does solve this problem though);
• duplication of code because we had to put all these values in the database (either by hand or by writing a series of insert T-SQL statements) and then write a corresponding enumeration as well;
• human factors when a developer adds a new value in the lookup database table and forgets to add an equivalent into the enumeration type;
• etc.

We can avoid these risks by writing a T4 template once that automatically generates C# enumerations based on database values. Then we can reuse this template 'till death do us part...

T4 template that generates enum types

It's wise that we automate enum generation as much as possible. By this I mean that we want to write a T4 template that doesn't need any additional configuration to be used afterwards. In practical use this means that this template should be smart enough to read the name of the template file to fetch database data and enum type naming. If nothing else.

Anyway this code here will generate enumerations for you. Put this code in a file like EnumGenerator.ttinclude so you can refer to it in actual enumeration generation files.

   1:  <#@ template debug="true" hostSpecific="true" #>

   2:  <#@ output extension=".generated.cs" #>

   3:  <#@ Assembly Name="EnvDTE.dll" #>

   4:  <#@ Assembly Name="System.Data" #>

   5:  <#@ import namespace="EnvDTE" #>

   6:  <#@ import namespace="System.Data" #>

   7:  <#@ import namespace="System.Data.SqlClient" #>

   8:  <#@ import namespace="System.IO" #>

   9:  <#@ import namespace="System.Text.RegularExpressions" #>

  10:  <#

  11:      string tableName = Path.GetFileNameWithoutExtension(Host.TemplateFile);

  12:      string path = Path.GetDirectoryName(Host.TemplateFile);

  13:      string columnId = tableName + "ID";

  14:      string columnName = "Name";

  15:      string columnDescription = "Description";

  16:      string connectionString = "data source=.;initial catalog=DBName;integrated security=SSPI";

  17:   

  18:      // Get containing project

  19:      IServiceProvider serviceProvider = (IServiceProvider)Host;

  20:      DTE dte = (DTE)serviceProvider.GetService(typeof(DTE));

  21:      Project project = dte.Solution.FindProjectItem(Host.TemplateFile).ContainingProject;

  22:  #>

  23:  using System;

  24:  using System.CodeDom.Compiler;

  25:   

  26:  namespace <#= project.Properties.Item("DefaultNamespace").Value #><#= Path.GetDirectoryName(Host.TemplateFile).Remove(0, Path.GetDirectoryName(project.FileName).Length).Replace("\\", ".") #>

  27:  {

  28:      /// <summary>

  29:      /// <#= tableName #> auto generated enumeration

  30:      /// </summary>

  31:      [GeneratedCode("TextTemplatingFileGenerator", "10")]

  32:      public enum <#= tableName #>

  33:      {

  34:  <#

  35:      SqlConnection conn = new SqlConnection(connectionString);

  36:      string command = string.Format("select {0}, {1}, {2} from {3} order by {0}", columnId, columnName, columnDescription, tableName);

  37:      SqlCommand comm = new SqlCommand(command, conn);

  38:   

  39:      conn.Open();

  40:   

  41:      SqlDataReader reader = comm.ExecuteReader();

  42:      bool loop = reader.Read();

  43:   

  44:      while(loop)

  45:      {

  46:  #>      /// <summary>

  47:          /// <#= reader[columnName] #> configuration setting.

  48:          /// </summary>

  49:          <#= Pascalize(reader[columnName]) #> = <#= reader[columnId] #><# loop = reader.Read(); #><#= loop ? ",\r\n" : string.Empty #>

  50:  <#

  51:      }

  52:  #>  }

  53:  }

  54:  <#+

  55:      private string Pascalize(object value)

  56:      {

  57:          Regex rx = new Regex(@"(?:[^a-zA-Z0-9]*)(?<first>[a-zA-Z0-9])(?<reminder>[a-zA-Z0-9]*)(?:[^a-zA-Z0-9]*)");

  58:          return rx.Replace(value.ToString(), m => m.Groups["first"].ToString().ToUpper() + m.Groups["reminder"].ToString().ToLower());

  59:      }

  60:  #>

You can as well grab a copy of this code on Google code.

What does this template do?

• generates enumeration values with explicit integer values;
• uses Visual Studio's namespace naming convention so generated enumerations have project's default namespace with any subfolders appended (just like any code file in Visual Studio);
• adds enumeration XML documentation by using Description table column values - if you don't have these you can easily change code line 47 to

    47:  /// <#= reader[columnName] #> configuration setting.

  and omit the column from the select statement in line 36;
• correctly names the generated file and adds an additional attribute in the code so the generated enum doesn't get scrutinised by code analysis;
• multi-word lookup table values are correctly concatenated to pascal-cased equivalents (ie. Multi word value becomes a MultiWordValue);
• enumeration values follow C# identifier naming conventions (except for underscores);
• all enumeration values consist of only letters and numbers, everything else gets cut out (including allowed underscores);

How do we use this template?

In order to use this template we have to add additional T4 templates. One for each enumeration we'd like to generate and put them in the correct project and folder so type and namespace naming will be as desired.

Every template file has to be named the same as database lookup table. In our example it would be StatusType.tt. When we add this template to our project, we have to put this line of code inside it:

   1:  <#@ include file="..\PathToYourT4RelativeToThisTemplate\EnumGenerator.ttinclude" #>

Then we simply save the file and let Visual Studio do its magic. And that's it really. Hope you like it and that it's helpful to you just as much as it will be to me 'till death do us part.