Database Pagination in GridView using nHibernate

Tweet

If we develop a complex business application with GB of data and binding all the records to the GridView by doing pagination with it will drastically reduce the performance.

For my current application, I am using WCF service to fetch lakhs of record and return to the UI layer. There are two major issues I was facing. Those are:

There are two major issues I was facing. Those are:

1. I was using WCF service to fetch the records and return to the UI layer. As the amount of data getting transferred much higher, it was throwing error "WCF System.Net.WebException: The underlying connection was closed: The connection was closed unexpectedly".
2. Consider today I may not get any issue as my records are very less. But after 3 years down the line, what could be the output of the application?

Finally I came to conclusion, doing pagination on database side is better. I am blogging on the same concept here.

This post explains how to achieve database pagination using NHibernate and in other two posts I will be explaining (will be posting soon) on pagination using Store Procedure and LINQ concepts.

I am using Northwind database for this example, so please make sure you installed the same database to test the code.

I have three projects for this example (created with layers as normal business applications have.. )

1. DotNetTwitter.Entities - This project is used for defining the business entity class and related mapping .hbm.xml files. (I am using View as I required to bind the name of Categories, Suppliers)
2. DotNetTwitter.DataAccess - This project is used for database operation. Here is where the records are fetched for the required page.
3. DotNetTwitter.DBPagination - Web Application which contains GridView to show the records

The implementation as follows:

Database Script (To create ProductViewview).

Create View [dbo].[ProductView]
As
Select Products.ProductID,
  Products.ProductName,
  Suppliers.CompanyName,
  Categories.CategoryName,
  Products.QuantityPerUnit,
  Products.UnitPrice,
  Products.UnitsInStock,
  Products.UnitsOnOrder,
  Products.ReorderLevel
from Products
Join Suppliers on Suppliers.SupplierID = Products.SupplierID
Join Categories on Categories.CategoryID = Products.CategoryID
GO

nHibernate requires a mapping xml file, which explains how the columns from the dataset links to an entity.
ProductView.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="DotNetTwitter.Entities.ProductView, DotNetTwitter.Entities" table="[ProductView]" lazy="false">
    <id name="ProductID" column="[ProductID]" type="Int32">
      <generator class="assigned"/>
    </id>
    <property name="ProductName" column="[ProductName]" type="String" />
    <property name="CompanyName" column="[CompanyName]" type="String" />
    <property name="CategoryName" column="[CategoryName]" type="String" />
    <property name="QuantityPerUnit" column="[QuantityPerUnit]" type="String" />
    <property name="UnitPrice" column="[UnitPrice]" type="double" />
    <property name="UnitsInStock" column="[UnitsInStock]" type="Int32" />
    <property name="UnitsOnOrder" column="[UnitsOnOrder]" type="Int32" />
    <property name="ReorderLevel" column="[ReorderLevel]" type="Int32" />
  </class>
</hibernate-mapping>

nHibernate require a config file in which all database connection related configurations needs to be mentioned.
nhibernate.config

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
  <session-factory >
    <!-- properties -->
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
    <property name="connection.connection_string">Data Source=KEOWD00144756\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True</property>
    <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
    <property name="show_sql">false</property>
    <!-- mapping files -->
    <mapping assembly="DotNetTwitter.Entities" />
  </session-factory>
</hibernate-configuration>

I have three button and one text box in my gridview to navigate between pages (Next, Previous, Go and a Text box to enter PageNo). The Codebehind would be as

//C# Code behind
/// <summary>
/// Method which binds the data to the Grid
/// </summary>
private void BindGrid()
{
    ProductDAO objProductDAO = new ProductDAO();

    // Defining int variable to get the Total Record Count from Data layer
    int totalRecordCount = 0;

    // Get the SortByExpression and SortType from Hidden Control (by default, that will be updated in script)
    string strSortExpression = ViewState["SortExpression"].ToString().Split(",".ToCharArray())[0];
    SortType sortType = (SortType)Enum.Parse(typeof(SortType), ViewState["SortExpression"].ToString().Split(",".ToCharArray())[1], true);

    // Getting how many records required to show in the Grid per page
    int intPageRecordCount = Convert.ToInt32(ConfigurationManager.AppSettings["PageRecordCount"].ToString());

    IList<ProductView> ProductViewList = objProductDAO.GetProducts(Convert.ToInt32(ViewState["CurrentPage"].ToString()), intPageRecordCount, strSortExpression, sortType, out totalRecordCount);
    //Adding one empty row for just to show the grid
    if (ProductViewList.Count == 0)
        ProductViewList.Add(new ProductView()); 
    
    grdViewProducts.DataSource = ProductViewList;
    grdViewProducts.DataBind();

    grdViewProducts.BottomPagerRow.Visible = true;

    // Assign the Total Record count to 
    ViewState["TotalRecords"] = totalRecordCount.ToString();

    Label lblPageInfo = grdViewProducts.BottomPagerRow.FindControl("lblPageInfo") as Label;
    lblPageInfo.Text = "Page " + Convert.ToInt32(ViewState["CurrentPage"].ToString()).ToString() + " out of " + ((totalRecordCount % intPageRecordCount) > 0 ? (totalRecordCount / intPageRecordCount) + 1 : (totalRecordCount / intPageRecordCount)).ToString();

    // Try to find the sorted column
    for (int intRowIndex = 0; intRowIndex < grdViewProducts.Columns.Count; intRowIndex++)
    {
        if (strSortExpression == grdViewProducts.Columns[intRowIndex].SortExpression)
            ((LinkButton)grdViewProducts.HeaderRow.Cells[intRowIndex].Controls[0])
            .CssClass = (sortType == SortType.Ascending ? "sortup" : "sortdown");
    }
}

/// <summary>
/// Call when clicking Previous button on pagination
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnPrevious_Click(object sender, EventArgs e)
{
    if (Convert.ToInt32(ViewState["CurrentPage"].ToString()) > 1)
        ViewState["CurrentPage"] = (Convert.ToInt32(ViewState["CurrentPage"].ToString()) - 1).ToString();
    else
        ViewState["CurrentPage"] = "1";
    BindGrid();
}

/// <summary>
/// Call when clicking Go button on pagination
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnGo_Click(object sender, EventArgs e)
{
    int intPageRecordCount = Convert.ToInt32(ConfigurationManager.AppSettings["PageRecordCount"].ToString());
    TextBox txtGoPage = ((TextBox)((ImageButton)sender).Parent.FindControl("txtGoPage"));
    if (txtGoPage.Text.Trim().Length > 0)
    {
        if ((Convert.ToInt32(txtGoPage.Text) > 0) && (Convert.ToInt32(txtGoPage.Text) <= ((Convert.ToInt32(ViewState["TotalRecords"].ToString()) % intPageRecordCount) > 0 ? (Convert.ToInt32(ViewState["TotalRecords"].ToString()) / intPageRecordCount) + 1 : (Convert.ToInt32(ViewState["TotalRecords"].ToString()) / intPageRecordCount))))
        {
            ViewState["CurrentPage"] = Convert.ToInt32(txtGoPage.Text).ToString();
            BindGrid();
        }
    }
}

/// <summary>
/// Call when clicking Next button on pagination
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnNext_Click(object sender, EventArgs e)
{
    int intPageRecordCount = Convert.ToInt32(ConfigurationManager.AppSettings["PageRecordCount"].ToString());
    if (Convert.ToInt32(ViewState["CurrentPage"].ToString()) < ((Convert.ToInt32(ViewState["TotalRecords"].ToString()) % intPageRecordCount) > 0 ? (Convert.ToInt32(ViewState["TotalRecords"].ToString()) / intPageRecordCount) + 1 : (Convert.ToInt32(ViewState["TotalRecords"].ToString()) / intPageRecordCount)))
        ViewState["CurrentPage"] = (Convert.ToInt32(ViewState["CurrentPage"].ToString()) + 1).ToString();
    else
        ViewState["CurrentPage"] = (Convert.ToInt32(ViewState["TotalRecords"].ToString()) / intPageRecordCount).ToString();
    BindGrid();
}
/// <summary>
/// Sorts the GridView by clicked column.
/// </summary>
/// <param name="sender">The event sender.</param>
/// <param name="e">The event argument.</param>
protected void grdViewProducts_Sorting(object sender, GridViewSortEventArgs e)
{
    string strSortExpression = ViewState["SortExpression"].ToString().Split(",".ToCharArray())[0];
    SortType sortType = (SortType)Enum.Parse(typeof(SortType), ViewState["SortExpression"].ToString().Split(",".ToCharArray())[1], true);
    ViewState["CurrentPage"] = "1";

    if (strSortExpression == e.SortExpression)
        sortType = sortType == SortType.Ascending ? SortType.Descending : SortType.Ascending;
    else
    {
        strSortExpression = e.SortExpression;
        sortType = SortType.Ascending;
    }
    ViewState["SortExpression"] = e.SortExpression + "," + sortType.ToString();

    BindGrid();
}

'VB Code behind
    ''' <summary>
    ''' Method which binds the data to the Grid
    ''' </summary>
    Private Sub BindGrid()
        Dim objProductDAO As New ProductDAO()

        ' Defining int variable to get the Total Record Count from Data layer
        Dim totalRecordCount As Integer = 0

        ' Get the SortByExpression and SortType from Hidden Control (by default, that will be updated in script)
        Dim strSortExpression As String = ViewState("SortExpression").ToString().Split(",".ToCharArray())(0)
        Dim sortType As SortType = DirectCast([Enum].Parse(GetType(SortType), ViewState("SortExpression").ToString().Split(",".ToCharArray())(1), True), SortType)

        ' Getting how many records required to show in the Grid per page
        Dim intPageRecordCount As Integer = Convert.ToInt32(ConfigurationManager.AppSettings("PageRecordCount").ToString())

        Dim ProductViewList As IList(Of ProductView) = objProductDAO.GetProducts(Convert.ToInt32(ViewState("CurrentPage").ToString()), intPageRecordCount, strSortExpression, sortType, totalRecordCount)
        'Adding one empty row for just to show the grid
        If ProductViewList.Count = 0 Then
            ProductViewList.Add(New ProductView())
        End If

        grdViewProducts.DataSource = ProductViewList
        grdViewProducts.DataBind()

        grdViewProducts.BottomPagerRow.Visible = True

        ' Assign the Total Record count to 
        ViewState("TotalRecords") = totalRecordCount.ToString()

        Dim lblPageInfo As Label = TryCast(grdViewProducts.BottomPagerRow.FindControl("lblPageInfo"), Label)
        lblPageInfo.Text = "Page " & Convert.ToInt32(ViewState("CurrentPage").ToString()).ToString() & " out of " & (If((totalRecordCount Mod intPageRecordCount) > 0, (totalRecordCount \ intPageRecordCount) + 1, (totalRecordCount \ intPageRecordCount))).ToString()

        ' Try to find the sorted column
        For intRowIndex As Integer = 0 To grdViewProducts.Columns.Count - 1
            If strSortExpression = grdViewProducts.Columns(intRowIndex).SortExpression Then
                DirectCast(grdViewProducts.HeaderRow.Cells(intRowIndex).Controls(0), LinkButton).CssClass = (If(sortType = sortType.Ascending, "sortup", "sortdown"))
            End If
        Next
    End Sub

    ''' <summary>
    ''' Call when clicking Previous button on pagination
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Protected Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        If Convert.ToInt32(ViewState("CurrentPage").ToString()) > 1 Then
            ViewState("CurrentPage") = (Convert.ToInt32(ViewState("CurrentPage").ToString()) - 1).ToString()
        Else
            ViewState("CurrentPage") = "1"
        End If
        BindGrid()
    End Sub

    ''' <summary>
    ''' Call when clicking Go button on pagination
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Protected Sub btnGo_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        Dim intPageRecordCount As Integer = Convert.ToInt32(ConfigurationManager.AppSettings("PageRecordCount").ToString())
        Dim txtGoPage As TextBox = DirectCast(DirectCast(sender, ImageButton).Parent.FindControl("txtGoPage"), TextBox)
        If txtGoPage.Text.Trim().Length > 0 Then
            If (Convert.ToInt32(txtGoPage.Text) > 0) AndAlso (Convert.ToInt32(txtGoPage.Text) <= (If((Convert.ToInt32(ViewState("TotalRecords").ToString()) Mod intPageRecordCount) > 0, (Convert.ToInt32(ViewState("TotalRecords").ToString()) / intPageRecordCount) + 1, (Convert.ToInt32(ViewState("TotalRecords").ToString()) / intPageRecordCount)))) Then
                ViewState("CurrentPage") = Convert.ToInt32(txtGoPage.Text).ToString()
                BindGrid()
            End If
        End If
    End Sub

    ''' <summary>
    ''' Call when clicking Next button on pagination
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        Dim intPageRecordCount As Integer = Convert.ToInt32(ConfigurationManager.AppSettings("PageRecordCount").ToString())
        If Convert.ToInt32(ViewState("CurrentPage").ToString()) < (If((Convert.ToInt32(ViewState("TotalRecords").ToString()) Mod intPageRecordCount) > 0, (Convert.ToInt32(ViewState("TotalRecords").ToString()) / intPageRecordCount) + 1, (Convert.ToInt32(ViewState("TotalRecords").ToString()) / intPageRecordCount))) Then
            ViewState("CurrentPage") = (Convert.ToInt32(ViewState("CurrentPage").ToString()) + 1).ToString()
        Else
            ViewState("CurrentPage") = (Convert.ToInt32(ViewState("TotalRecords").ToString()) / intPageRecordCount).ToString()
        End If
        BindGrid()
    End Sub

    ''' <summary>
    ''' Sorts the GridView by clicked column.
    ''' </summary>
    ''' <param name="sender">The event sender.</param>
    ''' <param name="e">The event argument.</param>
    Protected Sub grdViewProducts_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
        Dim strSortExpression As String = ViewState("SortExpression").ToString().Split(",".ToCharArray())(0)
        Dim sortType As SortType = DirectCast([Enum].Parse(GetType(SortType), ViewState("SortExpression").ToString().Split(",".ToCharArray())(1), True), SortType)
        ViewState("CurrentPage") = "1"

        If strSortExpression = e.SortExpression Then
            sortType = If(sortType = sortType.Ascending, sortType.Descending, sortType.Ascending)
        Else
            strSortExpression = e.SortExpression
            sortType = sortType.Ascending
        End If
        ViewState("SortExpression") = Convert.ToString(e.SortExpression) & "," & sortType.ToString()

        BindGrid()
    End Sub

The nHibernate (ProductView.hbm.xml) file. Make sure you have set the Build Action = Embedded Resource to the property of this file.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="DotNetTwitter.Entities.ProductView, DotNetTwitter.Entities" table="[ProductView]" lazy="false">
    <id name="ProductID" column="[ProductID]" type="Int32">
      <generator class="assigned"/>
    </id>
    <property name="ProductName" column="[ProductName]" type="String" />
    <property name="CompanyName" column="[CompanyName]" type="String" />
    <property name="CategoryName" column="[CategoryName]" type="String" />
    <property name="QuantityPerUnit" column="[QuantityPerUnit]" type="String" />
    <property name="UnitPrice" column="[UnitPrice]" type="double" />
    <property name="UnitsInStock" column="[UnitsInStock]" type="Int32" />
    <property name="UnitsOnOrder" column="[UnitsOnOrder]" type="Int32" />
    <property name="ReorderLevel" column="[ReorderLevel]" type="Int32" />
  </class>
</hibernate-mapping>

Below is the code from data access class, which returns only records for particular page.

//C# Code
public IList<ProductView> GetProducts(int currentPageNo, int pageRecordsCount, string sortBy, SortType sortType, out int totalRecordCount)
{
    try
    {
        IList<ProductView> ProductViewList = null;

        // Create the configuration object
        Configuration cfg = new Configuration();
        cfg.Configure(GetConfigFilePath);

        // Create the session
        ISessionFactory sessionFactory = cfg.BuildSessionFactory();
        // Open the session
        ISession session = sessionFactory.OpenSession();

        // Create Criteria
        ICriteria criteria = session.CreateCriteria(typeof(ProductView));

        if (sortBy != null) //If sortBy values pass (Pass null if not required)
            criteria.AddOrder(sortType == SortType.Ascending ? Order.Asc(sortBy) : Order.Desc(sortBy));

        if ((pageRecordsCount > 0) && (currentPageNo > 0)) // IF pageRecordsCount is more then 0 then only pagination will be done
        {
            // Filter only the required page
            criteria.SetFirstResult((currentPageNo - 1) * pageRecordsCount);
            criteria.SetMaxResults(pageRecordsCount);
        }

        ProductViewList = criteria.List<ProductView>();

        criteria = session.CreateCriteria(typeof(ProductView));
        totalRecordCount = criteria.List<ProductView>().Count; //(int)criteria.SetProjection(Projections.RowCount()).UniqueResult();

        //return
        return ProductViewList;
    }
    catch (Exception ex)
    {
        // Log
        throw ex;
    }
}

'VB Code
Public Function GetProducts(ByVal currentPageNo As Integer, ByVal pageRecordsCount As Integer, ByVal sortBy As String, ByVal sortType__1 As SortType, ByRef totalRecordCount As Integer) As IList(Of ProductView)
    Try
        Dim ProductViewList As IList(Of ProductView) = Nothing

        ' Create the configuration object
        Dim cfg As New Configuration()
        cfg.Configure(GetConfigFilePath)

        ' Create the session
        Dim sessionFactory As ISessionFactory = cfg.BuildSessionFactory()
        ' Open the session
        Dim session As ISession = sessionFactory.OpenSession()

        ' Create Criteria
        Dim criteria As ICriteria = session.CreateCriteria(GetType(ProductView))

        If sortBy IsNot Nothing Then
            'If sortBy values pass (Pass null if not required)
            criteria.AddOrder(If(sortType__1 = SortType.Ascending, Order.Asc(sortBy), Order.Desc(sortBy)))
        End If

        If (pageRecordsCount > 0) AndAlso (currentPageNo > 0) Then
            ' IF pageRecordsCount is more then 0 then only pagination will be done
            ' Filter only the required page
            criteria.SetFirstResult((currentPageNo - 1) * pageRecordsCount)
            criteria.SetMaxResults(pageRecordsCount)
        End If

        ProductViewList = criteria.List(Of ProductView)()

        criteria = session.CreateCriteria(GetType(ProductView))
        totalRecordCount = criteria.List(Of ProductView)().Count
        '(int)criteria.SetProjection(Projections.RowCount()).UniqueResult();
        'return
        Return ProductViewList
    Catch ex As Exception
        ' Log
        Throw ex
    End Try
End Function

This code has been tested with IE 6.0/8.0, Chrome 10.0, Firefox 3.6, Opera 11.01

Here is the output of the example.

Initial Screen (by default Product Name sorted with first page)

Sorted by Category

Sorted in Desc order and showing 3rd page

Skip to 4th page (usage of Go page)

You can see the output in video here

download the working example of the source code in C# here and in VB here