Just Code‎ > ‎

T4 - Generate POCO from all tables in a database

posted Apr 21, 2010, 6:55 AM by Peter Henell   [ updated Apr 26, 2010, 12:33 PM ]
If you want to generate simple POCO using the definition in the Sql Server you can do so by simply adding a text-file to your project and naming it GenerateAllPocosFromDatabase.tt and pasting the following code into it.

The classes will be saved to a single file, wich can be avoided with some more code. This will do for now. This currently require the database to be on your local machine.

Change the DataBaseName to the name of your database.

Warning! Using this on a database with many tables can cause Visual Studio to hang for a while.

Read more about T4 http://www.olegsych.com/tag/t4/
T4 Toolbox http://t4toolbox.codeplex.com/
T4 Editor for VS2008/2010 http://www.olegsych.com/2009/04/t4-editor-by-tangible-engineering/
<#@ template language="C#" debug="True" #>

<#@ assembly name="System.Data" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>


<#@ import namespace="System" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>


using System;

namespace Entities 
{ <# Server srv = new Server(); foreach (Table table in srv.Databases["DataBaseName"].Tables) { #> class <#= table.Name #>
{ <#
foreach (Column col in table.Columns) { #> public <#= GetNetDataType(col.DataType.Name) #> <#= col.Name #> { get; set; } <#
} #> } <# }
#> } <#+ public static string GetNetDataType(string sqlDataTypeName) { switch (sqlDataTypeName.ToLower()) { case "bigint": return "Int64"; case "binary": return "Byte[]"; case "bit": return "bool"; case "char": return "char"; case "cursor": return string.Empty; case "datetime": return "DateTime"; case "decimal": return "Decimal"; case "float": return "Double"; case "int": return "int"; case "money": return "Decimal"; case "nchar": return "string"; case "numeric": return "Decimal"; case "nvarchar": return "string"; case "real": return "single"; case "smallint": return "Int16"; case "text": return "string"; case "tinyint": return "Byte"; case "varbinary": return "Byte[]"; case "xml": return "string"; case "varchar": return "string"; case "smalldatetime": return "DateTime"; case "image": return "byte[]"; default: return string.Empty; } } #>


Example output:
class Department 
{ public int ID { get; set; } public int CompanyID { get; set; } public Decimal Code { get; set; } public string Name { get; set; } public string Description { get; set; } public bool IsDefault { get; set; } } class DepartmentTemplate
{ public int ID { get; set; } public Decimal Code { get; set; } public string Name { get; set; } public string Description { get; set; } public bool IsDefault { get; set; } } class District
{ public int RefID { get; set; } public int CityRef { get; set; } public string DistrictCode { get; set; } public string DistrictName { get; set; } public string DistrictDescription { get; set; } }
Comments