0 like 0 dislike
14 views
asked in C sharp (C#) by (4.8k points)

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

Your answer

pre, code

Preview

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.

1 Answer

0 like 0 dislike
answered by (4.8k 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=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.

Related questions

0 like 0 dislike
1 answer 8 views
0 like 0 dislike
1 answer 5 views
0 like 0 dislike
1 answer 30 views
0 like 0 dislike
1 answer 15 views
0 like 0 dislike
1 answer 26 views
0 like 0 dislike
1 answer 8 views
0 like 0 dislike
1 answer 11 views
0 like 0 dislike
1 answer 27 views
0 like 0 dislike
1 answer 25 views
...