Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 21 down vote favorite
2

my question is about how to get number of rows using SqlDataReader in C#. I've seen some answers around the net about this but none were clearly defined except for one that states to do a while loop with Read() method and increment a counter.

My problem is that I am trying to fill a multi-dimensional array with the first row being the column header names and every row after that to the be the row data.

I know that I can just dump the stuff in a List control and not worry about it, but for my own personal edification and I would also like to pull the data in and out of the array as I choose and display it in different formats.

So I think I can't do the Read() and then increment ++ way because that means that I would have to open Read(); and then open Read(); again to get amount of rows and then column data.

Not sure, so I turn to thee!

Just a small example of what I'm talking about:

int counter = 0;    

while (sqlRead.Read())
{
    //get rows
    counter++
}

and then a for loop to run through the columns and pop

something.Read();

int dbFields = sqlRead.FieldCount;

for (int i = 0; i < dbFields; i++)
{
   // do stuff to array
}
share|improve this question

5 Answers

up vote 28 down vote accepted

There are only two options:

  • Find out by reading all rows (and you might as well store them)

  • run a specialized SELECT COUNT(*) query beforehand.

Going twice through the DataReader loop is really expensive, you have to re-execute the query.

And (thanks to Pete OHanlon) the second option is only concurrency-safe when you use a transaction with a Snapshot|RepeatableRead isolation level.

Since you want to end up storing all rows in memory anyway the only sensible option is to read all rows in a flexible storage (List<>) and then copy the data to any format you want. The in-memory operation will always be much more efficient.

share|improve this answer
4  
Henk is right: there is no member of the DataReader that allows you to get the number of rows because it is a forward only reader. You are better off first doing getting the count and then executing the query, perhaps in a multi-result query so you only hit the database once. – flipdoubt Sep 5 '09 at 13:29
8  
The problem with the specialized count is that there's the potential for the count being different from the number of returned rows because somebody else has changed the data in a way that leads to the number of rows being returned. – Pete OHanlon Sep 5 '09 at 13:30
Pete, you are right, it would require an expensive IsolationLevel. – Henk Holterman Sep 5 '09 at 13:35
Good edit - I would suggest that this accepted as the answer by the OP. – Pete OHanlon Sep 5 '09 at 13:45
Thank you all! This is becoming more clear. So is it better to dump all the info to the DataSet or run through a SQL COUNT(*), store it and then run the required query? Or are we talking about running count and storing everything in the DataSet? – Tomaszewski Sep 6 '09 at 15:21
show 3 more comments
up vote 5 down vote

Per above, a dataset or typed dataset might be a good temorary structure which you could use to do your filtering. A SqlDataReader is meant to read the data very quickly. While you are in the while() loop you are still connected to the DB and it is waiting for you to do whatever you are doing in order to read/process the next result before it moves on. In this case you might get better performance if you pull in all of the data, close the connection to the DB and process the results "offline".

People seem to hate datasets, so the above could be done wiht a collection of strongly typed objects as well.

share|improve this answer
I love DataSets myself, since they're a well-written and extremely useful generic representation of table-based data. Weirdly enough, I've noticed that most people who eschew the DataSet for ORM are the same people who try to write their own code to be as generic as possible (usually pointlessly). – MusiGenesis Sep 5 '09 at 13:46
1  
Daniel, 'above' is not a good way to reference another answer. – Henk Holterman Sep 5 '09 at 14:01
up vote 5 down vote

Relational classes/DataSet is the suitable option.

using (DataTable dt = new DataTable())
 {
  dt.Load(sqlRead);
  Console.WriteLine(dt.Rows.Count);
}
share|improve this answer
Loading all data just to get number of rows is not a good idea. Especially with DataTable, which will result in bug memory overhead. – shatl Dec 13 '12 at 13:28
@shatl - Agree! but this option is suggested by many poster including Henk Holterman and I think OP wanted to make sure that there isn't any direct method which returns row count. – AVD Dec 13 '12 at 14:06
up vote 3 down vote

You can't get a count of rows directly from a data reader because it's what is known as a firehose cursor - which means that the data is read on a row by row basis based on the read being performed. I'd advise against doing 2 reads on the data because there's the potential that the data has changed between doing the 2 reads, and thus you'd get different results.

What you could do is read the data into a temporary structure, and use that in place of the second read. Alternatively, you'll need to change the mechanism by which you retrieve the data and use something like a DataTable instead.

share|improve this answer
up vote -4 down vote

Please try this

int rowCount = dataReader.Cast<><object>().Count();

in the "<>" please add "object".

this editor will not support to write any string inside "<>"

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.