How can I unkludgify this record-counting function?

Question

I have this code for getting a Count value from a table:

string connStr =
    @"Provider=Microsoft.ACE.OLEDB.12.0;User ID=NRBQBO;Password=NRBQCP;Data Source=C:\CCRWin\DATA\CCRDAT42.MDB;Jet OLEDB:System database=C:\CCRWin\Data\NRBQ.mdw";

using (var conn = new OleDbConnection(connStr))
{
    using (OleDbCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = @"SELECT Count(*) FROM platypi";
        cmd.CommandType = CommandType.Text;
        conn.Open();

        int i = 0;

        using (OleDbDataReader oleDbD8aReader = cmd.ExecuteReader())
        {
            while (oleDbD8aReader != null && oleDbD8aReader.Read())
            {
                i++;
            }
        }
        return i;
    }

It works, but I'm looking for a way to avoid the loop and simply return the count in one fell swoop, such as:

. . .
using (OleDbDataReader oleDbD8aReader = cmd.ExecuteReader())
{
    if (oleDbD8aReader != null && oleDbD8aReader.Read())
    {
        i = oleDbD8aReader.Value;
    }
}
return i;
. . . // 2B ||! 2B this. es la Frage

...but it's not obvious (to me) how to get that value. Surely it's possible, but how?

Answer 1

ExecuteScalar is your friend in this case

 int result = Convert.ToInt32(cmd.ExecuteScalar());

ExecuteScalar returns the first column of the first row in your resultset.
In this case you have just one row with just one value. So this is the perfect scenario for you.

Also notice that ExecuteScalar could return null, but in your case this problem doesn't exist because the function COUNT(*) returns always a count of records matched in a possible WHERE clause and, if there are no record matched, then the return value will be 0 (not null).

So your code becomes just

string connStr =
    @"Provider=Microsoft.ACE.OLEDB.12.0;User ID=NRBQBO;Password=NRBQCP;Data Source=C:\CCRWin\DATA\CCRDAT42.MDB;Jet OLEDB:System database=C:\CCRWin\Data\NRBQ.mdw";

using (var conn = new OleDbConnection(connStr))
{
    using (OleDbCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = @"SELECT Count(*) FROM platypi";
        // Not needed, Text is the default 
        // cmd.CommandType = CommandType.Text;
        conn.Open();

        int i = Convert.ToInt32(cmd.ExecuteScalar());
    }
}