A Neat Solution to GridView Custom Paging

• Download source code - 40.4 KB

Introduction

The GridView control in ASP.NET 2.0 provides great support for standard paging when binding to a data source that contains more items than the page size. In a simple application with small data sets, this is often an adequate solution. However, in a larger project where you have like 10000 records to display, it is inefficient to pull back all the data to bind to the GridView using the standard built-in pagination feature. We need to have custom paging and deal with data blocks dynamically. Looking at the solutions available on CodeProject, MSDN, and Google, I found two suggestions. The first suggestion is to provide a custom pager where you will create your own page navigation controls, handle all the events, and deal with the display. The second suggestion emphasizes using the SelectCountMethod in ObjectDataSource to return the virtual item count, which the GridView will use for setting up its pagination. This suggestion seems adequate and elegant, but it doesn't work if your data source is not an ObjectDataSource. In our current project, our data source is mostly DataTable or a GenericContainer (such as List<T>) of some business objects that are returned through the business services tier, hence the ObjectDataSource solution doesn't fit in well. Many have experienced the same problem, and without a choice they resolved to create their own pager. This is very frustrating and if you have gone through a similar experience, you will understand where I'm coming from and really appreciate this article.

I think a neat solution to custom pagination is to allow setting of the VirtualItemCount property just like in DataGrid and not to restrict the user to only using ObjectDataSource while fully utilizing the paging display and interaction already built-in to the GridView. This inspired me to write the PagingGridView control and this article.

This article intentionally focuses on the GridView paging display and interaction aspects and not on retrieving of the data block. However, to be complete, I have included an example of retrieving a page block of rows from SQL Server 2005 using the ROW_NUMBER() feature.

Using the Code

If you include the PagingGridView in your ASP.NET Web Application project or a Class Library that is referenced by your ASP.NET Web Site project, the PagingGridView component will appear in your toolbox. To add it to your page, you can just drag and drop it the same way you would use any other web control.

Or if you wish to add in the code manually to the ASCX/ASPX file, first you have to register the tag in the beginning of the file and include the PagingGridView control element qualified by the TagPrefix. See the example below:

<%@ Register Assembly="PagingGridView" 
             Namespace="Fadrian.Web.Control" TagPrefix="cc1" %> 

... 

<cc1:PagingGridView ID="PagingGridView2" runat="server"/>

PagingGridView Code Explanation

Achieving the paging functionality described above is actually quite simple The key to the implementation lies in the InitializePager method. PagingGridView overrides this method and checks if CustomPaging is turned on. If custom paging is turned on, then we have a few settings to tweak so that the pager will render the virtual item in the data source and also the current page index correctly.

protected override void InitializePager(GridViewRow row, 
          int columnSpan, PagedDataSource pagedDataSource)
{
    // This method is called to initialise the pager
    // on the grid. We intercepted this and override
    // the values of pagedDataSource to achieve
    // the custom paging using the default pager supplied
    if (CustomPaging)
    {
        pagedDataSource.AllowCustomPaging = true;
        pagedDataSource.VirtualCount = VirtualItemCount;
        pagedDataSource.CurrentPageIndex = CurrentPageIndex;
    }
    base.InitializePager(row, columnSpan, pagedDataSource);
}

PagingGridView exposes a public property VirtualItemCount. The default value of this property is -1 and the user can set this value to any integer value. If this value is set to anything other than -1, the CustomPaging property will return true to indicate CustomPaging is turned on for this control.

public int VirtualItemCount
{
    get 
    {
        if (ViewState["pgv_vitemcount"] == null)
            ViewState["pgv_vitemcount"] = -1;
        return Convert.ToInt32(ViewState["pgv_vitemcount"]);
    }
    set { ViewState["pgv_vitemcount"] = value; }
}

private bool CustomPaging
{
    get { return (VirtualItemCount != -1); }
}

There is an internal property CurrentPageIndex in this control to store the current page index. The question raised here is why don't we just use PageIndex? PageIndex stores the current PageIndex of the GridView, but in a custom paging scenario, every time we bind a new data source (calling DataBind), PageIndex will reset to 0 if the number of items in the data source is less than or equal to the PageSize. In our case where we pull back page block data only, the number of items in the data source is always going to be the same as the PageSize, hence PageIndex will always be reset. We solve this problem by introducing the CurrentPageIndex and we capture the value and store it to the ViewState every time we set the DataSource.

private int CurrentPageIndex
{
    get
    {
        if (ViewState["pgv_pageindex"] == null)
            ViewState["pgv_pageindex"] = 0;
        return Convert.ToInt32(ViewState["pgv_pageindex"]);
    }
    set { ViewState["pgv_pageindex"] = value; }
} 

public override object DataSource 
{
   get { return base.DataSource; }
   set
   {
      base.DataSource = value;
      // we store the page index here so we dont lost it in databind
      CurrentPageIndex = PageIndex;
   }
}

Data Source and Paged Data

This article is not intended to go into the details of how to retrieve paged data from a database; instead, it provides information here for completeness of demonstrating the use of the PagingGridView control with data coming from a SQL Server 2005 database. To keep things simple in the sample code, all queries are written in code, avoiding the risk of SQL Injection or efficiency overheads compared to using Stored Procedures.

To support custom paging, we really need at least two things: the total number of records that we want to display (we set this to the VirtualItemCount) and the data that will be used to display the specific page item. The code below presents the GetRowCount method which is just a simple SELECT COUNT (*) SQL statement to retrieve the row count.

The GetDataPage method is implemented to retrieve a specific block of records for the specific page to display on the grid using the ROW_NUMBER() feature of SQL Server 2005. The SQL statement in this method retrieves the top x records of interest ordered by ROW_NUM in the inner Select statement, and the outer Select statement filters out the rows further using the WHERE clause. For example, if we are interested in retrieving a block of records for PageIndex = 3 where the PageSize is set to 20, the resultant block of records we want to display is rows 61-80. Using the same example, the SQL in the code below when executed will have an inner Select that retrieves the "TOP 80" rows, and the outer Select then filters out all the rows <= 60 through the "ROW_NUM > 60" expression to return the 20 records (rows 61-80).

For more information on using ROW_NUMBER(), please refer to MSDN or other online articles.

private const string demoConnString = 
     @"Integrated Security=SSPI;Persist Security Info=False;" + 
     @"Initial Catalog=NorthwindSQL;Data Source=localhost\SQLEXPRESS";
private const string demoTableName = "Customers";
private const string demoTableDefaultOrderBy = "CustomerID";
private int GetRowCount()
{
    using (SqlConnection conn = new SqlConnection(demoConnString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand(@"SELECT COUNT(*) FROM " + demoTableName, conn);
        int count = Convert.ToInt32(comm.ExecuteScalar());
        conn.Close();
        return count;
    }
} 

private DataTable GetDataPage(int pageIndex, int pageSize, string sortExpression)
{
    using (SqlConnection conn = new SqlConnection(demoConnString))
    {
        // We always need a default sort field for ROW_NUMBER() to work correctly
        if (sortExpression.Trim().Length == 0)
        sortExpression = demoTableDefaultOrderBy;
        conn.Open();

        string commandText = string.Format(
            "SELECT * FROM (select TOP {0} ROW_NUMBER() OVER (ORDER BY {1}) as ROW_NUM, * " 
            +"FROM {2} ORDER BY ROW_NUM) innerSelect WHERE ROW_NUM > {3}",
        ((pageIndex + 1) * pageSize), 
        sortExpression, 
        demoTableName,
        (pageIndex * pageSize)); 

        SqlDataAdapter adapter = new SqlDataAdapter(commandText, conn);
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        conn.Close();
        dt.Columns.Remove("ROW_NUM");
        return dt;
    }
}

In case you are wondering where the sample data for this article comes from, I created the NorthwindSQL database by opening the Northwind Access database and used the Upsizing wizard to create a new database (complete schema with data) in my SQL Server 2005 Express. You can repeat this process to recreate the data to test the code, or otherwise simply modify demoConnString, demoTableName, and demoTableDefaultOrderBy to reflect your data store.

In Action

To allow custom paging in your target page, you will have to set VirtualItemCount in code or set it in the property window of the PagingGridView control. In the example below, we set VirtualItemCount to a value returned by a method that returns the row count of the total records that we want to retrieve.

Syntactically, we code the DataSource and DataBind of the PagingGridView exactly the same as with GridView. All you need is to keep clearly in mind that when we are assigning the DataSource, whether it is a DataTable or a GenericContainer, the data set should only contain the data items for that page; otherwise, we are just wasting all our effort for enabling CustomPaging :)

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        PagingGridView1.VirtualItemCount = GetRowCount();
        BindPagingGrid();
    }
} 

protected void PagingGridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    PagingGridView1.PageIndex = e.NewPageIndex;
    BindPagingGrid();
} 

private void BindPagingGrid()
{
    PagingGridView1.DataSource = GetDataPage(PagingGridView1.PageIndex,
    PagingGridView1.PageSize, PagingGridView1.OrderBy);
    PagingGridView1.DataBind();
}