Automatically Generate POCOs From DB With T4

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 .cs which is placed in a directory relative to the template’s location. Give this a go – you won’t be disappointed! And of course, if you need it to do more than I do, just modify it and make it your own!

<#@ 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!


See also