Difference with Parameters.Add and Parameters.AddWithValue

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.

I'd use the AddWithValue for normal cases. And use Add(name, dbtype... only when your column type is different from how .net converts the CLR type.

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

Using AddWithValue() adds parameter with length of current value. If the length of your parameter value varies often this means new plan is generated every time. This makes your queries run slower(additional time for parsing, compiling) and also causes higher server load.