How to get row count from OdbcDataReader?

Question

I am using OdbcDataReader to get the results from my database and I want to know row count of the result. What would be the best approach to achieve this?

public string[] GetHeaderAndColumnValues(string[] arrAllColumns, string[] arrExtColumns, string sRowDelimiterDisplayText, OdbcDataReader readerOdbc)
{
    //Approach-1: Code to get row count but this is expensive approach.
    using (DataTable dt = new DataTable())
    {
        dt.Load(readerOdbc);
        int i = dt.Rows.Count;
    }   
}

NOTE: I just want row count from OdbcDataReader.

Answer 1

Well, you won't (and can't) know how many rows are in the result set until you read them all.

And while OdbcDataReader does have a RecordsAffectedProperty: it's

• valid until you've read all the rows, and
• only applicable for rows affected by insert, delete or update statements.

Which doesn't make it of much use for reads. So, you can either

• Count each row as you read it:

  using ( OdbcConnection connection = new OdbcConnection(connectionString) )
  using ( OdbcCommand command = connection.CreateCommand() )
  {
    connection.Open();
    command.CommandText = "select * from sys.objects";
    using ( OdbcDataReader reader = command.ExecuteReader() )
    {
      int rowcount = 0 ;
      while ( reader.Read() )
      {
        ++rowcount;
      }
    }
  }
  

• Or, if you need to know the total rowcount prior to doing something with the results, your best option is to use something like a dataAdapter and slurp the results set down en masse:

  DataTable dt = new DataTable() ;
  int rows ;
  
  using ( OdbcConnection connection = new OdbcConnection(connectionString) )
  using ( OdbcCommand command = connection.CreateCommand() )
  using ( OdbcDataAdapter adapter = new OdbcDataAdapter(command) )
  {
    connection.Open();
    command.CommandText = "select * from sys.objects";
    rows = adapter.Fill(dt);
  }
  
  Console.WriteLine( "adapter.Fill() returned {0}",rows);
  Console.WriteLine( "The data table contains {0} rows and {1} columns.",
    dt.Rows.Count ,
    dt.Columns.Count
    );
  

  which produces, in my case,

  adapter.Fill() returned 74
  The data table contains 74 rows and 12 columns.
  

A third, not very useful option would be to run two queries: one that fetches the rowcount:

select count(*)
from foo
where some-conditions

And a second, identical to the first, that returns the rows:

select *
from foo
where some-conditions

Execute the 1st using ExecuteScalar() and the second using your data reader.

This approach has two big issues:

1. You're doing twice the amount of work.
2. It introduces a concurrency issue: the row count might well change between the execution of the first and second queries.