Problem You need to display a 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. 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 2-13. Examples 2-33, 2-34 through 2-35 show the .aspx and code-behind files for an application that illustrates this approach; the application uses the stored procedure shown in Example 2-32 to retrieve the data to display. Discussion The solution we advocate for the problem of paging through large datasets requires a somewhat different approach to custom paging and managing the labels used to display the current and total pages. Figure 2-13. Paging through a record-heavy DataGrid output 
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 runat="server" BorderColor="#000080" BorderWidth="2px" AutoGenerateColumns="False" Width="90%" HorizontalAlign="Center" 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 used throughout the class. In the Page_Load event handler, the currentPage variable is initialized to 0 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. To improve performance, the event handlers first check to see if the page 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 2-32 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. If you are using SQL Server 2005, you may want to consider implementing the stored procedure in VB.NET or C# and use the new ExecutePagedReader method in the SqlCommand class. The Execute-PagedReader method provides the ability to retrieve only the data you need in an efficient manner. |
|
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 used to inform the user of the current page and total number of pages are initialized. See Also Recipe 2.8 Example 2-32. 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 2-33. Paging through a record-heavy DataGrid (.aspx) <%@ Page Language="VB" MasterPageFile="~/ASPNetCookbookVB.master" AutoEventWireup="false" CodeFile="CH02 LargeDatasetPagingVB1.aspx.vb" Inherits="ASPNetCookbook.VBExamples.CH02LargeDatasetPagingVB1" Title="DataGrid With Large Data Set Paging" %> <asp:Content runat="server" ContentPlaceHolder> <div align="center" > DataGrid With Large Data Set Paging (VB) </div> <asp:DataGrid runat="server" BorderColor="#000080" BorderWidth="2px" AutoGenerateColumns="False" Width="90%" HorizontalAlign="Center" AllowPaging="True" AllowCustomPaging="True" PageSize="10" PagerStyle-Visible="False"> <HeaderStyle HorizontalAlign="Center" Css /> <ItemStyle Css /> <AlternatingItemStyle Css /> <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> <table width="40%" border="0" align="center"> <tr> <td colspan="4" align="center"> Displaying page <asp:Literal runat="server" /> of <asp:Literal runat="server" /></td> </tr> <tr> <td align="center"> <input runat="server" type="button" value="First" onserverclick="btnFirst_ServerClick"/> </td> <td align="center"> <input runat="server" type="button" value="Prev" onserverclick="btnPrev_ServerClick"/> </td> <td align="center"> <input runat="server" type="button" value="Next" onserverclick="btnNext_ServerClick"/> </td> <td align="center"> <input runat="server" type="button" value="Last" onserverclick="btnLast_ServerClick"/> </td> </tr> </table> </asp:Content>
|
Example 2-34. Paging through a record-heavy DataGrid (.vb) Option Explicit On Option Strict On Imports Microsoft.VisualBasic Imports System.Configuration Imports System.Data Imports System.Data.OleDb Namespace ASPNetCookbook.VBExamples ''' <summary> ''' This class provides the code behind for ''' CH02 LargeDatasetPagingVB1.aspx ''' </summary> Partial Class CH02LargeDatasetPagingVB1 Inherits System.Web.UI.Page '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 '''*********************************************************************** ''' <summary> ''' This routine provides the event handler for the page load event. It ''' is responsible for initializing the controls on the page. ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Protected Sub Page_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.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 '''*********************************************************************** ''' <summary> ''' 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. ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Protected Sub btnFirst_ServerClick(ByVal sender As Object, _ ByVal e As System.EventArgs) 'set new page index and rebind the data If (currentPage > 0) Then currentPage = 0 bindData() End If End Sub 'btnFirst_ServerClick '''*********************************************************************** ''' <summary> ''' 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. ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Protected Sub btnPrev_ServerClick(ByVal sender As Object, _ ByVal e As System.EventArgs) 'set new page index and rebind the data If (currentPage > 0) Then currentPage -= 1 bindData() End If End Sub 'btnPrev_ServerClick '''*********************************************************************** ''' <summary> ''' 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. ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Protected Sub btnNext_ServerClick(ByVal sender As Object, _ ByVal e As System.EventArgs) 'set new page index and rebind the data If (currentPage < totalPages - 1) Then currentPage += 1 bindData() End If End Sub 'btnNext_ServerClick '''*********************************************************************** ''' <summary> ''' 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. ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Protected Sub btnLast_ServerClick(ByVal sender As Object, _ ByVal e As System.EventArgs) 'set new page index and rebind the data If (currentPage < totalPages - 1) Then currentPage = totalPages - 1 bindData() End If End Sub 'btnLast_ServerClick '''*********************************************************************** ''' <summary> ''' This routine queries the database for the data to displayed and binds ''' it to the datagrid ''' </summary> Private Sub bindData() Dim dbConn As OleDbConnection = Nothing Dim dCmd As OleDbCommand = Nothing Dim dReader As OleDbDataReader = Nothing Dim param As OleDbParameter = Nothing 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 = ConfigurationManager. _ ConnectionStrings("dbConnectionString").ConnectionString 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 'CH02 LargeDatasetPagingVB1 End Namespace
|
Example 2-35. Paging through a record-heavy DataGrid (.cs) using System; using System.Configuration; using System.Data; using System.Data.OleDb; using System.Web.UI.WebControls; namespace ASPNetCookbook.CSExamples { ///*********************************************************************** /// <summary> /// This class provides the code behind for /// CH02LargeDatasetPagingCS1.aspx /// </summary> public partial class CH02 LargeDatasetPagingCS1 : System.Web.UI.Page { // 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; ///*********************************************************************** /// <summary> /// This routine provides the event handler for the page load event. /// It is responsible for initializing the controls on the page. /// </summary> /// /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> protected void Page_Load(object sender, EventArgs e) { 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 ///*********************************************************************** /// <summary> /// 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. /// </summary> /// /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> protected void btnFirst_ServerClick(Object sender, System.EventArgs e) { // set new page index and rebind the data if (currentPage > 0) { currentPage = 0; bindData(); } } // btnFirst_ServerClick ///*********************************************************************** /// <summary> /// 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. /// </summary> /// /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> protected void btnPrev_ServerClick(Object sender, System.EventArgs e) { // set new page index and rebind the data if (currentPage > 0) { currentPage -= 1; bindData(); } } // btnPrev_ServerClick ///****************************************************************** /// <summary> /// 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. /// </summary> /// /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> protected void btnNext_ServerClick(Object sender, System.EventArgs e) { // set new page index and rebind the data if (currentPage < totalPages - 1) { currentPage += 1; bindData(); } } // btnNext_ServerClick ///*********************************************************************** /// <summary> /// 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. /// </summary> /// /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> protected void btnLast_ServerClick(Object sender, System.EventArgs e) { // set new page index and rebind the data if (currentPage < totalPages - 1) { currentPage = totalPages - 1; bindData(); } } // btnLast_ServerClick ///****************************************************************** /// <summary> /// This routine queries the database for the data to displayed and binds /// it to the datagrid /// </summary> 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 = ConfigurationManager. ConnectionStrings["dbConnectionString"].ConnectionString; 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 } // CH02LargeDatasetPagingCS1 }
|
|
