DBtoPOCO.tt

<#@ 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 = ".";
    // The SQL username
    string sqlLogin = "sa";
    // The SQL password
    string sqlPassword = "somepass";
    // The SQL database to generate the POCOs for
    string sqlDatabase = "MyDB";
    // The namespace to apply to the generated classes
    string classNamespace = "MyVSNameSpace";
    // The destination folder for the generated classes, relative to this file's location.
    string destinationFolder = "";
 
    // 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.Replace("tbl", "") #> 
    {
<# 
        // 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.Replace("tbl", "") + ".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);
    }
#>