Parameterized SqlCommand - advantage of specifying SqlDbType?

Question

Folks,

Unless I'm mistaken, a parameterized sql query can be generated by specifying SqlDbType for each parameter or not. It seems that I can construct an SqlParameter by providing the parameter name and the value of the parameter only. What is the advantage to specifying SqlDbType?

Answer 1

The benefit is: you have clear control over what SqlDbType the parameter will be.

Otherwise, ADO.NET has to make a guess, based on the value you provide e.g. in the .AddWithValue method. Those guesses are pretty good most of the time - but if you e.g. provide a DBNull.Value, it's a bit tricky to make a really well thought out guess...

Also, if you provide a string, it's often beneficial to be able to specify the max. length of that string parameter (the x in the VARCHAR(x) definition in your stored proc). If you don't, ADO.NET will use the current length, and that might be a good or a bad thing at times.

So overall: it just gives you more control and it's more explicit / clearer what your intentions are.

Answer 2

If you specify SqlDbType you ensure that value you provide for parameter is validated against provided SqlDbType, so if the data doesn't fit you'd catch an error earlier (otherwise you'd get an exception from SQL Server).