How in Dapper I pass argument to a select query in an Oracle database?

asp.net dapper model-view-controller oracle select

Question

I'm attempting to send arguments to Dapper for an Oracle database select query, but for some reason, the ":" identifier doesn't work.

Using string concatenation, it works properly:
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();

The second example should return the same string, however it gives me nothing.

1
1
10/18/2017 11:02:15 AM

Accepted Answer

When passing a parameter, you are not need to enclose your values in quotes. On the other hand, the engine will search a column containing your values enclosed in literal quotes, which results in a failure.
Utilize just the simple 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();
1
10/18/2017 12:20:23 PM


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow