Tuesday, February 26, 2013

Generate Class from table using T4 template

here is a t4 file. save it using .tt extension and change the connection accordingly and drag to visual studio solution.







<#@ template language="C#" debug="True" #>

<#@ assembly name="System" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Xml" #>

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

<#@ import namespace="System" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="Microsoft.SqlServer.Management.Common" #>

namespace Namespace
{

<#
var databaseName = "testDb";
var serverConnection = new SqlConnection(
@"Data Source=.\SQLEXPRESS; Integrated Security=true; Initial Catalog=" + databaseName);
var svrConnection = new ServerConnection(serverConnection);

Server srv = new Server(svrConnection);
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;
}



}
#>

No comments:

Post a Comment