I have to create a page that browses through a very large MSSQL database. The development environment is C# and ASP.NET.
There seem to be two choices: Use a Dataset and paging, or use a Datareader. All the doc I can find seems to imply that all the records in a Dataset are saved "in memory" on the server. Using a Dataset for a large database seems to be likely to cause performance problems.
But if I use a Datareader, is there some way to get it to skip reading through the first X records in the result set from the query? Otherwise, every time they user pages forward, I am going to have to read the whole database up that point again.
Alternatively, is there an SQL method of asking for "the 100th through 150th rows that meet such and such a where clause"?
The goal is actually very simple. It is just the list function in and add, change, delete and list application for a large database. But I am at a loss as to the best way to code it for a real world, large database.