The T4 template engine is insanely powerful. I didn’t really realize just how powerful it was until I had a use case for it today. I stood up a database with about 40 tables in it, and planned to use an ORM to access the database. To use the ORM, I needed POCOs (Plain Old C# Objects) that represented my database. Some of these tables had 30-50 or so columns and I didn’t want to code all of this by hand – it would take literally days.
Surprisingly, I got the whole thing done in about an hour with the help of the T4 template engine.
For those who are not familiar, T4 is a text template engine created by Microsoft which combines plain text and control logic to generate text output. In reality it’s a lot like how you use Razor or WebForms view engines to generate HTML; you can embed “code nuggets” almost exactly like you do in WebForms. The only real difference is that with T4 you’re creating a text file instead of a webpage.
To create a T4 template in Visual Studio 2010 or 2012, simply add a text file to your project… I called mine PocoGenerator.txt for example. Then, rename the file’s extension from “.txt” to “.tt” – the file will then be treated as a T4 Template by Visual Studio. Your output will appear in the code-behind .cs file attached to the .tt file you just created. In my scenario I wanted each of my POCOs to have their own file, so I did a bit of trickery to make that happen.
I wrote this nifty T4 template which connects to a database, queries all tables (ignoring sys tables), and then creates one POCO per table under a file named
<#@ template language="C#" hostspecific="true" debug="True" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.Sfc" #>
<#@ import namespace="System" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#
//**********************************************************************************************
// This T4 generates POCOs from the specified DB and saves them to the specified folder which
// is relative to the template's location. One file per table/POCO.
//**********************************************************************************************
//****************************
// DEFINE YOUR VARIABLES HERE
//****************************
// The SQL server name or IP
string sqlServer = "9.9.9.9";
// The SQL username
string sqlLogin = "admin";
// The SQL password
string sqlPassword = "password";
// The SQL database to generate the POCOs for
string sqlDatabase = "MyDatabase";
// The namespace to apply to the generated classes
string classNamespace = "Your.Namespace.Here";
// The destination folder for the generated classes, relative to this file's location.
string destinationFolder = "PocoFolder";
// Loop over each table and create a class file!
Server server = new Server(sqlServer);
server.ConnectionContext.LoginSecure = false;
server.ConnectionContext.Login = sqlLogin;
server.ConnectionContext.Password = sqlPassword;
server.ConnectionContext.Connect();
foreach (Table table in server.Databases[sqlDatabase].Tables)
{
// Skip sys tables
if (table.Name.StartsWith("sys"))
{
continue;
}
#>
using System;
namespace <#= classNamespace #>
{
/// <summary>
/// Represents a <#= table.Name #>.
/// NOTE: This class is generated from a T4 template - you should not modify it manually.
/// </summary>
public class <#= table.Name #>
{
<#
// Keep count so we don't whitespace the last property/column
int columnCount = table.Columns.Count;
int i = 0;
// Iterate all columns
foreach (Column col in table.Columns)
{
i++;
string propertyType = GetNetDataType(col.DataType.Name);
// If we can't map it, skip it
if (string.IsNullOrWhiteSpace(propertyType))
{
// Skip
continue;
}
// Handle nullable columns by making the type nullable
if (col.Nullable && propertyType != "string")
{
propertyType += "?";
}
#>
public <#= propertyType #> <#= col.Name #> { get; set; }
<#
// Do we insert the space?
if (i != columnCount)
{
#>
<#
}
#>
<#
}
#>
}
}
<#
// Write new POCO class to its own file
SaveOutput(table.Name + ".cs", destinationFolder);
}
#>
<#+
public static string GetNetDataType(string sqlDataTypeName)
{
switch (sqlDataTypeName.ToLower())
{
case "bigint":
return "Int64";
case "binary":
case "image":
case "varbinary":
return "byte[]";
case "bit":
return "bool";
case "char":
return "char";
case "datetime":
case "smalldatetime":
return "DateTime";
case "decimal":
case "money":
case "numeric":
return "decimal";
case "float":
return "double";
case "int":
return "int";
case "nchar":
case "nvarchar":
case "text":
case "varchar":
case "xml":
return "string";
case "real":
return "single";
case "smallint":
return "Int16";
case "tinyint":
return "byte";
case "uniqueidentifier":
return "Guid";
default:
return null;
}
}
void SaveOutput(string outputFileName, string destinationFolder)
{
// Write to destination folder
string templateDirectory = Path.Combine(Path.GetDirectoryName(Host.TemplateFile), destinationFolder);
string outputFilePath = Path.Combine(templateDirectory, outputFileName);
File.Delete(outputFilePath);
File.WriteAllText(outputFilePath, this.GenerationEnvironment.ToString());
// Flush generation
this.GenerationEnvironment.Remove(0, this.GenerationEnvironment.Length);
}
#>
Note that when the files are generated, they will not automatically be included in the project. You will have to add them manually as existing items.
This T4 Template supports regeneration, so anytime you update your database schema just re-run the template to create updated .cs files! Enjoy!