Database Pagination in GridView using SQL Store Procedure

Tweet

I have blogged a post on Database pagination in GridView using nHibernate before. But if someone not using nHibernate and using Database Store Procedure, this post will be useful. In this post I am using SQL Server 2008 (This method also will work SQL Server 2005 and higher versions).

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

Store Procedure

CREATE PROCEDURE [dbo].[SP_GET_PRODUCTS]
@CurrentPageNo AS INT,
@PageRecordsCount AS INT,
@SortBy AS NVARCHAR(250),
@SortType AS NVARCHAR(250),
@TotalRecordCount AS INT OUTPUT
AS

SET NOCOUNT ON

DECLARE @SQL AS NVARCHAR(1000)
DECLARE @RecordsFrom AS INT
DECLARE @RecordsTo AS INT

-- RecordsFrom => For Ex: CurrentPage 2, PageRecordCount = 10, then (((2-1) * 10) + 1) => ((1 * 10) + 1) => 11
SET @RecordsFrom = (((@CurrentPageNo - 1) * @PageRecordsCount) + 1) 

-- @RecordsTo => For Ex: CurrentPage 2, PageRecordCount = 10, then (2 * 10) => 20
SET @RecordsTo = (@CurrentPageNo * @PageRecordsCount)

IF @SortType = '' OR @SortType = NULL
 SET @SortType = 'Asc'

-- Getting the Total Record Count
SELECT @TotalRecordCount = COUNT(*) FROM ProductView

-- Getting the records for a particular page
SET @SQL = 
  'SELECT * FROM ' +
  '( ' +
  ' SELECT ' +
  '  ROW_NUMBER() OVER (ORDER BY ' + @SortBy + ' ' +  @SortType + ') AS RowNumber, *' +
  ' FROM ProductView ' +
  ') AS RECORDS ' +
  'Where RowNumber between ' + CAST(@RecordsFrom AS VARCHAR) + ' and ' + CAST(@RecordsTo AS VARCHAR)

EXECUTE(@SQL)

Note: As I have created a View, I have used View in SP. Incase if you don’t follow creating View, you can use the following Store Procedure (infact both are same).

CREATE PROCEDURE [dbo].[SP_GET_PRODUCTS]
@CurrentPageNo AS INT,
@PageRecordsCount AS INT,
@SortBy AS NVARCHAR(250),
@SortType AS NVARCHAR(250),
@TotalRecordCount AS INT OUTPUT
AS

SET NOCOUNT ON

DECLARE @SQL AS NVARCHAR(1000)
DECLARE @RecordsFrom AS INT
DECLARE @RecordsTo AS INT

-- RecordsFrom => For Ex: CurrentPage 2, PageRecordCount = 10, then (((2-1) * 10) + 1) => ((1 * 10) + 1) => 11
SET @RecordsFrom = (((@CurrentPageNo - 1) * @PageRecordsCount) + 1) 

-- @RecordsTo => For Ex: CurrentPage 2, PageRecordCount = 10, then (2 * 10) => 20
SET @RecordsTo = (@CurrentPageNo * @PageRecordsCount)

IF @SortType = '' OR @SortType = NULL
 SET @SortType = 'Asc'

-- Getting the Total Record Count
-- SELECT @TotalRecordCount = COUNT(*) FROM ProductView

-- Getting the records for a particular page
SET @SQL = 
  'SELECT * FROM ' +
  '( ' +
  ' SELECT ' +
  '  ROW_NUMBER() OVER (ORDER BY ' + @SortBy + ' ' + @SortType + ') AS RowNumber, ' +
  '  Products.ProductID, ' +
  '  Products.ProductName, ' +
  '  Suppliers.CompanyName, ' +
  '  Categories.CategoryName, ' +
  '  Products.QuantityPerUnit, ' +
  '  Products.UnitPrice, ' +
  '  Products.UnitsInStock, ' +
  '  Products.UnitsOnOrder, ' +
  '  Products.ReorderLevel ' +
  ' FROM Products ' +
  ' INNER JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID ' +
  ' INNER JOIN Categories ON Categories.CategoryID = Products.CategoryID ' +
  ') AS RECORDS ' +
  'Where RowNumber between ' + CAST(@RecordsFrom AS VARCHAR) + ' and ' + CAST(@RecordsTo AS VARCHAR)
  
 EXECUTE(@SQL)
SET NOCOUNT OFF

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

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)
{
    SqlConnection connection = null;
    SqlDataReader dataReader = null;

    try
    {
        IList<ProductView> productViewList = new List<ProductView>();
        connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());

        SqlDataAdapter dataAdaptor = new SqlDataAdapter("SP_GET_PRODUCTS", connection);
        dataAdaptor.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

        dataAdaptor.SelectCommand.Parameters.Add(new SqlParameter("@CurrentPageNo", SqlDbType.Int));
        dataAdaptor.SelectCommand.Parameters["@CurrentPageNo"].Value = currentPageNo;

        dataAdaptor.SelectCommand.Parameters.Add(new SqlParameter("@PageRecordsCount", SqlDbType.Int));
        dataAdaptor.SelectCommand.Parameters["@PageRecordsCount"].Value = pageRecordsCount;

        dataAdaptor.SelectCommand.Parameters.Add(new SqlParameter("@SortBy", SqlDbType.VarChar));
        dataAdaptor.SelectCommand.Parameters["@SortBy"].Value = sortBy;

        dataAdaptor.SelectCommand.Parameters.Add(new SqlParameter("@SortType", SqlDbType.VarChar));
        dataAdaptor.SelectCommand.Parameters["@SortType"].Value = (sortType == SortType.Ascending ? "Asc" : "Desc");

        dataAdaptor.SelectCommand.Parameters.Add(new SqlParameter("@TotalRecordCount", SqlDbType.Int));
        dataAdaptor.SelectCommand.Parameters["@TotalRecordCount"].Direction = ParameterDirection.Output;
        connection.Open();

        DataSet dataSet = new DataSet();
        dataAdaptor.Fill(dataSet);

        foreach(DataRow dataRow in dataSet.Tables[0].Rows)
        {
            ProductView productView = new ProductView();
            productView.ProductID = Convert.ToInt32(dataRow["ProductID"].ToString());
            productView.ProductName = dataRow["ProductName"].ToString();
            productView.CompanyName = dataRow["CompanyName"].ToString();
            productView.CategoryName = dataRow["CategoryName"].ToString();
            productView.QuantityPerUnit = dataRow["QuantityPerUnit"].ToString();
            productView.UnitPrice = Convert.ToDouble(dataRow["UnitPrice"].ToString());
            productView.UnitsInStock = Convert.ToInt32(dataRow["UnitsInStock"].ToString());
            productView.UnitsOnOrder = Convert.ToInt32(dataRow["UnitsOnOrder"].ToString());
            productView.ReorderLevel = Convert.ToInt32(dataRow["ReorderLevel"].ToString());

            productViewList.Add(productView);
        }

        totalRecordCount = Convert.ToInt32(dataAdaptor.SelectCommand.Parameters["@TotalRecordCount"].Value);
        connection.Close();

        //return
        return productViewList;
    }
    catch (Exception ex)
    {
        if (connection != null) connection.Close();
        if (dataReader != null) dataReader.Close();

        // 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)
        Dim connection As SqlConnection = Nothing
        Dim dataReader As SqlDataReader = Nothing

        Try
            Dim productViewList As IList(Of ProductView) = New List(Of ProductView)()
            connection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())

            Dim dataAdaptor As New SqlDataAdapter("SP_GET_PRODUCTS", connection)
            dataAdaptor.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure

            dataAdaptor.SelectCommand.Parameters.Add(New SqlParameter("@CurrentPageNo", SqlDbType.Int))
            dataAdaptor.SelectCommand.Parameters("@CurrentPageNo").Value = currentPageNo

            dataAdaptor.SelectCommand.Parameters.Add(New SqlParameter("@PageRecordsCount", SqlDbType.Int))
            dataAdaptor.SelectCommand.Parameters("@PageRecordsCount").Value = pageRecordsCount

            dataAdaptor.SelectCommand.Parameters.Add(New SqlParameter("@SortBy", SqlDbType.VarChar))
            dataAdaptor.SelectCommand.Parameters("@SortBy").Value = sortBy

            dataAdaptor.SelectCommand.Parameters.Add(New SqlParameter("@SortType", SqlDbType.VarChar))
            dataAdaptor.SelectCommand.Parameters("@SortType").Value = (If(sortType__1 = SortType.Ascending, "Asc", "Desc"))

            dataAdaptor.SelectCommand.Parameters.Add(New SqlParameter("@TotalRecordCount", SqlDbType.Int))
            dataAdaptor.SelectCommand.Parameters("@TotalRecordCount").Direction = ParameterDirection.Output
            connection.Open()

            Dim dataSet As New DataSet()
            dataAdaptor.Fill(dataSet)

            For Each dataRow As DataRow In dataSet.Tables(0).Rows
                Dim productView As New ProductView()
                productView.ProductID = Convert.ToInt32(dataRow("ProductID").ToString())
                productView.ProductName = dataRow("ProductName").ToString()
                productView.CompanyName = dataRow("CompanyName").ToString()
                productView.CategoryName = dataRow("CategoryName").ToString()
                productView.QuantityPerUnit = dataRow("QuantityPerUnit").ToString()
                productView.UnitPrice = Convert.ToDouble(dataRow("UnitPrice").ToString())
                productView.UnitsInStock = Convert.ToInt32(dataRow("UnitsInStock").ToString())
                productView.UnitsOnOrder = Convert.ToInt32(dataRow("UnitsOnOrder").ToString())
                productView.ReorderLevel = Convert.ToInt32(dataRow("ReorderLevel").ToString())

                productViewList.Add(productView)
            Next

            totalRecordCount = Convert.ToInt32(dataAdaptor.SelectCommand.Parameters("@TotalRecordCount").Value)
            connection.Close()

            'return
            Return productViewList
        Catch ex As Exception
            If connection IsNot Nothing Then
                connection.Close()
            End If
            If dataReader IsNot Nothing Then
                dataReader.Close()
            End If

            ' 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