DataGrid Paging | Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)

Chapter 5 - Reading Data using the Dataset Object

byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

For displaying data with a large number of rows, it makes good sense to show only a limited number of records at a time, and to allow the user to 'walk through' the pages of records. For example, if we were binding the OrderDetails table of Northwind (which has over 2000 records) to a DataGrid, we would want to display a handful of these at a time, and to offer the user navigation hyperlinks to the other pages of records. ASP.NET and ADO.NET enable us to implement this feature with a minimum of fuss - and by adding a bit more code, to make it look quite attractive. We'll take a look at how to do this in outline, and then introduce a practical example.

First, and as usual, the DataGrid must be within a <form runat="server"> element. In order to navigate through the pages, we will have navigation buttons, and these buttons require the form.

Second, there are several new DataGrid properties that we need to work with:

Setting the AllowPaging attribute to True provides us with a paging footer containing Previous and Next hyperlinks
The PageSize attribute sets the number of rows to display on one page
CurrentPageIndex is a zero-based index that identifies the page currently being displayed (and therefore, through simple arithmetic, the number of the row at the top of the current page)

To show the next page at any stage in the proceedings, we simply set CurrentPageIndex to the index of the new page, and re-bind the data. The place to do this is in a handler that executes when a PageIndexChanged event occurs, as specified by the OnPageIndexChanged attribute.

Third, we must understand some more about the passage of information from the DataGrid object to our event handlers. Here, for example, when the DataGrid.PageIndexChanged event is fired, it sends (among other things) a value called NewPageIndex that contains the index of the page that should be displayed next.

Fourth, we have to get the right code in the right procedure. Of the three that follow, only Page_Load() has a fixed name; the names of the others are up to you:

DataFiller() creates the connection, data adapter, and DataTable objects, sets the data source for the DataGrid, and binds.
Page_Load() needs to call DataFiller() on the first occasion that the page is loaded, but otherwise does nothing at all.
GridPageChanged() sets the CurrentPageIndex to the index of the new page to be displayed before making a call to DataFiller().

With these four points in mind, let's have an example that will allow us to see how paging actually works.

Try It Out - DataGrid Paging

Our objective is simple: we want to show a DataGrid of Northwind products (showing names and ID numbers), but we only want to show eight products at a time. We want to offer the user the default hyperlinks to navigate to the next and previous set of products in the list.

In folder ch05, create a file named DataGrid_paging. aspx and enter the following code:

 <%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.SqlClient" %> <html>   <head><title>DataGrid Paging</title></head>   <body>     <h3>DataGrid Paging</h3>     <form runat="Server">       <asp:DataGrid  runat="server"                     AllowPaging="True"                     OnPageIndexChanged="GridPageChange"                     PageSize="8" />     </form>   </body> </html>

In the above, the only difference between a Literal and the Labels we've worked with before is that the former doesn't allow you to apply styles to its content.

 <script language="VB" runat="server"> Sub Page_Load(Source As Object, E As EventArgs)   If Not Page.IsPostBack Then     DataFiller()   End IF End Sub Sub DataFiller()   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")   Dim objConnection As New SqlConnection(strConnection)   Dim strSqlProducts As String = "SELECT ProductID, ProductName " & _                                  "FROM Products ORDER BY ProductID"   Dim objAdapter As New SqlDataAdapter(strSqlProducts, objConnection)   Dim objDataSet As New DataSet()   objAdapter.Fill(objDataSet, "dtProducts")   dgProducts.PagerStyle.NextPageText = "Next"   dgProducts.PagerStyle.PrevPageText = "Previous"   dgProducts.DataSource = objDataSet.Tables.Item("dtProducts")   dgProducts.DataBind() End Sub Sub GridPageChange(S As Object, E As DataGridPageChangedEventArgs)   dgProducts.CurrentPageIndex = E.NewPageIndex   DataFiller() End Sub </script>

When the above page is viewed, you'll see the following:
After clicking the Next hyperlink, you should the second screen:

How It Works

Starting with the HTML code, our first action is to switch on paging by setting the grid's AllowPaging attribute to "True". The default number of rows in a page is ten, but we'll change that to eight. Also, we set GridPageChange() to be the event handler that's called when the user goes to a different page.

         <form runat="Server">           <asp:DataGrid  runat="server"                         AllowPaging="True"                         OnPageIndexChanged="GridPageChange"                         PageSize="8" />         </form>

Moving on to DataFiller(), recall that our objective is simply to perform all of the actions normally required to fill a DataGrid, just as we've done before. By default, the first row displayed will be the first DataRow from the DataTable.

    Sub DataFiller()      Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")      Dim objConnection As New SqlConnection(strConnection)      Dim strSqlProducts As String = "SELECT ProductID, ProductName " & _                                     "FROM Products ORDER BY ProductID"      Dim objAdapter As New SglDataAdapter(strSqlProducts, objConnection)      Dim objDataSet As New DataSet()      objAdapter.Fill(objDataSet, "dtProducts")

Before binding to the DataGrid, we set the text for the 'next page' and 'previous page' links:

      dgProducts.PagerStyle.NextPageText = "Next"      dgProducts.PagerStyle.PrevPageText = "Previous"

Then we do finally bind our table to the DataGrid, and we're finished.

      dgProducts.DataSource = objDataSet.Tables.Item("dtProducts")      dgProducts.DataBind()    End Sub

From here, things get much easier - Page_Load(), for example, shows few changes from its incarnations in earlier examples. On the first occasion that the page is loaded, we call DataFiller(); the rest of the time, we do nothing at all.

    Sub Page_Load(Source As Object, E As EventArgs)      If Not Page.IsPostBack Then        DataFiller()      End If    End Sub

Finally, when the user clicks on a navigation button, GridPageChange() is called. All we do here is set the CurrentPageIndex to a specific value, and then call DataFiller() again. The "specific value" in question is the index number passed to the handler by the DataGrid; it will be one higher then the index of the page currently being shown.

    Sub GridPageChange(S As Object, E As DataGridPageChangedEventArgs)      dgProducts. CurrentPageIndex = E.NewPageIndex      DataFiller()    End Sub

To review paging, the procedure - which you'll surely have noticed to be very similar to that for sorting - is fairly straightforward, once you understand the theory. Keep in mind that several properties must be set in the DataGrid, and that you must separate out the DataGrid-filling code so that it can be executed at two junctures: when the page is first loaded, and when a navigation button is clicked.