Database T4 Templating Tricks

I love T4 templates. It’s incredible how much canned code can be generated automatically for you. T4 can not only generate static code, but it can do much more like access the database through SMO. All you need to do is define the server and database like so:

<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.Sfc" #>

<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
lt;#@ import namespace="Microsoft.SqlServer.Management.Common" #>


<#
 var server = new Server("localhost");
 var db = server.Databases["MYDB"];
#%gt;

In this database, suppose you have a Types table that’s static. Your types table may be a ID/Name pairing in the database, which you reference either using magic numbers (the ID since it’s static) or do a Name cross reference. With T4, instead of using numbers or strings, you could hard-code the values by querying the table and creating constants for each type:

public class Constants
{

      public partial class SettingTypes
      {
<#
         var catDS = db.ExecuteWithResults("select ID, Name from dbo.SettingTypes where Visible = 1");
         foreach (DataRow row in catDS.Tables[0].Rows)
         {
#>
         public const long <#= row["Name"].ToString() #> = <#= row["ID"].ToString() #>;
<#
         }
#>
      }
}

Above using the global DB server reference, we use the ExecuteWithResults method to run a specified query against that database, and return the results as a DataSet. The system processes the results and generates “public const long XYZ = 1” where XYZ is the name in the table, and 1 is the ID. We can generate whatever we want; for instance, you could generate just the names as strings by changing the constant to:

public const string <#= row["Name"].ToString() #> = "<#= row["Name"].ToString() #>;

This produces a result similar to the following (using our first example):

public class Constants
{
   public partial class SettingTypes
   {
       public const long AppName = 1;
       public const long URLRedirect = 2;
       .
       .
   }
}

Or as (using our second example):

public class Constants
{
   public partial class SettingTypes
   {
       public const long AppName = "AppName";
       public const long URLRedirect = "URLRedirect";
       .
       .
   }
}

Through our application, we may have had references to “AppName” or “URLRedirect” or whatever common values this type table would have (this is unfortunately a very generic example and I hope you see how it’s applicable). With our new approach, we can do:

Utilities.LookupSettingType(Constants.SettingTypes.AppName);

We now have a compiled-time reference. This helps to provide extra checking for situations where we may remove entries from the SettingsTypes table. Any code references to it would now produce a compile error.

It’s also possible to connect to a database table’s metadata too, to generate the appropriate metadata for that table. The SMO API has table and column objects, used like below that can generate the metadata to a generated class.

public partial class ApplicationDetails
{
<#
         var ncTable = db.Tables["ApplicationDetails"];

         foreach (Column column in ncTable.Columns)
         {
#>
            public const string <#= column.Name #> = "<#= column.Name #>";
<#
         }
#>
}

This would generate a class like the following:

public partial class ApplicationDetails
{
   public string ID = "ID";
   public string Name = "Name";
   public string Description = "Description";
}

Now we can change any static reference to compile time references by referring to the description column as Constants.ApplicationDetails.Description, rather than just “Description”. It’s also possible to pull other metadata in too, such as the data type and more. If you can’t get everything you need, you can try to query the INFORMATION_SCHEMA views to get any additional information.

One useful utility I created with T4 was a generic ReferenceTypes class, where all of my _Types tables are defined in a static class. This processes all of the tables in the database, generating a static reference to the name of a reference table.

public partial class ReferenceTypes
{

<# foreach (Table table in db.Tables) {
            if (table.Name.EndsWith("Types")) {
#>
         public const string <#= table.Name #> = "<#= table.Name #>";
<#
            }
         }
#>

}

Sounds good? I’ve found a lot of good uses for T4 templates; I even use it to generate a starter shell to create stored procedures, application repositories, started controllers and views for MVC, and more. I’ll touch base on that in another blog post but I hope I helped you see how useful T4 is.

There are some things you shouldn’t use it for. You shouldn’t use it for anything you expect to change dynamically. Because it’s generating classes that get compiled with the projects, you can’t change it from live code and expect the changes to be available. Any changes included are defined as of the last compilation of the application. Another thing that you may find out is you will attempt to implement some common task in T4, but T4 turns out to be not a good solution. I’ve run into this a couple of times.

All in all, T4 templates are great to generate common code and can really cut down on writing redundant code, while adding compile-type checking on reference data.