Using a strongly-typed approach in working with database lookups, where all lookup values are kept in the container classes, makes it super easy to work with and maintain my code. However, since lookups are frequently mirroring the database lookup table structure and values, there is an overhead involved in maintaining the lookup class definition each time the underlying table changes.
To resolve this issue, I thought it would be a good idea to create a T4 template that generates partial lookup classes using data from the database tables.
As described in my previous post, there are numerous tasks that can be put to use with T4 text template generation tool included in the VS2010. In that post I described how to setup a new project and create a template for generating EF4 Repository and UnitOfWork classes.
Now I'll show how to create a template that access the database and generates code for lookup classes based on the data from the lookup tables.
The general idea was to make a template that can be dropped to a project where we want to generate our lookup classes and apply only the minimal configuration
changes to get a working solution.
We are working with a framework that already has support for lookups and our generated classes should follow that logic.
Since I already have a sample class with code to create, I'll use that class in this example:
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
public partial class GenderLookup : ILookup<Gender>
{
#region Lookup items
public partial class LookupItemsHandler : BaseLookupItems<Gender>
{
#region Properties
public GenderLookup Female { get { return GetLookup().Entities.SingleOrDefault(p => p.Abrv == "F"); } }
public GenderLookup Male { get { return GetLookup().Entities.SingleOrDefault(p => p.Abrv == "M"); } }
#endregion
}
#endregion
public Lookup<GenderEntity> GetLookup()
{
return Lookup<GenderEntity>.GetInstance(GenderFields.Name);
}
public LookupItemsHandler GetLookupItems()
{
return new LookupItemsHandler();
}
}
I started by creating an empty project in VS and adding 2 text templates to it. One template will hold the generation code; this will be our entry template and I'll name it Lookup.tt.
The other template will be an "include" template and will hold all of the helper methods to keep entry template code clean and easier to maintain. This template name is UtilTemplate.ttinclude.
Here is complete listing of Lookup.tt:
<#@ template language="C#" debug="true" hostspecific="true"#>
<#@ assembly Name="System.Core.dll" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Diagnostics" #>
<#@ import namespace="System.Linq" #>
<#@ include file="UtilTemplate.ttinclude"#>
<#@ output extension=".cs"#>
<#
Util util = new Util();
List<Util.LookupItem> itemsToGenerate = util.GetItemsToGenerate();
var classesToGenerate = itemsToGenerate.Select(p => p.TableName).Distinct();
#>
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
<#
foreach (var classItem in classesToGenerate)
{
#>
public partial class <#= classItem #>Lookup : ILookup<<#= classItem #>>
{
#region Lookup items
public partial class LookupItemsHandler : BaseLookupItems<<#= classItem #>>
{
#region Properties
<#
foreach (var item in itemsToGenerate.Where(p=>p.TableName==classItem).ToList())
{
#>
public <#= classItem #>Lookup <#= item.ItemName.Replace(" ", String.Empty).Replace(".", String.Empty) #> { get { return GetLookup().Entities.SingleOrDefault(p => p.Abrv == "<#= item.ItemAbrv #>"); } }
<#
}
#>
#endregion
}
#endregion
public Lookup<<#= item.ItemName.Replace(" ", String.Empty).Replace(".", String.Empty) #>Entity> GetLookup()
{
return Lookup<<#= item.ItemName.Replace(" ", String.Empty).Replace(".", String.Empty) #>Entity>.GetInstance(<#= item.ItemName.Replace(" ", String.Empty).Replace(".", String.Empty) #>Fields.Name);
}
public LookupItemsHandler GetLookupItems()
{
return new LookupItemsHandler();
}
}
<#
}
#>
Note the 'include' statement in our template header. This is how we include code from another template to our template.
To keep the code clean we will instantiate the Util class from the included template and use helper methods to get lookup items to generate. I am using LookupItem object to store generated data.
Everything else if pretty self-explanatory...
<#@ Assembly Name="System.Data" #>
<#@ Import Namespace="System.Data.SqlClient" #>
<#@ Import Namespace="System.Collections.Generic" #>
<#
#>
<#+
public class Util
{
//TODO Set connection to database with data
SqlConnection connection = new SqlConnection("server=DELLSERVER;Trusted_Connection=True;database=TestData;Connect Timeout=60;");
List<string> tablesToInclude = new List<string>();
public Util()
{
//TODO Add list of table for include in generation
tablesToInclude.Add("Gender");
tablesToInclude.Add("Location");
tablesToInclude.Add("WorkType");
}
//Storage for items to generate
public class LookupItem
{
public string TableName { get; set; }
public Guid ItemId { get; set; }
public string ItemAbrv { get; set; }
public string ItemName { get; set; }
public string ItemDescription { get; set; }
public LookupItem(string tableName, Guid itemId, string itemAbrv, string itemName, string itemDescription)
{
this.TableName = tableName;
this.ItemId = itemId;
this.ItemAbrv = itemAbrv;
this.ItemName = itemName;
this.ItemDescription = itemDescription;
}
}
public List<LookupItem> GetItemsToGenerate()
{
//Load data from tables
var result = new List<LookupItem>();
var cmd = new SqlCommand();
cmd.Connection = connection;
connection.Open();
foreach (var tableName in tablesToInclude)
{
cmd.CommandText = String.Format("SELECT * FROM {0}", tableName);
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
result.Add(new LookupItem(tableName,
rdr.GetGuid(0),
rdr.GetString(1),
rdr.GetString(2),
rdr.GetString(3)));
}
rdr.Close();
}
return result;
}
}
#>
As you can see, there is nothing too complicated here either. We have a connection string, a constructor for the Util class where we define tables we want to include in the generation process, DTO LookupItem and GetItemsToGenerate() method which opens a connection and loads data from each table.
I decided to keep all generated code in one file, but it can be separated to several files if needed.
To use the approach described above, just place these two templates in your project, set a connection string and specify tables for the lookup generation process. Right click on the Lookups.tt, click 'Run Custom Tool' and the lookup code will be generated.
Attached to this post is a project with complete template code and SQL script to create tables and sample data.