Without stored procedures, how are your result sets of pages in ASP.NET?
You can use LINQ like:
var customerPage = dataContext.Customers.Skip(50).Take(25);
and then display those 25 clients.
See Scott Guthrie excellent Using LINQ-to-SQL - Section 6 - Loading Products with Server Side Swap.
Another option (on SQL Server 2005 and up) is to use an ordered CTE (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
Basically, you define CTEs by your critical category using a function ROW_NUMBER
, and then you can select any number of them you want (here: 150 to 200). It is very efficient and very useful server side paging. Join this CTE with your actual datasheets and you can get whatever you need!
Mark
PS: ok, so the OP only has SQL Server 2000 on hand, so CTE won't work: --(
If you can't upgrade to SQL Server 2005 or .NET 3.5, I'm afraid your only viable option is actually stored in procedures. You can do something like this - see this blog post Efficient and DYNAMIC Server Paging with SQL Server 2000 or Paging with SQL Server Stored Procedures
source to share
Your best bet is to use an ORM that will generate dynamic swap code for you - LINQ To SQL, NHibernate, Entity Framework, SubSonic , etc.
If you have a small result set, you can create a page on the server using DataPager , PagedDataSource, or manually using LINQ Skip and accept commands.
source to share
(new answer since you are using SQL Server 2000, .NET 2.0 and don't want to use ORM)
There are two ways to handle paging in SQL Server 2000:
-
If you have an id column that does not contain holes, you can execute a SQL line that says something like
SELECT Name, Title FROM Customers WHERE CustomerID BETWEEN @low and @high
- @low and @high, which will be computed based on the page and page size you are on. More on this here . -
If you don't have a sequential id, you can use the minimum id and @@ rowcount to select the range. For example
SET @@rowcount 20; SELECT Name, Title FROM Customers WHERE CustomerID > @low'
- either calculating @low from page and page size or from the last displayed CustomerID. There's information on this approach here .
If you have a small dataset, you can view it in .NET code, but it is less efficient. I would recommend PagedDataSource, but if you want to write it yourself, you can simply read your records from the SqlDataReader into an Array and then use the Array.Range function to view it .
source to share
This is how I handled all 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
source to share
Well my general approach is usually to create two tables to output the results. The first is an information table with a lookup id identification column and has line numbers min and max. The second table contains the actual results and has an ID 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 scroll the page by selecting the lines I want. I usually finish the results after 24 hours using the code right before pasting. I usually use a stored procedure for insert for me, but you can do it without a stored procedure.
This has the advantage of only performing the more complex SQL lookup once. And the dataset won't change between pages. This is a snapshot of the data. It can also facilitate server side sorting. I just need to select these rows in order and insert again into the second table.
source to share