I'm writing an ASP.NET application that uses a SQL Server 2000 database. The application searches in large tables with 500, 000+ Records and then displays the search results, the search results could be easily 20,000 or 30,000 results. Ofcourse i need to use paging to show like 10 or 20 results per page.
I can't use the DataSet ofcourse, that would be too stupid from me to get 30,000 records to display only 20 records. On the other hand the DataReader is not a solution too as it's forward only and doesn't have the paging functions that were found in ADO (like PageSize or AbsolutePosition) . I see that many people are trying to do work arounds to do this work from SQL Server side.
My question is why didn't Microsoft include the nice features that were in ADO i really miss these old good days! Why didn't they make an object similar to ADO in .NET such that people can use it like they used ADO before (Calling ADO through COM from.NEY is a bad solution) I know that they felt that this must be done After releasing ASP.NET 1 So they decided to include ExecutePageReader function to solve what they messed up but then they removed this function in the final Release of ASP.NET 2.0 (Why )
Frankly speaking i took much time reviewing the work arounds for this problem and i wasn't impressed by the performance of any of them like Using Server Cursors (Resource Intensive) or Using 2 Nested Select Top Queries Asc-Desc (Too Slow)..... more stuff can be found here: http://codeproject.com/aspnet/PagingLarge.asp
Anybody knows why we have to suffer all of this, I'm sure they made ADO.NET for a reason but didn't they have all of this in mind Or am i asking for too much
I'm pretty sure that thousands of people have my same problem.
Paging Through Large Data
bakerb
First of all, I must challenge the decision of paging through 30,000 records - 10 rows at a time. Do you expect the user to page through 3000 pages to find the one row he needs
Besides that, there are inbuilt facilities in both .NET and SQL Server to make this happen. Of course the newer .NET 2.0 and SQL2k5 have better facilities than the predecessors, but DataAdapter.Fill has an overload that does exactly what you need to do.
However, given the amount of data you are dealing with, this problem is better solved using search, rather than paging.
Sahil Malik [MVP C#]
Author: Pro ADO.NET 2.0
http://www.winsmarts.com
Liora Milbaum
No such feature was implemented because of the .NET disconnected data architecture. It is not possible to create automatic paging with a disconnected data source; how do you know which record you are on once the connection is closed Whether the disconnected data architecture was a good idea in the first place is debatable, but I think it was the right decision. It allows much more flexibility in how the data is handled in memory without worrying about how you are connected to the database.
Regardless, you have no control over the .NET architecture, so what you really need is an answer to your question, which is, as I understand it: what is the best way to implement paging in a .NET application The answer: the TOP clause. Basically, you store the current "first record" (call it X, which would start at 0), and structure your query such that you always get the next n records, where n is your page size. Something like this:
SELECT TOP n * FROM MyTable where MyPrimaryKey > X order by MyPrimaryKey
As you page back and forth, increment or decrement x by n as appropriate. This can be done where the sort is any unique field. Sorting and paging on non-unique fields is much more difficult, and requires a much more specific knowledge of your db schema and data.
Obviously this is not as simple as the ADO paging functions; however, it has the big advantage of not requiring you to keep an open connection to the database while the results are being paged.
edmund1