How does paging work in asp.net?

Question

I am curious to know how does paging work in asp.net? If my query returns 500 records, and my gridview paging limits to 25 records per page, when the gridview loads, does the recordset return 25 records or 500 records?

If the recordset returns 25 records, how does ado communicate with SQL to return records for page two?

If the recordset returns 500 records, are they cached within the client side?

Answer 1

http://msdn.microsoft.com/en-us/library/tx1c9c2f.aspx

http://www.sql-server-performance.com/articles/asp_ado/new_features_ado.net_2_p1.aspx

Answer 2

From MSDN:

the GridView control will perform paging by getting all of the data records from the source, displaying only the records for the current page, and discarding the rest.

So the answer is, it doesn't really do efficient "paging", like many aspects of Web Forms, its all abstracted away. It's not really doing a "SELECT TOP 10".

It's simply disregarding the records it doesnt need - but the DB call is still a "SELECT *".

That is why many people (myself included), prefer to write custom yet simple paging with LINQ, using the Skip and Take IEnumerable extension methods.

E.g

yourDbContext.Where(s => somePredicate).Skip((pageNum - 1) * pageSize).Take(pageSize);

Answer 3

You might want to depend on client side pagination approaches via javascript. jqGrid is an excellent jQuery based solution. Of course this would mean that you'd have to load the entire data on the page. Else you'd need to write your stored procedure to return data from a particular page. But in this case you'd have to implement your own paging techniques. Such stored procedures you create must always return two result sets. One that returns the actual data, and the other resultset which returns basic pagination info, like total no.of records, total pages, & current page.