Parameterized LIKE clause in SQL statement using Dapper

Question

I want to perform the following query using Dapper, which currently doesn't return expected results (I think it must be treating the @pName param as literal text within the single quotes?):

var q = "SELECT * FROM Users WHERE Name LIKE '@pName%'";

@pName is the param I assign a value to upon executing the query.

Things work if I just build the SQL like:

var q = "SELECT * FROM Users WHERE Name LIKE '" + name + "%'";

.. but I would prefer to use a param if possible.

I am executing the query using the following code:

o = _cn.Query<User>(q, new { pName = new DbString { Value = name, IsFixedLength = false, Length = 25, IsAnsi = true } }).ToList();

How do I got about this using Dapper?

make sure you remember to escape your like clause (see comment on answer) — Sam Saffron, Sep 22 '11 at 23:59

Marcelo Cantos · Accepted Answer · 2014-09-13T03:06:45.150

16

SELECT * FROM Users WHERE Name LIKE @pName + '%'

edited Sep 13 '14 at 03:06

answered Sep 22 '11 at 08:55

Marcelo Cantos

174,413
38
319
360

3

Make sure you escape your like clause though: http://stackoverflow.com/questions/439495/tsql-like-escape-clause – Sam Saffron Sep 22 '11 at 23:59
Thanks Sam. I wasn't familiar with this so will add it. – marcusstarnes Oct 03 '11 at 09:58
Buggered if I know. Someone else did that as an edit. I rolled it back. – Marcelo Cantos Sep 13 '14 at 03:06

Parameterized LIKE clause in SQL statement using Dapper

1 Answers1