Recently my collegue asked me to create "something" that could generate enumerations based on values from code tables in our database. Writing these enumerations manually is not a fun job, especially if you have many code tables and code values to include. Also, keeping these enumerations in sync with values in the database is not something you would like to maintain "by hand". Therefore, code generation is a pretty obvious solution for this problem.
With the previous Euricom framework we had a tool that could generate enumerations based on values in the database from within Visual Studio and that was actually quite useful. Instead of writing business code like...
If (Customer.Country == 1)
... you could write something like ...
If (Customer.Country == Country.Belgium)
... which makes your code easier to read, maintain and less error-prone.
Using the previous Euricom framework for this feature alone would not make a very good investment for the project. So, in this article I will show you how we solved the problem using T4 templates and code generation in Visual Studio 2008 in a very easy way. With this article, you'll get a very solid background on T4 templates and probably know everything you need to know about the process of code generation to start using it in your daily life as a developer.
Step 1: Define your code tables
First, I created a table called "CodeType" and another table called "CodeValue" in SQL Server with the following layout:
... and the following entries:
I guess these tables are pretty self explaining. Your application probably uses a different approach towards code tables, but that is not a vital issue for this demo. I certainly don't want to initiate a discussion on the best approach towards code tables, 'cause your solution is probably way better than these demo-tables anyway ;-)
You'll soon see that the process of code generation I would like to show you can easily be modified towards your approach on code tables, so please bare with me.
Step 2: Create a query to retrieve code values
With the following query you can retrieve the values for the enumerations by using the name of the code type. No rocket science here.
SELECT
CodeValue.CodeValueId,
CodeValue.Name
FROM CodeValue
INNER JOIN CodeType
ON CodeValue.CodeTypeId = CodeType.CodeTypeId
WHERE CodeType.Name = 'Country'
ORDER BY CodeValue.CodeValueID
Step 3: Create .NET code that uses this query
The following C# code is also pretty self explaining. The name of each code type we would like to create an enumeration for is added to a list of strings. Then, we use the query from above to create a dataset that holds the values for each of the code tables. (Yeah, yeah, I know ... I could have used a stored procedure, DataReader or LINQ to SQL instead, but I wanted to make this demo as simple as possible.)
string dbConnection = @"Integrated Security=SSPI;Initial Catalog=T4Samples;Data Source=localhost";
System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(dbConnection);
System.Collections.Generic.List<string> codeTypeNames = new List<string>();
codeTypeNames.Add("Country");
codeTypeNames.Add("Status");
foreach (string codeTypeName in codeTypeNames)
{
string query = "SELECT CodeValue.CodeValueId, CodeValue.Name " +
"FROM CodeValue " +
"INNER JOIN CodeType ON CodeValue.CodeTypeId = CodeType.CodeTypeId " +
"WHERE CodeType.Name = @CodeTypeName " +
"ORDER BY CodeValue.CodeValueId";
System.Data.DataSet dataSet = new System.Data.DataSet();
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(query, sqlConnection);
System.Data.SqlClient.SqlParameter sqlParameter = sqlCommand.Parameters.Add("@CodeTypeName", SqlDbType.NVarChar);
sqlParameter.Value = codeTypeName;
System.Data.SqlClient.SqlDataAdapter sqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(dataSet, "CodeValue");
System.Diagnostics.Debug.WriteLine("public enum " + codeTypeName);
System.Diagnostics.Debug.WriteLine("{");
foreach (System.Data.DataRow dataRow in dataSet.Tables["CodeValue"].Rows)
{
System.Diagnostics.Debug.WriteLine(dataRow["Name"].ToString() + " = " + dataRow["CodeValueId"].ToString() + ",");
}
System.Diagnostics.Debug.WriteLine("}");
}
By running this code, the enumeration-code is directed to the output window by the Debug.WriteLine statements.
Instead of writing to the output window, we could redirect the output to a *.cs file, include that file in our project and we're done. But by creating a T4 template, things can get far more interesting.
Step 4: Create a T4 template file
T4 stands for "Text Template Transformation Toolkit" and is a free alternative to CodeSmith, MyGeneration, NVelocity and other types of code generators. T4 templates is a feature that is included in Visual Studio 2008, but not all developers seem to know this, mainly because the Add New Project Item dialog does not provide a specific item for T4 templates, which makes it difficult for developers to discover.
To keep things simple, create a standard C# project of your choice. Right-click this project and select "Add - New Item". Choose "Text File" and name this file "Enumeration.tt".
Please make sure you give the file the extension ".tt" and not ".txt"!
In order for your code to run as a T4 template, move the code from above to your TT-file (not the linked .cs file!) and surround it with the following tags <# and #>. These tags are called statement blocks and are used to enclose control code for the code generation.
If you try to save the file like this, you will get compilation errors, which is normal.
Just like "regular code" you need to add a reference to some assemblies in order for the template code to work. This can be achieved by including the following directives: <#@ assembly name="System.Data.dll" #> and <#@ assembly name="System.XML.dll" #>.
Furthermore, you need to import some additional namespaces, which is basically the same as a "using" statement in C# or an "imports" statement in VB.NET. Therefore you need to add directives like <#@ import namespace="System.Data" #> on top of your template until it looks exactly like this:
<#@ assembly name = "System.Data.dll" #>
<#@ assembly name = "System.XML.dll" #>
<#@ import namespace = "System.Data" #>
<#@ import namespace = "System.Collections.Generic" #>
<#
string dbConnection = @"Integrated Security=SSPI;Initial Catalog=T4Samples;Data Source=localhost";
System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(dbConnection);
System.Collections.Generic.List codeTypeNames = new List();
codeTypeNames.Add("Country");
codeTypeNames.Add("Status");
foreach (string codeTypeName in codeTypeNames)
{
string query = "SELECT CodeValue.CodeValueId, CodeValue.Name " +
"FROM CodeValue " +
"INNER JOIN CodeType ON CodeValue.CodeTypeId = CodeType.CodeTypeId " +
"WHERE CodeType.Name = @CodeTypeName " +
"ORDER BY CodeValue.CodeValueId";
System.Data.DataSet dataSet = new System.Data.DataSet();
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(query, sqlConnection);
System.Data.SqlClient.SqlParameter sqlParameter = sqlCommand.Parameters.Add("@CodeTypeName", SqlDbType.NVarChar);
sqlParameter.Value = codeTypeName;
System.Data.SqlClient.SqlDataAdapter sqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(dataSet, "CodeValue");
System.Diagnostics.Debug.WriteLine("public enum " + codeTypeName);
System.Diagnostics.Debug.WriteLine("{");
foreach (System.Data.DataRow dataRow in dataSet.Tables["CodeValue"].Rows)
{
System.Diagnostics.Debug.WriteLine(dataRow["Name"].ToString() + " = " + dataRow["CodeValueId"].ToString() + ",");
}
System.Diagnostics.Debug.WriteLine("}");
}
#>
Now, when you save the TT-file, everything will compile (and run), but the Enumeration.cs file that is linked to your TT-file, still does not contain any code.
All you have to do is place stop and start statement block tags around the code where you currently write to the output window and replace that code by literal values. All the text outside a statement block is treated as actual chunks of text that will be outputted to the final file.
Whenever you need to use a variable from the code in your output, simply surround it with a <#= and #> tag. These tags are called expression blocks. The final code of your template file should look exactly like this:
<#@ assembly name = "System.Data.dll" #>
<#@ assembly name = "System.XML.dll" #>
<#@ import namespace = "System.Data" #>
<#@ import namespace = "System.Collections.Generic" #>
<#
string dbConnection = @"Integrated Security=SSPI;Initial Catalog=T4Samples;Data Source=localhost";
System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(dbConnection);
System.Collections.Generic.List codeTypeNames = new List();
codeTypeNames.Add("Country");
codeTypeNames.Add("Status");
foreach (string codeTypeName in codeTypeNames)
{
string query = "SELECT CodeValue.CodeValueId, CodeValue.Name " +
"FROM CodeValue " +
"INNER JOIN CodeType ON CodeValue.CodeTypeId = CodeType.CodeTypeId " +
"WHERE CodeType.Name = @CodeTypeName " +
"ORDER BY CodeValue.CodeValueId";
System.Data.DataSet dataSet = new System.Data.DataSet();
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(query, sqlConnection);
System.Data.SqlClient.SqlParameter sqlParameter = sqlCommand.Parameters.Add("@CodeTypeName", SqlDbType.NVarChar);
sqlParameter.Value = codeTypeName;
System.Data.SqlClient.SqlDataAdapter sqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(dataSet, "CodeValue");
#>
public enum <#=codeTypeName#>
{
<#
foreach (System.Data.DataRow dataRow in dataSet.Tables["CodeValue"].Rows)
{
#>
<#=dataRow["Name"].ToString()#> = <#=dataRow["CodeValueId"].ToString()#>,
<#
}
#>
}
<#
}
#>
Now, when you save the template file, you will see that the code file that is linked to your T4 template contains the enumerations you retrieved from the database. Pretty cool, isn't it?
Now you can (re)generate these enumerations any time you want from within your Visual Studio development environment simply by saving the TT-file or running the "TextTemplatingFileGenerator" custom tool on that file.
Even if your code is put under source control (TFS, SourceSafe, etc.) files are automatically checked in and out the moment you start generating code. But, there is still room for some improvement...
Step 5: Enhance your template
If you want to share your template among several projects and pass parameters to your template, it is probably a very good improvement to take the following steps:
Select your TT-file and in the properties window clear the Custom Tool which was set to "TextTemplatingFileGenerator" by default. Clearing the custom tool will prevent the creation of the linked C# file with the enumerations. The file is now treated as a regular text file in your project.
Then you move the definition of your connection string variable and the list of codetypenames below the template code and surround them with a <#+ and #> tag. Your base template file should look exactly like this:
<#@ assembly name = "System.Data.dll" #>
<#@ assembly name = "System.XML.dll" #>
<#@ import namespace = "System.Data" #>
<#@ import namespace = "System.Collections.Generic" #>
<#
string dbConnection = @"Integrated Security=SSPI;Initial Catalog=T4Samples;Data Source=localhost";
System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(dbConnection);
System.Collections.Generic.List codeTypeNames = new List();
codeTypeNames.Add("Country");
codeTypeNames.Add("Status");
foreach (string codeTypeName in codeTypeNames)
{
string query = "SELECT CodeValue.CodeValueId, CodeValue.Name " +
"FROM CodeValue " +
"INNER JOIN CodeType ON CodeValue.CodeTypeId = CodeType.CodeTypeId " +
"WHERE CodeType.Name = @CodeTypeName " +
"ORDER BY CodeValue.CodeValueId";
System.Data.DataSet dataSet = new System.Data.DataSet();
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(query, sqlConnection);
System.Data.SqlClient.SqlParameter sqlParameter = sqlCommand.Parameters.Add("@CodeTypeName", SqlDbType.NVarChar);
sqlParameter.Value = codeTypeName;
System.Data.SqlClient.SqlDataAdapter sqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(dataSet, "CodeValue");
#>
public enum <#=codeTypeName#>
{
<#
foreach (System.Data.DataRow dataRow in dataSet.Tables["CodeValue"].Rows)
{
#>
<#=dataRow["Name"].ToString()#> = <#=dataRow["CodeValueId"].ToString()#>,
<#
}
#>
}
<#
}
#>
<#+
string dbConnection;
System.Collections.Generic.List codeTypeNames = new List();
#>
Now add a second TT-file to your project with the following lines:
<#
dbConnection = @"Integrated Security=SSPI;Initial Catalog=T4Samples;Data Source=localhost";
codeTypeNames.Add("Country");
codeTypeNames.Add("Status");
#>
<#@ include file="Enumeration.tt" #>
When you save this new T4 template, you'll see that the enumerations are generated as before. Only now, you made your first template reusable between several projects and the codetypenames and connectionstring can be passed as a parameter to the template. I bet you can see the power of this.
Of course there are many more improvements you can make to this particular sample, like adding a configurable namespace, using a regular expression to format the names of your code types to valid enumeration names or adding errorhandling code in case your database is unaccessible. But with this small sample here, you already got a pretty good idea on how you can use T4 templates to generate code quite easy, with techniques and tools you as a developer are already familiar with.
Other interesting stuff
Your TT-file will create a file with extension ".cs" by default. If, for some reason you would like to create other file types (like .vb) or use another extension, you can include an output directive like this in your template:
<#@ output extension=".vb" #>
That way you can use T4 templates to generate all kinds of text files like Visual Basic code, C# code, T-SQL, XML or any other type of file.
It is also worth noting that, instead of using stop and start tags to close and re-open a statement block to write literal values to the output file. You can also write to the output like this:
<#
PushIndent("\t");
this.WriteLine("This will be written on a single line");
this.Write("This will be written ... ");
this.Write("on the same line as this.");
PopIndent();
#>
If you're not a C# programmer and would like to create your template based on Visual Basic code instead of C# code, you can also set another template directive to do so:
<#@ template language = "VB" #>
If your project contains several T4 templates and you would like to run them all at once, there is also a button available within the Solution Explorer Window, called "Transform All Templates".
T4 under the hood: How it all works?
Now I will explain the 2 steps involved in the code generation process.
In the first step, the T4 engine compiles the template. It parses the processing instructions, text and code blocks and generates a concrete TextTransformation class which is then compiled into a .NET assembly. The files that are created during this process are put under %USERPROFILE%\Local Settings\Temp.
In the second step, the T4 engine creates an instance of the GeneratedTextTransformation class, calls its TransformText method, saves the string and returns to the output file.
The picture below makes the generation process a bit more visual:
Now that you understand the process of code generation, it is also a good time to tell you on how the <#+ and #> tags in your template (known as a class feature block) actually work.
T4 takes all the class features in your template and adds them to the class that is compiled from your template behind the scenes. Of course, this is not just limited to things like properties. You can also define methods that you can use as helper functions to avoid repeating common code in your template. The following sample will illustrate this feature for you:
What if errors occur?
Errors can occur during the two steps of the generation process. They can occur during the generation of the compiled template, and during the execution of that template. Errors that occur during the first step of the generation process will be visible as compilation errors within Visual Studio. We already saw them in the beginning of this article. Errors that occur during the second step of the process will result in runtime errors.
If you are experiencing problems or unexpected behavior with your T4 template, you can debug your T4 template if you want. You can take the following steps to do so:
· Start a second instance of Visual Studio.
· Select "Attach to Process" from the "Debug" menu
· In the list of "Available Processes", select "devenv.exe" from the first Visual Studio instance and click the "Attach" button. This will attach the current instance of Visual Studio as a debugger for the first instance of Visual Studio running the template.
· Set a debugging template directive in the template file <#@ template language="C#" debug="True" #>
· Set a breakpoint in the template code from where you want to debug using the following code: System.Diagnostics.Debugger.Break();
· The second instance of Visual Studio will start debugging the moment you hit the breakpoint during code generation.
More interesting stuff to conclude
T4 templates are not restricted to Visual Studio 2008. If you're using Visual Studio 2005, you can still make use T4 templates, but not out of the box. You need to download and install additional DSL and Guidance Automation toolkits to do so.
T4 also comes with a command line utility "TextTransform.exe". You can use it to process templates outside the Visual Studio environment, similar to xsd.exe for strongly-typed datasets.
All the code samples used in this article can be downloaded here. The new Euricom framework makes extensive use of T4 templates for its code generation. Enjoy using it too!
By David Stroobants, .Net Solutions Architect