in C# by (10.1k points)
0 like 0 dislike

How I cat to get number of rows using SqlDataReader in C# (C Sharp)?

1 Answer

0 like 0 dislike
by (10.1k points)

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=;Database=MyDb;User Id=Me;Password=glop;")) {
        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();

You may have to add a transaction not sure if reusing the same command will automatically add a transaction on it.

Your answer

Try to answer the question as detailed as possible.
Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
To avoid this verification in future, please log in or register.