ASP.NET Data Web Controls: Providing DataGrid Pagination

This chapter is from the book

ASP.NET Data Web Controls Kick Start

Learn More Buy

This chapter is from the book

This chapter is from the book 

ASP.NET Data Web Controls Kick Start

Learn More Buy

Paging Through the DataGrid Using Custom Paging

Recall that whenever the user clicks a pager hyperlink, the ASP.NET Web page is posted back and the DataGrid's PageIndexChanged event is fired. When using the DataGrid's default paging mechanism, we need to provide an event handler for this event that performs the following two steps:

1. Update the DataGrid's CurrentPageIndex property.

2. Rebind the DataSource to the DataGrid by calling the BindData() subroutine.

These two steps lead to a rather simple and concise event handler. The problem is that every time the user requests a new page of data, the BindData() subroutine reads the entire contents of the DataSource, even though only a subset of records are being displayed. For example, the entire contents of the titles table will be retrieved each time the user clicks on a paging link.

To understand the implications of this, imagine that your DataSource is comprised of 5,000 records, and you are displaying 15 records per page. When the user first visits the page, all 5,000 records will be retrieved from the database, but only the first 15 will be shown. When the user clicks to visit the next page of data, all 5,000 records will again be retrieved from the database, but only records 16 through 30 will be shown. If the user steps through, say, 10 pages, she will have seen a total of 150 records, but we will have actually retrieved 50,000 records from the database!

Retrieving 5,000 records per page is clearly inefficient if we only want to display 15 records. If there was some way we could only retrieve the 15 needed records, we'd be able to dramatically reduce the total number of records transmitted from the database to the ASP.NET Web page. Fortunately, there is such a way—it's called custom paging.

Remember that the default paging mechanism needs to have the entire contents of the query that's to be paged through each time the DataGrid is rendered so that it can correctly determine how many pages the data can be broken up into. Essentially, it needs to know how many total records we're paging through. As we'll see in the next section, when employing custom paging we will be returning only those records that need to be displayed on the current page of data being viewed; however, we will also need to let the DataGrid know the number of total records in the DataSource that the query being paged through consists of.

Examining Custom Paging

With custom paging, only the records that need to be displayed on a particular page are returned—this is the benefit of custom paging over the DataGrid's default paging. However, as we pointed out at the end of the last section, the DataGrid needs to know how many records are to be paged through. However, because the DataGrid needs to calculate the number of pages based on the number of records, we must explicitly specify this value. We accomplish this by setting the DataGrids' VirtualItemCount property.

To help clarify things a bit, let's consider an example. Imagine that we have a Web page that allows visitors to add their name and a brief comment, similar to a guestbook. To facilitate this, we would create a database table named Comments with the fields CommentID, Name, Comment, and DateAdded, which would be of types integer primary key, varchar(50), varchar(255), and datetime, respectively. In addition to this, we'd need to provide an ASP.NET Web page that would allow users to add entries to the guestbook. (This is left as an exercise to the reader.)

In addition to allowing a user to add a comment to the guestbook, we'd like to allow users to view the contents of the guestbook. If we have a popular Web site, the number of people leaving comments might be quite large, and the Comments database table could quickly grow to hundreds, thousands, or tens of thousands of records. We obviously need to provide pagination through this data, because it would be impractical to present the user with all the potential thousands of guestbook entries on one page.

It would be wise to decide to implement custom paging to reduce the total number of records that must be brought from the database to the ASP.NET Web page as the user pages through the guestbook comments.

In implementing custom paging, we are faced with two challenges:

1. Determining the total number of records we want to page through

2. Querying the database for only those records that we need on a particular page

We need to be able to accomplish the first task to properly set the DataGrid's VirtualItemCount property, so that the paging interface is able to be correctly displayed. Accomplishing the second task is fundamental to the motivation behind custom paging—retrieving only the rows from the database that are needed for displaying the current page.

In the next two subsections, we will look at how to accomplish these two tasks.

Obtaining the Number of Records

The first task can be accomplished by issuing a SQL query that uses the SQL COUNT aggregate function. In general, to obtain the number of total records that need to be paged through, we can use

SELECT COUNT(*)
FROM TableName
WHERE OptionalWhereClause

In our example, if we want to page through all the records in the Comments table, we would use

SELECT COUNT(*)
FROM Comments

If, on the other hand, we only want to page through those comments that were left by a visitor named Scott, we could use

SELECT COUNT(*)
FROM TableName
WHERE Name = 'Scott'

Retrieving Only the Needed Records via a Stored Procedure

The benefit of using custom paging is that we only need to retrieve the records to be displayed on the page of data that the user is currently viewing. But how can we construct a SQL query that retrieves just the needed rows?

Imagine that for each record of the data we want to page through, we add an integer field named IncreasingID to the data, such that the ith row in the data has a value of i. To clarify this concept, let's look at an example. Assume that the data we want to allow the end user to page through is shown in Figure 8.6. Now, imagine that we have some way to augment the data shown in Figure 8.6 to contain the additional field IncreasingID, as shown in Figure 8.7.

Figure 8.6. The data from the Comments table that is to be paged through.

Figure 8.7. The augmented data from Figure 8.6, which includes an IncreasingID field.

Note that the CommentID fields in Figure 8.6 have holes in them. That is, there is no record where CommentID equals 4. Perhaps this was an offensive guestbook entry that the site's administrator deleted. Figure 8.6 has two other holes—it's missing records with CommentIDs 8 and 11, as well.

The purpose of the IncreasingID field is to fill these holes. As Figure 8.7 shows, the IncreasingID has no holes, and for any given row i, the value of IncreasingID is i.

When you have such an IncreasingID, you can use custom paging with either the Next/Previous paging interface, or the paging interface that consists of links for each page. In general, to retrieve the records for the kth page of data, where you are displaying i records per page, the following SQL query can be used:

SELECT ColumnList
FROM TableName
WHERE IncreasingID > k * i AND IncreasingID <= (k+1) * i

For example, let's say that we want to show 10 records per page—here, i would equal 10. When displaying the first page of data, k will equal 0, meaning we can retrieve the proper subset of rows for the first page by using

SELECT ColumnList
FROM TableName
WHERE IncreasingID > 0 * 10 AND IncreasingID <= (0+1) * 10

This will get the records whose IncreasingID is between 1 and 10, inclusive. To display the third page of data, k will equal 2, and our SQL statement will look like

SELECT ColumnList
FROM TableName
WHERE IncreasingID > 2 * 10 AND IncreasingID <= (2+1) * 10

This will get the records whose IncreasingID is between 21 and 30, inclusive.

One problem remains: how do we augment our data so that it increases an IncreasingID field? Intuitively, you might reason that your database table's primary key column already exhibits the properties of an IncreasingID field. You might reason that because your database table has an integer primary key that is an auto-increment value starting at 1 and incrementing by 1 for each record, that this primary key field can be used in place of the IncreasingID field in the SQL queries given previously. Such a primary key field can be used as the IncreasingID field, but only if there are no holes in the primary key values, such as the ones shown in Figure 8.6.

Holes can also appear when records are deleted. When confronted with holes, there are two options. The first is to compact the holes, which requires some tricky SQL that is beyond the scope of this book. Furthermore, this process needs to be done whenever a hole is created. The second approach is to use an IncreasingID field, as shown in Figure 8.7.

When we add an IncreasingID field, we are not adding a new field to the database table. Rather, we are constructing a SQL resultset that has the fields of the table along with an additional field. To add an IncreasingID field, you should use a SQL stored procedure to build a temporary table that contains two fields: an auto-incrementing integer field with initial and incremental values of 1 called IncreasingID, and an integer field called OtherID. After the temporary table is created, it should be populated by the SQL query that we want to page through. That is, if we want to construct a SQL resultset that contains an IncreasingID and the records of the Comments database table, we'd populate the temporary table with the primary key values from the Comments table (CommentID) using the following SQL:

-- Create the temporary table
CREATE TABLE #CommentTempTable
(
 IncreasingID 	int   IDENTITY(1,1),
 OtherID     int
)

-- Now, populate the temporary table
INSERT INTO #CommentTempTable (OtherID)
SELECT CommentID
FROM Comments
ORDER BY CommentID ASC

After we have populated the temporary table, all that remains is to return the correct resultset. This is accomplished by doing a SELECT query on the temporary table, returning the temporary table's primary key value as well as the fields of the Comments table. An inner join should be performed, joining the temporary table to the Comments table on the temporary table's second field and the Comment table's primary key field. The final SQL SELECT statement should return only those rows whose corresponding IncreasingID is within the range specified by the page number being displayed (see lines 25 and 26 in Listing 8.5) .

The complete code for such a stored procedure is shown in Listing 8.5.

Listing 8.5 Adding an IncreasingID Field via a Temporary Table

 1: CREATE PROCEDURE sp_PageThroughData
 2: (
 3:   @PageSize  int,
 4:   @CurrentPage  int
 5: )
 6: AS
 7: 
 8: CREATE TABLE #CommentTempTable
 9: (
10:  IncreasingID     int   IDENTITY(1,1),
11:  OtherID     int
12: )
13: 
14: -- Now, populate the temporary table
15: INSERT INTO #CommentTempTable (OtherID)
16: SELECT CommentID
17: FROM Comments
18: ORDER BY CommentID ASC
19: 
20: -- Finally, return the records
21: SELECT IncreasingID, Comments.*
22: FROM #CommentTempTable
23:  INNER JOIN Comments ON
24:   Comments.CommentID = #CommentTempTable.OtherID
25: WHERE IncreasingID > @PageSize * @CurrentPage
26:   AND IncreasingID <= @PageSize * (@CurrentPage+1)

The stored procedure in Listing 8.5 takes two input parameters: @PageSize, which specifies how many records the stored procedure should return, and @CurrentPage, which indicates what page of data is currently being requested. These parameters allow the stored procedure to return only those records that we are interested in displaying in our DataGrid. That is, if @PageSize is set to 10, then the stored procedure will return 10 records, perhaps fewer if we want to view the records on the last page.

Although only the needed records are transmitted from the database server to the ASP.NET Web page, the stored procedure itself must take the time to create a temporary table and populate it with the entire contents of the SQL query (see the INSERT statement, lines 15 through 18). This means that if the Comments table contains 5,000 rows, each time the user pages through the DataGrid's data, 5,000 rows are copied into a temporary table. Of course, the stored procedure returns only the needed rows to the ASP.NET Web page.

The differences between the stored procedure option and the DataGrid's default paging are subtle. With the default paging approach, we copy all the rows from the database to the ASP.NET Web page. With the stored procedure approach, all of the records are copied to a temporary table on the database server, and then only those needed rows are copied to the ASP.NET Web page. While both approaches seem to do the same thing, the stored procedure route will likely provide better performance because it's copying less data across application boundaries.

Now that we've examined this stored procedure, let's create an ASP.NET Web page that allows the user to page through the Comments table. Listing 8.6 contains the code and HTML for an ASP.NET page that uses the stored procedure from Listing 8.5.

Listing 8.6 Using a Stored Procedure and Custom Paging

 1: <%@ import Namespace="System.Data" %>
 2: <%@ import Namespace="System.Data.SqlClient" %>
 3: <script runat="server">
 4:   Sub Page_Load(sender as Object, e as EventArgs)
 5:    If Not Page.IsPostBack then
 6:     dgCommentsdgComments.VirtualItemCount = GetTotalItemsInQuery()
 7:     dgCommentsdgComments.CurrentPageIndex = 0
 8:
 9:     BindData()
10:    End If
11:   End Sub
12:
13:
14:   Sub BindData()
15:    'Create a connection to the DB
16:    Const strConnString as String = "server=localhost;uid=sa;pwd=;
database=pubs"
17:    Dim objConn as New SqlConnection(strConnString)
18:
19:    Const strSQL as String = "sp_PageThroughData"
20:
21:    Dim objCmd as New SqlCommand(strSQL, objConn)
22:    objCmd.CommandType = CommandType.StoredProcedure
23:
24:    Dim pageSizeParam as New SqlParameter("@PageSize", SqlDbType.Int, 4)
25:    pageSizeParam.Value = dgComments.PageSize
26:    objCmd.Parameters.Add(pageSizeParam)
27:
28:    Dim currentPageParam as New SqlParameter("@CurrentPage", 
SqlDbType.Int, 4)
29:    currentPageParam.Value = dgComments.CurrentPageIndex
30:    objCmd.Parameters.Add(currentPageParam)
31:
32:    objConn.Open()  'Open the connection
33:
34:    'Finally, specify the DataSource and call DataBind()
35:    dgComments.DataSource = objCmd.ExecuteReader(CommandBehavior.
CloseConnection)
36:    dgComments.DataBind()
37:
38:    objConn.Close()  'Close the connection
39:   End Sub
40:
41:
42:   Sub dgComments_Paging(sender As Object, e As DataGridPageChangedEventArgs)
43:    dgComments.CurrentPageIndex = e.NewPageIndex
44:    BindData()
45:   End Sub
46:
47:
48:   Function GetTotalItemsInQuery()
49:    '1. Create a connection
50:    Const strConnString as String = "server=localhost;uid=sa;pwd=;
database=pubs"
51:    Dim objConn as New SqlConnection(strConnString)
52:
53:    '2. Create a command object for the query
54:    Dim strSQL as String
55:    strSQL = "SELECT COUNT(*) FROM Comments"
56:
57:    Dim objCmd as New SqlCommand(strSQL, objConn)
58:
59:    objConn.Open()  'Open the connection
60:
61:    ' Get the number of records
62:    Dim recCount as Integer
63:    recCount = objCmd.ExecuteScalar()
64:
65:    objConn.Close()
66:
67:    Return recCount
68:   End Function
69: </script>
70:
71: <form runat="server">
72:  <asp:DataGrid runat="server" id="dgComments"
73:    Font-Name="Verdana" Font-Size="9pt" CellPadding="5"
74:    AlternatingItemStyle-BackColor="#dddddd"
75:    AutoGenerateColumns="True" Width="75%"
76:    PageSize="10" AllowPaging="True"
77:    OnPageIndexChanged="dgComments_Paging"
78:    AllowCustomPaging="True">
79:
80:   <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt"
81:        Font-Bold="True" HorizontalAlign="Center" />
82:
83:   <PagerStyle BackColor="Navy" ForeColor="White" Font-Size="8pt"
84:       Font-Bold="True" HorizontalAlign="Right"
85:       Mode="NumericPages" />
86:  </asp:DataGrid>
87: </form>

Recall that with custom paging, we need to set the DataGrid's VirtualItemCount property to the number of records we plan on paging through. We do this on line 5 in the page's Page_Load event handler when the page is first visited (when Page.IsPostBack is False). Note that the DataGrid's VirtualItemCount property is set to the return value of a function, GetTotalItemsInQuery(). This function, which can be found spanning lines 48 to 68, performs a SQL COUNT query (line 55) to calculate this number of total records that will be paged through.

After the VirtualItemCount property is set in the Page_Load event handler, the BindData() subroutine is called. BindData() has the same semantics in this ASP.NET Web page as it has always had: to bind the appropriate data to the DataGrid. To retrieve the appropriate data, the sp_PageThroughData store procedure, which was presented in Listing 8.5, needs to be called. In calling this stored procedure, we must specify two input parameters: @PageSize and @CurrentPage. The values we need to pass in for these two parameters are simply the values of the DataGrid's PageSize and CurrentPageIndex properties. On lines 24 through 30, we create the needed SqlParameter instances, set the parameters to the proper values, and add them to the SqlCommand object objCmd. The BindData() subroutine finishes by opening the database connection (line 32), setting the DataGrid's DataSource to the SqlDataReader returned by the ExecuteReader(CommandBehavior.CloseConnection) method (line 35), calling the DataGrid's DataBind() method (line 36), and closing the connection (line 28) .

dgComments_Paging, the event handler for the DataGrid's PageIndexChanged event (lines 42–45) is identical to the version of dgComments_Paging in previous examples in this chapter. It simply updates the DataGrid's CurrentPageIndex property and calls the BindData() method. Figure 8.8 is a screenshot of the code in Listing 8.6 in action.

To summarize, to select only those records for a particular page of data, we add an IncreasingID field to the data that we want to page through. This can be accomplished via a stored procedure that creates a temporary table with an auto-increment field and an integer field. This table is then populated with the primary key field of the records that are to be paged through. After this temporary table has been populated, a SQL SELECT clause can be used to extract the correct set of primary key values from the table, which, via a join, can be paired with the other fields of the table that is being paged through.

The stored procedure approach provides better performance than the default paging approach, which must copy all the rows to be paged through from the database server to the ASP.NET Web page. Yet, creating a temporary table and populating it with all the rows from the table each time the user requests to view a different page of data seems a bit inefficient. Although there is a bit of a performance hit with inserting all the rows of the desired table into a temporary table, it is not severe for smaller tables.

Figure 8.8. Only the records that need to be displayed on the particular page of data are returned from the stored procedure.

For example, on the Web site I run, 4GuysFromRolla.com, there is an Articles database table that has a row for each article written on 4Guys. When a user wants to search 4GuysFromRolla.com, this Articles table is searched, and the results are sent back to the Web page using the stored procedure approach we just discussed. The performance of this stored procedure approach is nothing to scoff at, running through the query analyzer in less than one second. Keep in mind, though, that the Articles table is by no means a large table—it is comprised of about 1,500 rows.

For large tables that might contain hundreds of thousands or millions of rows, copying over all the records from the table that you want to page through to the temporary table might incur an overhead of several seconds, or even minutes! For example, using the stored procedure on my personal computer for a table with 250,000 records took five seconds to complete. Clearly, the stored procedure isn't viable for larger tables.

Fortunately, there is another option that involves paging through the data using the data's primary key values. We'll examine this option in detail in the next section.

Retrieving Only the Needed Records Using Primary Key Values

In the previous section, we saw that if we had an IncreasingID field we could display any particular page of data by using a simple formula: to view the kth page of data, where each page of data displays i records, we choose IncreasingIDs between k * i + 1 and (k+1) * i, inclusive.

If we have a database table that has an integer with an auto-increment primary key value, we can apply this formula only if it is guaranteed that there are no "holes" in the auto-increment values. Recall that a hole can occur by having one or more records deleted.

There is, however, a way to page through the data using auto-incremented primary key information regardless of the presence of holes. The secret is to save the first and last auto-incremented primary key values each time a page is displayed. That is, if we are viewing a page of data that displays 10 records, starting with record 17 and ending with record 28 (signifying that two records between 17 and 28 must have been deleted at sometime), we save the values 17 and 28 in the ViewState. If the user opts to view the previous page, we get only the first 10 records whose primary key is less than 17; if the user opts to view the next page, we get only the first 10 records whose primary key is greater than 28.

SELECT TOP N ColumnList
FROM TableName

A quick example is in order. Imagine that we want to page through our Comments table, part of which was shown in Figure 8.6. The data shown in Figure 8.6 contained holes in the auto-incremented primary key field, CommentID. Specifically, there is no record where CommentID equals 4, 8, or 11. Now, imagine that we want to page through the comments five at a time. Recall that we keep two variables in the ASP.NET Web page's ViewState—the first and last primary key values that have been displayed. On the first visit to the page, these values are defaulted to 0. Hence, our initial SQL query for the first page will look like this:

SELECT TOP 5 CommentID, Name, Comment, DateAdded
FROM Comments
WHERE CommentID > 0
ORDER BY CommentID ASC

Note that we use a TOP 5 because we're interested in displaying five records per page. This query will return the first five records from the Comments table whose CommentID is greater than 0 (1, 2, 3, 5, and 6). At this point, we need to store the first and last primary key values in the ViewState; that is, we'll store 1 in a ViewState variable named LowID, and we'll store 6 in a ViewState variable named HighID.

When the user opts to view the next page of data, our SQL query will become

SELECT TOP 5 CommentID, Name, Comment, DateAdded
FROM Comments
WHERE CommentID > 6
ORDER BY CommentID ASC

We know to retrieve records from the Comments table whose CommentID is greater than 6, because that was the CommentID of the last record in the previous page. The preceding SQL query will return five records namely(7, 9, 10, 12, and 13). The LowID ViewState variable would be assigned the value 7, whereas the HighID ViewState variable would be assigned the value 13.

If the user opts to view the previous page at this point, the SQL query used to grab the particular records will be as follows:

SELECT TOP 5 CommentID, Name, Comment, DateAdded
FROM Comments
WHERE CommentID < 7
ORDER BY CommentID DESC

This will return the five rows from the Comments table that need to be displayed.

It is important to note that the SQL query's ORDER BY clause is slightly different, depending on whether we're retrieving the records for the next or previous page. When obtaining records for the next page, the ORDER BY clause sorts the results by CommentID in ascending order; when obtaining records for the previous page, the ORDER BY clause sorts the results by CommentID in descending order. The reason these ORDER BY clauses must be included is because if they are not, there is no guarantee that we'll get the five records we want. For example, imagine that we have a SQL query to retrieve the records for the previous page that do not include an ORDER BY clause, and look like

SELECT TOP 5 CommentID, Name, Comment, DateAdded
FROM Comments
WHERE CommentID < 100

Without the ORDER BY clause, we could theoretically get back any five records less than 100, such as records 1, 2, 3, 5, and 6. By using the ORDER BY clause, we are guaranteed to get the records 99, 98, 97, 96, and 95.

Listing 8.7 contains the code to implement this form of custom paging. Note that the GetTotalItemsInQuery() function has been omitted from Listing 8.7 for brevity; it is identical to the GetTotalItemsInQuery() function presented in Listing 8.6, lines 48 through 68.

Listing 8.7 Paging Through Data Using the Primary Key Method

 1: <%@ import Namespace="System.Data" %>
 2: <%@ import Namespace="System.Data.SqlClient" %>
 3: <script runat="server" language="VB">
 4: '... The GetTotalItemsInQuery() function has been omitted for brevity.
 5:  ' ... Refer back to Listing 8.6 for details on this function ...
 6:
 7:   Sub Page_Load(sender as Object, e as EventArgs)
 8:    If Not Page.IsPostBack then
 9:     dgComments.VirtualItemCount = GetTotalItemsInQuery()
 10:     ViewState("LowID") = 0
 11:
 12:     Const strConnString as String = "server=localhost;uid=sa;pwd=;
database=pubs"
 13:     Dim objConn as New SqlConnection(strConnString)
 14:     Dim strSQL as String
 15:
 16:     strSQL = "SELECT TOP " & dgComments.PageSize & " CommentID, 
Name, " & _
 17:         "Comment, DateAdded " & _
 18:         "FROM Comments " & _
 19:         "WHERE CommentID > 0 " & _
 20:         "ORDER BY CommentID ASC"
 21:
 22:     'Get the results
 23:     Dim objCmd as New SqlCommand(strSQL, objConn)
 24:
 25:     objConn.Open()
 26:     BindData(objCmd)
 27:     objConn.Close()
 28:    End If
 29:   End Sub
 30:
 31:
 32:   Sub BindData(objCmd as SqlCommand)
 33:    'Create a DataAdapter and Fill the DataSet
 34:    Dim objDA as New SqlDataAdapter()
 35:    objDA.SelectCommand = objCmd
 36:
 37:    Dim objDS as DataSet = New DataSet()
 38:    objDA.Fill(objDS, "titles")
 39:
 40:    'Get a DataView from the DataSet's "titles" DataTable
 41:    Dim myDataView as DataView = New DataView(objDS.Tables("titles"))
 42:
 43:    'Sort the DataView on the commentID
 44:    myDataView.Sort = "CommentID ASC"
 45:
 46:    'Bind the dataView to the DataGrid
 47:    dgComments.DataSource = myDataView
 48:    dgComments.DataBind()
 49:
 50:    'Now, get the high and low IDs
 51:    ViewState("LowID") = myDataView(0)("CommentID")
 52:    ViewState("HighID") = myDataView(myDataView.Count-1)("CommentID")
 53:   End Sub
 54:
 55:
 56:   Sub dgComments_Paging(sender As Object, 
e As DataGridPageChangedEventArgs)
 57:    'See if the user is visiting the next page or previous page
 58:    'Create a connection
 59:    Const strConnString as String = "server=localhost;uid=sa;pwd=;
database=pubs"
 60:    Dim objConn as New SqlConnection(strConnString)
 61:    Dim strSQL as String
 62:    Dim objCmd as New SqlCommand()
 63:    Dim IDParam as New SqlParameter("@IDParam", SqlDbType.Int, 4)
 64:
 65:    If dgComments.CurrentPageIndex = e.NewPageIndex - 1 then
 66:     'The user wants to see the next page
 67:     strSQL = "SELECT TOP " & dgComments.PageSize & " CommentID, 
Name, " & _
 68:         "Comment, DateAdded " & _
 69:         "FROM Comments " & _
 70:         "WHERE CommentID > @IDParam " & _
 71:         "ORDER BY CommentID ASC"
 72:
 73:     IDParam.Value = ViewState("HighID")
 74:    Else
 75:     'The user wants to see the previous page
 76:     strSQL = "SELECT TOP " & dgComments.PageSize & " CommentID, 
Name, " & _
 77:         "Comment, DateAdded " & _
 78:         "FROM Comments " & _
 79:         "WHERE CommentID < @IDParam " & _
 80:         "ORDER BY CommentID DESC"
 81:
 82:     IDParam.Value = ViewState("LowID")
 83:    End If
 84:
 85:    'Set the CurrentPageIndex property
 86:    dgComments.CurrentPageIndex = e.NewPageIndex
 87:
 88:    'Get the results
 89:    objCmd.Parameters.Add(IDParam)
 90:
 91:    objCmd.CommandText = strSQL
 92:    objCmd.Connection = objConn
 93:
 94:    objConn.Open()
 95:    BindData(objCmd)
 96:    objConn.Close()
 97:   End Sub
 98: </script>
 99:
100: <form runat="server">
101:  <asp:DataGrid runat="server" id="dgComments"
102:    Font-Name="Verdana" Font-Size="9pt" CellPadding="5"
103:    AlternatingItemStyle-BackColor="#dddddd"
104:    AutoGenerateColumns="True" Width="75%"
105:    PageSize="10" AllowPaging="True"
106:    OnPageIndexChanged="dgComments_Paging"
107:    AllowCustomPaging="True">
108:
109:   <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt"
110:        Font-Bold="True" HorizontalAlign="Center" />
111:
112:   <PagerStyle BackColor="Navy" ForeColor="White" Font-Size="8pt"
113:       Font-Bold="True" HorizontalAlign="Right"
114:       Mode="NextPrev" NextPageText="Next >" PrevPageText="< Prev" />
115:  </asp:DataGrid>
116: </form>

The code in Listing 8.7 is quite lengthy, especially when compared to the custom paging code that utilized the stored procedure (see Listing 8.6). Let's turn our attention first to the Page_Load event handler (lines 7–29), which only executes instructions on the page's first load, when Page.IsPostBack is False (line 8). On line 9, the DataGrid's VirtualItemCount is set to the value returned by the GetTotalItemsInQuery() function.

 SELECT COUNT(*) FROM Comments

On line 10, we set the ViewState variable LowID to its initial value of 0. Next, on lines 12 through 23 a connection to the database is established, and a SqlCommand object created using the SQL query spelled out on lines 16–20. The SQL query uses the TOP keyword to grab the first dgComments.PageSize number of records from the Comments table, where the CommentID is greater than 0. It then orders the results by the DateAdded field. After we have this SqlCommand object and have opened the database connection, we call BindData(objCmd), passing in the SqlCommand object (line 26).

The BindData(objCmd) subroutine in Listing 8.7 can be found spanning lines 32 to line 53. The BindData(objCmd) subroutine starts by filling a DataSet using the passed in SqlCommand object. On line 34 the SqlDataAdapter object is created, and on line 35, its SelectCommand property is set to the passed-in SqlCommand object objCmd. A DataSet, objDS, is created on line 37, and is filled on line 38.

Next, we need to create a DataView object from the DataSet (line 41). The DataView class has a Sort property that allows us to sort the contents contained in the DataView; on line 44, we sort the results on the CommentID field in ascending order. The reason we need to use a DataView is because we need to sort the results by the CommentID in ascending order when the user opts to view the previous page of data the results are sorted in descending order. For example, if the user is viewing a page of data whose first record has CommentID 100, and then the user opts to view the previous page of data, the following SQL query will be issued:

SELECT TOP 5 CommentID, Name, Comment, DateAdded
FROM Comments
WHERE CommentID < 100
ORDER BY CommentID DESC

This will retrieve records 99, 98, 97, 96, and 95, in that order. However, we want to show them in ascending order (95, 96, 97, 98, and 99). To accommodate this, we use a DataView and sort its results on the CommentID field in ascending order.

The BindData(objCmd) subroutine next binds the sorted DataView to the DataGrid (lines 47 and 48). It completes by setting the values of the LowID and HighID ViewState variables. On line 51, ViewState("LowID") is set to the CommentID field of the first record in the DataView. Similarly, on line 52 ViewState("HighID") is set to the CommentID field of the last record in the DataView. These primary key values written to the ViewState are used in SQL queries issued by the dgComments_Paging event handler. Recall that the dgComments_Paging event handler is the event handler for the DataGrid's PageIndexChanged event, which fires every time the user clicks either the Next or Previous hyperlink.

The dgComments_Paging event handler, which can be found starting on line 56, is responsible for forming the correct SQL query based on whether the user wants to view the next or previous page. On lines 59 through 63, the needed SqlConnection, SqlCommand, and SqlParameter classes are created. On line 65, we check to see whether the user has opted to view the next page by checking whether the current page is one less than the page the user is requesting to view. Lines 66 through 73 build up the proper SQL for viewing the next page. The SQL query used includes a parameter, @IDParam, which is set to ViewState("HighID") on line 73. If, on the other hand, the user has opted to view the previous page, the code from lines 75 through 82 is executed. An appropriate SQL query is constructed (lines 76 through 80), and the @IDParam parameter is set to the value of ViewState("LowID") on line 82.

After the dgComments_Paging event handler has formed the correct SQL statement, we can update the DataGrid's CurrentPageIndex to the NewPageIndex (line 86), add the IDParam SqlParameter to the SqlCommand objCmd (line 89), and set objCmd's CommandText and Connection properties (lines 91 and 92). At this point, all that we need to do is open the connection (line 94) and call the BindData(objCmd) subroutine, passing in the objCmd SqlCommand object (line 95). In calling BindData(objCmd), the DataGrid will be bound to the records for the requested page and the LowID and HighID ViewState variables will be updated.

Lines 100 through 116 contain the DataGrid declaration, which is enclosed in a Web form. This declaration is nearly identical to the one in Listing 8.6. The important difference is that this DataGrid uses the Next/Previous paging interface, as opposed to a list of hyperlinks of pages. Although the stored procedure approach to custom paging can support either the Next/Previous interface or the hyperlinked list of pages, when using the primary key method, you must use the Next/Previous interface. This is because with the primary key method, there might be holes in the primary key value, which means there isn't a simple function we can use to precisely grab the records for any given page. Rather, we have to know the last primary key value of the current page to get the next page of data, or the first primary key value of the current page to get the previous page of data.

Figure 8.9 contains a screenshot of Listing 8.7 in action. Note that the DataGrid includes the CommentID field of each comment, and that there are some holes in the CommentIDs. Regardless, precisely 10 records are shown per page.

Figure 8.9. Custom paging can be efficiently accomplished by using the primary key method.

The Stored Procedure Method Versus the Primary Key Method—Which Is Better?

In the last two sections, we examined two methods for employing custom paging: using a stored procedure to add an IncreasingID field to the data to be paged through; and storing the auto-incremented primary key value for each page being displayed in order to correctly determine the subset of records to retrieve when the user opts to view the next or previous page. Both of these approaches have their advantages and disadvantages, which we'll examine here.

The stored procedure option allows for the most flexibility in the paging interface. With the stored procedure approach, either the Next/Previous or the hyperlinked list of pages interfaces can be used. With the primary key method, however, only the Next/Previous paging interface is an option. Additionally, the stored procedure method moves most of the complexity involved in selecting the correct subset of the records to be displayed to the database. This leads to a shorter, cleaner, and more readable ASP.NET Web page.

The big disadvantage of the stored procedure method is that each time the user requests to see a new page of data, all the records from the underlying database table must be copied into a temporary table. As mentioned before, this operation can take several seconds or even minutes on sufficiently large tables. For example, using the stored procedure method on a table with 250,000 rows on my personal computer required five seconds, a painfully long time to have to wait for a Web page to start loading. The TOP N SQL query, however, took a fraction of a second to run on the 250,000 row table on my computer.

Despite the performance advantages of the primary key method, it requires that the data to be paged through has an auto-incremented primary key field. If this is not the case, you will have to resort to using the stored procedure approach. Furthermore, the primary key method involves more difficult coding than the stored procedure counterpart.

So which approach should you choose? If you are planning on paging through a large table, by all means use the primary key method if possible. If the data to page through is small, however, and you are certain it won't start growing in size, there's no reason not to use the stored procedure method, which will offer more manageable code and more features in the paging interface.

If performance is a key concern, you might not want to use either custom paging method, but rather use the DataGrid's default paging with a cached DataSet. We'll examine how to do this in the next section.