How does the SqlCommand handle parameters with integer types?

Question

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();

e.g. ` var parameter = new SqlParameter { ParameterName = "@value", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = value };` — huMpty duMpty, Dec 09 '13 at 16:52
**NO**, it will **NOT** be converted to a string! On the contrary - the parametrized query including its parameters, their definition and their values will be sent to SQL Server, and SQL Server will handle the details of mapping the parameter values to the query execution. — marc_s, Dec 09 '13 at 17:17

Tim Schmelter · Accepted Answer · 2013-12-09T16:46:41.473

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);

Nice, I'll pay more attention to the documentation instead of just jumping in there :) — user2405469, Dec 09 '13 at 16:43
yeah I usually use ToString()...but it is nice to know how sqlparameters handles integer types. — user2405469, Dec 09 '13 at 16:59

How does the SqlCommand handle parameters with integer types?

1 Answers1