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

Ugaritic

if you use Int32 count = (Int32) cmd.ExecuteScalar(); still you get exceptions? – Dewittdewlap

@zenwalker: int and Int32 are synonyms. – Toothy

What's the exact exception message that you get? – Toothy

int and Int32 is the same thing, as int is only an alias to System.Int32, as is ulong for System.UInt64 and so on – Jedthus

Yes yes guys i know that already. I just saw at MSDN and with out thinking i pasted to see if there was some charm could happen. Stupid me! – Dewittdewlap

Show the exception and the returned data type. – Haifa

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.

Cotinga

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.

Sinew

Highly unlikely a "COUNT(1)" is getting returned as a string. – Awhile

Answer 3

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.

Jedthus

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.

Interpret

command.ExecuteScalar() return an object no difference if i assigned to object then unboxed or directly unboxed same – Ugaritic

if so it would make no sense trying to unbox something that is an object. What I was trying to say is that you should try to determine the runtime type of the ExecuteScalar() return value. The static type is obviously object, we do agree about this. Now, what is written in your output when System.Diagnostics.Trace.WriteLine(count.GetType()); is executed ? – Interpret

Answer 5

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.

Ike

Answer 6

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.

Liba

Highly unlikely a "COUNT(1)" is coming back null. – Awhile