OledbDataReader function to return integer

Question

Working in C#, I'm having trouble with a function. Function runs a query and the query is supposed to return one integer value but I'm having trouble returning it. I keep getting errors like:

• Unable to cast object of type oleDbDataReader to type Int32
• Specified cast is not valid

Not sure how to do this with C# and OleDbDataReader. My code is below

     public static int FifthQuery()
     {
         int _value = 0;
         OleDbConnection _connectMe = Utilities.OledbConnect();

         OleDbCommand _query1 = new OleDbCommand();
         _query1.Connection = _connectMe;
         _query1.CommandText = "SELECT count(*) FROM GIS.PERSONS where Name_Prefix = 'Dr.'";
         OleDbDataReader _reader = _query1.ExecuteReader();

         _reader.Read();

              //_value = Convert.ToInt32(_reader);
              _value = _reader.GetInt32(0);


         return _value;
     }

Answer 1

Since you using COUNT(*), using ExecuteScalar would be better approach.

Executes the query, and returns the first column of the first row in the result set returned by the query.

int _value = (int)_query1.ExecuteScalar();

Also use using statement to dispose your OleDbConnection and OleDbCommand.

using(OleDbConnection _connectMe = Utilities.OledbConnect())
using(OleDbCommand _query1 = _connectMe.CreateCommand())
{
    _query1.CommandText = "SELECT count(*) FROM GIS.PERSONS where Name_Prefix = 'Dr.'";
    _connectMe.Open();
    int _value = (int)_query1.ExecuteScalar();
}