How does the SqlCommand handle parameters with integer types?

6.5k views Asked by At

If I have this variable:

int value = 4;

which is to be passed as some sql parameter:

SqlCommand sqlcmd = new SqlCommand();
sqlcmd.Parameters.Add(new SqlParameter("@value", value));

Will it be converted to string and handled automatically? or could it possibly cause some trouble? ie. when I do this:

sqlcmd.ExecuteNonQuery();

1

There are 1 answers

2
Tim Schmelter On BEST ANSWER

Always provide the correct type, especially int is dangerous.

From MSDN:

Use caution when you use this overload of the SqlParameter constructor to specify integer parameter values. Because this overload takes a value of type Object, you must convert the integral value to an Object type when the value is zero, as the following C# example demonstrates:

Parameter = new SqlParameter("@pname", (object)0);

If you do not perform this conversion, the compiler assumes that you are trying to call the SqlParameter (string, SqlDbType) constructor overload.

So if you want to use a string parameter, convert it to the correct type:

sqlcmd.Parameters.Add(new SqlParameter("@value", value.ToString()));

or

sqlcmd.Parameters.AddWithValue("@value", value.ToString());

or (with the type)

var p = new SqlParameter("@value", typeof(string));
p.Value = value.ToString()
sqlcmd.Parameters.Add(p);