Obtaining the number of records in a sqlDataReader

Can anyone tell me how I can obtain the number of records returned in a sqlDataReader recordset?You will need to read through the records to know how many rows, or alternately, return the rowcount as a column:

SELECT *,(SELECT COUNT(*) FROM Customer WHERE ID=7)
FROM Customer WHERE ID=7

If using stored procedures, perhaps you can do this more elegantly.

Why do you need the count?|||You can also check the @.@.rowcount system variable after the select

select ...
set @.Count = @.@.rowcount|||I need the count because the project I am working on needs to do specific tasks dependent on the no of records returned.

How can I access the @.@.rowcount from inside my code?|||Well, then I think the reality is you are going to have to (either in a seperate query or in a subquery) run the COUNT(*) query and send that as a column in the select statement. You could pass an OUTPUT parameter, but I do not believe the OUTPUT parameter will be readable until the DataReader has been read through.

Perhaps you should just use a DataSet, which has the count available.|||Perhaps a dataset is the better tool for the job then. Will it have any significant overhead compared to a datareader?|||Yes. Rather than reading records one at a time in firehose mode, it will read all rows into memory, and store them in memory (the dataSet is an in-memory representation). If you are processing a huge resultset, it could be a problem vs. the DataReader, but generally it is OK as well.|||You can also try using a stored procedure and you can return the count as either a seperate resultset or using an output paramater.