IDataReader - Any way to get the total rows?

Question

Is there any way to get the total number of rows returned from a SQL query (from the IDataReader) before iterating through the rows by using reader.Read();?

Apraxia

@sll: The reference you point to is no longer valid, go figure. – Apraxia

Updated link, See how to get number of rows using SqlDataReader in C# – Grub

Answer 1

No.

IDataReader is a simple forward-only view of a resultset; it cannot get a count.

Buy

Answer 2

No, the datareader will not return a count first. However, if you do need to do it, use two queries that return multiple result sets.

for example in SQL Server:

sql = "SELECT COUNT(1) FROM A; SELECT * FROM A;"

Iterate the result sets. Use the IDataReader on the second result set.

The database server should be able to do this quite fast if it is using appropriate indexes.

Nonoccurrence

Something like this fails in the general case, e.g. EXECUTE ReportQuestionsWithMoreThanTenAnswersAndNoAccepted – Leffert

Answer 3

Well past the date posted but there is a way, that worked for me took a while to get because i suck at wording my google searches.

dbCommand.Connection.Open();
//dbReader = dbCommand.ExecuteReader();  I left this here to show not to do the read

searchCount = dbCommand.ExecuteScalar(); // this line returns the value count
                                         // from my tests on my reader it works perfectly

Neoptolemus

Wouldn't this require two separate executions, meaning that it could be vulnerable to a race condition if someone changes something between calling ExecuteScalar() (to get the count) and calling ExecuteReader() (to get the rows themselves)? – Nutriment

Answer 4

Maybe not an good idea, but i got count using variable inside while loop. This code work fine for me:

IDataReader reader = SqlHelper.ExecuteReader(sqlConnectionString, Procedure.GetSuperUserOwnerDetails, ProfileName);
int count = 0;
while (reader.Read())
{
   count = count + 1;
}
lblProfileOperator.Text = " Owner : " + count;

Unweighed

Answer 5

The count of rows calculated only when you read the DataReader but i didnt get it , why you want to know the rows count before reading it.

Dibbuk

To allocate exactly the perfect amount of storage necessary if storing a copy of the whole result set (instead of, for example, letting List(T).Add() automatically grow its Capacity often). – Meredith

That's the most obvious one I had in mind too. Another use case would be to update a determinate progress bar while reading rows. Another MIGHT be as input into a heuristic for how to process the rows (i.e., you may want to process 100 rows differently from how you would process 10 million rows, and it might be a pain to switch partway through). Probably tons of other legitimate reasons, too. – Nutriment

To create a progress bar during an export. To provide user feedback. – Leffert