Difference with Parameters.Add and Parameters.AddWithValue

Question

Basically Commands has Parameters and parameters has functions like Add, AddWithValue, and etc. In all tutorials i've seen, i usually noticed that they are using Add instead of AddWithValue.

.Parameters.Add("@ID", SqlDbType.Int) 

vs

.Parameters.AddWithValue("@ID", 1) 

Is there a reason NOT to use AddWithValue? I'd prefer to use that over

Parameters.Add("@ID", SqlDbType.Int, 4).Value = 1 

since it saves my coding time. So which is better to use? Which is safe to use? Does it improves performance?

Answer 1

With Add() method you may restrict user input by specifying type and length of data - especially for varchar columns.

.Parameters.Add("@name",SqlDbType.VarChar,30).Value=varName; 

In case of AddWithValue() (implicit conversion of value) method, it sends nvarchar value to the database.

Answer 2

I believe there are also some cons to using AddWithValue which affect the SQL Cache Excection Plan, see the Parameter Length section here