Google analytics script

Latest jQuery CDN with code tiggling.

Saturday, 8 January 2011

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.

17 comments:

  1. Thank you for the excellent t4. One question.
    Do you know what I need to change in the Regex for when the row's Name contains a parenthesis?
    Example:
    Pacific Standard Time (North America)

    ReplyDelete
  2. @David: you're probably having problems with the last parenthesis.

    You can try and use this regular expression that should do the trick for you.

    (?:[^a-zA-Z]*)(?<first>[a-zA-Z])(?<reminder>[a-zA-Z0-9]+)(?:[^a-zA-Z0-9]*)

    ReplyDelete
  3. I keep getting the following error - it appears to be looking for a table, base on the include filename!

    Running transformation: System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'EnumGenerator'.

    What am I doing wrong?

    ReplyDelete
  4. I finally got it executing, but it won't work for me, as I use schemas :(

    ReplyDelete
  5. @Anonymous: Sorry to hear that. I hope you can at least use some parts of my T4 so you don't have to do it all from scratch.

    ReplyDelete
  6. Hi and thanks for this great tool!

    I can't get the t4 to work with my connection string, even though I follow your instructions. I use ASP.NET MVC3.

    I made a new template, which only has: <#@ include file="...path...\EnumGenerator.ttinclude" #>

    My web config: connectionString="Data Source=.\SQLEXPRESS;Integrated Security=true;User Instance=true;AttachDBFilename=|DataDirectory|\MyDBName.mdf;Connection Timeout=20;"

    So I pasted into EnumGenerator.ttinclude:
    string connectionString = "data source=.\SQLEXPRESS;integrated security=true;User Instance=true;AttachDBFilename='c:\...\App_Data\MyDBName.mdf';Connection Timeout=20;";

    But it complains about: "Compiling transformation: Unrecognized escape sequence" in the main ttinclude.

    What did I do wrong?

    Thanks,
    Samantha

    ReplyDelete
  7. @Samantha: you basically have three possibilities to solve your problem:

    1. put an @ sign in front of your string literal as in
    string connectionString = @"data...";

    2. replace all backslashes with two backslashes so c:\... becomes c:\\.... In C# a backslash denotes an escape sequence hence your backslashes are misinterpreted by compiler.

    3. try using (forward)slashes instead of backslashes. Haven't tested but it may as well work.

    ReplyDelete
  8. I cannot believe it was something so small and obvious! I was looking for some complex db-related problem, SQL drivers, etc. And it was basic string handling. :-)

    Thanks Robert, this t4 is great.

    PS: there is a nice way to pull the connection string from your web.config, see here: http://stackoverflow.com/questions/4607028/t4-templates-and-connection-strings-in-a-class-library

    ReplyDelete
  9. @Samantha: I'm glad you've solved your problem. And thanks for the link although I don't see any particularly obvious benefit in reading connection string from web.config. You'd still need to make some changes in the generator template. The best solution would of course be to just use the file as is without the need to change anything. Now that would be a great solution.

    But thanks anyway. And thanks for using my template that proved to be helpful to you.

    ReplyDelete
  10. I guess it's a matter of personal preference. I like that approach because it means I specify/edit the connecntion string in one place only (DRY).

    ReplyDelete
  11. @Samantha: Perfectly valid reason...

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. Thanks, this is great!
    I have to agree with Samantha, is there a way to specify a connection string from the web config?

    Also I am getting a build error: 'Compiling transformation: The name 'Host' does not exist in the current context'. L(11). And help would be much appreciated.

    ReplyDelete
  14. @Chris: Sure there's a way to read connection string from the web.config file. Check links that @Samantha provided and see how it's done. But I don't intend to put this code into my T4. You're of course free to change it at your own will.

    But regarding Host this is all I can say: As long as you haven't changed the first line (or written anything before it so it's not the first line anymore) that sets hostSpecific="true" then Host should be visible. This is actually the setting that provides access to Host object instance.

    I suggest you check the web for T4 usage. I can also see you've probably asked a somehow similar question to Oleg Sych (T4 guru) back in 2009. He talks about the same setting in the template.

    ReplyDelete
  15. Thanks for this template. I was able to use it with minor modifications with very little T4 knowledge. Saved me a ton of time. Thanks.

    ReplyDelete
  16. Robert, great template.

    But, I have a word of warning to readers trying to use it for some aspnet tables, for me the template generates a System.UriFormatException.

    I had 3 problems creating a "Role" enum.

    1. The table name is aspnet_Roles.
    2. The table name does not appear in the column names: RoleId, RoleName, intead of aspnet_RolesId or aspnet_RoleName.
    3. The RoleId column is a Guid.

    This was quite easy to solve:

    A. Call the file Role as per the column names and desired enum name.
    B. Insert this text: tableName = "aspnet_" + tableName + "s"; just before line 35.
    C. Add a loop counter and replace id value with this.

    Save as, say AspNetEnumGenerator.ttinclude in order to use alongside the original template in your project.

    ReplyDelete
    Replies
    1. Thank you very much for this very informative report. It will surely be helpful to those that will be facing a similar situation than you.

      My template was of course never meant to cover 100% of cases, but a healthy majority. There may always be some specifics that need changing it. The good thing is that I wanted to keep my template as simple and easy to understand as possible for such margin cases so anybody can change it along their needs.

      So thank you for all this.

      Delete