Generate enums from a database using the Text Template Transformation Toolkit (T4)
A while ago I came across the Text Template Transformation Toolkit, or T4 for short, for the very first time and I was immediately amazed by the potential. The short introduction to T4 is to say that it enables you to write a template file and introduce your model into it and based on those two it can generate an output file. This file can be pretty much anything for example you can generate the classes for a data access layer based on your database structure, wrap stored procedures in classes, generate configuration files and so on.
This is how Microsoft describes the tool
Domain-Specific Language Tools includes a text template transformation toolkit that supports the processing of text templates. A text template is a file that contains a mixture of text blocks and control logic. When you transform a text template, the control logic combines the text blocks with the data in a model to produce an output file. You can use text templates to create text artifacts such as code files and HTML reports. For example, a model of the flow of control between the individual pages in a user interface, such as a wizard, can be passed as input to a text template. The text template can generate code and configuration files that implement that flow of control.
T4 is available with Visual Studio 2008 and is available as a download for Visual Studio 2005. At the current versions, the built in support for T4 is modest at best, not offering any intellisense or syntax highlighting.
Fortunately there is a tool available (in the form of a Visual Studio Add-in) to help improve the T4 experience, called Clarius T4 Editor which has been created by Clarius Consulting. I highly recommend you to download the free version if you are going to work with T4 since it makes it a lot more user-friendly. Let’s take a quick look on how this works, I’ll be using C# but it works just as well for VB.NET
- Fire up Visual Studio and create a new ConsoleApplication, give it what ever name you please.
- Right click you project and select Add –> New Item….
- Choose a new Textfile and call it Generator.tt (it’s important that you give it the .tt extension or Visual Studio won’t apply it’s magic on it)
If you look at your solution explorer you will now have a file called Generator.tt and it will contain a sub-file called Generator.cs, this is the file where the output will be generated by the T4 engine. As you will see, the file extension of the output file is controlled by a tag in the .tt file so if you, for example would like to generate a .sql file then thats where you’d need to change it, not on the file it self since it will be overwritten each time the T4 engine parses the template
Next open up the .tt file and write the following code
1: <#@ template language="C#v3.5" #>
2: <#@ output extension="CS" #>
3:
4: namespace NameOfYourRootNameSpace
5: {6: using System;
7:
8: public static class Foo
9: {10: public static void Bar()
11: {12: <#
13: PushIndent("\t");14: for (int i = 0; i < 10; i++)
15: {16: WriteLine("Console.WriteLine(" + i + ");");17: }
18: PopIndent();
19: #>
20: }
21: }
22: }
Be sure to replace the NameOfYourRootNameSpacei part with the actual root namespace of your project. Next right click the .tt file and select Run Custom Tool (or compile your project). If everything worked like it should (no errors in the error task pane) then you should be able to swicth to the .cs file and see the following code which was generated for us.
1: namespace NameOfYourRootNameSpace
2: {3: using System;
4:
5: public static class Foo
6: {7: public static void Bar()
8: {9: Console.WriteLine(0);
10: Console.WriteLine(1);
11: Console.WriteLine(2);
12: Console.WriteLine(3);
13: Console.WriteLine(4);
14: Console.WriteLine(5);
15: Console.WriteLine(6);
16: Console.WriteLine(7);
17: Console.WriteLine(8);
18: Console.WriteLine(9);
19: }
20: }
21: }
Notice how the file contains a real class complete with a static method! To try the class out just switch back over to the Program.cs file make a call to the static method and run your program! The Run Custom Tool command tells Visual Studio to invoke the tool which is associated with the file. So how does it know which tool to use? Select the .tt file in the Solution Explorer and press F4 (or manually swap over to the property window) and have a look at the Custom Tool property and you’ll notice it says TextTemplatingFileGenerator. This is defined in the windows registry as a part of the Generators section for Visual Studio.
I will not go into detail on the actual syntax, you should refer to the MSDN documentation I liked at the beginning of the post and also check out the blog of Oleg Sych, a T4 genius and his blog contains a lot of high quality information and tutorials on the T4 engine.
So, if you are anything like me than after seeing this you want more and start to think of all sort of things you could do. I decided to try and make something useful out of it and decided I was going to try and generate enums on the fly from a database. We’ve all experienced working with a database where there are “type” fields which defines something about an entity which is begin stored, for example OrderStatus or similar. We’ve also had to manually create and maintain these enums in our code and if a value was changed or added in the database we would manually have to update them in our enum declarations. I was hoping to get ride of that!
Sounds pretty easy. Create data structures which represents an enum in code, write the code that gets the values from the database, create the T4 template and inject it with the enum representations and out comes an automatically generated enum. I also decided I wanted to be able to add XML comments in the generated code to add intellisense and improve the user-experience when using the enums.
The first data structure I needed was to represent the actual name/value pairs which make up the enum members, along with the ability to tie a comment to it. This resulted in the EnumMember class
1: /// <summary>
2: /// Defines the information needed to create an enum member.
3: /// </summary>
4: public class EnumMember
5: {6: /// <summary>
7: /// Gets or sets the description of the enum member.
8: /// </summary>
9: /// <value>A string containing the description.</value>
10: /// <remarks>This will go into the summary section of the xml comment.</remarks>
11: public string Description { get; set; }12:
13: /// <summary>
14: /// Gets or sets the name of the enum member.
15: /// </summary>
16: /// <value>A string containing the name.</value>
17: /// <remarks>This will be the name of the enum type.</remarks>
18: public string Name { get; set; }19:
20: /// <summary>
21: /// Gets or sets the value of the enum member.
22: /// </summary>
23: /// <value>An integer containing the value.</value>
24: /// <remarks>This will be the value of the enum member.</remarks>
25: public int Value { get; set; }26: }
Pretty straight forward. Next I needed a data structure to represent the enum itself.
1: /// <summary>
2: /// Defines the information needed to create an enum.
3: /// </summary>
4: public class EnumDefinition
5: {6: /// <summary>
7: /// Gets or sets the list of columns to retrieve.
8: /// </summary>
9: /// <value>A <see cref="List{T}"/> of column names.</value>10: public List<string> Columns { get; set; }11:
12: /// <summary>
13: /// Gets or sets the description of the enum.
14: /// </summary>
15: /// <value>A string containing the description.</value>
16: /// <remarks>This will go into the summary section of the xml comment.</remarks>
17: public string Description { get; set; }18:
19: /// <summary>
20: /// Gets or sets the function used to map the values.
21: /// </summary>
22: /// <value>A <see cref="Func{T,TResult}"/> object.</value>23: public Func<DataRow, EnumMember> Mapper { get; set; }24:
25: /// <summary>
26: /// Gets or sets the name of the enum.
27: /// </summary>
28: /// <value>A string containing the name.</value>
29: /// <remarks>This will be the name of the enum type</remarks>
30: public string Name { get; set; }31:
32: /// <summary>
33: /// Gets or sets the namespace of the enum.
34: /// </summary>
35: /// <value>A string containing the namespace.</value>
36: public string Namespace { get; set; }37:
38: /// <summary>
39: /// Gets or sets the name of the database table.
40: /// </summary>
41: /// <value>A string containing the name of the table.</value>
42: public string Table { get; set; }43: }
This class is a bit more complex as I need to know from which columns and table to get the data, what namespace to put the enum in and how to convert the SQL query result (a DataTable) into an EnumMember object. This last part is the most complex thing in the entire class, but it’s really not that complicated. Since the properties of the EnumMember are known but the column names of the result set can vary from enum to enum (depending on the table you retrieve the values from) we need to tell it how to convert the result set into an EnumMember.
This it done using a lambda function which accepts a DataRow as its parameter and returns an EnumMember object, so all you have to do is write the code to perform the mapping and you’ll see a bit later how this can be done.
I had to put these two classes in a Class Library of their own because T4 cannot consume classes which are defined in the same project as the template and not only that, but you have to do it in separate solutions. The reason for this is that the T4 engine will lock a file when its working with it and if you create the project with the classes and the project which consumes them in a T4 template in the same solution, and add a project reference to it from the consuming project you will get an error. This is because of the lock T4 takes. The lock will prevent the class library project from being able to overwrite the output dll-file. So I had to create two different solutions and add a reference to the built assembly instead.
In my second project (a console application) I then created a T4 file called DynamicEnumGenerator.tt which looks like this
1: <#@ template language="C#v3.5" debug="True" #>
2: <#@ output extension="CS" #>
3:
4: <#@ assembly name="System.Configuration" #>
5: <#@ assembly name="System.Core" #>
6: <#@ assembly name="System.Data" #>
7: <#@ assembly name="System.Xml" #>
8: <#@ assembly name="CodeJunkie.DynamicEnums.Framework.dll" #>
9:
10: <#@ import namespace="System" #>
11: <#@ import namespace="System.Collections.Generic" #>
12: <#@ import namespace="System.Configuration" #>
13: <#@ import namespace="System.Data" #>
14: <#@ import namespace="System.Data.SqlClient" #>
15: <#@ import namespace="System.Xml" #>
16: <#@ import namespace="CodeJunkie.DynamicEnums.Framework" #>
17:
18: <#
19:
20: List<EnumDefinition> definitions =
21: new List<EnumDefinition>
22: {23: new EnumDefinition
24: {25: Description = "The description of the enum",
26: Name = "OrderStatus",
27: Columns = new List<string> {"Name", "Description", "Value"},28: Mapper = row => new EnumMember
29: {30: Description = row.Value<string>("Description"),31: Name = row.Value<string>("Name"),32: Value = row.Value<int>("Value")33: },
34: Namespace = "CodeJunkie.DynamicEnums.Framework.Enums",
35: Table = "OrderStatusType"
36: },
37:
38: new EnumDefinition
39: {40: Description = "The description of the enum",
41: Name = "ProductStatus",
42: Columns = new List<string> {"Name", "Description", "Value"},43: Mapper = row => new EnumMember
44: {45: Description = row.Value<string>("Description"),46: Name = row.Value<string>("Name"),47: Value = row.Value<int>("Value")48: },
49: Namespace = "CodeJunkie.DynamicEnums.Framework.Enums",
50: Table = "OrderStatusType"
51: },
52: };
53:
54: foreach (EnumDefinition definition in definitions)
55: {56: WriteLine(string.Format("namespace {0}", definition.Namespace));57: WriteLine("{");58: PushIndent("\t");59:
60: if (!string.IsNullOrEmpty(definition.Description))
61: {62: WriteLine("/// <summary>");63: WriteLine(string.Format("/// {0}", definition.Description));64: WriteLine("/// </summary>");65: WriteLine(string.Format("/// <remarks>This enum was auto-generated from the {0} table.</remarks>", definition.Table));66: }
67:
68: WriteLine(string.Format("public enum {0}", definition.Name));69: WriteLine("{");70: PushIndent("\t");71:
72: List<EnumMember> members =
73: GetMembers(definition);
74:
75: for (int index = 0; index < members.Count; index++)
76: {77: EnumMember member = members[index];
78:
79: if (!string.IsNullOrEmpty(member.Description))
80: {81: WriteLine("/// <summary>");82: WriteLine(string.Format("/// {0}", member.Description));83: WriteLine("/// </summary>");84: }
85:
86: string memberString =
87: string.Format("{0} = {1}", member.Name, member.Value);88:
89: if (index != (members.Count-1)) {90: memberString = string.Concat(memberString, ",");
91: }
92:
93: WriteLine(memberString);
94: }
95:
96: PopIndent();
97: WriteLine("}");98:
99: PopIndent();
100: WriteLine("}");101: }
102: #>
103:
104: <#+
105:
106: public List<EnumMember> GetMembers(EnumDefinition definition)
107: {108: string connectionString =
109: @"Data Source=.\SQLExpress;Initial Catalog=DynamicEnums;User ID=...;Password=...;";
110:
111: using (SqlConnection connection = new SqlConnection(connectionString))
112: {113: DataTable values =
114: new DataTable();
115:
116: string columnList =
117: string.Join(", ", definition.Columns.ToArray());118:
119: SqlCommand command =
120: new SqlCommand();
121: command.Connection = connection;
122: command.CommandType = CommandType.Text;
123: command.CommandText =
124: string.Format("SELECT {0} FROM [{1}]", columnList, definition.Table);125:
126: SqlDataAdapter adapter =
127: new SqlDataAdapter(command);
128: adapter.Fill(values);
129:
130: List<EnumMember> members =
131: new List<EnumMember>();
132:
133: if (values.Rows.Count > 0)
134: {135: foreach (DataRow row in values.Rows)
136: {137: members.Add(definition.Mapper(row));
138: }
139: }
140:
141: return members;
142: }
143: }
144:
145: #>
I’m sorry for the poor syntax formatting but the syntax highlighter plug-in I’m using with Windows Live Writer Beta doesn’t play that well with the CSS on my blog. I’ve done some tweaks in the stylesheet to make it look ok, but the authors of the blog template sure could use some education on proper markup and CSS semantics. But enough of this derailment.
If this looks daunting then don’t be alarmed, it’s not (also please not that I’ve not included any error handling code to keep the example short and to the point). The top part creates two EnumDefinition objects (they map to the same table for the ease of this demonstration but will create two enums with different names) and adds them to a list. Let’s take a closer look at one of the definitions
1: new List<EnumDefinition>
2: {3: new EnumDefinition
4: {5: Description = "The description of the enum",
6: Name = "OrderStatus",
7: Columns = new List<string> {"Name", "Description", "Value"},8: Mapper = row => new EnumMember
9: {10: Description = row.Value<string>("Description"),11: Name = row.Value<string>("Name"),12: Value = row.Value<int>("Value")13: },
14: Namespace = "CodeJunkie.DynamicEnums.Framework.Enums",
15: Table = "OrderStatusType"
16: }
The description will be used as the XML comment, the name is the name of the generated enum, the Columns list is the name of the columns in the source table which should be retrieved from the database, the namespace property sets the namespace which the enum should be placed in and the table property tells the code from what table it should retrieve the values in the specified columns. The final propery which is set is the mapper property, the lambda expression which knows how to convert a DataRow into an EnumMember.
If you are wondering about the Value<T>(string name) method that’s being called on the DataRow then you have every right to do so. It’s not a built in method on the DataRow class but an extension method I wrote to help cast the column values into the type I want. It looks like this
1: public static class DataRowExtensions
2: {3: public static T Value<T>(this DataRow row, string columnName)
4: {5: return (T) row[columnName];
6: }
7: }
The next section of code (the foreach block) is where the actual template is defined. It’s the part where the definitions are consumed and an enum is created. If you were able to read the first example in this blog then this part of the code shouldn’t be a mystery to you. Near the bottom of the code a local method, GetMembers, is defined. This method accepts an EnumDefinition object as a parameter and will return a list of EnumMember objects which has been retrieved from the specified table and columns in each of the definitions created at the top of the code.
Pay attention to how the mapper lambda expression is used to create the EnumMember objects
1: foreach (DataRow row in values.Rows)
2: {3: members.Add(definition.Mapper(row));
4: }
I should mention that I did try and store the connection string in the app.config file and use the ConfigurationManager class to retrieve it but it appears that this was impossible from within a T4 template because the variable was always set to null, even though I confirmed the code to work outside of the template. If anyone has a solution to this, please let me know.
Now if I switch over to my DynamicEnumGenerator.cs file I get this
1: namespace CodeJunkie.DynamicEnums.Framework.Enums
2: {3: /// <summary>
4: /// The description of the enum
5: /// </summary>
6: /// <remarks>This enum was auto-generated from the OrderStatusType table.</remarks>
7: public enum OrderStatus
8: {9: /// <summary>
10: /// The order has been processed.
11: /// </summary>
12: Processed = 1,
13: /// <summary>
14: /// The order is waiting to be processed
15: /// </summary>
16: Pending = 2,
17: /// <summary>
18: /// The order has been rejected
19: /// </summary>
20: Rejected = 3,
21: /// <summary>
22: /// The order has been closed
23: /// </summary>
24: Closed = 4
25: }
26: }
27: namespace CodeJunkie.DynamicEnums.Framework.Enums
28: {29: /// <summary>
30: /// The description of the enum
31: /// </summary>
32: /// <remarks>This enum was auto-generated from the OrderStatusType table.</remarks>
33: public enum ProductStatus
34: {35: /// <summary>
36: /// The order has been processed.
37: /// </summary>
38: Processed = 1,
39: /// <summary>
40: /// The order is waiting to be processed
41: /// </summary>
42: Pending = 2,
43: /// <summary>
44: /// The order has been rejected
45: /// </summary>
46: Rejected = 3,
47: /// <summary>
48: /// The order has been closed
49: /// </summary>
50: Closed = 4
51: }
52: }
Isn’t that cool?! The enums have 100%, dynamically, been generated from information stored inside tables in a database. Now I can just add a using declaration for the namespace of the enum I want to use and then start using it in my code just as if I had manually written the code myself! If a new value is added to the database, or if a value/name is changed then that will automatically be reflected the next time I compile the project. So there you have it, dynamic enum generation using T4 and Visual Studio!
I've been told, by Patrik Löwendalh, that Microsoft plans to use T4 for Entity Framework to give the developers control over the generated code and as such I think we can expect to see Visual Studio 2010 have much richer support for working with T4 than we currently have.
I hope you enjoyed the read, you can download the full source code at my codeplex page.
