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.
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 :
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.
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 :
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
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 .
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 :
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.
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.