.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal

Home >> Articles >> .Net Framework >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Data Paging

Posted By:Shashi Ray       Posted Date: November 30, 2009    Points: 25    Category: .Net Framework    URL: http://www.dotnetspark.com  


Data Paging

Paging through data is a common requirement in distributed applications. For example, the user might be presented with a list of books where it would be prohibitive to display the entire list at once. The user will want to perform familiar activities on the data, such as viewing the next or previous page of data or jumping to the beginning or end of the list.

This section discusses options for implementing this functionality, and the effect of each option on scalability and performance.

Comparing the Options

The options for data paging are:

  • Using the Fill method of the SqlDataAdapter to fill a DataSet with a range of results from a query
  • Using ADO through COM interoperability and use a server-side cursor
  • Using stored procedures to implement data paging manually

The best option for paging your data is dependent on the factors listed below:

  • Scalability requirements
  • Performance requirements
  • Network bandwidth
  • Database server memory and power
  • Middle-tier server memory and power
  • Number of rows returned by a query you want to page
  • Size of your data pages

Performance tests have shown that the manual approach using stored procedures offers the best performance across a wide range of stress levels. However, as the manual approach performs its work on the server, server stress levels can become a significant issue if a large proportion of your site's functionality relies upon data paging functionality. To ensure that this approach suits your particular environment, you should test all options against your specific requirements.

The various options are discussed below.

Using the Fill Method of SqlDataAdapter

As previously discussed, the SqlDataAdapter is used to fill a DataSet with data from a database. One of the overloaded Fill methods (shown in the following code) takes two integer index values.

public int Fill(
   DataSet dataSet,
   int startRecord,
   int maxRecords,
   string srcTable

The startRecord value indicates the zero-based index of the start record. The maxRecords value indicates the number of records, starting from startRecord, to copy into the new DataSet.

Internally, the SqlDataAdapter uses a SqlDataReader to execute the query and return the results. The SqlDataAdapter reads the results and creates a DataSet based on the data read from the SqlDataReader. The SqlDataAdapter copies all of the results through startRecord and maxRecords into a newly generated DataSet and discards the results that it doesn't need. This means that a lot of unnecessary data could potentially be pulled across the network to the data access client, which is the primary drawback to this approach.

For example, if you have 1,000 records and want records 900 through 950, the first 899 records are still pulled across the network and discarded on the client side. This overhead would probably be minimal for small result sets, but could be significant when you page through larger sets of data.


Shashi Ray

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend