1.10.1 Problem 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. 1.10.2 Solution 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. Figure 1-11. Paging through a record-heavy DataGrid output 
1.10.3 Discussion 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).  | 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
| | 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 . | | 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 Namespace
Example 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 }
| 