March 6, 2011, 2:14 p.m.
posted by pitbull
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, _ max-page-rows)
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.
' 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 oConn.Open() 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 Else ' display rows in DataGrid on page dgrGrid1.DataSource = oReader dgrGrid1.DataBind() End If oReader.Close()
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.