Recipe 1.10 Paging Through a Record-Heavy DataGrid

     

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
figs/ancb_0111.gif

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 } 



ASP. NET Cookbook
ASP.Net 2.0 Cookbook (Cookbooks (OReilly))
ISBN: 0596100647
EAN: 2147483647
Year: 2006
Pages: 179

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net