The major project we've been working on had a non-unusual requirement: take these SQL database tables and convert them to enumerations we can use over a service. A quick Google search found some code that did exactly what we wanted, so of course we (ok, I) copied-and-pasted that code into our solution and BAM, it worked like a charm.
Problem was, when someone asked me to explain what it did, I couldn't. I can't stand not understanding something, especially not something that enables my projects to work, so I dove into this unfamiliar T4 Templates technology and came out the other side with a little more knowledge than I'd started with. Perhaps it will help you too.
Something Really Clever
As I said earlier, we had a requirement for this big project we're working on to use values from a set of database lookup tables as Enumerations in my server code. Given that I'm already a big fan of using Enums to reduce ambiguity it didn't take much convincing for me to get started. A quick google search revealed this StackOverflow answer in which Robert Koritnik shared his method of using T4 Templates to generate files for Enumerations generated from database lookup tables.
As always, let's see the finished solution first. Here's the T4 template from the StackOverflow answer:
<#@ template debug="true" hostSpecific="true" #>
<#@ output extension=".generated.cs" #>
<#@ Assembly Name="System.Data" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#
string tableName = Path.GetFileNameWithoutExtension(Host.TemplateFile);
string path = Path.GetDirectoryName(Host.TemplateFile);
string columnId = tableName + "ID";
string columnName = "Name";
string connectionString = "data source=.;initial catalog=DBName;integrated security=SSPI";
#>
using System;
using System.CodeDom.Compiler;
namespace Services.<#= GetSubNamespace() #>
{
/// <summary>
/// <#= tableName #> auto generated enumeration
/// </summary>
[GeneratedCode("TextTemplatingFileGenerator", "10")]
public enum <#= tableName #>
{
<#
SqlConnection conn = new SqlConnection(connectionString);
string command = string.Format("select {0}, {1} from {2} order by {0}", columnId, columnName, tableName);
SqlCommand comm = new SqlCommand(command, conn);
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
bool loop = reader.Read();
while(loop)
{
#> /// <summary>
/// <#= reader[columnName] #> configuration setting.
/// </summary>
<#= Pascalize(reader[columnName]) #> = <#= reader[columnId] #><# loop = reader.Read(); #><#= loop ? ",\r\n" : string.Empty #>
<#
}
#> }
}
<#+
private string Pascalize(object value)
{
Regex rx = new Regex(@"(?:[^a-zA-Z0-9]*)(?<first>[a-zA-Z0-9])(?<reminder>[a-zA-Z0-9]*)(?:[^a-zA-Z0-9]*)");
return rx.Replace(value.ToString(), m => m.Groups["first"].ToString().ToUpper() + m.Groups["reminder"].ToString().ToLower());
}
private string GetSubNamespace()
{
Regex rx = new Regex(@"(?:.+Services\s)");
string path = Path.GetDirectoryName(Host.TemplateFile);
return rx.Replace(path, string.Empty).Replace("\\", ".");
}
#>
Then, for every Enum we need generated, we create a t4 file with the enum name and place the following line of code in it:
<#@ include file="..\..\T4 Templates\EnumGenerator.ttinclude" #>
I plugged this in to our application, added a few t4 files for enumerations we needed generated, and boom, it just worked. All my lookup tables were now perfect little enums.
So we chugged along for a little while, perfectly content with our newfound solution, until my teammate Stacy asked about these T4 Templates and what they actually, y'know, did. My response went something like:
Matt: Well, it works like this. The first line..... Uh.... Well, actually, this set of lines here does something really clever....
I had no idea what this code was doing. I knew what it resulted in (a set of Enums generated from database tables) but couldn't sufficiently explain to anyone, even myself, exactly how it did this, which I unquestionably should be able to do. This code snippet was a black box, unknown to me, and experience told me that if I don't know how something works I won't be able to fix it if it breaks.
So I needed to understand just what it was that this code snippet was doing. That's what I'm going to do here: break down the snippet so that I (and hopefully you readers) will better understand what is being accomplished.
So let's get started!
What are T4 Templates?
If I'm going to understand what the code is doing, I first need to understand what technology is being used. MSDN has this to say about T4 Templates:
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 generated file can be text of any kind, such as a Web page, or a resource file, or program source code in any language.
OK so, in my mind, T4 templates allow you to write code that will generate other code. Seems simple enough. But how does it do that?
Breaking Down The Template
Let's walk through this template and see if we can understand what it is doing.
<#@ template debug="true" hostSpecific="true" #>
<#@ output extension=".generated.cs" #>
<#@ Assembly Name="System.Data" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text.RegularExpressions" #>
This appears to be some kind of collection of using statements, but the <#@ syntax was throwing me off. MSDN states that this syntax is used to create "directives" which (more or less) tell the T4 template how to behave. In our case, this template will cause the generated file to end in ".generated.cs" and imports several namespaces that we will need this template to use.
<#
string tableName = Path.GetFileNameWithoutExtension(Host.TemplateFile);
string path = Path.GetDirectoryName(Host.TemplateFile);
string columnId = tableName + "ID";
string columnName = "Name";
string connectionString = "data source=.;initial catalog=DBName;integrated security=SSPI";
#>
Now we start to see the meat of the template. The <# syntax is a boundary that specifies that anything within it is code running within the template (not the output code being generated). In our particular case, we are getting the name of the database table from the name of the file, which implies that each .tt file we want for each generated enum needs to be named the same as the database table.
We're also setting up the common values for the ID column and Name column, and getting our connection string ready. Just a bunch of setup here.
using System;
using System.CodeDom.Compiler;
namespace Services.<#= GetSubNamespace() #>
{
IMO this is the first truly interesting snippet. Given what I know about how T4 templates work (read: nothing) this snippet appears to be outputting actual code into the target file. Specifically, this is outputting two using statements and a namespace.
Since we're here, let's go ahead and examine the GetSubNamespace method:
private string GetSubNamespace()
{
Regex rx = new Regex(@"(?:.+Services\s)");
string path = Path.GetDirectoryName(Host.TemplateFile);
return rx.Replace(path, string.Empty).Replace("\\", ".");
}
Now I'm no regular expressions whiz-kid or anything (it looks like a bunch of gobbeldy-gook to me), but this appears to be using the folder structure where this file is located and transforming that structure into a namespace, which works very well for Visual Studio application since that's pretty much what VS does anyway. I'd be hard-pressed to explain exactly how it does this, though.
Back to the main portion of the code, specifically the first part emitted within the namespace declaration:
/// <summary>
/// <#= tableName #> auto generated enumeration
/// </summary>
[GeneratedCode("TextTemplatingFileGenerator", "10")]
public enum <#= tableName #>
{
<#
SqlConnection conn = new SqlConnection(connectionString);
string command = string.Format("select {0}, {1} from {2} order by {0}", columnId, columnName, tableName);
SqlCommand comm = new SqlCommand(command, conn);
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
bool loop = reader.Read();
while(loop)
{
#>
Now we start seeing the <# syntax again. The first time we see it is when it uses the tableName to output some comments about the generated enumeration. After that, we set up a SqlConnection, select from the appropriate table, and use a SqlDataReader to parse the results. What we do with those results is in the next snippet:
while(loop)
{
#> /// <summary>
/// <#= reader[columnName] #> configuration setting.
/// </summary>
<#= Pascalize(reader[columnName]) #> = <#= reader[columnId] #><# loop = reader.Read(); #><#= loop ? ",\r\n" : string.Empty #>
<#
}
#> }
}
For each of the values in the SqlDataReader, we output an enumeration value that uses the name found in the Name column in the table, after that name is run through a method called Pascalize(). Here's the implementation for Pascalize():
private string Pascalize(object value)
{
Regex rx = new Regex(@"(?:[^a-zA-Z0-9]*)(?<first>[a-zA-Z0-9])(?<reminder>[a-zA-Z0-9]*)(?:[^a-zA-Z0-9]*)");
return rx.Replace(value.ToString(), m => m.Groups["first"].ToString().ToUpper() + m.Groups["reminder"].ToString().ToLower());
}
Oh goody, regexes again (/sarcasm). I'm inferring somewhat here, but Pascalize() appears to transform strings into Pascal-cased objects. So, if the value of Name was
movie theatre or cinema
Pascalize would transform that string into:
MovieTheatreOrCinema
and this conforms to the naming conventions for enumerations. I do wonder how Pascalize strips out non-alphanumerics (since that is what the "(?:[^a-zA-Z0-9]*)" portion of the regular expression appears to be doing), but that investigation is for a later time.
Summary
When I started writing this post, I knew thismuch about how T4 templates worked. Now I know a little more, and all it took was a bit of intuition, some luck, and some googling.
The point of all this is simple: you learn by discovering, by guessing, by failing, and by succeeding. It is part of my job to understand what the code that my group is using actually does, and when I didn't know enough, I had to stumble around for a bit before understanding came to me. Don't be afraid to admit you don't know something, since you can always learn it!
Regular expressions, though.... not sure I'll ever understand that nonsense.
Happy Coding!
