Retrieve parts of recordset on web request is a very common task. Trivial method using SqlDataSource as Data Connector and GridView or List View with Data Pager Control to view recordset and its page.
Web request resulting million records will degrade performance even though developer break down onto pages. According to some experiments there is a better way than usual method to do web paging.
Please check these links http://www.4guysfromrolla.com/articles/031506-1.aspx, http://www.4guysfromrolla.com/webtech/042606-1.shtml
The best performance using record ranking method and fortunately start from SQL Server 2005 it has record ranking function
In earlier post about SQL Server Row_Number() function, I have explained how to code and the function’s result on sql.
To do custom paging on web, we need ObjectDataSource because it has attributes like
MaximumRowsParameterName, etc which are enabled us to write a code / class that handles web paging behaviour.
We can write any database operation on methods inside ObjectDataSource so that makes ObjectDataSource flexible.
Other common component on ASP .Net like SqlDataSource is only work for SQL Server and does not has default custom paging capabilities.