Query result page

The following is an excerpt from msdn: "query result pagination"

Dataadapter provides the function of returning only one page of data through the fill method overload. However, for a large number of query results, it may not be the preferred paging method, because although dataadapter only uses the requested record to fill the target datatable or dataset, however, the resource that returns the entire query will still be used. To return a page of data from a data source without returning resources for the entire query, specify additional conditions for the query so that only the required rows are returned.Remember that the database server returns all query results even if only one page of records is added to the dataset.

Int currentindex = 0; <br/> int pagesize = 5; </P> <p> string ordersql = "select * from orders order by orderid "; <br/> // assumes that connection is a valid sqlconnection object. <br/> sqldataadapter adapter = new sqldataadapter (ordersql, connection); </P> <p> dataset = new dataset (); <br/> adapter. fill (dataset, currentindex, pagesize, "orders ");

In the preceding example, dataset only fills in five records, but returns the entire orders table. If you want to fill in dataset with the same five records but only return these five records, use the top and where clauses in the SQL statement, as shown in the following code example.

 

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 querying results by page in this way, you must retain the unique identifier used to sort rows,
To pass the unique ID to the command used to return the next page record, as shown in the following code example.
string lastRecord =   dataSet.Tables["Orders"].Rows[pageSize - 1]["OrderID"].ToString();
In the following code example, clear the table rows first, and then fill the table rows with the data on the next page. You may need to keep a certain number of returned rows in the local cache,
To reduce the number of round-trips with the database server.
To return records on the next page without the database server returning the entire query, specify the constraints on the SQL SELECT statement.
Since the previous example retains the last returned record, you can use it in the WHERE clause to specify the start point of 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 ");