Custom DataGrid Paging at the Server
by Peter A. Bromberg, Ph.D.
Peter Bromberg
"We are all acting like there isn't an elephant in the room."
- Sen. Joe Biden, at opening of Samuel Alito Confirmation Hearings
The ASP.NET DataGrid's built-in Paging capabilty is a great feature, and it's relatively easy to learn to use. However, as most developers are aware, the downside is that this paging is done based on bringing back the entire resultset every time a new page is requested. Of course, you can cache the resultset to cut down on bandwidth.
But what if you have 50,000 or 100,000 rows that you need to offer your user to page through? That's a lot of baggage to bring along! Certainly you do not want to load and / or cache all this data when, for example, your user is really only looking at ten rows at a time on a paged DataGrid. What you really want to do is show them the total number of pages in a standard DataGrid Pager Bar arrangement, and send them only the ten rows representing the actual page that they have selected to see.
The answer to this question is to use Custom Paging. The DataGrid has two features that you need to use for this:
1) Set the AllowCustomPaging property to "true".
2) Set the VirtualItemCount to the total number of available rows from each query so that the Pager knows what to display on the Pager Bar.
The final piece of the equation is to have a stored procedure that can dynamically select the correct rows and only send back "one page" worth of rows, whatever your PageSize happens to be.
Fortunately, the code to implement custom paging in an ASP.NET page is trivial. The stored procedures necessary for this arrangement are not. However, there is a broad range of solutions and my philosophy is "keep it simple". So I will present my own version of a dynamically created stored proc to handle this where you can pass in parameters consisting of the table's PrimaryKey, the table name, the Current Page index, the number of rows per Page, and finally, if desired, a custom "WHERE" clause of your choosing. The stored proc will take care of the rest.
My version is a pretty short sproc - a lot shorter and simpler than many you will find. However, you may find that you need more, and if you do, then at least this solution will give you the basis from which to understand what you need for your custom solution.
What I'll do here is show the stored proc, fashioned to get rows from the Northwind Orders table, which almost everyone has handy. However, it can be used or modified to work with most any table. If you need rows from more than one table, then you'll need to modify this to include the proper JOIN syntax.
Now, the sproc:
CREATE PROC dbo.GetPagedData
@pageSize int,
@tablename varchar(100) ,
@PrimaryKey varchar(50) ,
@CurrentPage int ,
@WhereClause varchar(250)
AS
if(@WhereClause IS NULL or @WhereClause='') Set @WhereClause=' 1=1 '
Declare @sql nvarchar(4000)
declare @numrecs int
Set @numrecs=@pageSize*@currentPage
set @sql='SELECT TOP ' + cast(@pageSize as varchar(5))+' * FROM ' +@tablename
Set @sql =@sql + ' WHERE '+cast(@PrimaryKey as varchar(50))+ ' NOT IN (SELECT TOP '
set @sql=@sql+ cast(@numrecs as varchar(5))
set @sql=@Sql+ ' ' +@primarykey +' FROM ' +@tableName + ' WHERE '+@whereClause +
' ORDER BY ' +cast(@primarykey as varchar(50)) +' ) '
set @Sql=@sql + ' AND '+ @whereClause
Set @Sql=@Sql + ' ORDER BY '+cast(@primarykey as varchar(50))
--print @sql
EXEC sp_executeSql @sql
Set @sql='Select count(*) FROM ' +@tablename + ' WHERE ' +@whereclause
EXEC sp_executeSql @sql
As can be easily seen, all this sproc does is construct dynamic SQL to return @pageSize number of rows using the TOP Keyword along with a limiting SubSelect which also uses the TOP keyword, and it applies your where clause, if any. Finally it does a second Select to get the total number of rows that match the query. I use sp_ExecuteSql as it is more efficient. You can even see the commented "print @sql" line I used to ensure my dynamic sql turned out OK. I really don't like dynamic Sql, but like any other evil thing, it has its uses. Pretty simple!
The only other thing we need to look at is the codebehind for the page, which is also surprisingly simple:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Microsoft.ApplicationBlocks.Data;
namespace PagedData
{
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected int curPageSize =0;
private void Page_Load(object sender, System.EventArgs e)
{
curPageSize=this.DataGrid1.PageSize;
// if we just loaded the page for the first time, display page 1 of our data:
if(!IsPostBack)
BindGrid(curPageSize,1);
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}
private void InitializeComponent()
{
this.DataGrid1.PageIndexChanged+=new System.Web.UI.WebControls.DataGridPageChangedEventHandler(
this.DataGrid1_PageIndexChanged);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void BindGrid(int pageSize, int pageNumber)
{
string cnString= System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
/* sproc parameters: @pageSize int,
@tablename varchar(100),
@PrimaryKey varchar(50),
@CurrentPage int,
@WhereClause varchar(250) */
string tableName = "orders";
string primaryKey="orderid";
string whereClause="orderDate >='8/1/1997'";
// string whereClause=String.Empty ; // null string for no "where" clause
object[] parms = new object[] {pageSize,tableName,primaryKey, pageNumber, whereClause};
DataSet ds= SqlHelper.ExecuteDataset(cnString,"dbo.GetPagedData",parms);
// Our second datatable has 1 row, 1 column - the number of total rows available to page
// with custom paging, that gets assigned to the VirtualItemCount property:
this.DataGrid1.VirtualItemCount =Convert.ToInt32(ds.Tables[1].Rows[0][0]);
this.DataGrid1.DataSource=ds.Tables[0];
DataGrid1.CurrentPageIndex =pageNumber;
DataGrid1.DataBind();
}
private void DataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
// user clicks forward or back, just call BindGrid with the NewPageIndex:
BindGrid(this.curPageSize , e.NewPageIndex);
}
}
}
How it works:
The main part of the working code is the BindGrid method, which accepts a PageSize and the expected pageNumber (or PageIndex, in DataGridSpeak). It puts together the parameter list for the Stored Proc of PageSize, TableName, PrimaryKey (the column name), CurrentPage, and any Where Clause, then calls the SqlHelper ExecuteDataSet method to get the DataSet, which contains two tables: the first is our "Page" of data, and the second is a single row and column containing the count of total records that can be retrieved for this particular query. This is needed to store in the grid's VirtualItemCount, which it uses to construct the correct Pager Bar elements.
And of course, I use the MS Data Access Application Block "SqlHelper Class" for my data access. Two lines of code to make a stored proc call with parameters, and your SqlParameters get cached automatically for a performance boost to boot! Hey, if you can write better "Best Practices" data access code than this, knock yourself out - I can't!
Whenever a user clicks on a numbered page in the PagerBar, the PageIndexChanged event is fired, and it simply calls BindGrid again, supplying the curPageSize of the grid on the page, and the NewPageIndex. That's all there is to it! You can find a lot of solutions for server-side DataGrid paging. Most of them will be nowhere near as simple and easy to understand as this one!
You will note that I have left out exception handling. The sole purpose of this omission is to keep the code as simple and easy - to - understand as possible for the purpose of a tutorial. In "real life" you should always have good quality exception checking that looks for specific exception types (e.g. "SqlException") whereever in your code anything could possibly go wrong. And, you don't just catch an exception -- you make a business decision -- should I allow the app to continue, show a message to the user, or do I need to terminate the app?
Hope this is helpful to you in your travails.
Download the VS.NET 2003 Solution that accompanies this article
Posted at |
☆ |
Labels: Custom Paging , Query Optimization