Why this SqlParameterCollection call is throwing error?

This is code sample to call a stored procedure. Second process throws InvalidCastException at runtime. I am trying to add a new SqlParameter into the SqlParametersCollection. I have seen lots of example using the same syntax.

using (SqlCommand db = new SqlCommand("[StoredProcedureName]"))
{
    db.CommandType = CommandType.StoredProcedure;
    db.Connection = new SqlConnection(WebConfigurationAccess.ConnectionString);

    //1) works but this is long so wanted to try shortcut
    db.Parameters.Add(new SqlParameter("@pID", SqlDbType.Int));
    db.Parameters["@pID"].Value = 12345;
    //2) <<>>throws InvalidCastExpception. Id is set up as Int32
    db.Parameters.Add(new SqlParameter("@pID", SqlDbType.Int).Value = 12345);
    //3)but following codes does the job
    db.Parameters.Add("@pID", SqlDbType.Int).Value = 12345;
    db.Connection.Open();
    var dr = db.ExecuteReader();
}
Jon Skeet
people
quotationmark

I suspect you haven't seen that exact syntax. Look at what you've got:

db.Parameters.Add(new SqlParameter("@pID", SqlDbType.Int).Value = 12345);

That's calling db.Parameters.Add with an int argument. I suspect what you've actually seen is:

db.Parameters.Add(new SqlParameter("@pID", SqlDbType.Int)).Value = 12345;

Or more simply:

db.Parameters.Add("@pID", SqlDbType.Int).Value = 12345;

In both cases, the int is only used to set the Value property on the parameter, after it's been created and added to the collection.

Your code only compiles because there's a SqlParameterCollection.Add(object) overload - which should basically never be used, as far as I can see.

people

See more on this question at Stackoverflow