Data Access Layer Generator using T4 Template and Database Application Block 5

Database application block 5 gives us automatic data mapping with new extension which is called Accessors. I have discussed more about Database Application block 5 features in previous blog post  http://ciintelligence.blogspot.com/2011/01/retrieving-data-as-objects-using.html . Let see how application block 5 provides auto data mapping.

   1: public IEnumerable GetCompanies() 

   2:       { 

   3:           return _database.CreateSqlStringAccessor(GetCompaniesSQL()); 

   4:       } 

Here Company is DTO class which has same naming information as in Database. If there have different name of same column in database and DTO then mapping would fail. So as DTO should contains same name property as column name so from database table we can create DTO with code generator. Also in the above code there have no such complicacy so we can also generate code with T4 template with proper naming.

Now lets give another layer of abstraction from this data mapping and query using Repository pattern.

According to Martin Fowler :
A Repository mediates between the domain and data mapping layers, acting like an in-memory domain object collection. Client objects construct query specifications declaratively and submit them to Repository for satisfaction.
An object can be added and removed from repository as we do in C# List collection with Add(), Remove() functions.
The structure I have defined for repository is :

   1: namespace DG.Common

   2: {

   3:     public interface IRepository

   4:     {

   5:         List GetAllBySortingAndPaged(SortDefinitions sortDefinitions, int startIndex, int pageSize);

   6:         List GetAllBySorting(SortDefinitions sortDefinitions);

   7:         List GetAll();

   8:         List GetAllById(long id);

   9:         long Insert(T item);

  10:         void Update(T item);

  11:         void Delete(T item);

  12:     }

  13: }

IRepository interface defines a structure of a simple collection. There have GetAll() for getting repository item collections. We can also tell repository to get value of a perticular key with GetAllById(…) method. Also creating the object of Repository type we can add, remove and update by calling Insert(), Remove() and Update() methods. The user will only concern only on DTO class and to add this DTO in repository as collection. Data access layer from application block 5 will be abstracted from user with repository interface.
In IRepository inteface I have defined only those functions which we are usually required for CRUD operations. So this layer can also be created using T4 template.
For generating automatic C# code for DAO, DTO and Repository Object definitions I have used T4 template. Generating code using T4 template is very easy with few syntax and Visual Studio has nice support for T4 template.
T4 templates are  composed  of three parts: Directives, Text blocks and Control  blocks. This is almost same as we define ASP.NET Control block and Text block in markup page.
In MSDN http://msdn.microsoft.com/en-us/library/bb126478.aspx  you will find syntax for writing T4 templates.
For generating DTO and DAO I will be needed to access Database Schema information for getting database Tables, Columns of Tables. Primary Keys, Auto Increment columns. I do not like reinventing wheel where from SubSonic T4 Template I get more functionality like plural and singular naming conversions. I have used SubSonic  http://subsonicproject.com . I have used only those classes which are needed for me to get schema information and proper naming. So I mainly combined functionality of Settings.ttinclude and SQLServer.ttinclude as I am using SQL Server with Application block 5. As SQLServer.ttinclude is mainly directives so I have included there my own directives so that I can reuse those in generating DAO.

Generating DTO :

DTO generation code is very simple as I am getting Table schema information using Subsonic T4 template directives.

   1: <#@ include file="SQLServer.ttinclude" #>

   2: using System;

   3:  

   4: namespace <#=Namespace #>

   5: {

   6: <#

   7:  

   8:     var tables = LoadTables();

   9:  

  10:     foreach(Table tbl in tables)

  11:     {

  12:        

  13: #>

  14: public class <#=tbl.ClassName #>

  15: {

  16:   <# foreach(Column col in tbl.Columns)

  17: {

  18:    if(col.SysType !="string" && col.SysType !="byte[]" && col.IsNullable==true)

  19:    {

  20:     #>

  21:     public <#=col.SysType#>? <#=col.CleanName#> {get;set;}

  22:  

  23: <#

  24: }

  25: else

  26: {

  27: #>

  28:     public <#=col.SysType#> <#=col.CleanName#> {get;set;}

  29:  

  30: <#}

  31: }#>

  32: }

  33: <#}#>

  34: }

Here LoadTables() function is defined inside SQLServer.ttinclude file so this file is imported. <#= … #> is Text block symbol so that I can get value of a property. ClassName is singular name of table name. If you create a Table in database with name Countries” then ClassName will be Country. So SubSonic helped me to make proper naming.
There have another symbol <#… #> which define Control blocks. The control flow is defined in this block. In DTO if any column is defined as Nullable then in DTO same named property will be nullable.

Generating DAO :

Now DAO part. When I first designed my DAO that time I wrote simple scripts for selection columns, inserting columns also updating without using any variable for column Name. So if any of column name changed or if I do not need to consider a single column I had to find out that column from script string. After doing my operation I could understand after running as queries were in string. So I had to generate script again from database for safety. To overcome this problem I have used name of column with constants and also made a collections of columns names for query operations.
For country table the constants are :

   1: private const string IdentityColumn = "Id";

   2:         private const string ISOColumn = "ISO";

   3:         private const string NameColumn = "Name";

   4:         private const string PrintableNameColumn = "PrintableName";

   5:         private const string ISO3Column = "ISO3";

   6:         private const string NumCodeColumn = "NumCode";

   7:         private const string HasStateColumn = "HasState";

   8:         private static readonly string[] ColumnCollections = new[] { IdentityColumn, ISOColumn, NameColumn, PrintableNameColumn, ISO3Column, NumCodeColumn, HasStateColumn };

There you can change any column name or can delete columns that will effect at compile time. So now modifying query is easy and safe.
For making where clause for update or delete operations we have to set where clause like

   1: WHERE

   2:     Id = @Id

   3: AND 

   4:     ISO = @ISO

Here Id and ISO are primary keys. To make where clause I created an extension method which I have discussed in my previous blog.http://ciintelligence.blogspot.com/2012/03/linq-joining-list-or-collection-with.html .

   1: private static readonly string[] KeyColumns = new string[] { "Id", "ISO" };

   2: private static readonly string WhereClause = KeyColumns.JoinFormat(" AND ", "{0} = @{0}");

Here you can see T4 template can generate query with multiple key columns. Mostly tables have auto incremented primary key or Identity column. In that case I do not consider KeyColumns variable. But if there do not have any Identity columns and also have more than one key columns then KeyColumns is considered in Where clauses.

Lets see the Update query for Country table :

   1: private string UpdateCountrySQL()

   2:        {

   3:            var sql = string.Format(@" UPDATE

   4:                                 Countries

   5:                            SET

   6:                               {1}

   7:                            WHERE 

   8:                                  {0}

   9:                                       ", WhereClause, ColumnCollections.Except(KeyColumns).JoinFormat(" , ", "{0} = @{0}"));

  10:            return sql;

  11:        }

Here columns names which need to update clause is built from ColumnCollections  by JoinFormat extension method and Where clause is made with KeyColumns.
Also another thing I have considered is giving parameter for nullable columns.

   1: parameter = command.CreateParameter();

   2: parameter.ParameterName = string.Format("@{0}", @ISO3Column);

   3: parameter.Value = (object)country.ISO3 ?? DBNull.Value;

   4: command.Parameters.Add(parameter);

Here if ISO3 column value is null then will send database DBNull value.
T4 Template generation for DAO with such logic was not difficult. Also did not took very long code. JoinFormat() extension method saved many code also made it easy to write.

Generating Repository:

T4 template code for repository is also very simple. Just a little have considered when table do not have Identity Column then we have to use primary keys columns to get single item from repository. T4 template code for this logic :

   1: if(tbl.Columns.Any(c=>c.AutoIncrement==true))

   2: {

   3: #>

   4:     public <#=tbl.ClassName#> GetById(object id)

   5:     {

   6:        return _<#=tbl.ClassName.ToLower()#>Dao.Get<#=tbl.ClassName#>ById(id);

   7:     }

   8: <#}

   9: else if(IsKeyColumnsRequire(tbl.Columns.SingleOrDefault(c=>c.AutoIncrement==true),tbl.Columns.Where(c=>c.IsPK==true)))

  10: {

  11: #>

  12: public <#=tbl.ClassName#> GetByKey(<#=tbl.ClassName#> <#=tbl.ClassName.ToLower()#>)

  13:     {

  14:        return _<#=tbl.ClassName.ToLower()#>Dao.Get<#=tbl.ClassName#>ByKey(<#=tbl.ClassName.ToLower()#>);

  15:     }

  16: <# } #>

CodePlex

I have published T4 Template code generator in CodePlex http://dalgeneratort4.codeplex.com/ page. This is under New BSD License as Subsonic. You can now easily generate CRUD operations functionality and use this as repository functionality.