Dapper query - fix the error "Unclosed quotation mark near ..."
This request works; that is, it returns the expected results:
var r = sql.Query<T>("select * from TableName where Name = '" + name + "'");
but if one of the name values contains an apostrophy (this is true), then an exception is thrown '{"Incorrect syntax next to" Resources ". \ r \ nQuote excluded after the character string' '." } '- in an attempt to fix this problem, my query no longer returns any results; but it should be.
I tried to modify the code in several ways, but no results are returned with any of the following changes:
var r = sql.Query<T>("select * from TableName where Name = '@name'", new { name });
or
var args = new DynamicParameters(name);
var r = sql.Query<T>("select * from TableName where Name = '@name'", args);
or
var args = new DynamicParameters(); args.AddDynamicParams(new { name });
var r = sql.Query<T>("select * from TableName where Name = '@name'", args);
or
var args = new DynamicParameters(); args.Add("@name", name);
var r = sql.Query<T>("select * from TableName where Name = '@name'", args);
This is probably something trivial that I just haven't figured out the concept yet ... but I find myself wasting too much time trying to figure it out - hence the question.
source to share
Using the parameter is the correct way. You absolutely don't want to insert the value into the query like you did in the first snippet. However, you put @name
in quotes, which means it is treated as a string literal ... it looks for the name value exactly @name
, not the parameter value @name
. Do you want to:
var r = sql.Query<T>("select * from TableName where Name = @name", new { name });
(This is probably the easiest way to pass parameters, although other approaches should work too.)
Now, I haven't actually used Dapper, but this is what I would expect ...
source to share