Getting the rowcount from a DataReader

Since ADO.NET's DataReader is nothing more than a direct stream from the database, there's no way to find out the number of records being returned ... until now.

This idea just popped into my head, you can use the fact that multiple resultsets can be returned from a stored procedure. all you have to do is return a count, then return the actual rows ... very simple.

SELECT Count(*) as theCount FROM yourtable WHERE theID=@theID

SELECT * FROM yourtable WHERE theID=@theID

The trick to this is using the same WHERE statement in each query so that the same resultset will be reflected in the count. You can then use the .NextResult() method of the datareader to get each resultset

SqlDataReader reader = command.ExecuteReader();
reader.Read(); //must be called once before accessing the record

int count = (int)reader["thecount"];

reader.NextResult();

while (reader.Read())
{
//Loop through the actual resultset and do what you wish
}

Hope that helps someone out there :-)