There are only two options:
- Find out by reading all rows (and then you might as well store them)
- run a specialized SELECT COUNT(*) query beforehand.
Going twice through the DataReader loop is really expensive, you would 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 or 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<> or DataTable) and then copy the data to any format you want. The in-memory operation will always be much more efficient.
If you do not need to retrieve all the row and want to avoid to make a double query, you can probably try something like that:
using (var sqlCon = new SqlConnection("Server=127.0.0.1;Database=MyDb;User Id=Me;Password=glop;")) {
sqlCon.Open();
var com = sqlCon.CreateCommand();
com.CommandText = "select * from BigTable";
using (var reader = com.ExecuteReader()) {
//here you retrieve what you need
}
com.CommandText = "select @@ROWCOUNT";
var totalRow = com.ExecuteScalar();
sqlCon.Close();
}
You may have to add a transaction not sure if reusing the same command will automatically add a transaction on it.