You need to display a very large
set of data in a
DataGrid, yet the user must be able to page
through it quickly. This approach is beneficial anytime you have to
navigate through thousands of records.
Use custom paging with a DataGrid and, using a
stored procedure, read from the database only the data that is needed
for a given page. An example of the output that can be achieved with
this approach is shown in Figure 1-11. Examples
Example 1-27 through Example 1-29
show the .aspx and code-behind files for an
application that illustrates this approach; the application uses the
stored procedure shown in Example 1-26 to retrieve
the data to display.
The solution we advocate for the problem of paging through large datasets requires a somewhat different approach to both custom paging and managing the labels used to display the current and total pages.
To enable paging and to allow you to control movement within the
dataset when data binding, the
DataGrid’s
AllowPaging and
AllowCustomPaging attributes
must be set to
True. When AllowCustomPaging is
set to False (the default), the
DataGrid assumes that all of the data that can be
displayed in all pages is present in the data source, and it
calculates the group of records to display from the
CurrentPageIndex and PageSize
attributes. When AllowCustomPaging is set to
True, the DataGrid expects only
one page (as defined by the PageSize attribute) to
be present in the data source and you are responsible for filling the
data source with the proper page of data.
<asp:DataGrid id="dgBooks" runat="server" BorderColor="000080" BorderWidth="2px" AutoGenerateColumns="False" width="100%"AllowPaging="True"AllowCustomPaging="True"PageSize ="10"PagerStyle-Visible="False" >
For this example, the internal paging controls of the
DataGrid are not used, so the
PagerStyle-Visible attribute is set
False to hide the
DataGrid’s pager control.
A pair of labels is used to display the current page and total number of pages available. In addition, four buttons are used to provide navigation (First, Prev, Next, and Last).
Tip
If you want to use the internal paging functionality with custom
paging, the VirtualItemCount attribute must be set
to the total number of items that can be displayed in the
DataGrid (all pages). In addition, the
CurrentPageIndex attribute must be set to the
currently displayed page.
The code-behind uses two private variables to store the current page
and total number of pages that are used throughout the class. In the
Page_Load event handler, the
currentPage variable is initialized to zero when
the page is initially loaded, and then the
bindData method is called to populate the
DataGrid. When the page is being posted back, the
currentPage and totalPages
variables are set from the values in the labels used to display the
information to the user. The data binding is then done, as required,
by the specific event handlers.
Four event handler routines are included in the code-behind to handle
the click events for the four buttons. The event handlers alter the
currentPage variable as appropriate and rebind the
data. Note that, to improve performance, the event handlers first
check to see if the page really needs changing and rebinding.
With standard paging, all of the data is returned, even if there are
thousands of rows, and the DataGrid determines
which ones are displayed. In this case, however, the
bindData method uses the stored procedure shown in
Example 1-26 to retrieve only the data to be
displayed for the required page.
The stored procedure uses three parameters:
pageNumber, pageSize, and
totalRecords. The pageNumber is
an input parameter that defines the page to be displayed. The
pageSize is an input parameter that defines the
number of rows to be displayed per page; this must be the same as the
DataGrid’s
PageSize property. totalRecords
is an output parameter used to obtain the total number of rows of
data available for display.
The stored procedure first calculates the index of the first record and the last record to display in the requested page, as shown here:
SELECT @firstRecordInPage = @pageNumber * @pageSize + 1 SELECT @lastRecordInPage = @firstRecordInPage + @pageSize
A temporary table is then created to store the data from the Book table in the desired order. This table contains an Identity column that is set up to number the records from 1 to the total number of records added to the table. This provides the ability to select only the specific rows needed for the requested page number.
CREATE TABLE #Book ( [ID] [int] IDENTITY (1, 1) NOT NULL , [BookID] [int] NOT NULL , [Title] [nvarchar] (100) NOT NULL , [ISBN] [nvarchar] (50) NOT NULL , [Publisher] [nvarchar] (50) NOT NULL )
Next, the data from the Book table is copied into the temporary table and ordered by the book title. Now you have an ordered list of the books with the ID column set to 1 for the first book and N for the last book.
INSERT INTO #Book (BookID, Title, ISBN, Publisher) SELECT BookID, Title, ISBN, Publisher FROM Book ORDER BY Title
The next step is to query the temporary table for only the rows required for the page being displayed. This is done by qualifying the query based on the ID being within the range of the first and last records to display.
SELECT * FROM #Book WHERE ID >= @firstRecordInPage AND ID < @lastRecordInPage
Finally, you query the Book table for the total number of books and
set the totalRecords output parameter to the
count:
SELECT @totalRecords = COUNT(*) FROM Book
Tip
The stored procedure used here was kept simple to illustrate the concept of returning only the required data. One negative of the example code is that all of the data from the Book table is copied to the temporary table, unnecessarily bloating the table. One way to reduce the amount of data copied is to copy rows only up to the last row required, a modification you will want to consider when adapting this code to your unique environment.
The bindData method first
opens
a connection to the database. A command is then created to execute
the stored procedure, and the three parameters required for the
stored procedure are added to it. The command is then executed using
the ExecuteReader method and the returned data
reader is set as the data source for the DataGrid.
Tip
The returned DataReader must be closed to retrieve
the output parameter from the stored procedure. Attempting to access
the output parameter before the DataReader is
closed will return null.
Finally, the total number of records is retrieved from the parameter collection, and the labels on the form that are used to inform the user of the current page and total number of pages are initialized.
Example 1-26. Stored procedure for record-heavy DataGrid
CREATE PROCEDURE getPageData @pageNumber INT, @pageSize INT, @totalRecords INT OUTPUT AS DECLARE @firstRecordInPage INT DECLARE @lastRecordInPage INT -- Calculate the number of rows needed to get to the current page SELECT @firstRecordInPage = @pageNumber * @pageSize + 1 SELECT @lastRecordInPage = @firstRecordInPage + @pageSize -- Create a temporary table to copy the book data into. -- Include only the columns needed with an additional ID -- column that is the primary key of the temporary table. -- In addition, it is an identity that will number the -- records copied into the table starting with 1 thus allowing -- us to query only for the specific records needed for the -- requested page. CREATE TABLE #Book ( [ID] [int] IDENTITY (1, 1) NOT NULL , [BookID] [int] NOT NULL , [Title] [nvarchar] (100) NOT NULL , [ISBN] [nvarchar] (50) NOT NULL , [Publisher] [nvarchar] (50) NOT NULL ) -- Copy the data from the book table into the temp table INSERT INTO #Book (BookID, Title, ISBN, Publisher) SELECT BookID, Title, ISBN, Publisher FROM Book ORDER BY Title -- Get the rows required for the passed page SELECT * FROM #Book WHERE ID >= @firstRecordInPage AND ID < @lastRecordInPage -- Get the total number of records in the table SELECT @totalRecords = COUNT(*) FROM Book GO
Example 1-27. Paging through a record-heavy DataGrid (.aspx)
<%@ Page Language="vb" AutoEventWireup="false"
Codebehind="CH01LargeDatasetPagingVB.aspx.vb"
Inherits="ASPNetCookbook.VBExamples.CH01LargeDatasetPagingVB" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>DataGrid With Large Data Set Paging</title>
<link rel="stylesheet" href="css/ASPNetCookbook.css">
</head>
<body leftmargin="0" marginheight="0" marginwidth="0" topmargin="0">
<form id="frmData" method="post" runat="server">
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tr>
<td align="center">
<img src="images/ASPNETCookbookHeading_blue.gif">
</td>
</tr>
<tr>
<td class="dividerLine">
<img src="images/spacer.gif" height="6" border="0"></td>
</tr>
</table>
<table width="90%" align="center" border="0">
<tr>
<td><img src="images/spacer.gif" height="10" border="0"></td>
</tr>
<tr>
<td align="center" class="PageHeading">
DataGrid With Large Data Set Paging (VB)
</td>
</tr>
<tr>
<td><img src="images/spacer.gif" height="10" border="0"></td>
</tr>
<tr>
<td align="center">
<asp:DataGrid
id="dgBooks"
runat="server"
BorderColor="000080"
BorderWidth="2px"
AutoGenerateColumns="False"
width="100%"
AllowPaging="True"
AllowCustomPaging="True"
PageSize ="10"
PagerStyle-Visible="False" >
<HeaderStyle
HorizontalAlign="Center"
ForeColor="#FFFFFF"
BackColor="#000080"
Font-Bold=true
CssClass="TableHeader" />
<ItemStyle
BackColor="#FFFFE0"
cssClass="TableCellNormal" />
<AlternatingItemStyle
BackColor="#FFFFFF"
cssClass="TableCellAlternating" />
<Columns>
<asp:BoundColumn HeaderText="Title" DataField="Title" />
<asp:BoundColumn HeaderText="ISBN" DataField="ISBN"
ItemStyle-HorizontalAlign="Center" />
<asp:BoundColumn HeaderText="Publisher" DataField="Publisher"
ItemStyle-HorizontalAlign="Center" />
</Columns>
</asp:DataGrid>
</td>
</tr>
<tr>
<td align="center">
<table width="70%" border="0">
<tr>
<td colspan="4" align="center">
Displaying page
<asp:Literal id="labCurrentPage" runat="server" /> of
<asp:Literal id="labTotalPages" runat="server" /></td>
</tr>
<tr>
<td align="center">
<input type="image" id="btnFirst" runat="server"
src="images/buttons/button_first.gif"></td>
<td align="center">
<input type="image" id="btnPrev" runat="server"
src="images/buttons/button_prev.gif"></td>
<td align="center">
<input type="image" id="btnNext" runat="server"
src="images/buttons/button_next.gif"></td>
<td align="center">
<input type="image" id="btnLast" runat="server"
src="images/buttons/button_last.gif"></td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</body>
</html>Example 1-28. Paging through a record-heavy DataGrid (.vb)
Option Explicit On
Option Strict On
'-----------------------------------------------------------------------------
'
' Module Name: CH01LargeDatasetPagingVB.aspx.vb
'
' Description: This class provides the code behind for
' CH01LargeDatasetPagingVB.aspx
'
'*****************************************************************************
Imports Microsoft.VisualBasic
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.OleDb
Namespace ASPNetCookbook.VBExamples
Public Class CH01LargeDatasetPagingVB
Inherits System.Web.UI.Page
'controls on form
Protected dgBooks As System.Web.UI.WebControls.DataGrid
Protected labCurrentPage As System.Web.UI.WebControls.Literal
Protected labTotalPages As System.Web.UI.WebControls.Literal
Protected WithEvents btnPrev As System.Web.UI.HtmlControls.HtmlInputImage
Protected WithEvents btnNext As System.Web.UI.HtmlControls.HtmlInputImage
Protected WithEvents btnLast As System.Web.UI.HtmlControls.HtmlInputImage
Protected WithEvents btnFirst As System.Web.UI.HtmlControls.HtmlInputImage
'private variables used to store the current page and total number of
'pages. This is required since the CurrentPageIndex and PageCount
'properties of the datagrid cannot be used with custom paging
Private currentPage As Integer
Private totalPages As Integer
'*************************************************************************
'
' ROUTINE: Page_Load
'
' DESCRIPTION: This routine provides the event handler for the page load
' event. It is responsible for initializing the controls
' on the page.
'-------------------------------------------------------------------------
Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
If (Page.IsPostBack) Then
'This is a post back so initialize the current and total page variables
'with the values currently being displayed
currentPage = CInt(labCurrentPage.Text) - 1 'zero based page numbers
totalPages = CInt(labTotalPages.Text)
Else
'This is the first rendering of the form so set the current page to the
'first page and bind the data
currentPage = 0
bindData( )
End If
End Sub 'Page_Load
'*************************************************************************
'
' ROUTINE: btnFirst_ServerClick
'
' DESCRIPTION: This routine provides the event handler for the first
' button click event. It is responsible for setting the
' page index to the first page and rebinding the data.
'-------------------------------------------------------------------------
Private Sub btnFirst_ServerClick(ByVal sender As Object, _
ByVal e As System.Web.UI.ImageClickEventArgs) _
Handles btnFirst.ServerClick
'set new page index and rebind the data
If (currentPage > 0) Then
currentPage = 0
bindData( )
End If
End Sub 'btnFirst_ServerClick
'*************************************************************************
'
' ROUTINE: btnPrev_ServerClick
'
' DESCRIPTION: This routine provides the event handler for the previous
' button click event. It is responsible for setting the
' page index to the previous page and rebinding the data.
'-------------------------------------------------------------------------
Private Sub btnPrev_ServerClick(ByVal sender As Object, _
ByVal e As System.Web.UI.ImageClickEventArgs) _
Handles btnPrev.ServerClick
'set new page index and rebind the data
If (currentPage > 0) Then
currentPage -= 1
bindData( )
End If
End Sub 'btnPrev_ServerClick
'*************************************************************************
'
' ROUTINE: btnNext_ServerClick
'
' DESCRIPTION: This routine provides the event handler for the next
' button click event. It is responsible for setting the
' page index to the next page and rebinding the data.
'-------------------------------------------------------------------------
Private Sub btnNext_ServerClick(ByVal sender As Object, _
ByVal e As System.Web.UI.ImageClickEventArgs) _
Handles btnNext.ServerClick
'set new page index and rebind the data
If (currentPage < totalPages - 1) Then
currentPage += 1
bindData( )
End If
End Sub 'btnNext_ServerClick
'*************************************************************************
'
' ROUTINE: btnLast_ServerClick
'
' DESCRIPTION: This routine provides the event handler for the last
' button click event. It is responsible for setting the
' page index to the last page and rebinding the data.
'-------------------------------------------------------------------------
Private Sub btnLast_ServerClick(ByVal sender As Object, _
ByVal e As System.Web.UI.ImageClickEventArgs) _
Handles btnLast.ServerClick
'set new page index and rebind the data
If (currentPage < totalPages - 1) Then
currentPage = totalPages - 1
bindData( )
End If
End Sub 'btnLast_ServerClick
'*************************************************************************
'
' ROUTINE: bindData
'
' DESCRIPTION: This routine queries the database for the data to
' displayed and binds it to the datagrid
'-------------------------------------------------------------------------
Private Sub bindData( )
Dim dbConn As OleDbConnection
Dim dCmd As OleDbCommand
Dim dReader As OleDbDataReader
Dim param As OleDbParameter
Dim strConnection As String
Dim strSQL As String
Dim totalRecords As Integer
Try
'get the connection string from web.config and open a connection
'to the database
strConnection = _
ConfigurationSettings.AppSettings("dbConnectionString")
dbConn = New OleDb.OleDbConnection(strConnection)
dbConn.Open( )
'create command to execute the stored procedure along with the
'parameters required in and out of the procedure
strSQL = "getPageData" 'name of stored procedure
dCmd = New OleDbCommand(strSQL, dbConn)
dCmd.CommandType = CommandType.StoredProcedure
param = dCmd.Parameters.Add("pageNumber", OleDbType.Integer)
param.Direction = ParameterDirection.Input
param.Value = currentPage
param = dCmd.Parameters.Add("pageSize", OleDbType.Integer)
param.Direction = ParameterDirection.Input
param.Value = dgBooks.PageSize
param = dCmd.Parameters.Add("totalRecords", OleDbType.Integer)
param.Direction = ParameterDirection.Output
'execute the stored procedure and set the datasource for the datagrid
dReader = dCmd.ExecuteReader( )
dgBooks.DataSource = dReader
dgBooks.DataBind( )
'close the dataReader to make the output parameter available
dReader.Close( )
'output information about the current page and total number of pages
totalRecords = CInt(dCmd.Parameters.Item("totalRecords").Value)
totalPages = CInt(Math.Ceiling(totalRecords / dgBooks.PageSize))
labTotalPages.Text = totalPages.ToString( )
labCurrentPage.Text = (currentPage + 1).ToString( )
Finally
'cleanup
If (Not IsNothing(dReader)) Then
dReader.Close( )
End If
If (Not IsNothing(dbConn)) Then
dbConn.Close( )
End If
End Try
End Sub 'bindData
End Class 'CH01LargeDatasetPagingVB
End NamespaceExample 1-29. Paging through a record-heavy DataGrid (.cs)
//----------------------------------------------------------------------------
//
// Module Name: CH01LargeDatasetPagingCS.aspx.cs
//
// Description: This class provides the code behind for
// CH01LargeDatasetPagingCS.aspx
//
//****************************************************************************
using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
namespace ASPNetCookbook.CSExamples
{
public class CH01LargeDatasetPagingCS : System.Web.UI.Page
{
// controls on form
protected System.Web.UI.HtmlControls.HtmlInputImage btnFirst;
protected System.Web.UI.HtmlControls.HtmlInputImage btnPrev;
protected System.Web.UI.HtmlControls.HtmlInputImage btnNext;
protected System.Web.UI.HtmlControls.HtmlInputImage btnLast;
protected System.Web.UI.WebControls.DataGrid dgBooks;
protected System.Web.UI.WebControls.Literal labCurrentPage;
protected System.Web.UI.WebControls.Literal labTotalPages;
// private variables used to store the current page and total number of
// pages. This is required since the CurrentPageIndex and PageCount
// properties of the datagrid cannot be used with custom paging
private int currentPage;
private int totalPages;
//************************************************************************
//
// ROUTINE: Page_Load
//
// DESCRIPTION: This routine provides the event handler for the page
// load event. It is responsible for initializing the
// controls on page.
//------------------------------------------------------------------------
private void Page_Load(object sender, System.EventArgs e)
{
// wire in the button click events
this.btnFirst.ServerClick +=
new System.Web.UI.ImageClickEventHandler(this.btnFirst_ServerClick);
this.btnPrev.ServerClick +=
new System.Web.UI.ImageClickEventHandler(this.btnPrev_ServerClick);
this.btnNext.ServerClick +=
new System.Web.UI.ImageClickEventHandler(this.btnNext_ServerClick);
this.btnLast.ServerClick +=
new System.Web.UI.ImageClickEventHandler(this.btnLast_ServerClick);
if (Page.IsPostBack)
{
// This is a post back so initialize the current and total page
// variables with the values currently being displayed
currentPage = Convert.ToInt32(labCurrentPage.Text) - 1;
totalPages = Convert.ToInt32(labTotalPages.Text);
}
else
{
// This is the first rendering of the form so set the current page to
// the first page and bind the data
currentPage = 0;
bindData( );
}
} //Page_Load
//************************************************************************
//
// ROUTINE: btnFirst_ServerClick
//
// DESCRIPTION: This routine provides the event handler for the first
// button click event. It is responsible for setting the
// page index to the first page and rebinding the data.
//------------------------------------------------------------------------
private void btnFirst_ServerClick(Object sender,
System.Web.UI.ImageClickEventArgs e)
{
// set new page index and rebind the data
if (currentPage > 0)
{
currentPage = 0;
bindData( );
}
} //btnFirst_ServerClick
//************************************************************************
//
// ROUTINE: btnPrev_ServerClick
//
// DESCRIPTION: This routine provides the event handler for the previous
// button click event. It is responsible for setting the
// page index to the previous page and rebinding the data.
//------------------------------------------------------------------------
private void btnPrev_ServerClick(Object sender,
System.Web.UI.ImageClickEventArgs e)
{
// set new page index and rebind the data
if (currentPage > 0)
{
currentPage -= 1;
bindData( );
}
} //btnPrev_ServerClick
//************************************************************************
//
// ROUTINE: btnNext_ServerClick
//
// DESCRIPTION: This routine provides the event handler for the next
// button click event. It is responsible for setting the
// page index to the next page and rebinding the data.
//------------------------------------------------------------------------
private void btnNext_ServerClick(Object sender,
System.Web.UI.ImageClickEventArgs e)
{
// set new page index and rebind the data
if (currentPage < totalPages - 1)
{
currentPage += 1;
bindData( );
}
} //btnNext_ServerClick
//************************************************************************
//
// ROUTINE: btnLast_ServerClick
//
// DESCRIPTION: This routine provides the event handler for the last
// button click event. It is responsible for setting the
// page index to the last page and rebinding the data.
//------------------------------------------------------------------------
private void btnLast_ServerClick(Object sender,
System.Web.UI.ImageClickEventArgs e)
{
// set new page index and rebind the data
if (currentPage < totalPages - 1)
{
currentPage = totalPages - 1;
bindData( );
}
} //btnLast_ServerClick
//************************************************************************
//
// ROUTINE: bindData
//
// DESCRIPTION: This routine queries the database for the data to
// displayed and binds it to the repeater
//------------------------------------------------------------------------
private void bindData( )
{
OleDbConnection dbConn = null;
OleDbCommand dCmd = null;
OleDbDataReader dReader = null;
OleDbParameter param = null;
String strConnection = null;
String strSQL = null;
int totalRecords = 0;
try
{
// get the connection string from web.config and open a connection
// to the database
strConnection =
ConfigurationSettings.AppSettings["dbConnectionString"];
dbConn = new OleDbConnection(strConnection);
dbConn.Open( );
// create command to execute the stored procedure along with the
// parameters required in/out of the procedure
strSQL = "getPageData"; // name of stored procedure
dCmd = new OleDbCommand(strSQL, dbConn);
dCmd.CommandType = CommandType.StoredProcedure;
param = dCmd.Parameters.Add("pageNumber", OleDbType.Integer);
param.Direction = ParameterDirection.Input;
param.Value = currentPage;
param = dCmd.Parameters.Add("pageSize", OleDbType.Integer);
param.Direction = ParameterDirection.Input;
param.Value = dgBooks.PageSize;
param = dCmd.Parameters.Add("totalRecords", OleDbType.Integer);
param.Direction = ParameterDirection.Output;
//execute the stored procedure and set the datasource for the datagrid
dReader = dCmd.ExecuteReader( );
dgBooks.DataSource = dReader;
dgBooks.DataBind( );
// close the dataReader to make the output parameter available
dReader.Close( );
// output information about the current page and total number of pages
totalRecords = Convert.ToInt32(dCmd.Parameters["totalRecords"].Value);
totalPages = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(totalRecords) /
dgBooks.PageSize));
labTotalPages.Text = totalPages.ToString( );
labCurrentPage.Text = (currentPage + 1).ToString( );
} // try
finally
{
//clean up
if (dReader != null)
{
dReader.Close( );
}
if (dbConn != null)
{
dbConn.Close( );
}
} // finally
} // bindData
} // CH01LargeDatasetPagingCS
}Get ASP.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
