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?

Have a look at this SO question, this looks like the same thing: stackoverflow.com/questions/5445663/… — CCBlackburn
– CCBlackburn, Commented Feb 6, 2012 at 2:36
Have a read of: blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… — RemarkLima
– RemarkLima, Commented Jan 1, 2015 at 15:23

KV Prajapati · Accepted Answer · 2015-10-04 02:11:45Z

30

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.

edited Oct 4, 2015 at 2:11

answered Feb 6, 2012 at 2:52

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michel van Engelen Over a year ago

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

learnerplates · Accepted Answer · 2012-09-17 11:13:47Z

12

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

answered Sep 17, 2012 at 11:13

learnerplates

4,4076 gold badges35 silver badges46 bronze badges

2 Comments

Viktor Svub Over a year ago

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

Maslow Over a year ago

that article is pretty old, is that still the behavior?

DNQ · Accepted Answer · 2018-11-20 05:49:04Z

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.

answered Nov 20, 2018 at 5:49

DNQ

579 bronze badges

Comments

Remco Ros · Accepted Answer · 2012-02-06 02:36:53Z

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.

answered Feb 6, 2012 at 2:36

Remco Ros

1,47715 silver badges32 bronze badges

Collectives™ on Stack Overflow

Difference with Parameters.Add and Parameters.AddWithValue

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related