Paging of Large Results Using Server Cursors

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. Unfortunetly ADO.NET doesn't support the paging functions that were found in ADO (like PageSize or AbsolutePosition) so i have to implement the paging myself.

I've read many articles that talk about how paging could be implemented. Most of them suggest doing the paging through SQL Server using Server Cursors. I know that cursors are resource intensive and should be avoided whenever possible but it seems that this is the only solution that fits. I just want you to notice that the cursor will just loop through 20 or 30 entries no more (Page Size) So is this a problem?

I will be using code that looks similar to this:

--

DECLARE @.PK /* PK Type */
DECLARE @.tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @.PK FROM Table ORDER BY SortColumn

OPEN PagingCursor
FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK

WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
BEGIN
INSERT @.tblPK(PK) VALUES(@.PK)
FETCH NEXT FROM PagingCursor INTO @.PK
SET @.PageSize = @.PageSize - 1
END

CLOSE PagingCursor
DEALLOCATE PagingCursor

SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn

I got this from the article http://codeproject.com/aspnet/PagingLarge.asp

Another method was suggested also that uses RowCount but it doesn't work for some technical reasons discussed in the article above.

So what do you think should i move on or what?

Regards,

Mohamed Salah

Please take a look at Aaron's article on this:

http://aspfaq.com/show.asp?id=2120