I'm trying to pass parameters to Dapper for a select query in an Oracle database, but somehow, it doesn't work with the ":" identifier.
It works fine using string concatenation :
string req = "SELECT * FROM contact WHERE code_comite = '"; req += user.Comite + "' AND (pers_name LIKE '"; req += name + "%' OR pers_surname LIKE '" + name + "%')"; contacts = db_conn.Query<Contact>(req).ToList();
But not with Dapper parameters passing :
string comite = "'" + user.Comite + "'";//e.g. comite = '120'
name = "'" + name + "%'";//e.g. name = 'John%'
contacts = db_conn.Query<Contact>("SELECT * FROM contact WHERE code_comite = :code_comite AND (pers_nom LIKE :search OR pers_prenom LIKE :search)", new { code_comite = comite, search = name }).ToList();
It should use the same string but the second example returns me nothing.
You don't need to put quotes around your values when you pass a parameter. On the contrary this causes a fail because the engine will search a column containing your values surrounded by the literal quotes.
Just use the plain text
string comite = user.Comite;
name = name + "%";
contacts = db_conn.Query<Contact>(@"SELECT * FROM contact
WHERE code_comite = :code_comite
AND (pers_nom LIKE :search
OR pers_prenom LIKE :search)",
new { code_comite = comite, search = name }).ToList();
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments