How to build dynamic parameterised query using dapper?

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

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.

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" });

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);