Number of rows from datareader?

[dew]

How do I get the number of rows a datareader has? I can tell HasRows, but
not how many.

Thanks.

 

[Bruce Barker]

you need to read read to the end with a counter.

a datareader (at least for sqlserver) is a forward only cursor returning
data from the response stream buffer. thus you can not lnow how many rows
there are until you read to the end. on a big query you can read rows while
the server is still performing the query.

this is also why you can not read return paramater from a sp until you've
read all the result sets.

-- bruce (sqlwork.com)

 

[Ranjan Sakalley]

Hello dew,

No way other than iterating through the reader. Sometimes the overhead of
doing this is so much that I have seen people running a count query too,
before/after Read() ing the data, if needed.

You might want to post this question in microsoft.public.dotnet.framework.adonet.

HTH,
r.

 

[dew]

Thanks, all, I guess I'll have to use a dataset.

 

[Guest]

As one poster has already, commented, you can still use a DataReader. Just
populate it with 2 resultsets, the first one being the count of rows returns.
then use the DataReader's NextResultSet() method to switch to the one
containing all the rows.
Peter

 

[Bruce Barker]

this is really a bad pattern. it requires running the query twice, and if a
row is inserted/deleted between queries, the wrong answer can appear. to
make the query stable you have to use exclusive lock, or select into a temp
table.

-- bruce (sqlwork.com)

 

[Guest]

Good point, Bruce, if that precision is critical to the business logic
scenario.
But then, doing so would make it a "good pattern"?
Cheers,
Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com