C# – Adding dynamic parameters with Dapper

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

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

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.

Getting a stored proc output parameter

You can use the DynamicParameters class to get a stored proc output parameter. When you add this parameter, be sure to set the DbType and use ParameterDirection.Output. After you execute the query, you can get the output parameter value from the DynamicParameters object. Here’s an example of adding known input parameters (with AddDynamicParams()) and an output parameter:

var year = 2022; using (var con = new SqlConnection(connectionString)) { var dynamicParameters = new DynamicParameters(); dynamicParameters.AddDynamicParams(new { year }); dynamicParameters.Add("count", DbType.Int32, direction: ParameterDirection.Output); var results = con.Query<Movie>("spGetMoviesForYear", dynamicParameters, commandType: CommandType.StoredProcedure); var count = dynamicParameters.Get<int>("count"); Console.WriteLine($"Got {count} records"); return results; }
Code language: C# (cs)

Do you need to set the DbType parameter? Yes. Otherwise it will potentially get the wrong value. For example, if I don’t set the DbType parameter to DbType.Int32, the output parameter comes back as 2 instead of 65. Always set the DbType for output parameters.

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. Use the IN syntax required by Dapper (no surrounding parentheses):

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

Leave a Comment