What data type does the SQLCommand method ExecuteScalar() return?

Question

In SQL Server, ID is a not null integer, and an identity.

When I run the following code, I get an InvalidCastException on the last line:

SqlCommand cmd = new SqlCommand();
cmd.Connection = _conn;
cmd.CommandText = @"INSERT INTO [Users] (Name, Email, Password) VALUES (@name, @email, @pass); SELECT SCOPE_IDENTITY()";
cmd.Parameters.AddWithValue("@name", newUser.Name);
cmd.Parameters.AddWithValue("@email", newUser.Email);
cmd.Parameters.AddWithValue("@pass", newUser.PasswordHash);
int id = (int)cmd.ExecuteScalar();

What is ExecuteScalar() returning here? Whatever its returning has a ToString() that makes it look like a number, so this awful line of code works:

int id = Int32.Parse(cmd.ExecuteScalar().ToString());

Answer 1

SCOPE_IDENTITY() returns a decimal in code, otherwise known as NUMERIC(38,0) in TSQL.

http://msdn.microsoft.com/en-us/library/ms190315.aspx

So if you want a direct cast, you can do (int)(decimal)cmd.ExecuteScalar();. Note that a decimal to int conversion can lose information in the strictest sense, so just be advised. But with your identity column being an integer, the conversion will be safe.

Answer 2

It's probably returning a boxed instance of a different numeric type, such as long.
A boxed long cannot be converted to int.

You can call GetType() on the return value to see what type it really is.