Why do I have to cast the return parameter value for a SQL query?

Question

I have a method that either adds or updates a record in a DB (SQL Server), and it returns the RecordID as an (Int32) Output Parameter, and a success/failure result as (Int32) Return Value.

Given that I'm specifying the type of these parameters, why do I have to cast them when they are returned?

I expected to used the following:

enquiryID = cmd.Parameters["@EnquiryID"].Value;

...but I and up having to jump through a couple of extra hoops:

enquiryID = Int32.Parse(cmd.Parameters["@EnquiryID"].Value.ToString());

It's not the end of the world, but it just seems like a longwinded solution. Why does Parameters.Value return an SqlParameters.Value object rather than an Int32?

UPDATE:

OK, I'm convinced - direct casting FTW: (int)cmd.Parameters["@EnquiryID"].Value

Answer 1

SQL Server does not specify the type of data it is returning, so C# has no way to infer the type before processing it. Also, SqlParamater is generalized, so that it passes and object to and from SQL Server. SQL Server is performing the cast on its end.

Also, instead of using Int32.Parse(cmd.Parameters["@EnquiryID"].Value.ToString()), if you know its always going to be an int, you could just do a direct cast (int)cmd.Parameters["@EnquiryID"].Value. If the value might be returned as a null, you could then add

object value = cmd.Parameters["@EnquiryID"].Value;
if(!value.Equals(DBNull.Value)
    enquiryID = (int)value;

Answer 2

The Value property of a SqlParameter object is typed as object, which means that it can return any given type.

Your way of returning the actual value is wrong, you should never convert to string in order to parse an int32. What is in the Value propery is just a boxed version of your value, therefore this should suffice:

enquiryID = (int)cmd.Parameters["@EnquiryID"].Value;