C# – Adding dynamic parameters with Dapper

by

The simplest way to add dynamic parameters when executing queries with Dapper is by passing in Dictionary<string, object>, like this:

//Built dynamically somewhere
var query = "SELECT * FROM Movies WHERE Name=@Name";
var parameters = new Dictionary<string, object>()
{
	["Name"] = "The Matrix"
};

//Executing the query with dynamic parameters
using (var con = new SqlConnection(connectionString))
{
	var results = con.Query<Movie>(query, parameters);
	return results;
}
Code language: C# (cs)

Read more about how to add items to a dictionary.

You can also add dynamic parameter by using the DynamicParameters class. You can use whichever approach is simplest in the given scenario. In this article, I’ll show examples of adding dynamic parameters in different scenarios.

Add dynamic parameters one at a time

You can add individual dynamic parameters one at a time by using DynamicParameter.Add().

//Built dynamically somewhere
var query = "SELECT * FROM Movies WHERE Id=@Id";
var paramName = "@Id"; //works without the @ too
var paramValue = 3;

//Dynamic parameters added individually
using (var con = new SqlConnection(connectionString))
{
	var dynamicParameters = new DynamicParameters();
	dynamicParameters.Add(paramName, paramValue);

	var results = con.Query<Movie>(query, dynamicParameters);
	return results;
}
Code language: C# (cs)

At a bare minimum, you have to add the name and value. Add() has other optional parameters. This is mostly useful when you’re adding additional info about the parameters.

Add known parameters and dynamic parameters

When you know what parameters you’re using ahead of time, you can pass Dapper a param object with properties for each parameter. If you also need to add dynamic parameters, add them with the DynamicParameters class, and add the param object using the AddDynamicParams() method. Here’s an example:

//Built dynamically somewhere
var query = "SELECT * FROM Movies WHERE Name=@Name AND YearOfRelease=@Year";
var parameters = new Dictionary<string, object>()
{
	["Year"] = 1999
};

//Using hardcoded (known) parameters  + dynamic parameters
using (var con = new SqlConnection(connectionString))
{
	var dynamicParameters = new DynamicParameters(parameters);

	dynamicParameters.AddDynamicParams(new { name = "The Matrix" });
		
	var results = con.Query<Movie>(query, dynamicParameters);
	return results;
}
Code language: C# (cs)

As shown, you can initialize the DynamicParameters object with Dictionary<string, object>, and then add any additional parameters you need.

WHERE LIKE with a dynamic parameter

When you’re building queries/parameters dynamically, it’s quite common to need to support partial matches using the LIKE operator. To make LIKE work with a parameter, add %’s to the parameter value:

//Built dynamically somewhere
var movieNameLike = "Matrix";
var query = "SELECT * FROM Movies WHERE Name LIKE @Name";
var parameters = new Dictionary<string, object>()
{
	["Name"] = $"%{movieNameLike}%"
};

//Using the dynamic parameters like usual
using (var con = new SqlConnection(connectionString))
{
	var results = con.Query<Movie>(query, parameters);
	return results;
}
Code language: C# (cs)

Note: Same thing applies to known parameters you’re using with LIKE. This isn’t specific to dynamic parameters.

WHERE IN with a dynamic parameter

You can use WHERE IN with dynamic parameters by passing in a dictionary containing a list of values: 

//Built dynamically somewhere
var query = "SELECT * FROM Movies WHERE Id IN @Ids";
var parameters = new Dictionary<string, object>()
{
	["Ids"] = new List<int>()
	{
		17, 18, 19
	}
};

//Using the dynamic parameters like usual
using (var con = new SqlConnection(connectionString))
{
	var results = con.Query<Movie>(query, parameters);
	return results;
}
Code language: C# (cs)

Mapping results to a dynamic object

If you’re executing truly ad hoc queries, you might not have a class to map the results to, and might not want one. In this case, instead of specifying a type to map to (i.e. Query<Movie>()), use the non-generic Query() method – which returns IEnumerable<dynamic>:

using (var con = new SqlConnection(connectionString))
{
	IEnumerable<dynamic> results = con.Query(query, parameters);
	return results;
}
Code language: C# (cs)

Related Articles