Paging Database Results in ASP.NET
By Scott Mitchell


--------------------------------------------------------------------------------





--------------------------------------------------------------------------------

In Part 1 we looked at how to bind a DataSet to the DataGrid Web control. However, in our live demo we noted that the sheer number of results made the data hard to consume for visitors. Ideally, we'd like to page this data. In this part, we'll look at how to implement paging using the DataGrid Web control. It is surprisingly easy!

Database Paging with a DataGrid Web Control
To implement paging with the DataGrid Web control, perform the following simple steps:


Set the AllowPaging property of the DataGrid Web control to True.

Set the OnPageIndexChanged event handler of the DataGrid Web control to a Page-level event handler that contains the following definition:
Sub EventHandlerName(sender as Object, e as DataGridPageChangedEventArgs)
   ...
End Sub





That's all there is to it! So, in order to make the DataGrid we examined in Part 1 able to page data, we must first set the needed properties and event handlers of the DataGrid Web control. The following HTML content illustrates these changes:

<asp:datagrid id="dgPopularFAQs" runat="server" BorderWidth="0"
              CellPadding="2" Width="100%"
              Font-Name="Verdana"
              Font-Size="Smaller"
              AutoGenerateColumns="False"
                
              HeaderStyle-HorizontalAlign="Center"
              HeaderStyle-Font-Bold="True"
              HeaderStyle-BackColor="Navy"
              HeaderStyle-ForeColor="White"
                
              AlternatingItemStyle-BackColor="#dddddd"
                
              AllowPaging="True"
              PageSize="15"
              OnPageIndexChanged="dgPopularFAQs_Paged">
   ...
</asp:datagrid>        




Note that I also took a moment to set the PageSize property to a value of 15. This property indicates how many records to show per page; if not specified, it defaults to a value of 10. Now all we need to do is provide the event handler for when a page index is changed, dgPopularFAQs_Paged. The code for this event handler is painfully simple: all we need to do is set the DataGrid Web control's CurrentPageIndex property to the value of the new page, which is passed in through the DataGridPageChangedEventArgs parameter of the event handler.

<script language="vb" runat="server">

  ... Other functions/subs omitted for brevity ...

  Sub dgPopularFAQs_Paged(sender as Object , e as DataGridPageChangedEventArgs)
    dgPopularFAQs.CurrentPageIndex = e.NewPageIndex
    BindData()
  End Sub
</script>



Note that we need to recreate and rebind our DataSet to the DataGrid Web control after we set the DataGrid Web control's CurrentPageIndex property. Also note that we'll want to change the Page_Load event handler so that the BindData() subroutine is only called when the page is first visited. On any postbacks, the dgPopularFAQs_Paged event handler will handle recreating the DataSet and rebinding it to the DataGrid Web control. To implement this change in the Page_Load event handler, use the following code:

<script language="vb" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    If Not Page.IsPostBack then
      BindData()
    End If    
  End Sub

  ... Other functions/subs omitted for brevity ...
</script>



That's all that's needed! A live demo of the paged DataGrid Web control can be seen; note that in the live demo some further enhancements are made to the DataGrid Web control in order to improve the end output. Additionally, the DataGrid employs a PagerStyle tag, which provides for a more customized output of the paging controls.

Caveats
The most important thing to realize when paging data with the DataGrid's AllowPaging property is that each time the user navigates to a new page the entire DataSet is rebuilt. While this is not a big concern for small DataSets, imagine that you are wanting to page the results of a SQL query that results in, say, 1,000 rows. Such an approach would be taxing, since the database would have to rebuild a 1,000-row DataSet each and every time the user visited any page of the data. The DataGrid Web control provides an AllowCustomPaging option that does not suffer from this limitation. To learn more about custom paging, consult the documentation.

Also note that to utilize the default paging with a DataGrid Web control you must use a DataSet. That is, if you set AllowPaging to True but do not employ custom paging, you cannot bind a SqlDataReader or OleDbDataReader to the DataGrid Web control and implement paging. If you attempt to do so you will receive an error along the lines of: "To support paging, you must use an object that supports the ICollection interface."

This error message occurs because the DataReader objects do not support the ICollection interface. Note that the DataSet does not support this intercace directly either; however, the DataSet does support the IListSource interface, which is inherited from the IList interface, which is inherited from the ICollection interface. Hence, the DataSet can be used to implement paging with the DataGrid Web control.

Conclusion
As this article has (hopefully) illustrated, paging database data with ASP.NET is painfully easy. By using the DataGrid Web control and just a few lines of code, you can simply implement a nice-looking, easy-to-use paging system. Compare this technique and output to the mountains of classic ASP script code that was required. Bleh. For more information on ASP.NET be sure to check out the ASP.NET Article Index.

Happy Programming!