Adding dynamic parameters with Dapper in C#

C# Handling Dynamic Parameters in Dapper

// Query built dynamically
var query = "SELECT * FROM Employees WHERE Department = @Department";
var parameters = new Dictionary<string, object>
{
    ["Department"] = "HR"
};

// Execute the query with dynamic parameters
using (var con = new SqlConnection(connectionString))
{
    var results = con.Query<Employee>(query, parameters);
    return results;
}

In this case, we create a dictionary with dynamic parameters and pass it to the Query method. Alternatively, we can use the DynamicParameters class for greater flexibility.

Adding Dynamic Parameters One at a Time

If you prefer adding parameters individually, the DynamicParameters class allows you to do so using the Add method.

// Query built dynamically
var query = "SELECT * FROM Employees WHERE EmployeeId = @EmployeeId";
var paramName = "@EmployeeId";
var paramValue = 101;

// Add dynamic parameters one at a time
using (var con = new SqlConnection(connectionString))
{
    var dynamicParams = new DynamicParameters();
    dynamicParams.Add(paramName, paramValue);

    var results = con.Query<Employee>(query, dynamicParams);
    return results;
}

In this case, we add the @EmployeeId parameter dynamically with a value of 101.

Combining Known and Dynamic Parameters

If you have known parameters and need to add additional dynamic ones, you can create an object for the known parameters and use the DynamicParameters class to add the dynamic ones.

// Query built dynamically
var query = "SELECT * FROM Employees WHERE Department = @Department AND JoinDate >= @StartDate";
var knownParameters = new Dictionary<string, object>
{
    ["Department"] = "Sales"
};

// Using known and dynamic parameters together
using (var con = new SqlConnection(connectionString))
{
    var dynamicParams = new DynamicParameters(knownParameters);
    dynamicParams.AddDynamicParams(new { StartDate = new DateTime(2020, 1, 1) });

    var results = con.Query<Employee>(query, dynamicParams);
    return results;
}

In this example, we pass both a Department as a known parameter and a dynamic StartDate parameter.

Using LIKE with Dynamic Parameters

In cases where you need to use the LIKE operator in your query for partial matches, you can append % to the value dynamically.

// Query built dynamically
var partialName = "John";
var query = "SELECT * FROM Employees WHERE Name LIKE @Name";
var parameters = new Dictionary<string, object>
{
    ["Name"] = $"%{partialName}%"
};

// Execute the query with dynamic parameters
using (var con = new SqlConnection(connectionString))
{
    var results = con.Query<Employee>(query, parameters);
    return results;
}

In this example, %John% will search for employees whose names contain "John" anywhere in the string.

Using WHERE IN with Dynamic Parameters

You can dynamically pass a list of values for the IN operator using a dictionary.

// Query built dynamically
var query = "SELECT * FROM Employees WHERE EmployeeId IN @EmployeeIds";
var parameters = new Dictionary<string, object>
{
    ["EmployeeIds"] = new List<int> { 101, 102, 103 }
};

// Execute the query with dynamic parameters
using (var con = new SqlConnection(connectionString))
{
    var results = con.Query<Employee>(query, parameters);
    return results;
}

In this example, the query filters employees with EmployeeId values in the list 101, 102, 103.

Returning Results as a Dynamic Object

If you don't want to map query results to a predefined class, you can return them as a dynamic object by using the non-generic Query method.

using (var con = new SqlConnection(connectionString))
{
    IEnumerable<dynamic> results = con.Query(query, parameters);
    return results;
}