Is it possible to retrieve the count of rows in a SqlDataReader object?

Question

Is it possible to retrieve the count of rows in a SqlDataReader object?

Larry Kruse

SSCrazy

Points: 2617
More actions
October 13, 2010 at 11:00 am

#98914

I have created a SqlCommand object that executes a stored procedure to return a result set. I have been unable to find a method or property that will show me the count of rows that have been returned with a call similar to this:SqlDataReader myRdr = myCmd.ExecuteReader();
while (myRdr.Read())
{
msg = String.Format("{0} users retrieved for Geocoding for Org_id = {1}.", myRdr.Rowcount, Org_ID ;
}
The class definition only shows a HasRows() method which returns a boolean value. I'd like to know how many rows the execution of the stored procedure retrieved. It seems like it would be a very commonly used/wanted property.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think the reader has a reader.RecordsAffected property;

so you should be able to get the # records from reader.RecordsAffected?, right? or is that for a different purpose than i think?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

ok a bit of reearch reveals a few things.the .RecordsAffected will pretty much return -1 every single time.

There is no count because the reader is streaming the results in from the

DB server. IOW, it's possible the DB server is still finding records that

match your criteria after you start using the data reader.

everything i googled said if you need a count before you start using the datareader, to do an executescalar to get the count(*) of the nearly same query before hand, or stick the results in a DataTable object so you can get the Rows.Count.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Larry Kruse SSCrazy Points: 2617 More actions · Answer 3

Thanks Lowell...I was kind of afraid that was the case. It seems kind of like it would be something that a lot of folks would want to know.

I guess that it sort-of makes sense given that it could potentially return to the caller before it's completed fetching all of the data, but that seems like it opens up problems in other areas too...

I was really hoping that it wouldn't be necessary to utilize a table adapter just to determine this value ahead of time.

I appreciate your taking a look at it though.

Just as an afterthought: Can I read through the result set twice? (I'm thinking that if it can be done, then I could just read through it all once to obtain the count and then run through it a second time to process the data.)

Lowell SSC Guru Points: 323518 More actions · Answer 4

Larry Kruse (10/13/2010)
Thanks Lowell...I was kind of afraid that was the case. It seems kind of like it would be something that a lot of folks would want to know.
I guess that it sort-of makes sense given that it could potentially return to the caller before it's completed fetching all of the data, but that seems like it opens up problems in other areas too...
I was really hoping that it wouldn't be necessary to utilize a table adapter just to determine this value ahead of time.
I appreciate your taking a look at it though.
Just as an afterthought: Can I read through the result set twice? (I'm thinking that if it can be done, then I could just read through it all once to obtain the count and then run through it a second time to process the data.)

Do you need the count BEFORE you read it? stating the obvious, you could count the records in the loop while you are doing each Reader.read, but then the count is only ready when you are done reading.... but if you need the count beforehand, i think i'd stick with an executeScalar featuring SELECT count(*) before i'd loop thru the reader twice.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!