A Neat Solution to GridView Custom Paging

• Download PagingGridView.zip - 53.6 KB

Introduction

The GridView control in ASP.NET 2.0 provides great support for standard paging when binding to a datasource that contains more items than the pagesize. In a simple application with small data sets, this is often an adequate solution. However, in a larger project where you have up to 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 solution 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 adequte and elegant, but it doesn't work if your datasource is not an ObjectDataSource. In our current project, our datasource 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 similar experience, you will understand where I'm coming from and really appreciate this article.

I think a neat solution to the custom pagination is to allow the 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 the retrieving of the data block. However to be complete I have included example of retrieving 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 controls.

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 Explaination

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 datasource 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 the custom paging scenario every time we bind a new datasource (calling DataBind), PageIndex will reset to 0 if the number of items in the datasource is less than equal to the PageSize. In our case where we pull back page block data only, the number of items in the datasource is always going to be the same as the PageSize, hence PageIndex will always be reset. We solve this problem by introducting the CurrentPageIndex and we capture the value and store it to the ViewState everytime 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 detail on how to retrieve the paged data from database; instead it provides the information here for completeness of demonstrating the use of the PagingGridView control with data coming from SQL Server 2005 database. To keep thing simple in the sample code, all queries are written in code, avoiding the risk of SQL Injection or efficiency overheads compared to using stored procedure.

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 for 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 ROW_NUMBER() feature of SQL Server 2005. The SQL statement in this method retrieves 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 to retrieve a block of records for PageIndex = 3 where the PageSize is set to 20, the resultant block of records we want to display is row 61-80. Using the same example the SQL in the code below when executed will have an inner select that retrieve "TOP 80" rows, the outer select then filters out all the rows <= 60 through "ROW_NUM > 60" expression to return the 20 records (row 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 the demoConnString, demoTableName and demoTableDefaultOrderBy to reflect your data store.

In Action

To allow custom paging in your target page, you will have to set the VirtualItemCount in code or set in the property window of the PagingGridView control. In the example below, we set VirtualItemCount to a value returned by a method that returned 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 DataTable or GenericContainer, the data set should only contain the data items for that page otherwise we are just wasting all our effort of 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();
}