AddWithValue difficulty

Question

I want to run parameterized SQL with .NET. But when I use AddWithValue() the generated command does not work.

SqlCommand cmd = new SqlCommand("SELECT * FROM table WHERE table.name = @A")
cmd.Parameters.AddWithValue("@A", "A string");

Generates this sql command:

exec sp_executesql N'SELECT * FROM table WHERE table.name = @A',N'@A nvarchar(10)',@A=N'''A string'''

But that command returns no value while the following command returns the value I want (i.e. matches one row):

SELECT * FROM table WHERE table.name = 'A String'

The second query does what I expect, and I expect both queries to return the same result.

What am I doing wrong in the code?

Answer 1

The Profiler does not lie, it says you are passing in a parameter with the values @A=A string. That si different than A String. It means that probably you are adding the parameter like this:

cmd.Parameters.AddWithValue("@A", "@A=A string");

Update

After you fixed the post. The test and sql_executesql are still different actually. This time the difference is subtle: the parameter is of type Unicode! that means that the query has to perform a convertion from the Name column type (probably Varchar) to Unicode (Nvarchar). This conversion will take into account your collation for the column and it may result into something else than you expect. As a side effect it also make the query un-SARG-able (will scan the entire table even if Name has an index!). The fact that AddWithValue creates parameters of type NVARCHAR is a known trap and this is why is avoided by most veterans. It also creates parameters of type length exactly the parameter length (ie. NVARCHAR(10)) and since a query plan is dependent on the parameter types and NVARCHAR(10) is a different type than NVARCHAR(11), this reults in poor plan cache reutilisation, higher compile times and cache polution. The solution to both problems is to use explicitly created parameters in CLR, and controll the parameter SqlDbType and length (ie. use the column length always). I know is less 'rapid development' than AddWithValue, but unfortunately this is the state of the affairs in ADO.Net right now.