Saturday, December 29, 2012

How to For Beginner: Entity Framework Database First Updating Complex type of Model after changing Stored Procedure Columns Selection


I see that some people do not know how to update model and complex type if they have to change stored procedure. This article goal is to help them.

Suppose you have stored procedure like this example
image

If you go to model browser right click on model as
image

You will see the structure of Model as bellow snapshot which is created from database using Database First approach:
image
You can see there have three configuration for store procedure GetCountries.
In Stored Procedure/ Functions section stored procedure schema is defined. If you see the Xml of Model1.edmx then you will see the structure as follows
image
But there have no mapping with complex type GetCountries_Result.
The mapping is defined in Function Imports Section. Lets see it’s Xml structure
image
You can see  here function name in first Xml section is mapped with Complex Type. There also has properties of ComplexType mapped with Column names of StoredProcedure.

And Complex Type GetCountries_Result is also defined in Complex Type section.
image

You now need anoter column “Name” have to be returned. So you have changed your stored procedure as
image
After that if you try to Update model from Database… then your complex type will not add another property with Name as because your stored procedure definition is not changed. What is changed is Function Import mapping. One column is added which is not mapped with any column of complex type.
So you have to edit your Function Import
image

Then dialog box will be opened to edit your mapping configuration. When you click on Get Column Information then you can see new column “Name” is there.
image
After that click Update button to Update complex type also and click OK button to commit your changes.
Now you can see your complex Type GetCountries_Result has new property Name which is actually mapped with Name column in Function Imports mapping

image
This is how database first model structured is defined and its easy to change column configuration after changing stored procedure.

Friday, December 28, 2012

Tips: Sorting/Ordering a Collection with Nested/Child Collection value

 

Problem:

Suppose you have a collection and inside that collection you have a child collection. Here is a example of such relation

   1: public class Vahicle
   2:     {
   3:         public int VahicleId { get; set; }
   4:         public string ValicleName { get; set; }
   5:         public List<Reminder> Reminders { get; set; }
   6:     }
   7:  
   8:     public class Reminder
   9:     {
  10:         public int ReminderId { get; set; }
  11:         public string ReminderType { get; set; }
  12:     }

Here you can see Reminder is a nested collection inside Vahicle. You can have  a collection of Vahicles and you may want to display Vahicles shorting by ReminderType.


 


Solution:


To solve this problem I have converted the collection to flat list where there have a property ReminderType so that when I do order by using ReminderType. The total collection will be sorted. SelectMany convert nested collection to flat collection. So I used SelectMany(). to make a flat collection with ReminderType.



   1: var orderedVahicles = vahicles.SelectMany(v => v.Reminders
   2:                                                           .Select(reminder => new { Vahicle = v, reminder.ReminderType })
   3:                                                      ).OrderBy(v => v.ReminderType)
   4:                                                      .Select(v => v.Vahicle).Distinct();

Here is the code Line 1-2 I converted Vahicle nested Collection into a collection where Vahicle collection is a property and also ReminderType is a property using SelectMany(). After Converting the collection I have done orderby with ReminderType and vahicle is also sorted Line 3. As I only need sorted Vahicle collection so I selected only Vahicle property from anonymous object Line 4.


Problem could be more complex  where you may need to select object based on certain criteria and also collection can be nested 3-4 levels or more. You can do another SelectMany if there have another nested collection.

Thursday, August 16, 2012

Automate Build from Mercurial Branch using TeamCity

I am using TeamCity for long time but new in mercurial. We were using SVN before but as Mercurial giving us many features like local commit, distributed source control so we moved to Mercurial. I faced few problems while migrating to Mercurial using HTTPS and found a good solution here http://www.zachburlingame.com/2011/11/using-mercurial-over-https-with-teamcity/. SVN has concept of branches and tags to which we are very familiar and it works almost different repository. But for Mercurial branch concept is different. Here each changeset is a branch. However Mercurial also allows us to create a branch from a particular revision within same repository which is called namedbranch (http://mercurial.selenic.com/wiki/NamedBranches).
I have created a WPF test project to show you how we can automate a build from mercurial using TeamCity. The project is very simple which display only current version from AssemblyInfo. We will change the assembly info version number after each release. In initial commit the version is 1.0.0.0. and application is displaying like that
image
Workbench is also very simple. Have only one straight line and also pushed to repository.
image
Now assume that we are giving a release of version 1.0.0.0 so we changed to revision of assembly to 1.1.0.0 and pushed to server. Lets see the Workbench now.
image
A straight line where you can see the in revision 1 I have added Nant file and configure TeamCity automation from default branch. and in revision 2 we committed our release code with comment. And in revision 3 we started working on version 1.1.0.0.
Now I want to make a namedbranch in revision 2 so that I can configure a build in TeamCity. To do that I will update my code to revision 2.  You can use Terminal of hg from Workbenchimage
Here you can see the command where I have updated to revision 2.
image
Now I will create a namedbranch in revision 2 and will also commit this.
image
You can see here revision 4 is created and here Branch name is “TestRelease1.0”
The branches command will show you 2 branches.
image
As this is new branch and does not exist in Server so we have to push this branch to Mercurial server but for that we have to use –new-branch option. Lets do it from Synchronizer.
In Synchronizer there have button “options” you will get a checkbox to allow to create a new branch with your given name of namedbranch.
image
Now if you push it other will see a branch also when they will pull from server.
You can also switch from one branch to another branch using Update command.
image
Now we will create a build configuration in TeamCity so that we can create a release build from Release 1.0.0.0.  I assumed here that you know how to configure a build from Mercurial default branch.
We have to create and attach new VCS Root for namedbranch.
image
TeamCity has option to give namedbranch name while creating a new VCSRoot and it will pull the source code from TestBranch1.0 branch. You can also fix bugs and also implement features in namedbranch and TeamCity will pull all changes from namedbranch.

Wednesday, June 27, 2012

ASP.NET MVC Ajax Load with Partial View and Maintaining Browser History for Ajax Call


A single page application I created where contents are in server side and with navigation from one link to other link the content are displaying in same page. As there have no redirect to any other page so Ajax loading is more applicable in such single page application. By clicking on any of the navigation link the detail content will only be requested from server. So full page is not render in such case only the detail portion of page. In such scenario Partial View of ASP.NET MVC  is suitable. The detail content will be displayed as Partial View and with Ajax call we will return only Partial View. Ajax output will be only detail content  and will be displayed to user setting as innerHTML or append inside a <div>. I used ASP.NET MVC 4 to create that application. The solution will also applicable for ASP.NET MVC 2.
From the Url of the following page you can understand Home is the Controller. DynamicContent is Action and Page1 is route parameter Id. As a single page application if I click on the navigation like in right side, only the detail content will changed. If I click on Page 2 link then the content will be “Content will be display here Page2”. Here the links are not changing and only detail content is changing with Ajax call.
image
Let create a Partial View DynamicContentPartialView.cshtml for detail content like this code:
<div>
    Content will be display here
    @ViewBag.SelectedPage
</div>



Now inside DynamicContent.cshtml partial view is rendered with RenderParial call.
<article>
    <div id="dynamicContent">      
        @{
            Html.RenderPartial("DynamicContentPartialView");
        }
    </div>
</article>
<aside>
    <ul id="dynamicActionLinks">
        <li>@Html.ActionLink("Page 1","DynamicContentPartialView","Home",new{id="Page1"},null)</li>
        <li>@Html.ActionLink("Page 2", "DynamicContentPartialView", "Home", new { id = "Page2" }, null)</li>
         <li>@Html.ActionLink("Page 3", "DynamicContentPartialView", "Home", new { id = "Page3" }, null)</li>
    </ul>
</aside>




Here in the code block DynamicContentPartialView control is displayed using RenderPartial call. And there have navigation link for Ajax call. So when first time initial loading or you refresh the page without Ajax call RenderPartial() method will be called. which will display content according the Url route value Id.

The server side code of DynamicContent Action is very simple.
  public ActionResult DynamicContent(string id)
        {
            ViewBag.SelectedPage = id;
            return View();
        }




For making it more simple the detail content is displaying only the selected route value Id.  So that we can only concentrate on main factors.

So if the Url will be like: http://localhost:1037/Home/DynamicContent/Page1 . Here Page1 is the Id route value then the output will be same as above page image.

Now in case of clicking one of right side links we will make Ajax call and we will fetch the detail content. Let say Page 2 link is clicked from right side navigation link. The action link for Page 2 is
@Html.ActionLink("Page 2", "DynamicContentPartialView", "Home", new { id = "Page2" }, null)

Here if you see the html hyperlink value after rendering ActionLink it will look like:
<a href="/Home/DynamicContentPartialView/Page2">Page 1</a>



Now from href attribute you can see Home is controller and DynamicContentPartialView is action. But we will not make redirect to DynamicContentPartialView. we will call DynamicContentPartialView with ajax call.

Javascript code for handling click on the navigation link is:
 $("#dynamicActionLinks li a").click(function () {
            var href = $(this).attr("href");
               LoadAjaxContent(href);             
            return false;
        });

 function LoadAjaxContent(href) {

        $.ajax(
            {
                url: href,
                success: function (data) {
                    $("#dynamicContent").html(data);
                }
            });

    }




Click event of <a> link is handled here in above code block. An Ajax request is made with href value of <a> (<a href="/Home/DynamicContentPartialView/Page2">Page 1</a>). So when $.ajax(fn); is called then this request goes to DynamicContentPartialView  Action inside Home Controller. The code block for DynamicContentPartialView is:
  public ActionResult DynamicContentPartialView(string id)
        {
            ViewBag.SelectedPage = id;
            return PartialView("DynamicContentPartialView");
        }




It also takes route value Id same as DynamicContent Action but here it return DynamicContentPartialView. where DynamicContent Action is returning full View. PartialView call basically write the content inside DynamicContentPartialView to the response. We will get only the content by making ajax call.
<div>
    Content will be display here
    Page2
</div>

and inside success method of $.ajax() call we set the InnerHTML of <div id=”dynamicContent”></div>.  Hence loading contents with ASP.NET MVC partial view is very easy with PartialView.

As ajax call is not redirecting to another page so now problem in case of maintaining history of browser with Url. When you will click on Page 2 navigation link Url of browser will not changed as it is a ajax request. Browser will display Url as http://localhost:1037/Home/DynamicContent/Page1 where it should be http://localhost:1037/Home/DynamicContent/Page2 but it is displaying detail content of Page2. So when we will make Ajax request we will also have to change Url of broswer. So that user can bookmark http://localhost:1037/Home/DynamicContent/Page2  also.

HTML5 browser supports history.js api which can not be applicable for non HTML5 browser. Let see the pushState() code while making ajax request.
 $("#dynamicActionLinks li a").click(function () {
            var href = $(this).attr("href");
            if (window.history && window.history.pushState) {
                LoadAjaxContent(href);
                var displayUrl = href.replace("PartialView", "");

                window.history.pushState(href, "Page Title", displayUrl);
            } else {
                $.address.value(href);

            }
            return false;
        });




Here displayUrl is used for displaying Url to the browser. Though we are making ajax request to DynamicContentPartialView Action but the browser will display http://localhost:1037/Home/DynamicContent/Page2. Now if you click on back and forward to http://localhost:1037/Home/DynamicContent/Page2. onpopstate event will raise. Let see the code :
window.onpopstate = function (event) {
        if (event.state != null)
            LoadAjaxContent(event.state);
    };

Here the state object contains  http://localhost:1037/Home/DynamicContentPartialView/Page2 as state ( first argument of pushState() method). And here we again make ajax request. You could also have detail content of Page2 inside pushState. Here I made ajax call again to fetch detail content from PartialView. But not all browser ( IE6 to IE9) does not support history.js api. So we need to check for those browser.

For non- HTML5 browser, only option for maintaining history is changing window.location.hash. I found a jquery nice plugin  Address.js. I have also used it for ajax call
$.address.value(href);



It also fire an event as like:
  $.address.change(function (event) {
            if (event.value == "/") {
                if (window.location.href.indexOf("DynamicContent") != -1) {
                    var quarylocation = window.location.href;
                    LoadAjaxContent(quarylocation.replace("DynamicContent", "DynamicContentPartialView"));
                }
                return;
            }
            try {

                LoadAjaxContent(event.value);

            }
            catch (e) {

            }
        });




We can get the value of hash with event.value.  As we did not push the state of browser when Full Page load with called with DynamicContent action . So inside the DynamicContent view  the first state is saved as:
  <div id="dynamicContent">
        <script type="text/javascript">
            if (window.history && window.history.pushState) {
                var href = window.location.href;
                var backUrl = href.replace("DynamicContent", "DynamicContentPartialView");
                window.history.pushState(backUrl, "Page Title", href);
            } else {
                $.address.value(window.location);
            }
        </script>
        @{
            Html.RenderPartial("DynamicContentPartialView");
        }
    </div>




With this call it also have the state of first load without ajax call. So when we will back to initial state it from browser history it will also make ajax request for initial state.

Here I have tried to give your same features as they get full page load but as modern web application where history is maintained with ajax call  and only the required detail content is requested from server.

My colleague Masudur Rahman also worked with me in pair programming.

Here you can download the solution with above explained code https://dl.dropbox.com/u/20275838/historybookmark.rar

Monday, June 18, 2012

Solution: Inline CSS issue and Ajax call with IE6 to IE8

 

Problem:

I have an application where I need to display html content by making Ajax call. The html content in html page with inline CSS (<style></style> tag inside html page). I have a <div> element in my main page and I make Ajax request to load the html content and set the content in <div> as innerHTML or using jQuery html(). The html content is displaying perfectly in Firefox 12 and Chrome 19 and also work nicely in IE 9. But when I made a Ajax request using IE 7 or IE 8 the html content is displaying without inline styling in page. The problem is also specified in web and is a known bug of IE  http://www.dougboude.com/blog/1/2008/05/Inline-CSS-and-Ajax-Issue-with-IE.cfm. They suggest to use CSS in global file. But my each content has different CSS style and that is not part of our application and  content can be changed any time also. So we can not replace inline CSS in another CSS file. I tried to find alternative solution of this problem  where inline CSS can be considered in IE 6 to 8 but It did not work. We also tried to refresh the DOM but failed to display content with inline style.

Solution:

jQuery has two method for inserting html content inside a <div> or other DOM elements. One is html() and another is append(). html() function of jQuery works same as setting InnerHTML inside DOM element and it replaces the previous content. But append() does not replace existing content and it appends content at the end of existing content. To replace the content empty() method should be called and just after we can call append(). It may look like both are doing same things. But append does not only set the InnerHTML inside DOM element. It do much complicated work. If you watch the append() function inside jQuery.js file you will see it first manipulates the DOM inside the html content. If you see the clean function inside jQuery then you will find it creates JQuery collection from html string. If you write your own code to create JQuery collection and append inside <div> using appendChild that will work with styling in FireFox not in IE also you will get less elements inside JQuery collection. Inside the append() function style problem is fixed but code is much complex to understand which code actually solved the problem. So if you use Jquery empty().append() function instead of html() or innerHTML for IE 6 to IE8 , you can see the content with inline CSS making Ajax call.

The simple solution for IE 6 to IE 8 is

    var url = "inline.html";
$.ajax({
url: url,
success: function (html) {
$('#content').empty();

$('#content').append(html);
},
error: function () { return null; }
});




Here is the demo of the solution.  https://dl.dropbox.com/u/20275838/testAjax/index.html

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