Paging, DataGrid and SQL Server

OK...I have an SQL statement:

SELECT * FROM tblWoof

This has say 1 million rows.
Lets say I want the 2nd page, and my page size is 50.

I could let the DataGrid handle thism but that would mean passing 1 million rows, in a datatable, up through the tiers of my application, when all I want to display is the 2nd block of 50.

It doesn't take a genius to work out that this is bad.

So we create a function:

Public Function FetchWoofs(ByVal StartPage As Integer, ByVal PageLength As Integer) As DataTable
'code here to load data for given page
End Function

Using the Data Adapater we can fill a table with only the data we want using:

Dim da As New System.Data.SqlClient.SqlDataAdapter(SQL, MY_CONN_STRING)
Dim ds As New DataSet
da.Fill(ds, StartPage, PageLength, "Woofs")

The dataset is now filled with only the rows we require...Woohooooo. Good. Way better than passing 1 Million rows up to the UI.

OK. At 1st glance this seems a perfect way of doing things...but.
What actually happens here, is that SQL chucks 1 Million rows to the IIS box, then ADO.NET strips out the rows you require...so this is still not very good. I don't want 1 million records being passed around my network :(

Without using evil JOINS in SQL server on Top x and Not IN Top x-PageLength, and dealing with ordering, is it possible to return JUST the rows you require from SQL Server...ie, is there a function called:

SELECT Rows(50 To 70) *
FROM tblWoofs

?
WokaWell, there is no stright forward TSQL statement like that apart from the Top statement. Though it should be possible with Stored Proc. Sounds like this is what you are looking for.

http://www.aspfaqs.com/webtech/062899-1.shtml

Hope this helps.

Danial
This is what I have been doing.

ALTER PROCEDURE dbo.Contacts_GetContactsPage
(
@.PageSize int,
@.PageIndex int
)
AS
CREATE TABLE #TempTable
(
ContactRowID int IDENTITY(1,1),
ContactKey char(36)
)

INSERT INTO #TempTable (ContactKey)
SELECT Guid FROM Contacts
WHERE EmailAddress != 'web_admin@.pkpromo.com'
ORDER BY LastName, FirstName, MiddleName, EmailAddress

SELECT #TempTable.ContactRowID, Contacts.*
FROM #TempTable
INNER JOIN Contacts ON
Contacts.Guid = #TempTable.ContactKey
WHERE ContactRowID > @.PageSize * @.PageIndex
AND ContactRowID <= @.PageSize * (@.PageIndex+1)
ORDER BY #TempTable.ContactRowID
RETURN