By: John Kilgo
The inbuilt paging mechanism of the ASP.Net datagrid control is convenient, but can be very inefficient. The problem with the inbuilt system is that the entire resultset is gathered again and again with each page change. Assume you have a table with 200 rows in it and that you are displaying 10 rows at a time. Everytime you change pages, all 200 rows are being returned again. The datagrid then has to do the work of sorting out which 10 rows you want to see. As you deal with larger tables, the problem only gets worse.
With custom paging you can return only the 10 rows being requested each time the page is changed. This is much more efficient. You also have more options as to the style of the paging mechanism. There is nothing wrong with NumericPages or NextPrev, but sometimes I want to do something a little different. In this example, we will be accessing the Northwind Products table, and our paging mechanism will be implemented outside the datagrid rather than within it. I'm not particularly proud of the method I chose, but perhaps it will give you some ideas on other approaches. As usual we will employ an aspx page for the datagrid design and then do all the work in a .vb code-behind file. First the .aspx page.
As you can see we have a very simple datagrid design, setting only a few properties with most of them being cosmetic in nature. We do, however, set AllowCustomPaging to True. Since we will be handling paging ourselves in code we don't need to set PagerStyle attributes or even set up an event for paging. Below the datagrid we have added two label controls to hold the current page number and the count of total pages. This is so we can display something like "Page 3 of 8". Below the labels we have created our own paging "control" using four buttons to allow paging to the first page, previous page, next page, and last page. I have used the poor man's version of VCR buttons. You could get fancy and use image buttons, or you could just use text-based link buttons.
<%@ Page language="vb" Src="CustPageDataGrid.aspx.vb" Inherits="DotNetJohn.CustPageDataGrid" %>
<html>
<head>
<title>CustPageDataGrid.aspx</title>
</head>
<body>
<form Runat="Server" ID="Form1">
<asp:DataGrid ID="dtgProd"
AllowCustomPaging="True"
CellPadding="4"
Runat="Server"
BorderColor="#898989"
BorderStyle="None"
BorderWidth="1px"
BackColor="White"
GridLines="Vertical">
<AlternatingItemStyle BackColor="#DCDCDC" />
<ItemStyle ForeColor="Black" BackColor="#EEEEEE" />
<HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#000084" />
</asp:DataGrid>
<p>
Page
<asp:Label id="lblCurPage" runat="server" />
of
<asp:Label id="lblTotPages" runat="server" />
</p>
<asp:Button id="btnFirst"
runat="server"
Text=" |<< "
font-bold="True"
onCommand="Nav_OnClick"
CommandName="First" />
<asp:Button id="btnPrev"
runat="server"
Text=" < "
font-bold="True"
onCommand="Nav_OnClick"
CommandName="Prev" />
<asp:Button id="btnNext"
runat="server"
Text=" > "
font-bold="True"
onCommand="Nav_OnClick"
CommandName="Next" />
<asp:Button id="btnLast"
runat="server"
Text=" >>| "
font-bold="True"
onCommand="Nav_OnClick"
CommandName="Last" />
</form>
</body>
</html>
And now for the code-behind page. In order to make it a little easier to see and discuss, we present the code-behind file in three sections. This first section is the top of the file down through the Page_Load sub routine. Before getting to the code I should point out that the techniques used in this example program only work when there is an identity column in the table. If there is no identity table, you could write a stored procedure which creates a temporary table that does include an identity column, copy the permanent table into the the temp table, and use the temp table to return results to the program.
The main purpose of the Page_Load routine is to determine the number of rows of data we will be dealing with. We do a SELECT Count(*) from the table and then store the result in the DataGrid's VirtualItemCount property. We then set our current page property (intCurPageNum) to 1 and call BindTheGrid() to do the databinding.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Namespace DotNetJohn
Public Class CustPageDataGrid : Inherits System.Web.UI.Page
Protected dtgProd As System.Web.UI.WebControls.DataGrid
Protected lblCurPage As System.Web.UI.WebControls.Label
Protected lblTotPages As System.Web.UI.WebControls.Label
Protected btnNext As System.Web.UI.WebControls.Button
Protected btnPrev As System.Web.UI.WebControls.Button
Protected intCurPageNum As Integer
Dim objConn As SqlConnection
Dim strSelect As String
Dim intStartIndex As Integer
Dim intEndIndex As Integer
Sub Page_Load (sender As Object, e As EventArgs)
Dim objCmd As SqlCommand
objConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
If Not IsPostBack Then
' Get Total Rows
strSelect = "Select Count(*) From Products"
objCmd = New SqlCommand( strSelect, objConn )
objConn.Open()
dtgProd.VirtualItemCount = objCmd.ExecuteScalar()
objConn.Close()
intCurPageNum = 1
BindTheGrid()
End If
End Sub
Now for the "BindTheGrid" sub-routine. In the upper part of the routine we use a paramaterized query to get the first 10 rows from the table. I say 10 rows because that is the value of dtgProd.PageSize. As you can see we are using a starting index (intStartIndex) and an ending index (intEndIndex) to specify the range of rows we want from the table using the ProductID (identity column). We then fill a DataSet with the results of the query, and set our current page label (lblCurPage.Text) to the current page number.
If this is the first time through the routine (Not Page.IsPostBack) we set a variable (intTotRecs) to hold the total number of rows we are dealing with in the table, and another variable (decTotPages) to the total number of pages (of 10 rows) we are dealing with. The problem with this variable (decTotPages) is that it is equal to 7.7. To fix this display problem we using the System.Math.Ceiling function to "round up" to the nearest integer. That is so we can display "Page 1 of 8" rather than "Page 1 of 7.7".
In the last section of BindTheGrid we test the value of intCurPageNum. If we are on the first page we want our previous page button to be disabled. If we are on the last page we want our next page button to be disabled. Otherwise, both buttons should be enabled.
Sub BindTheGrid()
Dim dataAdapter As SqlDataAdapter
Dim dataSet As DataSet
intEndIndex = intStartIndex + dtgProd.PageSize
strSelect = "Select ProductID, ProductName, SupplierID, CategoryID, " _
& "UnitPrice, UnitsInStock, Discontinued " _
& "From Products Where ProductID > @startIndex " _
& "And ProductID <= @endIndex Order By ProductID"
dataAdapter = New SqlDataAdapter( strSelect, objConn )
dataAdapter.SelectCommand.Parameters.Add( "@startIndex", intStartIndex )
dataAdapter.SelectCommand.Parameters.Add( "@endIndex", intEndIndex )
dataSet = New DataSet
dataAdapter.Fill( dataSet )
dtgProd.DataSource = dataSet
dtgProd.DataBind()
lblCurPage.Text = intCurPageNum.ToString()
If Not Page.IsPostBack Then
Dim intTotRecs As Integer = CInt(dtgProd.VirtualItemCount)
Dim decTotPages As Decimal = Decimal.Parse(intTotRecs.ToString()) / dtgProd.PageSize
lblTotPages.Text = (System.Math.Ceiling(Double.Parse(decTotPages.ToString()))).ToString()
End If
Select Case intCurPageNum
Case 1
btnPrev.Enabled = False
btnNext.Enabled = True
Case Int32.Parse(lblTotPages.Text)
btnNext.Enabled = False
btnPrev.Enabled = True
Case Else
btnPrev.Enabled = True
btnNext.Enabled = True
End Select
End Sub
And now to complete the code-behind page. You may recall that on the .aspx page when we defined our page navigation buttons we included an OnCommand method to raise the Command event (Nav_OnClick). It is here that we set the current page number (intCurPageNum) to be viewed. intCurPageNum is set as shown depending on which navigation button was clicked. We then set intStartIndex (one of our SELECT statement parameters) to the current page number minus 1 times the datagrid .PageSize propery. We then call BindTheGrid() and we are done.
Sub Nav_OnClick(sender as Object, e As system.Web.UI.WebControls.CommandEventArgs)
Select Case e.CommandName
Case "First"
intCurPageNum = 1
Case "Last"
intCurPageNum = Int32.Parse(lblTotPages.Text)
Case "Next"
intCurPageNum = Int32.Parse(lblCurPage.Text) + 1
Case "Prev"
intCurPageNum = Int32.Parse(lblCurPage.Text) - 1
End Select
intStartIndex = (intCurPageNum -1) * dtgProd.PageSize()
BindTheGrid()
End Sub
End Class
End Namespace
The code for custom paging can be a little tricky, but it does have efficiency advantages over the DataGrid's inbuilt paging mechanism. The larger the table you are dealing with the more you need paging. As the table grows larger you do not want to be returning the entire table in the resultset every time you change pages.
You may run the sample program here.
You may download the code here.