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?

Unless you edited this code prior to posting, I do not think this code will do what you and others think it is logically doing, so replacing it would change it. As it's posted above, this code will always return 1 because it's looping over the resultset and maintaining a counter, and the resultset will always have only a single row with a single column, which it's not actually reading. — Bruce Dunwiddie, Jun 26 '14 at 17:47

Steve · Accepted Answer · 2014-06-26T17:20:22.120

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());
    }
}

This way is not only simpler, it's much more efficient. It avoids having to return the entire data table just to obtain the record count. — Dom, Jun 26 '14 at 17:25

How can I unkludgify this record-counting function?

1 Answers1