Casting Exception when trying to get value from ExecuteScalar()

Question

In the below code, the statement 1 throws casting exception. I am wondering why isnt it unboxing?
The statement 2 works fine but I want to know why the first one is wrong?

using (IDbCommand command = connection.CreateCommand())
{
    command.CommandText = string.Format("SELECT COUNT(1) FROM {0}", tableName);
    int count = (int)command.ExecuteScalar(); //statement 1
}
//int count = Convert.ToInt32(command.ExecuteScalar()); //statement 2

Answer 1

Sigh, execute scalar returns a long/int64, seeing as you wrote the SQL command you know the return vale is going to be a computed whole number (SELECT COUNT(..., also SELECT MAX(...

Ignore the other advice, all you need is a cast. (NO these commands will never return a string, i.e. "42")

int count = (int)(long)command.ExecuteScalar();

or if worried about large numbers

long count = (long)command.ExecuteScalar();

Unless, for some weird reason you don't have control over the SQL statement being executed why complicate matters with Convert, or boxing/unboxing. Good greif people, K.I.S.S., down with code bloat, ... and just answer the question.

Answer 2

Casting and converting are not the same thing. Casting to an int is telling the compiler that the data returned from ExecuteScalar is already an int and should be put into an int variable.

Converting it will try and take the data returned from ExecuteScalar (regardless of datatype) and try to convert it to an int.

A common example is if your query returns a string "42". You can't cast "42" to an int because it's a string, but you can convert it.