Question
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?
Popular Answer
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.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow