ADO.NET and SQL Server Data Paging

12 Aug 2005 12:03 PM

Natan Vivo

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.

12 Aug 2005 12:17 PM

Cowboy (Gregory A. Beamer) - MVP

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.

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

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

Show quoteHide quote

"Natan Vivo" wrote:

> 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.
>

12 Aug 2005 8:49 PM

Natan Vivo

Cowboy (Gregory A. Beamer) - MVP wrote:

> 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.

12 Aug 2005 12:27 PM

Elton W

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_w***@hotmail.com

Show quoteHide quote

"Natan Vivo" wrote:

> 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.
>

12 Aug 2005 1:05 PM

Dan Guzman

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

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quoteHide quote

"Natan Vivo" <nv***@terra.com.br> wrote in message
news:u8ZuMXznFHA.3256@TK2MSFTNGP12.phx.gbl...
>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.

12 Aug 2005 1:06 PM

Rick Sawtell

Take a look at:

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

Rick Sawtell
MCT, MCSD, MCDBA

12 Aug 2005 2:43 PM

Marc Robitaille

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.

"Natan Vivo" <nv***@terra.com.br> a écrit dans le message de news:

u8ZuMXznFHA.3***@TK2MSFTNGP12.phx.gbl...
Show quoteHide quote

>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.