ADO.NET and SQL Server Data Paging

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Natan Vivo - 12 Aug 2005 13:03 GMT

I had to built a custom data paging control for my asp.net app and SQL
Server.

This is what I'm doing:

1. do a SELECT to a SqlDataReader
2. create a DataTable with data from GetSchema()
3. loop trough the datareader using .Read() to the point I want to start
4. add data to DataTable with max of [pageSize] records
5. close the data reader and return

The question is:

Isn't looping reader.Read() doing nothing to go to start point where I
want start getting data too expensive? Although DataReader is fast,
won't ".Read()" get the row data from the server to the client? If so,
if I need to get data from record 10000 ahead, it is actually reading
all 10 thousand rows of data, so it is a big waste of resources.

Maybe someone who knows more about the internals of the DataReader can
answer this.

Since SQL Server doesn't support "LIMIT" like keywords, if there is a
better way to page data in sql server, I would like to know.

Thanks.

Reply to this Message

Cowboy (Gregory A. Beamer) - MVP - 12 Aug 2005 13:17 GMT

Provided you are using an incrementing number, like an Identity column, you
can store first and last and then use a DataSet and bind (avoid reader to
fill a DataTable and make the code a bit simpler). If you really like using a
Reader (MS does under the hood), it is not a problem.

Using the "pointers" you are storing, you can easily request data from that
point. If you like persisting the data rather than requesting each time, read
the DataTable as XML and pull the subset you want to paint on the screen.

Signature

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

> I had to built a custom data paging control for my asp.net app and SQL
> Server.
[quoted text clipped - 22 lines]
>
> Thanks.

Reply to this Message

Natan Vivo - 12 Aug 2005 21:49 GMT

> Provided you are using an incrementing number, like an Identity column, you

No, I am not. That is why I needed to create my own pagination. There is
no way to determine what is beeing paginated neither the type of the
field in order by.. It may be text, may be number...

Does anyone know if the reader.Read() will actually read the data? if
yes, than probably there is no better way to do this. My inplementation
is pretty like using dataset, saving that i don't need to instantiate a
DataAdaper or neither a DataSet..

Thanks.

Reply to this Message

Elton W - 12 Aug 2005 13:27 GMT

You can think about following query:

SELECT TOP PAGE_SIZE * FROM TABLE_NAME WHERE KEY_ID > last_ID ORDER BY KEY_ID

HTH

Elton Wang
elton_wang@hotmail.com

> I had to built a custom data paging control for my asp.net app and SQL
> Server.
[quoted text clipped - 22 lines]
>
> Thanks.

Reply to this Message

Dan Guzman - 12 Aug 2005 14:05 GMT

Check out http://www.aspfaq.com/show.asp?id=2120 for various paging methods
Personally, I build parameterized dynamic SQL on the client side using the
query technique below. The primary key (or unique columns) are specified in
the WHERE and ORDER BY clauses.

use Northwind

--first page
SELECT TOP 10 OrderID, CustomerID, OrderDate
FROM Orders
ORDER BY OrderID ASC

--next page
SELECT TOP 10 OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderID > 10257 --last OrderID from currently displayed page
ORDER BY OrderID ASC

--prev page
SELECT OrderID, CustomerID, OrderDate
FROM
(
SELECT TOP 10 OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderID < 10258 --first OrderID from currently displayed page
ORDER BY OrderID DESC
) AS a
ORDER BY OrderID ASC

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

>I had to built a custom data paging control for my asp.net app and SQL
>Server.
[quoted text clipped - 22 lines]
>
> Thanks.

Reply to this Message

Rick Sawtell - 12 Aug 2005 14:06 GMT

Take a look at:

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

Rick Sawtell
MCT, MCSD, MCDBA

Reply to this Message

Marc Robitaille - 12 Aug 2005 15:43 GMT

You can use this

Dim oConnection As New SQLConnection("Provider...
oConnection.Open

Dim oDataAdapter As New SqlDataAdapter("SELECT ...", oConnection)

Dim oDataSet As DataSet = New DataSet

oDataAdapter.Fill(oDataSet, CurrentPageSize, PageSize, mstrTableName)

If you have 400 records and you need the first 100 records then
CurrentPageSize = 0 and PageSize = 100
If you need the next 100 records then CurrentPageSize = 100 and PageSize =
100

Good luck
Marc R.

>I had to built a custom data paging control for my asp.net app and SQL
>Server.
[quoted text clipped - 22 lines]
>
> Thanks.

Reply to this Message

ADO.NET and SQL Server Data Paging

Free Magazines