If the data does not change frequently, you can improve performance by maintaining a record cache locally in the dataset. For example, you can store 10 of pages of useful data in a local dataset and query for new data from the data source only if the user browses through the first and last pages of the cache.
1) ADO. NET paging avoids the use of DataAdapter.Fill overloads with Startrecord and maxrecords values. When the dataset is populated in this way, ADO. NET paging only the number of records specified by the MaxRecords parameter (starting with the record identified by the Startrecord parameter) is used to populate the dataset, but always returns the complete query anyway. This can cause unnecessary processing to read "unwanted" records and, in order to return additional records, deplete unnecessary server resources.
2 The technique used to return only one page of records at a time is to create an SQL statement that combines a WHERE clause with an ORDER BY clause and a top predicate. Ado. NET paging this technique depends on the existence of a unique way to identify each row. When browsing the next page of records, modify the WHERE clause to include all records that have a unique identifier greater than the last unique identifier of the current page.
When browsing the previous page record, modify the WHERE clause to return records that all unique identifiers are less than the first unique identifier of the current page. Both queries return only the top page of the record. When browsing the previous page, you need to sort the results in descending order. This effectively returns the last page of the query's ado.net (if necessary, you may want to reorder the results before displaying it).
3 Another technique to return only one page at a time is to create SQL statements that combine the use of the top predicate and the embedded SELECT statement. This technique does not depend on the existence of a method that uniquely identifies each row. The first step in using this technique is to multiply the number of pages you want with the page size.
The results are then passed to the top verb in SQL query, which is sorted in ascending order. Then embed this query in another query, ADO. NET pagination the latter selects the top page size from the results of an embedded query in descending order. Essentially, the last page of an embedded query is returned. For example, to return the third page of the query result (the page size is 10), you should write the following command:
Select Top * FROM (select top * to Customers ORDER by ID ASC) as Table1 an ORDER by ID DESC Note: The results page returned from the query is displayed in descending form. Ado. NET paging should be reordered if needed.