Without stored procedures, how do you page result sets in ASP.NET?

Question

Without stored procedures, how do you page result sets retrieved from SQL Server in ASP.NET?

Answer 1

You could use LINQ, for instance:

 var customerPage = dataContext.Customers.Skip(50).Take(25);

and then display those 25 customers.

See Scott Guthrie's excellent Using LINQ-to-SQL - section 6 - retrieve products with server side paging.

Another option (on SQL Server 2005 and up) is to use ordered CTE's (Common Table Expression) - something like this:

WITH CustomerCTE AS
(
  SELECT CustomerID, 
         ROW_NUMBER() OVER (ORDER BY CustomerID DESC) AS 'RowNum'
  FROM Customers
)
SELECT * FROM CustomerCTE
WHERE rownum BETWEEN 150 AND 200

You basically define a CTE over your sort critiera using the ROW_NUMBER function, and then you can pick any number of those at will (here: those between 150 and 200). This is very efficient and very useful server-side paging. Join this CTE with your actual data tables and you can retrieve anything you need!

Marc

PS: okay, so the OP only has SQL Server 2000 at hand, so the CTE won't work :-(

If you cannot update to either SQL Server 2005, or .NET 3.5, I'm afraid your only viable option really is stored procedures. You could do something like this - see this blog post Efficient and DYNAMIC Server-Side paging with SQL Server 2000, or Paging with SQL Server Stored Procedures

Answer 2

The best is to use an ORM which will generate dynamic paging code for you - LINQ To SQL, NHibernate, Entity Framework, SubSonic, etc.

If you have a small result set, you can page on the server using either DataPager, PagedDataSource, or manually using LINQ Skip and Take commands.

Answer 3

(new answer since you're using SQL Server 2000, .NET 2.0, and don't want to use an ORM)

There are two ways to handle paging in SQL Server 2000:

1. If you have a ID column that's sequential with no holes, you can execute a SQL string that says something like SELECT Name, Title FROM Customers WHERE CustomerID BETWEEN @low and @high - @low and @high being parameters which are calculated based on the page size and page that you're on. More info on that here.

2. If you don't have a sequential ID, you end up using a minimum ID and @@rowcount to select a range. For instance, SET @@rowcount 20; SELECT Name, Title FROM Customers WHERE CustomerID > @low' - either calculating @low from the page size and page or from the last displayed CustomerID. There's some info on that approach here.

If you have a small dataset, you can page through it in .NET code, but it's less efficient. I'd recommend the PagedDataSource, but if you want to write it yourself you can just read your records from a SqlDataReader into an Array and then use the Array.Range function to page through it.

Answer 4

This is how I handled all of my paging and sorting with AJAX in my ASP.NET 2.0 application.

http://programming.top54u.com/post/AJAX-GridView-Paging-and-Sorting-using-C-sharp-in-ASP-Net.aspx

Answer 5

Well my general approach is usually to create two tables for the results to be paged. The first is an info table that has a search id identity column and has the min and max row numbers. The second table contains the actual results and has an identity column for the row number. I insert into the second table and get the min and max rows and store them in the first table. Then I can page through by selecting just the rows I want. I usually expire the results after 24 hours by using code right before the insert. I usually use a stored procedure to do the inserts for me but you could do it without a stored procedure.

This has the advantage of only performing the more complex sql search once. And the dataset won't change between page displays. It is a snapshot of the data. It also can ease server side sorting. I just have to select those rows in order and reinsert into the second table.