dapper query - fix for "Unclosed quotation mark near ..." error

c# dapper

Question

This query works; that is, it returns the expected results:

var r = sql.Query<T>("select * from TableName where Name = '" + name + "'");

but, if one of the 'names' values contains an apostrophy (which is true), then an exception is thrown '{"Incorrect syntax near 'Resources'.\r\nUnclosed quotation mark after the character string ''."}' - in an attempt to fix that problem, my query no longer returns any results; but it should.

I've attempted to change the code in a few ways, but no results are returned with either 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 have simply just not yet grasped the concept for ... but I'm at the point of having spent too much time trying to figure it out - hence the question.

Accepted Answer

Using a parameter is the right way to go. You absolutely don't want to put the value into the query itself as you did in your first snippet. However, you've put the @name in quotes, which means it's being treated as a string literal... it's looking for a name value of exactly @name rather than the value of the @name parameter. You want:

var r = sql.Query<T>("select * from TableName where Name = @name", new { name });

(That's probably the simplest way of passing the parameters, though the other approaches should work too.)

Now I haven't actually used Dapper myself, but that's what I'd expect...



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why