in all the datapager examples i've seen (using a LinqDataSource tied to a ListView) the queries to the database return the full recordset. how can i get JUST the page of rows that i want to display?
for example, if i have a table with 1million rows and i want to see the 10 results on page 5, i would be getting rows 51 to 60.
i'm sure i have to implement custom paging but i haven't found any good examples.
If you're using MSSql2005, take a look at this article.
As you can see, the trick is to use the function ROW_NUMBER(), that allow you to get the sequential number of a row in a recordset. With it you can simply enable pagination based upon the number of rows you want to get in a page.
There are many ways of doing this, however, I personally like a SQL based solution that goes to the database and gets the result set. This 4GuysFromRolla Article does a good job of explaining it.
I was under the impression (from Scott Guthie's blog post "LINQ to SQL (Part 9)") that the LinqDataSource handles the paging for you at the database level:
One of the really cool things to notice above is that paging and sorting still work with our GridView - even though we are using a custom Selecting event to retrieve the data. This paging and sorting logic happens in the database - which means we are only pulling back the 10 products from the database that we need to display for the current page index in the GridView (making it super efficient).
(original emphasis)
If you are using some custom paging, you can do something like this in LINQ to SQL:
var tagIds = (from t in Tags where tagList.Contains(t.TagText) select t.TagID).Skip(10).Take(10).ToList();
This is telling LINQ to take 10 rows (equivalent to T-SQL "TOP 10"), after skipping the first 10 rows - obviously these values can be dynamic, based on the Page Size and page number, but you get the idea.
The referenced post also talks about using a custom expression with a LinqDataSource.
Scott has more information on Skip/Take in Part 3 as well.