Data Paging with the Command Object

Data Paging with the Command Object

An exciting new feature added to ADO.NET is the enabling of individual "pages" of data to be fetched from a data source. Where an application presents a rowset of data broken down into separate pages, this feature can provide a useful solution by reducing the number of rows passed over the network without requiring the application to cache them all itself.

The ExecutePageReader Method

In ADO.NET 2.0, the Command object gains a new method named Execute PageReader. This currently applies only to the SqlCommand and Oracle Command, and not to the OleDbComand and OdbcCommand. There is a single overload of this method, which takes three parameters:

Command.ExecutePageReader(command-behavior, start-index, _


  • command-behavior is a value from the CommandBehavior enumeration, such as SingleRow or CloseConnection.

  • start-index is the zero-based index of the first row to return.

  • max-page-rows is the number of rows to return.

If there are less than max-page-rows in the rowset following the specified start-index row, only these following rows are returned. Also note that the ExecutePageReader method returns the page or set of rows according to the current values in the database. If rows have been added to or removed from the table between calls to the method, the set of rows returned may contain rows from the previous page (where rows have been added to the table prior to the current page), or rows may be skipped (where rows prior to the current page have been deleted from the table). This means that paging is not well suited for very dynamic data when exact positioning is required, but it works well for relatively static data (e.g., publishing read-only data) or where consistency from one page to the next is not required.

Using the ExecutePageReader Method

Listing 2.12 shows an example of using the ExecutePageReader method of the Command class. The DataReader returned by the ExecutePageReader method is bound to a DataGrid within the page to display the rows. Specifying the value CloseConnection for the CommandBehavior parameter of the ExecutePageReader method means that the connection is automatically closed when the DataReader is closed at the end of the code routine.

Paging with the ExecutePageReader Method
' get connection string and specify SQL statement
Dim sConnectString As String = your-connection-string "
Dim sQueryText As String = "SELECT ProductID, ProductName, " _
  & "QuantityPerUnit, UnitPrice, UnitsInStock FROM Products"

' create connection and command statement
Dim oConn As New SqlConnection(sConnectString)
Dim oCmd As New SqlCommand(sQueryText, oConn)

' figure out row index of first row for current page
Dim iStart As Integer = (iPageNumber - 1) * iPageMaxRows

' get required page of rows from database
Dim oReader As SqlDataReader
oReader = oCmd.ExecutePageReader(CommandBehavior.CloseConnection, _
                                 iStart, iPageMaxRows)

' see if there are any rows
If Not(oReader.HasRows) And iPageNumber > 1 Then
  ' prevent moving back beyond page 1
  iPageNumber -= 1
  ' display rows in DataGrid on page
  dgrGrid1.DataSource = oReader
End If

Notice how the code detects whether there are any rows returned for the current page, by taking advantage of the HasRows property that was added to the DataReader in version 1.1 of the .NET Framework. The result is shown in Figure.

7. Data paging with the ExecutePageReader method


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows