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

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, Pass开发者_运维百科word) 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());

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.

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.

From the end of your INSERT statement

SELECT SCOPE_IDENTITY()

It's returning the identity value of the row inserted into the [Users] table.