Iterating through specific rows of DataReader...

[Jacko]

Hi guys,


Say I made a SELECT statement to my sql DB that would return 50 rows
that I will use a sqldatareader to access. Instead of iterating through
each and every row of the datareader, I'd like to just iterate through,
say, rows 20 through 30.

How can one do this?

Thanks very much

Jacko

 

[Wim Hollebrandse]

If you only need rows 20-30, why return all 50? Possibly to use them afterwards - at some point. But you want to close the DataReader as soon as possible and don't keep the connection open.

The DataSet offers the disconnected architecture that you want.

Regards,
Wim Hollebrandse
http://www.wimdows.net
http://www.wimdows.com

 

[Jacko]

Rajesh,

Just as I suspected. Thank you. One more question, though, if you will.
What if my SELECT statement gives 5,000 rows, or even 50,000. I want to
display this data only 50 rows per page. Would one populate a DataSet
with the entire 50,000 rows? Then programmatically display 50 at a time?
Or would one use a DataReader, and if one was viewing page 10, for
example, the program would skip the first 500 rows and then display 500
through 550?

Sorry if I'm a little too verbose about this. I just want the best way
to display a large amount of rows only a few rows per page.

Thanks very much for any help you can give. And thanks for your help to
everyone here, i see you have posted quite a few responses.

Jack

 

[fdg]

Rajesh,

We think alike. This is the way I had it before. But since i sometimes
have to delete rows, consecutive record numbers arent always 1,2,3,4,5
but can be 1,3,4,6, since i deleted 2 and 5 (for example). So a sql
query based purely on record number won't give me completely accurate
results if some of the rows are missing, thus the pattern of
consecutively growing record numbers is broken.

Would it be much of a performance hit if I do a datareader and just
DataReader.Read() through them until I get to the desired range? Even if
I go through, say, 500, or 1,000?

You're a big asset to this newsgroup!!

 

[Bob Lehmann]

Here is an article demonstrating paging using a Stored Procedure and temp
tables in SQL Server. It's for ASP, but you should be able to use it.
http://www.aspfaq.com/show.asp?id=2120

Bob Lehmann

 

[Rajesh.V]

Not possible, DataSet would be a candidate to start somewhere in the middle.

 

[Rajesh.V]

Nice question, which occurs to most dealing with huge data reporting
requiremnets.

Now for this, you will have to fool the user to thinking he can access any
record out of the data store. That is by using...
1. A search page which narrows down the result to manageable qty.
2. Now if he still wants to wade thru 50k or higher record we have to do
some tricky programming. will take time but worth the effort. So here goes.

a. first show him 20 records, (as much as can fit in a screen).
b. then when he requests to see next page fetch the next 20 records.
c. This way u have to keep the begin and end record numbers in a hidden
field.
d. Use custom paging which exposes the events next page and previous page of
the DataGrid.

At the db level say a storedproc accepts two params begin and start. Here is
where your Sql magic has to be worked in
extracting only the recordset for a given sql (say 25000 - 25100).

Also if you have a large no. of reports, duplicating the above is going to
be trying. Try dbnetgrid.com, their dbgrid is really good, i still don know
whether the component pulls all the records or a slice of them. The grid
handles this beautifully without any postback. Pagination comes with the
component as well as conversion to excel/xml/pdf.....

 

[Rajesh.V]

Hi fdg(din get ur name)

Tx and nice to be of some help. As u said of consecutive record nos. Anyway
to delete or update we have to fallback on the primarykey to uniquely id the
rec and not based on which position u are.

Talking of perf hit, if the records are small say less than 1k, its ok. But
jus thinkin of the fact u are pulling records from the db just to get at the
reqd ones and discarding is not good.

 

[fdg]

Thanks to all who helped me with my problem. After going to a couple
sites one of you suggested, ive decided to simply use the record number
of the 10th row of everypage as a hidden field, and will use that record
number as the marker for any future requests. Thanks again!