Paging Through a Query Result (ADO.NET)
Paging through a query result is the process of returning the results of a query in smaller subsets of data, or pages. This is a common practice for displaying results to a user in small, easy-to-manage chunks.
The DataAdapter provides a facility for returning only a page of data, through overloads of the Fill method. However, this might not be the best choice for paging through large query results because, although the DataAdapter fills the target DataTable or DataSet with only the requested records, the resources to return the entire query are still used. To return a page of data from a data source without using the resources to return the entire query, specify additional criteria for your query that reduce the rows returned to only those required.
To use the Fill method to return a page of data, specify a startRecord parameter, for the first record in the page of data, and a maxRecords parameter, for the number of records in the page of data.
The following code example shows how to use the Fill method to return the first page of a query result where the page size is five records.
Dim currentIndex As Integer = 0
Dim pageSize As Integer = 5
Dim orderSQL As String = "SELECT * FROM dbo.Orders ORDER BY OrderID"
' Assumes that connection is a valid SqlConnection object.
Dim adapter As SqlDataAdapter = _
New SqlDataAdapter(orderSQL, connection)
Dim dataSet As DataSet = New DataSet()
adapter.Fill(dataSet, currentIndex, pageSize, "Orders")
int currentIndex = 0;
int pageSize = 5;
string orderSQL = "SELECT * FROM Orders ORDER BY OrderID";
// Assumes that connection is a valid SqlConnection object.
SqlDataAdapter adapter = new SqlDataAdapter(orderSQL, connection);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, currentIndex, pageSize, "Orders");
In the previous example, the DataSet is only filled with five records, but the entire Orders table is returned. To fill the DataSet with those same five records, but only return five records, use the TOP and WHERE clauses in your SQL statement, as in the following code example.
Dim pageSize As Integer = 5
Dim orderSQL As String = "SELECT TOP " & pageSize & _
" * FROM Orders ORDER BY OrderID"
Dim adapter As SqlDataAdapter = _
New SqlDataAdapter(orderSQL, connection)
Dim dataSet As DataSet = New DataSet()
adapter.Fill(dataSet, "Orders")
int pageSize = 5;
string orderSQL = "SELECT TOP " + pageSize +
" * FROM Orders ORDER BY OrderID";
SqlDataAdapter adapter = new SqlDataAdapter(orderSQL, connection);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Orders");
Note that, when paging through the query results in this way, you must preserve the unique identifier that orders the rows, in order to pass the unique ID to the command to return the next page of records, as shown in the following code example.
Dim lastRecord As String = _
dataSet.Tables("Orders").Rows(pageSize - 1)("OrderID").ToString()
string lastRecord =
dataSet.Tables["Orders"].Rows[pageSize - 1]["OrderID"].ToString();
To return the next page of records using the overload of the Fill method that takes the startRecord and maxRecords parameters, increment the current record index by the page size and fill the table. Remember that the database server returns the entire query results even though only one page of records is added to the DataSet. In the following code example, the table rows are cleared before they are filled with the next page of data. You might want to preserve a certain number of returned rows in a local cache to reduce trips to the database server.
currentIndex = currentIndex + pageSize
dataSet.Tables("Orders").Rows.Clear()
adapter.Fill(dataSet, currentIndex, pageSize, "Orders")
currentIndex += pageSize;
dataSet.Tables["Orders"].Rows.Clear();
adapter.Fill(dataSet, currentIndex, pageSize, "Orders");
To return the next page of records without having the database server return the entire query, specify restrictive criteria to the SELECT statement. Because the preceding example preserved the last record returned, you can use it in the WHERE clause to specify a starting point for the query, as shown in the following code example.
orderSQL = "SELECT TOP " & pageSize & _
" * FROM Orders WHERE OrderID > " & lastRecord & " ORDER BY OrderID"
adapter.SelectCommand.CommandText = orderSQL
dataSet.Tables("Orders").Rows.Clear()
adapter.Fill(dataSet, "Orders")
orderSQL = "SELECT TOP " + pageSize +
" * FROM Orders WHERE OrderID > " + lastRecord + " ORDER BY OrderID";
adapter.SelectCommand.CommandText = orderSQL;
dataSet.Tables["Orders"].Rows.Clear();
adapter.Fill(dataSet, "Orders");