Casting Exception when trying to get value from Ex

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

You can only cast to int if the database field is indeed a 32bit signed integer. If it is long and/or unsigned the cast will fail.

Since the type is unlikely to change you can split the execution and the cast, and set a breakpoint right before the cast to see the correct type.

Answer 2

According to SqlCommand.ExecuteScalar Method document it returns null if the result is empty. You can't cast null into int.

However from my experience, it may also return Decimal type as well for some cases. But this is not documented on that page. So use Convert.ToInt32 is a safer choice.

Answer 3

The reason for this is that a boxed T can only be unboxed to T or Nullable<T>. The numeric result in your case happens to be a long that's been boxed as an object, so when you use a cast it's only valid to cast to a long, not an int. Even though once you have it as a long you can then cast it to an int.

See http://blogs.msdn.com/b/ericlippert/archive/2009/03/19/representation-and-identity.aspx for more discussion.

Answer 4

I elaborate on Dylan Smith and Darcara. It has to do with boxing and unboxing. Change your code to this :

using (System.Data.IDbCommand command = connection.CreateCommand())
{
    command.CommandText = string.Format("SELECT COUNT(1) FROM {0}", tableName);
    object count = command.ExecuteScalar();
    System.Diagnostics.Trace.WriteLine(count.GetType());
    int iCount = (int)count; //statement 1
}

The runtime time of count is certainly not int. That's why you get the InvalidCastException. When you unbox a value type, you have to unbox it to the actual type of the variable. It's only after unboxing that you can cast to int.

Keep in mind that statement 1 is still failing in this piece of code but you can now determine the actual type of the returned scalar.

Answer 5

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.

Answer 6

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.