10

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();?

michael
  • 14,844
  • 28
  • 89
  • 177
  • @sll: The reference you point to is no longer valid, go figure. – michael Jul 31 '13 at 20:36
  • Updated link, See [how to get number of rows using SqlDataReader in C#](http://stackoverflow.com/q/1383315/485076) – sll Aug 07 '13 at 13:29

5 Answers5

13

No.

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

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
6

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.

Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
sgtz
  • 8,849
  • 9
  • 51
  • 91
  • Something like this fails in the general case, e.g. `EXECUTE ReportQuestionsWithMoreThanTenAnswersAndNoAccepted` – Ian Boyd Jan 15 '19 at 14:37
4

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
j0k
  • 22,600
  • 28
  • 79
  • 90
Josh
  • 41
  • 1
  • 1
    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)? – Joe Amenta May 05 '15 at 18:17
0

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;
Julien Jacobs
  • 2,561
  • 1
  • 24
  • 34
spujaname
  • 33
  • 5
-1

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.

Muhammad Al-Own
  • 237
  • 3
  • 13
  • 2
    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()](http://msdn.microsoft.com/en-us/library/3wcytfd1) automatically grow its [Capacity](http://msdn.microsoft.com/en-us/library/y52x03h2) often). – binki Oct 01 '13 at 18:58
  • 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. – Joe Amenta May 05 '15 at 18:15
  • To create a progress bar during an export. To provide user feedback. – Ian Boyd Jan 15 '19 at 14:38