Twitter Feed Popout byInfofru

Modifying Connection Properties of excel file

We need to modify about 100+ excel files which are connecting to data connections files sitting in d

We were in need to modify about 100+ excel files which are connecting to data connections files sitting in data connection library of SharePoint 2010. The property we needed to change is “Always user connection file” in connection properties as shown below. Of course programmatically.


First of, I added the reference of “Microsoft.Office.Interop.Excel” in project and include it in code, like below

using xls = Microsoft.Office.Interop.Excel;

Then, read excel file from physical location and changed its connection properties using the code below

var application = new xls.Application();

var application = new xls.Application();   // Creating excel application object
     string readLocation = @"D:\Documents\myfile_1.xlsx";
     var workbook = application.Workbooks.Open(readLocation); // Open excel workbook

    foreach (xls.WorkbookConnection con in workbook.Connections) // Iterating all connections

            // Check for other properties, if you have to. For me, I am dead sure that all files wouldn’t be using connection files
             con.OLEDBConnection.AlwaysUseConnectionFile = true;

    workbook.Save(); // Saving back workbook
catch (Exception)

        // These clean up lines are important to run otherwise, you will end up having an orphan excel instance sitting idle.

Permission denied on IE 11

From yesterday, I was having a weird issue in our legacy SharePoint 2010, where all IE 11 users were reporting problems in new release that had substantial amount of JavaScript modifications. I started to investigate by looking at the console and found Permission denied error. Awesome, a step closer to resolve the issue but haven’t had the grab of any useful information about it. I was having the Permission denied error various times on console like below.


I had a look into all these lines of code and I realize that permission denied was occurring each time the “document” object being called and then I notice the following line at the top of the console section.


It led me to believe that compatibility setting would resolve this. So in IE, I went to Emulation section of developers tools, change the document mode from 8 (Default) to 10 as can be seen in the snapshot below.


Aha! the errors had disappeared and JavaScript started to work fine. Now, in order to force client’s browser to use the document mode 10 by default, following meta tag should be specified in master page.

<meta http-equiv="X-UA-Compatible" content="IE=10"/>

How to redirect to default login page from action

I have been using Authorize attribute heavily to manage user authorizations, but at times just Authorize attribute is not enough. For an instance, I want user to access the Edit page of any entity which he created. In other words I don’t want him to edit records created by other users.

We cannot do this using Authorize only because it is design to limit access to an action regardless of what data is being passed to it. Thus, in this case you can maintain CreatedUser field in the record table and check on the Action if the logged in user is authorize to do this edit. Here is the code for that.


if (myrecord.OwnerId != User.Identity.Name) {

return new HttpUnauthorizedResult();



You can return HttpStatusCodeResult(403) too but that will only display the default access denied page of IIS, which is not we want and this is where “HttpUnauthorizedResult” comes handy.

Ambiguous in the namespace problem

From the last few days, I was ignoring an error that keep coming at the compile time. I spent some two hours on it before but didn’t get it work. The error is quit confusing and of course difficult to manage.

'ApplicationSettingsBase' is ambiguous in the namespace 'System.Configuration'

'MailMessage' is ambiguous in the namespace 'System.Net.Mail'

And there are couple of other similar errors that is pointing to some ambiguous references in my project.  The confusing part is that the MailMessage object throws similar error when you are importing the old and new email namespace.

For example,

Imports System.Web.Mail
Imports System.Net.Mail

So if you are only encountering ambiguous problem in MailMessage object. It is more possible that you have define both the namespaces in your code behind which is actually confusing the compiler about your referencing object.

The quick solve for this problem is that remove Imports System.Web.Mail and it should work smooth. But with me, I never used the old mail namespace in my project.

Then I start looking at my references and luckily I found the problem there. Follow the steps below to investigate the issue

1. Go to your project

2. Then references

3. Right click on “System” and see properties. it should point to the following path


Where x is name of your operating system directory. This was the problem with my project. I had my operating system install on “D” drive and some how it is pointing to “C” drive which is the root cause of this problem.

After that I verify all my references and found 5 –6 assemblies that are pointing to wrong path and get it worked.

Also note, the problem can occur in any type of project either it is website , web application etc.

Getting started with Unit Testing in C#

In this post, I will explain you how can we write a unit test in c#. It is a basic guideline for those who wants a quick start. Unit testing is an integral part of any software that is developed. It is an advantage which most of us are either not aware of or we are neglecting it. It actually helps a developer to write error free code.


In this post, I will explain you how can we write a unit test in c#. It is a basic guideline for those who wants a quick start.

Unit testing is an integral part of any software that is developed. It is an advantage which most of us are either not aware of or we are neglecting it.  It actually helps a developer to write error free code.

To write unit test, we will first install a unit-testing framework.


About Unit-Testing Framework:

Well, Unit-Testing Frameworks are useful to simplify the process of unit testing. If you don’t want to use any framework then you can still do unit-testing by writing the client code which implements assertions, exception handling etc.

There are numerous framework available for unit testing. A list of which can be found here . But in our case, we will use NUnit to test our code because it is easy to use, show detail test reports and of course open source.


Installing NUnit:

To download the NUnit goto : and download NUnit Windows MSI. The installation is a conventional next next stuff. So, you will not face any hard time.


Writing Testable code:

Now open visual studio and create a new console application (I name it TestAbleApp). Please note, to do unit-testing it is not important to write your code in console application. It is just a matter of my choice because I want to make it simple and easy to understand.

Create a new class call it “Utilities” and write the following code.

public class Utilities
    public enum Gender
    { Male =1,
      Female = 2

    public string GetCompleteProfession(string professionName, Gender g)
        string strPronoun = string.Empty;

        if (g == Gender.Male)
            strPronoun = "he";
            strPronoun = "she";

        if (Regex.IsMatch(professionName,"^[aeiou]"))
            return strPronoun + " is an " + professionName;
            return strPronoun + " is a " + professionName;

    public decimal GetWeeks(DateTime dtFrom, DateTime dtTo)
        int Days = ((TimeSpan)(dtTo - dtFrom)).Days;
        decimal Weeks = Math.Ceiling((decimal)Days / 7);
        return Weeks;
    public decimal GetDays(DateTime dtFrom, DateTime dtTo)
        int Days = ((TimeSpan)(dtTo - dtFrom)).Days;
        return Days;

Let me explain, we have an enum here which hold the gender and we have a function which have Profession and Gender as parameter. it will simply return a formatted string. For example if someone pass

GetCompleteProfession(“System Analyst”,Gender.Male). It will return “He is a System Analyst”. A very simple function.

Then we have a function that will take date range as parameter and return the number of weeks in that range. We call it GetWeeks and another function is GetDays which takes same date range as parameter but return days instead of week.

We are completed with the testable code. If you want, you can check the output of the functions.


Writing Unit-Test:

To write a unit-test, create a class library project under the same solution and call it, “TestProject”. Now, create a new class and name it “UtilitiesUnderTest”. The naming convention can explain that this class contain the unit test of class “Utilities”.

Now, Add the executable of console application which we have created before (In my case, it is TestableApp ) as a reference in TestProject.

To use the NUnit Testing framework, we also need to add the reference of NUnit Dll which you will find under the .net Tab in Add Reference window.



Create a class and call it UtilitiesUnderTest. Naming convention shows that we are creating a test code for class “Utilities”. Now write the following code.

class UtilitiesUnderTest
    public void GetCompleteProfession_Return_SheIsASoftwareEngineer()
        Utilities objUtil = new Utilities();

        string strResult = objUtil.GetCompleteProfession("software engineer", Utilities.Gender.Female);

        StringAssert.AreEqualIgnoringCase("She is a software engineer", strResult);

    public void GetCompleteProfession_Return_HeIsAProjectManager()
        Utilities objUtil = new Utilities();

        string strResult = objUtil.GetCompleteProfession("software engineer", Utilities.Gender.Male);

        StringAssert.AreEqualIgnoringCase("He is a software engineer", strResult);

    public void GetCompleteProfession_Return_HeIsAnEngineer()
        Utilities objUtil = new Utilities();

        string strResult = objUtil.GetCompleteProfession("engineer", Utilities.Gender.Male);

        StringAssert.AreEqualIgnoringCase("He is an engineer", strResult);

    public void GetWeeks_Return_6()
        Utilities objUtil = new Utilities();

        decimal weeks = objUtil.GetWeeks(DateTime.Now.AddDays(-42), DateTime.Now);

        Assert.AreEqual(6, weeks);

    public void GetDays_Return_25()
        Utilities objUtil = new Utilities();

        decimal days = objUtil.GetDays(DateTime.Now.AddDays(-25), DateTime.Now);

        Assert.AreEqual(25, days);

Notice the Attribute, [TestFixture] is used to mark a class as a test class where as [Test] is used to mark a method as Unit-Test (test method). It is used by Unit testing framework like N-Unit to test the code.

Also notice, the name of methods. It is named like “{MethodUndertestName}_Return_{ExpectedReturnValue}”. The naming convention is there to make your tests readable for others.

Now come to the explanation part of the first three functions.

In the first of line each function we are creating an object of the class under test (In our case Utilities).
In the second line we are calling a method under test by specifying parameters.
In the third line we are using StringAssert to Assert the returned value.

Now notice the last two functions, the first two lines are same. The minor difference is in the last line. Instead of using StringAssert, we are using Assert to test the return value.

Once, you have complete writing the Unit-Test. Now its time to see it in action.

Running Unit-Tests:

Run NUnit, Goto File –> New Project and specify the location.
Now, Goto Project menu and select Add Assembly and locate your TestProject DLL, the one which you created to test your code.
Once, you have done that, you will all your Unit-Test in the left pane as shown below.


Now, click the big run button on the right pane and you will see the result as give below.


Happy ending, green progress bar means every thing went well. Now let’s create another unit-test in out test class which will fail out tests. So that, we can see how NUnit reacts when a test fails.

Lets add the following method in our test class.

public void GetWeeks_Return_3()
    Utilities objUtil = new Utilities();

    decimal weeks = objUtil.GetWeeks(DateTime.Now.AddDays(-8), DateTime.Now);

    Assert.AreEqual(3, weeks);

Here I am giving the range of eight days and expecting my function to return 3 weeks instead of two. Now, when I run the test it will get fail and NUnit display us something like below.


Red progress bar means, some thing went wrong and notice the text area at the bottom. It will show you the detail that you were expecting three but the function returns two. That is the test get fails.

Now lets see what happen when any exception occur in the function we are testing.  Add the following line in GetWeek function

throw new ArgumentOutOfRangeException();


Now, when we run out tests, we will see some thing like below.


NUnit fails our test with the exception with Stacktrace at the botoom.



This was just a quick start of doing test driver development and write Unit-Tests that is why we create unit-test for very simple functions. In future, I will be posting the unit-tests which I will write for some more complex functions.

I have tried to make it simple for you guys to grab it and start writing your own unit-test. The resource I found very valuable for starting test driven development is Roy Osherove’s The Art of Unit Testing.

Get Countries Name in .Net

In this post, I will explain you how can we get the countries name filled in any collection using .net without using any database.


In this post, I will explain you how can we get the countries name filled in any collection using .net without using any database.

It is a regular task, which we all as developers did some past day but the difference is we used database table or xml file to hold the country names. But .net framework provide us with all the countries information in Globalization namespace.

So, here is the code for that

Dictionary<string,string> objDic = new Dictionary<string,string>();

foreach (CultureInfo ObjCultureInfo in CultureInfo.GetCultures(CultureTypes.SpecificCultures))
    RegionInfo objRegionInfo = new RegionInfo(ObjCultureInfo.Name);
    if (!objDic.ContainsKey(objRegionInfo.EnglishName))
        objDic.Add(objRegionInfo.EnglishName, objRegionInfo.TwoLetterISORegionName.ToLower());

var obj = objDic.OrderBy(p => p.Key );
foreach (KeyValuePair<string,string> val in obj)
    ddlCountries.Items.Add(new ListItem(val.Key, val.Value));



Notice that, we have used typed dictionary object to store the name and the values of the countries.

Then, we use CultureInfo.GetCultures to get the cultural information of the countries.

Later on, we use RegionInfo to get the regional information of that  culture.

Since, there can be multiple cultures of the same country that is why there is a condition which check either the country is already added in dictionary. If not, then simply add the country name and country two letter name. (Note : We are treating the two letter country name as the value)

After the loop, I used some LinQ stuff to sort county names, and then iterate through the returned object to add the values in drop down list.

That’s it. Now you are not only limited to show the English name of the country but you can also show the native name. For example, the name of my country in English is “Islamic Republic of Pakistan” but the native name is پاکستان.

Also, you can get the following country information using RegionInfo



Some developers are habitual of using country id along with the country name. if they still want to use some id to save the country information they can use the GeoId property of the RegionInfo.

Maintain Scroll Position With Validation Controls.

In this post I will demonstrate you how to maintain the scroll position of validation controls on a page

Few days ago, when I was trying to put validation controls on a form which is at the bottom of the page. I noticed that each time validation controls executed it reset the scroll position to top and the end user see no messages until page scroll back to the old position. Irritated :)




So, In this post I will demonstrate you how to maintain the scroll position of validation controls on a page. To start with an example, lets create a form like below




     <table class="style1">
                 <asp:TextBox ID="txtName" runat="server" autocomplete="off" Width="200"></asp:TextBox>
                 <asp:RegularExpressionValidator ID="revName" runat="server" 
                     ControlToValidate="txtName" Display="None" 
                     ErrorMessage="Please enter a valid name" 
                     <asp:RequiredFieldValidator ID="rfvName" runat="server" 
                     ControlToValidate="txtName" Display="None" ErrorMessage="Please enter a name." 
                     SetFocusOnError="true" ValidationGroup="grpTop"></asp:RequiredFieldValidator>
                 Email Address</td>
                 <asp:TextBox ID="txtUsername" runat="server" autocomplete="off" Text="" 
                 <asp:RequiredFieldValidator ID="rfvEmailAddress" runat="server" 
                     ControlToValidate="txtUsername" Display="None" 
                     ErrorMessage="Please enter a valid email address." ValidationGroup="grpTop"></asp:RequiredFieldValidator>
                 <asp:RegularExpressionValidator ID="revEmail" runat="server" 
                     ControlToValidate="txtUsername" Display="None" 
                     ErrorMessage="Please key in a valid email address." SetFocusOnError="true" 
                 <asp:CustomValidator ID="valEmailAlreadyExists" runat="server" Display="None" 
                     ErrorMessage="Email address already exists" ValidationGroup="grpTop"></asp:CustomValidator>
                 Date Format</td>
                 <asp:DropDownList ID="ddlDateFormat" runat="server" Width="200px">
                     <asp:ListItem Text="dd/MM/yyyy" Value="dd/MM/yyyy">dd/MM/yyyy</asp:ListItem>
                     <asp:ListItem Text="MM/dd/yyyy" Value="MM/dd/yyyy">MM/dd/yyyy</asp:ListItem>
                     <asp:ListItem Text="dd/MM/yy" Value="dd/MM/yy">dd/MM/yy</asp:ListItem>
                     <asp:ListItem Text="MM/dd/yy" Value="MM/dd/yy">MM/dd/yy</asp:ListItem>
                 <asp:RequiredFieldValidator ID="rfvDateFormat" runat="server" 
                     ControlToValidate="ddlDateFormat" Display="None" 
                     ErrorMessage="Please select date format." InitialValue="Select" 
                     SetFocusOnError="true" ValidationGroup="grpTop"></asp:RequiredFieldValidator>
                 <asp:CheckBox ID="cbIsAdmin" runat="server" Checked="false" 
                     Text="Administrator?" />
                 &nbsp;<asp:CheckBox ID="cbResetPW" runat="server" Checked="false" 
                     Text="Reset Password" />
                 <asp:Button ID="Button1" runat="server" Text="Submit Group 1"  ValidationGroup="grpTop"
                     onclick="Button1_Click" />
                 <asp:ValidationSummary ID="ValidationSummary1" runat="server" 
                     EnableClientScript="true" HeaderText="Fix the following issues :" 
                     ValidationGroup="grpTop" />

Notice that <p> tag which I have given in the above snippet to keep my form at the bottom of the page so that the scroll position problem can easily be demonstrated.

When you run the above snippet, you will realize that the page scroll position will lost as soon as you want to submit the form. If you try the page directive System.Web.Ui.Page.MaintainScrollPositionOnPostBack. Noting will change because by using this property engine will take care of scroll position after post back. But we want it on client script. It means, we need to modify the mechanism on which validation controls executed.

To start with the understanding, I came across a very good article “Understanding ASP.NET Validation Library” by DeepakRaghavan. Which gives me the understanding of two methods. Page_ClientValidate and WebForm_OnSubmit and ultimately I need to modify them and bear in mind that these function will be written inside form tag not in title.

The idea is to use the bookmark to maintain the scroll bar. So lets modify both the functions respectively.



   1: var bookMark = "#OtherBookMark";
   2: function Page_ClientValidate(validationGroup)
   3: {
   5:   Page_InvalidControlToBeFocused = null;  
   6:   if (typeof(Page_Validators) == "undefined") {  
   7:   return true;  
   8:   }  
   9:   var i;  
  10:   for (i = 0; i < Page_Validators.length; i++) {  
  11:   ValidatorValidate(Page_Validators[i], validationGroup, null);  
  12:   }  
  13:   ValidatorUpdateIsValid();  
  14:   ValidationSummaryOnSubmit(validationGroup);  
  15:   Page_BlockSubmit = !Page_IsValid;  
  17:   if (validationGroup == "grpTop")
  18:   {
  19:       bookMark = "#addForm";
  20:   }
  21:   else
  22:   {
  23:       bookMark = "#OtherBookMark";
  24:   }
  25:   return Page_IsValid;  
  26: }


Line No 1.       A variable that will hold the bookmark name

Line No 2-14   The default function implementation (We have simple nothing to do with it… simply copy / paste)

Line No 15- 22 Since we can have multiple forms on a page, that is why it is better to keep track that which validation group is called and decide the bookmark name accordingly.


Now instead of modifying WebForm_OnSubmit, let’s first create a following function



   1: function SubmitAction() {
   2: if (typeof(ValidatorOnSubmit) == "function" && ValidatorOnSubmit() == false)
   3:   {
   4:           var hashIndex = location.href.indexOf("#");
   6:           if (hashIndex > -1) 
   7:           {
   8:               var loc = location.href.substring(0,hashIndex);
   9:               location.href = loc + bookMark;
  10:           }
  11:           else
  12:           {
  13:               location.href = location + bookMark;
  14:           }
  16:           return false;
  17:   }
  18:   else
  19:   {
  20:       return true;
  21:   }
  22: }



Well, the function is very simple and don’t really need line by line commentary.
We are checking if the validation is fail, set the bookmark. other wise just let it go.



Now we will call the above SubmitAction function inside WebForm_OnSubmit. To inject this function you need to write the following lines on the Page_Load

   1: if (!Page.ClientScript.IsOnSubmitStatementRegistered("keySubmitAction"))
   2: {
   3:     Page.ClientScript.RegisterOnSubmitStatement(this.GetType(), "keySubmitAction", "return SubmitAction();");
   4: }

System.Web.UI.Page.ClientScript.RegisterOnSubmitStatement is actually used to inject your script inside WebForm_OnSubmit. To be more specific, the first lines will be yours :).

and if you notice the Line No : 3 I used return statement which means as soon as WebForm_OnSubmit will execute my function (SubmitAction) will run first and then return the execution out… no more processing on WebForm_OnSubmit.

Now when you look at the view source of your browser you will see some thing similar to the following.


Ok, now we only need to setup our bookmark.

Just give the id “addForm” to the table or div inside which the validation summary control is placed. that’s it.  However, you can download the VS 2008 Solution from here.

Show images on Grid View from File Stream SQL Server 2008

Background :

In my last post about SQL Server 2008 new feature File Stream (Saving and Retrieving File Using FileStream SQL Server 2008), we did an example of saving an image to the file stream and then retrieve it back and make it available for download.The result of that example looks like as below.fs_snapshot1But, one has to press the button to download the image file. One of my blog reader raise a point that he wants to display the same image instead of Get File button which is going to download.

Introduction :

So, in this post I will explain you, how can we rendered the image before actually downloading it and show that in the grid (Maybe as thumbnail, but this post will not discuss any thing about generating thumbnails).Note : If you want to know. How to add files to the file stream please see my post Saving and Retrieving File Using FileStream SQL Server 2008

Getting Started:

We will complete this goal by using HttpHandler. lets first alter our gridview.

   1: <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
   2:         onrowcommand="GridView1_RowCommand">
   3:         <Columns>
   4:             <asp:BoundField DataField="ID" HeaderText="ID" />
   5:             <asp:BoundField DataField="SystemNumber" HeaderText="System Id" />
   6:             <asp:TemplateField>
   7:                 <ItemTemplate>
   8:                     <asp:LinkButton ID="lbGetFile" runat="server" CommandName="GetFile" CommandArgument='<%#Eval("ID") %>' ><img src='<%#Eval("ID") %>.jpg' /></asp:LinkButton>
   9:                 </ItemTemplate>
  10:             </asp:TemplateField>
  11:         </Columns>
  13:     </asp:GridView>

Notice the link button on line no 8. I have now specify an image tag inside Link button and pass the primarykey of tbl_files as the file name along with random “.jpg”. So that, it can finally looks like as follows8e7af927-cc7e-4515-8409-d94566246de8.jpga3de6abb-382f-484c-822c-7f93e0ede0c7.jpg4ad64bf1-ea6e-4228-bdc0-300a0cd90f5a.jpgThe idea is, I will attach the handler with jpg file type to accommodate the incoming requests.Now, lets create HttpHandler and name it “imageHandler”

public class imageHandler : IHttpHandler
    #region IHttpHandler Members
    public bool IsReusable
        get { return false; }
    public void ProcessRequest(HttpContext context)
        //Getting file name from incoming request.
        string url = Path.GetFileName(context.Request.Path);
        Guid FileId;
            //Since we have all our primary keys stored in GUID
            //Try parsing the file name to Guid
            FileId = new Guid(Path.GetFileNameWithoutExtension(url));
        catch (FormatException)
            //If some other JPG file is requested
            FileId = Guid.Empty;
        if (FileId != Guid.Empty) // If the call is for valid Image File Stream
            SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            SqlTransaction objSqlTran = objSqlCon.BeginTransaction();
            SqlCommand objSqlCmd = new SqlCommand("FileGet", objSqlCon, objSqlTran);
            objSqlCmd.CommandType = CommandType.StoredProcedure;
            SqlParameter objSqlParam1 = new SqlParameter("@ID", SqlDbType.VarChar);
            objSqlParam1.Value = FileId.ToString();
            string path = string.Empty;
            string fileType = string.Empty;
            using (SqlDataReader sdr = objSqlCmd.ExecuteReader())
                while (sdr.Read())
                    path = sdr[0].ToString();
                    fileType = sdr[1].ToString();
            objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);
            byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();
            SqlFileStream objSqlFileStream = new SqlFileStream(path, objContext, FileAccess.Read);
            byte[] buffer = new byte[(int)objSqlFileStream.Length];
            objSqlFileStream.Read(buffer, 0, buffer.Length);
            context.Response.AddHeader("Content-disposition", "attachment; filename=" + Path.GetFileName(path) + fileType);
            // Here you need to manage the download file stuff according to your need
            context.Response.ContentType = "application/octet-stream";
        { // If the call is for some other JPG file, nothing to do with file stream.

Well, read the comments I wrote in the code. That will of course help you to understand what actually I have done.And then register the HttpHandler.

    <add verb="*" path="*.jpg" type="LearningApp.imageHandler, LearningApp"/>


There we go, In this way we can show the images stored on file stream in grid view. You can download both VS 2008 and VS 2010 project files.

Custom Paging in Grid view

So, it takes too long for me to write this post. even though I completed the coding stuff a week back but it is really hard to manage time these days.In this post, I will explain and demonstrate you how to create custom paging in Grid view control. Paging which works like the Google Paging. Let me elaborate more, say for example you have a record set of 500 items and you want to display 10 items per page. Now what happen to the pages numbers. Either you use the default with “..” sign after 10th page link which cause the post back and then get some new page numbers. Or you want to make it like Google. i.e as soon as user move forward on page index, hide the previous pages and show the new numbers and when user is getting back hide new numbers and show the previous page links. following image can give you some idea what we are going to do.gvpaging_scgvpaging_sc2To start, lets first create a Grid view .
   1: <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
   2:     onrowcreated="GridView1_RowCreated" >
   3: </asp:GridView>
Yes, we have allow the paging but we are not going to use the default paging of That is why we have write onrowcreated implementation in which we will simply detect and hide the pager row.
   1: protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
   2:  {
   3:      if (e.Row.RowType == DataControlRowType.Pager)
   4:      {
   5:          e.Row.Visible = false;
   6:      }
   7:  }
Now lets move to some global variables which we need through out our code.
   1: const int pageSize = 10;
   2: const int pageDispCount = 10;
   3: private DataTable dt = new DataTable();
pagesSize : number of records we want to display per page.pageDispCount : number of page numbers we want to display on custom paging.dt : A datatable which we will use to store data and use it on different post backs.Ok, now we need to get data from database, dump it to datatable and define the datasource of grid view.
   1: protected void bindData()
   2: {
   3:     SqlConnection objSqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ToString());
   4:     objSqlCon.Open();
   6:     SqlDataAdapter objSqlDa = new SqlDataAdapter("select * from Production.Product", objSqlCon);
   8:     objSqlDa.Fill(dt);
  10:     GridView1.DataSource = dt;
  11:     GridView1.DataBind();
  13:     managePaging(dt);
  14: }
On line number 13, managePaging function will be used later to implement the paging logic. But before that, lets understand that we have taken the datatable as a global variable and each time after postback when we try to read our datatable we will get no results because there is no state management for this object.So lets override the LoadViewState and SaveViewState function of System.Web.UI.Page to save and restore the datatable by using viewstate.
   1: protected override object SaveViewState()
   2: {
   3:   object baseState = base.SaveViewState();
   4:   return new object[] { baseState, dt };
   5: }
   6: protected override void LoadViewState(object savedState)
   7: {
   8:   object[] myState = (object[])savedState;
   9:   if (myState[0] != null)
  10:       base.LoadViewState(myState[0]);
  12:   if (myState[1] != null)
  13:   {
  14:       dt = (DataTable) myState[1];
  15:       GridView1.DataSource = dt;
  16:       GridView1.DataBind();
  18:       managePaging(dt);
  19:   }
  21:   }
Well, SaveViewState function is simply putting the base.SaveViewState object and datatable in and object and returning it. A Simple Logic :)Where as, LoadViewState is retrieving and type casting the object exactly in the sequence it was save in the SaveViewState method.
   1: protected void managePaging(DataTable _dt)
   2: {
   3:     if (_dt.Rows.Count > 0)
   4:     {
   6:         // Variable declaration
   7:         int numberOfPages;
   8:         int numberOfRecords = dt.Rows.Count;
   9:         int currentPage = (GridView1.PageIndex);
  10:         StringBuilder strSummary = new StringBuilder();
  13:         // If number of records is more then the page size (specified in global variable)
  14:         // Just to check either gridview have enough records to implement paging
  15:         if (numberOfRecords > pageSize)
  16:         {
  17:             // Calculating the total number of pages
  18:             numberOfPages = (int)Math.Ceiling((double)numberOfRecords / (double)pageSize);
  19:         }
  20:         else
  21:         {
  22:             numberOfPages = 1;
  23:         }
  26:         // Creating a small summary for records.
  27:         strSummary.Append("Displaying <b>");
  29:         // Creating X f X Records
  30:         int floor = (currentPage * pageSize) + 1;
  31:         strSummary.Append(floor.ToString());
  32:         strSummary.Append("</b>-<b>");
  33:         int ceil = ((currentPage * pageSize) + pageSize);
  35:         //let say you have 26 records and you specified 10 page size, 
  36:         // On the third page it will return 30 instead of 25 as that is based on pageSize
  37:         // So this check will see if the ceil value is increasing the number of records. Consider numberOfRecords
  38:         if (ceil > numberOfRecords)
  39:         {
  40:             strSummary.Append(numberOfRecords.ToString());
  41:         }
  42:         else
  43:         {
  44:             strSummary.Append(ceil.ToString());
  45:         }
  47:         // Displaying Total number of records Creating X of X of About X records.
  48:         strSummary.Append("</b> of About <b>");
  49:         strSummary.Append(numberOfRecords.ToString());
  50:         strSummary.Append("</b>Records</br>");
  53:         litPagingSummary.Text =  strSummary.ToString();
  56:         //Variable declaration 
  57:         //these variables will used to calculate page number display
  58:         int pageShowLimitStart = 1;
  59:         int pageShowLimitEnd = 1;
  63:         // Just to check, either there is enough pages to implement page number display logic.
  64:         if (pageDispCount > numberOfPages)
  65:         {
  66:             pageShowLimitEnd = numberOfPages; // Setting the end limit to the number of pages. Means show all page numbers
  67:         }
  68:         else
  69:         {
  70:             if (currentPage > 4) // If page index is more then 4 then need to less the page numbers from start and show more on end.
  71:             {
  72:                 //Calculating end limit to show more page numbers
  73:                 pageShowLimitEnd = currentPage + (int)(Math.Floor((decimal)pageDispCount / 2));
  74:                 //Calculating Start limit to hide previous page numbers
  75:                 pageShowLimitStart = currentPage - (int)(Math.Floor((decimal)pageDispCount / 2));
  76:             }
  77:             else
  78:             {
  79:                 //Simply Displaying the 10 pages. no need to remove / add page numbers
  80:                 pageShowLimitEnd = pageDispCount;
  81:             }
  82:         }
  84:         // Since the pageDispCount can be changed and limit calculation can cause < 0 values 
  85:         // Simply, set the limit start value to 1 if it is less
  86:         if (pageShowLimitStart < 1)
  87:             pageShowLimitStart = 1;
  90:         //Dynamic creation of link buttons
  92:         // First Link button to display with paging
  93:         LinkButton objLbFirst = new LinkButton();
  94:         objLbFirst.Click += new EventHandler(objLb_Click);
  95:         objLbFirst.Text = "First";
  96:         objLbFirst.ID = "lb_FirstPage";
  97:         objLbFirst.CommandName = "pgChange";
  98:         objLbFirst.EnableViewState = true;
  99:         objLbFirst.CommandArgument = "1";
 101:         //Previous Link button to display with paging
 102:         LinkButton objLbPrevious = new LinkButton();
 103:         objLbPrevious.Click += new EventHandler(objLb_Click);
 104:         objLbPrevious.Text = "Previous";
 105:         objLbPrevious.ID = "lb_PreviousPage";
 106:         objLbPrevious.CommandName = "pgChange";
 107:         objLbPrevious.EnableViewState = true;
 108:         objLbPrevious.CommandArgument = currentPage.ToString();
 111:         //of course if the page is the 1st page, then there is no need of First or Previous
 112:         if (currentPage == 0)
 113:         {
 114:             objLbFirst.Enabled = false;
 115:             objLbPrevious.Enabled = false;
 116:         }
 117:         else
 118:         {
 119:             objLbFirst.Enabled = true;
 120:             objLbPrevious.Enabled = true;
 121:         }
 124:         //Adding control in a place holder
 125:         plcPaging.Controls.Add(objLbFirst);
 126:         plcPaging.Controls.Add(new LiteralControl("&nbsp; | &nbsp;")); // Just to give some space 
 127:         plcPaging.Controls.Add(objLbPrevious);
 128:         plcPaging.Controls.Add(new LiteralControl("&nbsp; | &nbsp;"));
 131:         // Creatig page numbers based on the start and end limit variables.
 132:         for (int i = pageShowLimitStart; i <= pageShowLimitEnd; i++)
 133:         {
 134:             if ((Page.FindControl("lb_" + i.ToString()) == null) && i <= numberOfPages)
 135:             {
 136:                 LinkButton objLb = new LinkButton();
 137:                 objLb.Click += new EventHandler(objLb_Click);
 138:                 objLb.Text = i.ToString();
 139:                 objLb.ID = "lb_" + i.ToString();
 140:                 objLb.CommandName = "pgChange";
 141:                 objLb.EnableViewState = true;
 142:                 objLb.CommandArgument = i.ToString();
 144:                 if ((currentPage + 1) == i)
 145:                 {
 146:                     objLb.Enabled = false;
 147:                 }
 150:                 plcPaging.Controls.Add(objLb);
 151:                 plcPaging.Controls.Add(new LiteralControl("&nbsp; | &nbsp;"));
 152:             }
 153:         }
 155:         // Last Link button to display with paging
 156:         LinkButton objLbLast = new LinkButton();
 157:         objLbLast.Click += new EventHandler(objLb_Click);
 158:         objLbLast.Text = "Last";
 159:         objLbLast.ID = "lb_LastPage";
 160:         objLbLast.CommandName = "pgChange";
 161:         objLbLast.EnableViewState = true;
 162:         objLbLast.CommandArgument = numberOfPages.ToString();
 164:         // Next Link button to display with paging
 165:         LinkButton objLbNext = new LinkButton();
 166:         objLbNext.Click += new EventHandler(objLb_Click);
 167:         objLbNext.Text = "Next";
 168:         objLbNext.ID = "lb_NextPage";
 169:         objLbNext.CommandName = "pgChange";
 170:         objLbNext.EnableViewState = true;
 171:         objLbNext.CommandArgument = (currentPage + 2).ToString();
 173:         //of course if the page is the last page, then there is no need of last or next
 174:         if ((currentPage + 1) == numberOfPages)
 175:         {
 176:             objLbLast.Enabled = false;
 177:             objLbNext.Enabled = false;
 178:         }
 179:         else
 180:         {
 181:             objLbLast.Enabled = true;
 182:             objLbNext.Enabled = true;
 183:         }
 186:         // Adding Control to the place holder
 187:         plcPaging.Controls.Add(objLbNext);
 188:         plcPaging.Controls.Add(new LiteralControl("&nbsp; | &nbsp;"));
 189:         plcPaging.Controls.Add(objLbLast);
 190:         plcPaging.Controls.Add(new LiteralControl("&nbsp; | &nbsp;"));
 191:     }
 193: }
Yes, the code is complex that is why I wrote proper comments which will let you understand the stuff easily.One last thing which is left, is the implementation of dynamically created link button onclick event.
   1: void objLb_Click(object sender, EventArgs e)
   2: {
   3:     plcPaging.Controls.Clear();
   4:     LinkButton objlb = (LinkButton)sender;
   5:     GridView1.PageIndex = (int.Parse(objlb.CommandArgument.ToString()) - 1);
   7:     managePaging(dt);
   8: }
There it is, we have now completed Custom Paging in Grid View. If you want to download the source code, here is the VS 2008 Solution.Modifications: I have been receiving emails regarding the issues of this post. Especially with the initial five page numbers. I have modified this post to fix the bug it had. Please feel free to point further issues. Also, the download links are also modified.

Datatable to JSON

In this post I will explain you, how can we serialize Datatable to JSON. So that, it can easily pass to JavaScript to get the AJAX done.

First of all fill a Datatable with some results.

DataTable dt = new DataTable();
SqlConnection objSqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ToString());
SqlDataAdapter objSqlDa = new SqlDataAdapter("select * from Production.Product", objSqlCon);

Now create a String Builder object that will contain the JSON text and JavascriptSerializer which will serialize the output in JSON.

StringBuilder objSb = new StringBuilder();
JavaScriptSerializer objSer = new JavaScriptSerializer();

Now here we are going to iterate each row and column of data table and put all of them in Dictionary

Dictionary<string, object> resultMain = new Dictionary<string, object>();
int index = 0;
foreach (DataRow dr in dt.Rows)
    Dictionary<string, object> result = new Dictionary<string, object>();
    foreach (DataColumn dc in dt.Columns)
        result.Add(dc.ColumnName, dr[dc].ToString());
    resultMain.Add(index.ToString(), result);

Notice that, I have created a new dictionary object for every row and finally put all of the dictionaries in Another dictionary I.E. resultMain.

In the end, I have simply Serialize the resultMain Dictionary to render JSON.

A complete post of utilizing Datatable  in JavaScript through AJAX is in the process, I will post that soon.