Syed BASHAR

Friday, March 9, 2012

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.

public IEnumerable GetCompanies() 
      { 
          return _database.CreateSqlStringAccessor(GetCompaniesSQL()); 
      } 

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 :

namespace DG.Common
{
    public interface IRepository
    {
        List GetAllBySortingAndPaged(SortDefinitions sortDefinitions, int startIndex, int pageSize);
        List GetAllBySorting(SortDefinitions sortDefinitions);
        List GetAll();
        List GetAllById(long id);
        long Insert(T item);
        void Update(T item);
        void Delete(T item);
    }
}

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.

<#@ include file="SQLServer.ttinclude" #>
using System;
  
namespace <#=Namespace #>
{
<#
  
    var tables = LoadTables();
  
    foreach(Table tbl in tables)
    {
       
#>
public class <#=tbl.ClassName #>
{
  <# foreach(Column col in tbl.Columns)
{
   if(col.SysType !="string" && col.SysType !="byte[]" && col.IsNullable==true)
   {
    #>
    public <#=col.SysType#>? <#=col.CleanName#> {get;set;}
  
<#
}
else
{
#>
    public <#=col.SysType#> <#=col.CleanName#> {get;set;}
  
<#}
}#>
}
<#}#>
}

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 :

private const string IdentityColumn = "Id";
        private const string ISOColumn = "ISO";
        private const string NameColumn = "Name";
        private const string PrintableNameColumn = "PrintableName";
        private const string ISO3Column = "ISO3";
        private const string NumCodeColumn = "NumCode";
        private const string HasStateColumn = "HasState";
        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

WHERE
    Id = @Id
AND 
    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 :

private string UpdateCountrySQL()
       {
           var sql = string.Format(@" UPDATE
                                Countries
                           SET
                              {1}
                           WHERE 
                                 {0}
                                      ", WhereClause, ColumnCollections.Except(KeyColumns).JoinFormat(" , ", "{0} = @{0}"));
           return sql;
       }

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.

parameter = command.CreateParameter();
parameter.ParameterName = string.Format("@{0}", @ISO3Column);
parameter.Value = (object)country.ISO3 ?? DBNull.Value;
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 :

if(tbl.Columns.Any(c=>c.AutoIncrement==true))
{
#>
    public <#=tbl.ClassName#> GetById(object id)
    {
       return _<#=tbl.ClassName.ToLower()#>Dao.Get<#=tbl.ClassName#>ById(id);
    }
<#}
else if(IsKeyColumnsRequire(tbl.Columns.SingleOrDefault(c=>c.AutoIncrement==true),tbl.Columns.Where(c=>c.IsPK==true)))
{
#>
public <#=tbl.ClassName#> GetByKey(<#=tbl.ClassName#> <#=tbl.ClassName.ToLower()#>)
    {
       return _<#=tbl.ClassName.ToLower()#>Dao.Get<#=tbl.ClassName#>ByKey(<#=tbl.ClassName.ToLower()#>);
    }
<# } #>

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.

Sunday, March 4, 2012

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

Sunday, February 26, 2012

OpenXML: Converting Excel Theme Color and Tint value to RGB value

Problem:

I was needed to import Excel data with rich text format and display in WPF grid. From RunProperties of OpenXml api I used to get all type of properties like Bold, Italic , Underline, Color. But there have two types of colors : Theme Color and another RGB color. But Theme color is specific to Office and depends on theme. There have 10 sets of color for Excel 2007.

First Row contains 10 colors which is defined in Theme. In theme1.xml you will find these sets of colors like :

These 10 colors are used in Theme color palate but in different order. If we create a dictionary this will look like below snapshot where above 10 colors are ordered property according to Theme color.

When you will select first color then OpenXml Color property will give you Color.Theme = 0, which is integer value from 0 to 9. So from this dictionary you can map Color.Theme integer value to RGB value. So this is easy to get RGB value from first top 10 colors from theme colors from the above dictionary. But next 50 theme colors are not defined in theme1.xml. These colors are generated from top 10 theme colors in this dictionary by changing lightness. For example second row first color is 5% darker than Theme Color with 0 means “FFFFFF” . And these percentage value of darkness or lightness is defined in Tint value of OpenXml Color object. The value of Tint between –1 to +1 depends on lightness or darkness. Lets the color you have chosen is from column 6 and second row which is 80% lighter than top color of Column 6. So the Color.Theme value is 5. and Tint value will be 0.79998168889431442 a double value and for darkness this will be –0.0.79998168889431442 . So the challenge now is to convert Theme color and Tint color to RGB value.

Solution :

Tint value is a calculation of brightness or luminance of original color. So the final Luminance or brightness of color depend on calculation of 80% lightness/darkness. For that we need to convert of original RGB color value to HLS ( Hug, Luminance, Saturation) mode. Let say for RGB color for Theme color =5 from the above dictionary is #C0504D and we have to convert this color into HSL value and then from Tint value 0.79998168889431442 ( 80% lightness of that theme color) We have to calculate final HLS value of that color. After converting final HLS value to RGB we will get RGB value theme color 5 with 80% lightness.
Here we will need converter from RGB to HLS. I got help from codeproject article http://www.codeproject.com/Articles/49650/WPF-Color-Conversions to get method of converting RGB to HLS. I am also mentioning code here which actually you will need.

RGB to HLS Converter :

 private struct HlsColor
    {

        public double A;

        public double H;

        public double L;

        public double S;

    }
    private HlsColor RgbToHls(Color rgbColor)
    {

        var hlsColor = new HlsColor();

        double r = (double)rgbColor.R / 255;

        double g = (double)rgbColor.G / 255;

        double b = (double)rgbColor.B / 255;

        double a = (double)rgbColor.A / 255;

        double min = Math.Min(r, Math.Min(g, b));

        double max = Math.Max(r, Math.Max(g, b));

        double delta = max - min;

        if (max == min)
        {

            hlsColor.H = 0;

            hlsColor.S = 0;

            hlsColor.L = max;

            return hlsColor;

        }

        hlsColor.L = (min + max) / 2;

        if (hlsColor.L < 0.5)
        {

            hlsColor.S = delta / (max + min);

        }

        else
        {

            hlsColor.S = delta / (2.0 - max - min);

        }

        if (r == max) hlsColor.H = (g - b) / delta;

        if (g == max) hlsColor.H = 2.0 + (b - r) / delta;

        if (b == max) hlsColor.H = 4.0 + (r - g) / delta;

        hlsColor.H *= 60;

        if (hlsColor.H < 0) hlsColor.H += 360;

        hlsColor.A = a;

        return hlsColor;

    }

HLS to RGB Converter :

    private Color HlsToRgb(HlsColor hlsColor)
    {

        var rgbColor = new Color();

        if (hlsColor.S == 0)
        {

            rgbColor = Color.FromArgb((int)(hlsColor.A * 255), (int)(hlsColor.L * 255), (int)(hlsColor.L * 255),

            (int)(hlsColor.L * 255));

            return rgbColor;

        }

        double t1;

        if (hlsColor.L < 0.5)
        {

            t1 = hlsColor.L * (1.0 + hlsColor.S);

        }

        else
        {

            t1 = hlsColor.L + hlsColor.S - (hlsColor.L * hlsColor.S);

        }

        double t2 = 2.0 * hlsColor.L - t1;

        double h = hlsColor.H / 360;

        double tR = h + (1.0 / 3.0);

        double r = SetColor(t1, t2, tR);

        double tG = h;

        double g = SetColor(t1, t2, tG);

        double tB = h - (1.0 / 3.0);

        double b = SetColor(t1, t2, tB);

        rgbColor = Color.FromArgb((int)(hlsColor.A * 255), (int)(r * 255), (int)(g * 255), (int)(b * 255));

        return rgbColor;

    }

    private double SetColor(double t1, double t2, double t3)
    {

        if (t3 < 0) t3 += 1.0;

        if (t3 > 1) t3 -= 1.0;

        double color;

        if (6.0 * t3 < 1)
        {

            color = t2 + (t1 - t2) * 6.0 * t3;

        }

        else if (2.0 * t3 < 1)
        {

            color = t1;

        }

        else if (3.0 * t3 < 2)
        {

            color = t2 + (t1 - t2) * ((2.0 / 3.0) - t3) * 6.0;

        }

        else
        {

            color = t2;

        }

        // Set return value 

        return color;

    }

Calculate Final Luminance ( L ) Value from Tint value :

MSDN have mentioned the algorithm to calculate final Luminance from Tint value in this article. http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.tabcolor.aspx .
I have used that algorithm to calculate Final Luminance (L) value with following function :

    private double CalculateFinalLumValue(DoubleValue tint, float lum)
    {

        if (tint == null)
        {

            return lum;

        }

        double lum1 = 0;

        if (tint.Value < 0)
        {

            lum1 = lum * (1.0 + tint.Value);

        }

        else
        {

            lum1 = lum * (1.0 - tint.Value) + (255 - 255 * (1.0 - tint.Value));

        }

        return lum1;

    }

For top 10 colors Tint value will be null so do not need to calculate luminance or you can RGB value directly if you get Tint value is null. Here HLSMAX is considered 255.

Calculating Final RGB Value from Theme Volor and Tint value :

Now lets call these function to calculate final RGB value from theme color and Tint value .

        var colorProperty = (runProperty as DocumentFormat.OpenXml.Spreadsheet.Color);

        var themeValue = colorProperty.Theme;

        if (themeValue != null)
        {

            string colourValue = _colorPallate[Convert.ToInt32(themeValue.Value)];

            Color fromHtml = ColorTranslator.FromHtml("#" + colourValue);

            HlsColor rgbToHls = RgbToHls(fromHtml);

            double calculateFinalLumValue = CalculateFinalLumValue(colorProperty.Tint, (float)rgbToHls.L * 255) / 255;

            rgbToHls.L = calculateFinalLumValue;

            Color hlsToRgb = HlsToRgb(rgbToHls);

        }

Here as converting RGB value to HLS value we get L value between 0 to 1 so for converting it into range 0 to 255 it is multiplied by 255 for calculating Tint Value. Final L value is set to original HLS value and converted again to RGB value.
This is the full algorithm for converting Excel or Office theme color and Tint value to RGB color value.

My colleague Md. Masudur Rahman also worked with me to solve this problem.

About Me