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.

LINQ: Joining a list or collection with format. An extension of string.Join() with format

string.Join() works nice for creating a string from a list/collection with separator. We can use any string as separator and also as parameter we have to pass array of values. For example we have a collection of columns names of a table and for creating SQL select statement I need to create a statement with comma separation of those columns names. Code here

var collection = new string[] {"Id", "CountryName", "ISOName", "PrintableName"};
var joinOfCollection = string.Join(",", collection);

This will return you a string value with comma separation of those collection. 

Id,CountryName,ISOName,PrintableName

But the where clause statement will be

WHERE
     Id =@Id
AND
    ISO=@ISO

here separator is AND but in string.Join I can not give any format like “{0}=@{0}”. Also with LINQ aggregate it is difficult for giving any separator. For that I have to use substring() operation after that or have to give checking inside every iteration. If I could use string.Format() then I could append format while joining string collection. The code for solving this problem will be

public static string JoinFormat<T>(this T[] values, string separator, string format)
        {
            if (values == null)
                throw new ArgumentNullException("values");
            if (values.Length == 0 || values[0] == null)
                return string.Empty;
            if (separator == null)
                separator = string.Empty;
            var stringBuilder = new StringBuilder();
            string str1 = values[0].ToString();
            if (str1 != null)
                stringBuilder.AppendFormat(format, str1);
            for (int index = 1; index < values.Length; ++index)
            {
                stringBuilder.Append(separator);
                if (values[index] != null)
                {
                    string str2 = values[index].ToString();
                    if (str2 != null)
                        stringBuilder.AppendFormat(format, str2);
                }
            }
            return stringBuilder.ToString();
        }

This will work fine for array of data and formatting will work like this :

 var columnsList = new int[] { 1, 2, 3 };
 var joinFormat = columnsList.JoinFormat(",", "{0}=@{0}");
           

But for custom class collection like I have collection of country class and I wanted to pass the data as XML attribute.

public class Country
        {
            public string Name { get; set; }
            public string ISO { get; set; }
        }

And we have collection of data like 

var countryList = new List<Country>
                                  {
                                      new Country {Name = "United State", ISO = "US"},
                                      new Country {Name = "United Kingdom", ISO = "GB"},
                                      new Country {Name = "Bangladesh", ISO = "BD"}
                                  };

Here above extension function will not work as this does not support collection of custom class. For that I have used Action delegate so that user can define own format. Then this will support  both primitive types collections and custom type collections.

 public static string JoinFormat<T>(this IEnumerable<T> values, string separator, Func<T, string> action) 
        {
            if (values == null)
                throw new ArgumentNullException("values");
            var enumerator = values.GetEnumerator();
            var stringBuilder = new StringBuilder();

            if (enumerator.MoveNext() == false || enumerator.Current == null)
            {
                return string.Empty;

            }
            stringBuilder.Append(action(enumerator.Current));
            while (enumerator.MoveNext())
            {
                stringBuilder.Append(separator);
                if (enumerator.Current != null)
                {
                    stringBuilder.Append(action(enumerator.Current));
                }
            }

            return stringBuilder.ToString(); 
        }

This is solution for where class also making XML type elements. Lets call this method for both cases :

Where class :

  var columnList = new List<int> { 1, 2, 3 };
  var joinFormat = columnList.JoinFormat(",", c => string.Format("{0}=@{0}", c));

Also for XML format for Country class collection will be

 var countryList = new List<Country>
                                  {
                                      new Country {Name = "United State", ISO = "US"},
                                      new Country {Name = "United Kingdom", ISO = "GB"},
                                      new Country {Name = "Bangladesh", ISO = "BD"}
                                  };

 var joinFormat = countryList.JoinFormat("", country =>
                                                       string.Format(@"<Country Name=""{0}"" ISO=""{1}""></Country>",
                                                                       country.Name, country.ISO)
                                                        );

I am using this solution for generating SQL clauses like Select clause, Order by clause, Where clause, Update clause. I will upload solution with this code and unit tests.

Source Code link http://dl.dropbox.com/u/20275838/JoinWithFormat.zip