Last post Jan 12, 2009 10:52 AM by mudassarkhan
Jan 12, 2009 09:52 AM|harmonus|LINK
what is the difference between add and addwithvalue and when to use which? Thank you in advanced.
Jan 12, 2009 10:29 AMfirstname.lastname@example.org|LINK
Dim cmd as new SqlCommand("SELECT * FROM MyTable WHERE MyDate>@TheDate",conn)
Here, Add forces the conversion from string to date as it goes into the parameter. AddWithValue would have simply passed the string on to the SQL Server.
The difference is in performance and the ability for SQL to actually cache ado sql commands.../ queries
When using Parameters.Add - then SQL has to box and unbox to find out what sqldatatype the parameter is...when passed
Using Parameters.AddWithValue - you explicitly set the sqldb.type..and SQL knows exactly the dbtype when passed...
Additional benefits with the latter is that it is a bit more code safe and will assist against sql injection attacks , code safe in terms that if you try to pass a value that doesn't match the sqldb type defined - the error will be caught in the .Net code
and you will not have to wait for the round trip back from sql...
Jan 12, 2009 10:52 AM|mudassarkhan|LINK
AdWithValue accepts the Parameters Name and Value but not additional attributes
like parameter DataType, parameter Direction. If you need to pass all those you will need to use Parameters.Add
you can read more here