Parameterized SqlCommand - advantage of specifying SqlDbType?

Folks,

Unless I'm mistaken, a parameterized sql query can be generated by specifying SqlDbType for each parameter or not. It s开发者_如何学JAVAeems 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?

---

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.

---

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

---

I agree that by using a specific SqlDbType you obtain more control. Another reason you want more control when dealing with text is when dealing with unicode text. Fore example, using SqlDbType.NVarChar Vs SqlDbType.VarChar.

---

The main advantage is performance. If you do not specify the parameter type, the SqlClient engine will infer it from the parameter value. If you know the parameter type at design time, specifying it is a simple way to avoid the cost of the inference step.