36

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?

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    Have a look at this SO question, this looks like the same thing: http://stackoverflow.com/questions/5445663/difference-between-adding-parameters-to-stored-procedure-in-sql-server-2005 – CCBlackburn Feb 06 '12 at 02:36
  • 1
    Have a read of: http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ – RemarkLima Jan 01 '15 at 15:23

4 Answers4

29

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.

KV Prajapati
  • 93,659
  • 19
  • 148
  • 186
  • 2
    Please be advised that there's an overload on .Add that let's you add a SqlParameter/OleDbParameter/DbParameter. When you *omit* the parameter name (by setting it to NULL) in the parameter instance you'll get a nasty performance hit: the .Net framework will create names for you 'Parameter1..x'. Now this seems silly for a SqlParameter, you would want to match the @variable. With OleDbParameter however... – Michel van Engelen Feb 02 '17 at 14:42
12

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

learnerplates
  • 4,257
  • 5
  • 33
  • 42
  • 1
    Considering this horrifying sentence in the linked article: "By not specifying the length when you're using ParameterCollection.AddWithValue, you can have as many different queries in the plan cache as you have different string lengths." this may be most valuable response here :) – Viktor Svub Mar 04 '15 at 19:46
  • that article is pretty old, is that still the behavior? – Maslow Sep 17 '15 at 12:59
3

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.

DNQ
  • 57
  • 8
2

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.

Remco Ros
  • 1,467
  • 15
  • 31