CODEWITHSUNDEEP
c#.netdapper
Nitin S
Nitin S

Reputation: 7600

How to build dynamic parameterised query using dapper?

How to build dynamic parameterised query using dapper?

I have columns and their values in KeyValuePair

e.g.

Key   | Value
------| -------
FName | Mohan
LName | O'reily
Gender| Male

I want to build dynamic SQL statement using dapper and execute it,

    string statement = "SELECT * FROM Employee WHERE 1 = 1 ";
    List<KeyValuePair<string,string>> lst = new List<KeyValuePair<string,string>>();
    lst.Add(new KeyValuePair<string,String>("FName","Kim"));
    lst.Add(new KeyValuePair<string,String>("LName","O'reily"));
    lst.Add(new KeyValuePair<string,String>("Gender","Male"));
    foreach(var kvp in lst)
    {
        statement += " AND "+ kvp.Key +" = '"+ kvp.Value +"'";
    }
    using (var connection = _dataAccessHelper.GetOpenConnection())
    {
      try
      {
           //CommandDefinition cmd = new CommandDefinition(statement);

           var searchResult = await connection.QueryAsync<dynamic>(statement);

Above query fails because there is special character in query.
I found that for parameterised statements CommandDefinition can be used,

how to use CommandDefinition to execute the above statement without any error?
or
is there any better way to build dynamic sql statements?

Upvotes: 0

Views: 7187

Answers (4)

drizin
drizin

Reputation: 1975

DapperQueryBuilder is an alternative to Dapper SqlBuilder and would work like this:

List<KeyValuePair<string,string>> lst = new List<KeyValuePair<string,string>>();
lst.Add(new KeyValuePair<string,String>("FName","Kim"));
lst.Add(new KeyValuePair<string,String>("LName","O'reily"));
lst.Add(new KeyValuePair<string,String>("Gender","Male"));

using (var connection = _dataAccessHelper.GetOpenConnection())
{
    var query = connection.QueryBuilder($@"SELECT * FROM Employee /**where**/");

    foreach(var kvp in lst)
        query.Where($"{kvp.Key:raw} = {kvp.Value}");

    var searchResult = await query.QueryAsync<dynamic>();
}

The output is fully parametrized SQL (WHERE FName = @p0 AND LName = @p1 etc).

You just have to be aware that strings that you interpolate using raw modifier are not passed as parameters (in the code above the column name kvp.Key is dynamic value but can't be parameter), and therefore you should ensure that the column names (kvp.Key) are safe. The other interpolated values (kvp.Value in the example) look like regular (unsafe) string interpolation but the library converts them to SqlParameters.

Disclaimer: I'm one of the authors of this library

Upvotes: 3

GlaMin
GlaMin

Reputation: 53

You can use the DynamicParameters class for generic fields.

Dictionary<string, object> Filters = new Dictionary<string, object>();
Filters.Add("UserName", "admin");
Filters.Add("Email", "[email protected]");
var builder = new SqlBuilder();
var select = builder.AddTemplate("select * from SomeTable /**where**/");
var parameter = new DynamicParameters();
foreach (var filter in Filters)
{
    parameter.Add(filter.Key, filter.Value);
    builder.Where($"{filter.Key} = @{filter.Key}");                        
}


var searchResult = appCon.Query<ApplicationUser>(select.RawSql, parameter);

Upvotes: 1

Palle Due
Palle Due

Reputation: 6311

Don't build the query as text. You can use the Dapper SqlBuilder, it goes something like this:

List<KeyValuePair<string,string>> lst = new List<KeyValuePair<string,string>>();
lst.Add(new KeyValuePair<string,String>("FName","Kim"));
lst.Add(new KeyValuePair<string,String>("LName","O'reily"));
lst.Add(new KeyValuePair<string,String>("Gender","Male"));
var builder = new SqlBuilder();
var select = builder.AddTemplate("select * from Employee /**where**/");
foreach (var kvPair in lst)
{
    builder.Where($"{kvPair.Key} = @{kvPair.Key}", new { kvPair.Value });
}
using (var connection = _dataAccessHelper.GetOpenConnection())
{
    try
    {
        var searchResult = await connection.QueryAsync<dynamic>(select.RawSql, select.Parameters);
    }
    ...

You should never try to escape parameters yourself, leave it to Dapper. Then you will also be protected against SQL-injection.

Upvotes: 2

sc3w
sc3w

Reputation: 1164

There is no reason to use a list of key value pairs to build an SQL statement with dynamic parameters. You can simply put placeholders in the query, for example @FName from the example above, and provide the values for those placeholders as the second parameter of QueryAsync method by passing in an anonymous type with its keys corresponding to the placeholders and values to the dynamic values that you want to use for the query.

string statement = "SELECT * FROM Employee WHERE FName=@FName AND LName=@LName AND Gender=@Gender";
...
var searchResult = await connection.QueryAsync<dynamic>(statement, new { FName = "Kim", LName = "O'reily", Gender="Male" });

Upvotes: 1

Related Questions