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?
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);