Generate your database entities using T4 templates

June 15, 2014 by Anuraj

.Net .Net 4.0 ASP.Net ASP.Net MVC EF Code First Entity Framework SQL Server WPF

This post is about generating database entities using T4 templates. This code is pretty straight forward, you are reading the app.config to get the connection string, connecting to db server using Sql Server SMO objects, enumerating tables and columns.

Here is the code, which will read the App.Config file and get the connection string. (I am using a connection string with name, Default).

string appConfig = this.Host.ResolvePath("App.config");
if(!File.Exists(appConfig))
{
	Error("App.config not exists");
}

ExeConfigurationFileMap configFile = new ExeConfigurationFileMap();
configFile.ExeConfigFilename = appConfig;
var configuration = ConfigurationManager.OpenMappedExeConfiguration(configFile, ConfigurationUserLevel.None);
var connectionString = configuration.ConnectionStrings.ConnectionStrings["Default"].ConnectionString;

Here is the code which will connects to the database, enumerate tables and columns and generates classes and properties.

SqlConnection sqlConnection = new SqlConnection(connectionString);
ServerConnection serverConnection = new ServerConnection(sqlConnection);
Server server = new Server(serverConnection);
var tables = server.Databases[server.ConnectionContext.DatabaseName].Tables;
#>
namespace <#= server.ConnectionContext.DatabaseName #>.Entities
{
<#
foreach(Table table in tables)
{
#>
public sealed partial class <#= CleanName(table.Name) #>
{
<#
foreach(Column column in table.Columns)
{
#>
	public <#= ToClrType(column.DataType, column.Nullable) #> <#= CleanName(column.Name) #> { get; set; }
<#
}
#>
}
<#
}
#>
}

And here is the two functions which will fix any issue with table name and column names. And returns C# type by using a SQL Server type.

private string FixTableName(string tableName)
{
	var result = tableName.Replace(" ","_");
	return System.Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(result);
}

private string ToClrType(DataType dataType, bool isNullable)
{
	string returnType = string.Empty;
	switch (dataType.SqlDataType)
    {
        case SqlDataType.BigInt:
            returnType = string.Format("{0}{1}","long", isNullable ? "?":"");
			break;
        case SqlDataType.Binary:
        case SqlDataType.Image:
        case SqlDataType.Timestamp:
        case SqlDataType.VarBinary:
            returnType = "byte[]";
			break;
        case SqlDataType.Bit:
			returnType = string.Format("{0}{1}","bool", isNullable ? "?":"");
			break;
        case SqlDataType.Char:
        case SqlDataType.NChar:
        case SqlDataType.NText:
        case SqlDataType.NVarChar:
        case SqlDataType.Text:
        case SqlDataType.VarChar:
        case SqlDataType.Xml:
            returnType = string.Format("{0}{1}","string", "");
			break;
        case SqlDataType.DateTime:
        case SqlDataType.SmallDateTime:
        case SqlDataType.Date:
        case SqlDataType.Time:
        case SqlDataType.DateTime2:
			returnType = string.Format("{0}{1}","System.DateTime", isNullable ? "?":"");
			break;
        case SqlDataType.Decimal:
        case SqlDataType.Money:
        case SqlDataType.SmallMoney:
			returnType = string.Format("{0}{1}","decimal", isNullable ? "?":"");
			break;
        case SqlDataType.Float:
			returnType = string.Format("{0}{1}","double", isNullable ? "?":"");
			break;
        case SqlDataType.Int:
			returnType = string.Format("{0}{1}","int", isNullable ? "?":"");
			break;
        case SqlDataType.Real:
			returnType = string.Format("{0}{1}","float", isNullable ? "?":"");
			break;
        case SqlDataType.UniqueIdentifier:
			returnType = string.Format("{0}{1}","Guid", isNullable ? "?":"");
			break;
        case SqlDataType.SmallInt:
            returnType = string.Format("{0}{1}","short", isNullable ? "?":"");
			break;
        case SqlDataType.TinyInt:
            returnType = string.Format("{0}{1}","byte", isNullable ? "?":"");
			break;
        case SqlDataType.Variant:
            returnType = string.Format("{0}{1}","object", "");
			break;
        case SqlDataType.DateTimeOffset:
            returnType = string.Format("{0}{1}","DateTimeOffset", isNullable ? "?":"");
			break;
		}

		return returnType;
}

Happy Coding :)

You can find the Gist here

« How to get text from Masked Text Box without the masked format

Returning Multiple Files from MVC Action »

Copyright © 2024 Anuraj. Blog content licensed under the Creative Commons CC BY 2.5 | Unless otherwise stated or granted, code samples licensed under the MIT license. This is a personal blog. The opinions expressed here represent my own and not those of my employer. Powered by Jekyll. Hosted with ❤ by GitHub