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
– huMpty duMpty, Commented Dec 9, 2013 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
– marc_s, Commented Dec 9, 2013 at 17:17

Tim Schmelter · Accepted Answer · 2013-12-09 16:46:41Z

7

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

edited Dec 9, 2013 at 16:46

answered Dec 9, 2013 at 16:41

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2405469 Over a year ago

Nice, I'll pay more attention to the documentation instead of just jumping in there :)

user2405469 Over a year ago

yeah I usually use ToString()...but it is nice to know how sqlparameters handles integer types.

Collectives™ on Stack Overflow

How does the SqlCommand handle parameters with integer types?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related