
When sending parameters from C# to SQL Server, instead of .AddWithValue()
:
string SomeString = "some string"; ... cmdObject.Parameters.AddWithValue("@StringParam", SomeString);
Most rightly suggest .Add()
instead (e.g. "Steve" in this Stack Overflow answer):
string SomeString = "some string"; ... cmdObject.Parameters.Add(new SqlParameter { ParameterName = "@StringParam", Value = SomeString, SqlDbType = SqlDbType.VarChar, Size = 255 // given the column is varchar(255) (-1 for max) });
Why? It allows you to be more explicit about the type being passed in, as well as the length, and has other performance benefits as well.
- Bob Beauchemin talks about multiple ways AddWithValue hurts performance:
Under the Table – How Data Access Code Affects Database Performance - Erik Darling explains the impact of parameter sizes in this answer:
Parameterized query creating many plans - Dan Guzman also talks about the performance issues:
AddWithValue is Evil - And Joel Coehoorn has been begging us all to stop using this method for years:
Can we stop using AddWithValue() already?