How to use parameters in the sql query using Dapper?

Question

How can I make a simple select using Dapper and passing more than one parameter?

Method:

public IEnumerable<Locais> ConsultarPorTermo(string termo)
{
    const string sql = @"SELECT * FROM Locais " +
     "WHERE Nome LIKE '%@term%' or Cidade LIKE '%@term%' or Estado LIKE '%@term%';";

    //utilizando a connection do EF
    return Db.Database.Connection.Query<Locais>(sql, new { term = termo });
}

What am I doing wrong? No syntax error but the following error:

error CS0103: The name "$Exception" does not exist in the current context

Answer 1

I’m not sure about the mistake you’re getting. There is no way to identify this problem with the information you passed on the question, probably not even related to this query.

Anyway, it is a fact that you are passing the parameter wrong to the Dapper.

It is necessary to concatenate the character % in the query string, the way you are doing Dapper will not replace the parameter, because you understand that it is a literal.

An alternative is to do so:

public IEnumerable<Locais> ConsultarPorTermo(string termo)
{
    const string sql = @"SELECT * FROM Locais " +
     "WHERE Nome LIKE @term or Cidade LIKE @term or Estado LIKE @term;";

    return Db.Database.Connection.Query<Locais>(sql, new { term = $"%{termo}%" });
}

It is important to emphasize the importance of not do this in the string that forms the query. The form shown below may leave your application vulnerable to SQL injection.

sql = "... WHERE Nome LIKE '%{termo}%' ...";

You can see more about on How an SQL Injection Happens?