T4 templates for data model generating

Modified on 2010/08/30 19:55 by IT — Categorized as: Uncategorized


Supported databases

Database Template
MS SQL MSSQL.ttinclude
MySql MySql.ttinclude
PostgreSQLPostgreSQL.ttinclude
Sybase Sybase.ttinclude

Setup

To generate a data model from your database follow the steps below:


<#@ template language="C#v3.5"         #>
<#@ output extension=".generated.cs"   #>
<#@ include file="BLToolkit.ttinclude" #>
<#@ include file="Sybase.ttinclude"    #>
<#
    ConnectionString     = "Data Source=DBHost;Port=5000;Database=BLToolkitData;Uid=sa";
    DataProviderAssembly = @"......\Sybase.AdoNet2.AseClient.dll";

    Namespace       = "Templates";
    DataContextName = "DataModel";

    GenerateModel();
#>

The ConnectionString variable defines connection to your database. DataProviderAssembly specifies the path to data provider assembly. If the assembly is installed in GAC, try the following directive instead:

<#@ assembly name="Sybase.AdoNet2.AseClient" #>

It is not required for MS SQL data provider, so the template for MS SQL can be as demonstrated below:

<#@ template language="C#v3.5"         #>
<#@ output extension=".generated.cs"   #>
<#@ include file="BLToolkit.ttinclude" #>
<#@ include file="MSSQL.ttinclude"     #>
<#
    ConnectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI";

    Namespace        = "Templates";
    DataContextName  = "DataModel";

    GenerateModel();
#>

The Namespace and DataContextName variables specify namespace and DataContext class name. Unfortunately, T4 does not have a simple way to get Visual Studio settings. However as an alternative you can install T4 Toolbox and change the template in the following way:

<#@ template language="C#v3.5" hostspecific="True" #>
<#@ output extension=".generated.cs"     #>
<#@ include file="BLToolkit.ttinclude"   #>
<#@ include file="BLT4Toolkit.ttinclude" #>
<#@ include file="MSSQL.ttinclude"       #>
<#
    ConnectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI";

    GenerateModel();
#>

Now, these parameters will be taken from your project. Note, T4 Toolbox must be installed on your computer and this way works under Visual Studio only.

VB Support

The following example demonstrates how to generate VB code:

<#@ template language="C#v3.5"         #>
<#@ output extension=".generated.vb"   #>
<#@ include file="BLToolkit.ttinclude" #>
<#@ include file="MSSQL.ttinclude"     #>
<#@ include file="VB.ttinclude"        #>
<#
    ConnectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI";

    Namespace        = "Templates";
    DataContextName  = "DataModel";

    GenerateModel();
#>

Customization

The following variables can be used to customize generating code:

string DatabaseName             = null;
string DataContextName          = "DataContext";
string Namespace                = "DataModel";
string BaseDataContextClass     = "DbManager";
string BaseEntityClass          = null;
string OneToManyAssociationType = "IEnumerable<{0}>";
bool   RenderField              = false;

The generating process consists of two steps: metadata loading and model rendering. You can modify metadata after it's loaded. The following code provides a few examples:

<#@ template language="C#v3.5"         #>
<#@ output extension=".generated.cs"   #>
<#@ include file="BLToolkit.ttinclude" #>
<#@ include file="MSSQL.ttinclude"     #>
<#
    ConnectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI";

    Namespace        = "Templates";
    DataContextName  = "DataModel";

    // This method has to be called before any metadata change.
    //
    LoadMetadata();

    // Change the BinaryDataID field name of the BinaryData table to "ID".
    //
    Tables["BinaryData"].Columns["BinaryDataID"].MemberName = "ID";

    // Change the FK_Employees_Employees association name of the Employees table to "ReportsToEmployee".
    //
    Tables["Employees"].ForeignKeys["FK_Employees_Employees"].MemberName = "ReportsToEmployee";

    // Change a field name to "ID" if the field is a primary key and its name is ClassNameID.
    // 
    foreach (var t in Tables.Values)
        foreach (var c in t.Columns.Values)
            if (c.IsPrimaryKey && t.TableName + "ID" == c.ColumnName)
                c.MemberName = "ID";

    // Add inheritance to entity classes from EntityBase<T>.
    //
    Usings.Add("BLToolkit.Common");

    foreach (var t in Tables.Values)
        t.BaseClassName = "EntityBase<" + t.ClassName + ">";

    GenerateModel();
#>

The template has some basic logic to resolve nonstandard field names, association types and names. If this logic fails, you can make any changes yourself.

The metadata structure looks as the following:

Dictionary<string,Table> Tables = new Dictionary<string,Table>();

partial class Table
{
    public string       Owner;
    public string       TableName;
    public string       ClassName;
    public string       BaseClassName;
    public string       DataContextPropertyName;
    public bool         IsView;
    public List<string> Attributes;

    public Dictionary<string,Column>     Columns;
    public Dictionary<string,ForeignKey> ForeignKeys;
}

partial class Column
{
    public int          ID;
    public string       ColumnName;
    public string       MemberName;
    public bool         IsNullable;
    public bool         IsIdentity;
    public string       Type;
    public bool         IsClass;
    public DbType       DbType;
    public SqlDbType    SqlDbType;
    public int          PKIndex = -1;
    public List<string> Attributes = new List<string>();
    public bool         IsPrimaryKey;
}

enum AssociationType
{
    Auto,
    OneToOne,
    OneToMany,
    ManyToOne,
}

partial class ForeignKey
{
    public string          KeyName;
    public string          MemberName;
    public Table           OtherTable;
    public List<Column>    ThisColumns;
    public List<Column>    OtherColumns;
    public bool            CanBeNull;
    public ForeignKey      BackReference;
    public AssociationType AssociationType;
}



WCF Support

The following include allows generating WCF attributes.

<#@ template language="C#v3.5"         #>
<#@ output extension=".generated.cs"   #>
<#@ include file="BLToolkit.ttinclude" #>
<#@ include file="WCFAttributes.ttinclude" #>
<#@ include file="MSSQL.ttinclude"     #>
<#
    ConnectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI";
    GenerateModel();
#>

Pluralize / Singularize Table Names

<#@ template language="C#v3.5"         #>
<#@ output extension=".generated.cs"   #>
<#@ include file="BLToolkit.ttinclude" #>
<#@ include file="MSSQL.ttinclude"     #>
<#@ include file="PluralSingular.ttinclude" #>
<#
    ConnectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI";

    //PluralizeClassNames = true;
    SingularizeClassNames = true;

    PluralizeDataContextPropertyNames = true;
    //SingularizeDataContextPropertyNames = true;

    GenerateModel();
#>