CODEWITHSUNDEEP
c#dapper
Bret
Bret

Reputation: 2291

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

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.

Upvotes: 3

Views: 2769

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1500893

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...

Upvotes: 5

Related Questions