Iterating through specific rows of DataReader...

Discussion in 'ASP .Net' started by Jacko, Oct 22, 2003.

  1. Jacko

    Jacko Guest

    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
     
    Jacko, Oct 22, 2003
    #1
    1. Advertising

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

    ---
    Posted using Wimdows.net Newsgroups - http://www.wimdows.net/newsgroups/
     
    Wim Hollebrandse, Oct 22, 2003
    #2
    1. Advertising

  3. Jacko

    Jacko Guest

    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

    Rajesh.V wrote:

    > Not possible, DataSet would be a candidate to start somewhere in the middle.
    >
    > "Jacko" <> wrote in message
    > news:...
    >
    >>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
    >>
    >>

    >
    >
     
    Jacko, Oct 22, 2003
    #3
  4. Jacko

    fdg Guest

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





    Rajesh.V wrote:

    > 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.....
    >
    >
    > "Jacko" <> wrote in message
    > news:...
    >
    >>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
    >>
    >>Rajesh.V wrote:
    >>
    >>
    >>>Not possible, DataSet would be a candidate to start somewhere in the
    >>>

    > middle.
    >
    >>>"Jacko" <> wrote in message
    >>>news:...
    >>>
    >>>
    >>>>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
    >>>>
    >>>>
    >>>>
    >>>

    >
    >
     
    fdg, Oct 22, 2003
    #4
  5. Jacko

    Bob Lehmann Guest

    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

    "fdg" <> wrote in message news:...
    > 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!!
    >
    >
    >
    >
    >
    > Rajesh.V wrote:
    >
    > > 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.....
    > >
    > >
    > > "Jacko" <> wrote in message
    > > news:...
    > >
    > >>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
    > >>
    > >>Rajesh.V wrote:
    > >>
    > >>
    > >>>Not possible, DataSet would be a candidate to start somewhere in the
    > >>>

    > > middle.
    > >
    > >>>"Jacko" <> wrote in message
    > >>>news:...
    > >>>
    > >>>
    > >>>>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
    > >>>>
    > >>>>
    > >>>>
    > >>>

    > >
    > >

    >
     
    Bob Lehmann, Oct 22, 2003
    #5
  6. Jacko

    Rajesh.V Guest

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

    "Jacko" <> wrote in message
    news:...
    > 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
    >
     
    Rajesh.V, Oct 22, 2003
    #6
  7. Jacko

    Rajesh.V Guest

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


    "Jacko" <> wrote in message
    news:...
    > 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
    >
    > Rajesh.V wrote:
    >
    > > Not possible, DataSet would be a candidate to start somewhere in the

    middle.
    > >
    > > "Jacko" <> wrote in message
    > > news:...
    > >
    > >>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
    > >>
    > >>

    > >
    > >

    >
     
    Rajesh.V, Oct 22, 2003
    #7
  8. Jacko

    Rajesh.V Guest

    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" <> wrote in message news:...
    > 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!!
    >
    >
    >
    >
    >
    > Rajesh.V wrote:
    >
    > > 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.....
    > >
    > >
    > > "Jacko" <> wrote in message
    > > news:...
    > >
    > >>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
    > >>
    > >>Rajesh.V wrote:
    > >>
    > >>
    > >>>Not possible, DataSet would be a candidate to start somewhere in the
    > >>>

    > > middle.
    > >
    > >>>"Jacko" <> wrote in message
    > >>>news:...
    > >>>
    > >>>
    > >>>>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
    > >>>>
    > >>>>
    > >>>>
    > >>>

    > >
    > >

    >
     
    Rajesh.V, Oct 23, 2003
    #8
  9. Jacko

    fdg Guest

    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!

    fdg wrote:

    > 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!!
    >
    >
    >
    >
    >
    > Rajesh.V wrote:
    >
    >> 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.....
    >>
    >>
    >> "Jacko" <> wrote in message
    >> news:...
    >>
    >>> 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
    >>>
    >>> Rajesh.V wrote:
    >>>
    >>>
    >>>> Not possible, DataSet would be a candidate to start somewhere in the
    >>>>

    >> middle.
    >>
    >>>> "Jacko" <> wrote in message
    >>>> news:...
    >>>>
    >>>>
    >>>>> 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
    >>>>>
    >>>>>
    >>>>>
    >>>>

    >>
    >>

    >
     
    fdg, Oct 23, 2003
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JC

    Missing rows in DataReader

    JC, Jun 24, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    408
  2. Daves

    Datareader and specific row

    Daves, Mar 26, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    494
    Mike Douglas
    Mar 26, 2005
  3. Mattyw
    Replies:
    2
    Views:
    2,912
    Mattyw
    Sep 12, 2005
  4. dew
    Replies:
    6
    Views:
    39,498
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
    Jan 21, 2006
  5. carl
    Replies:
    5
    Views:
    2,379
    James Kanze
    Nov 25, 2009
Loading...

Share This Page