Dapper Dynamic Parameters Dictionary values

I’m using dapper in my project to call stored procedures. I’ve set up the following post call in my api

  [HttpPost("{procName}")]
  public List<dynamic> RunProcWithParameters(string procName, [FromQuery]Dictionary<string, string> procParameters)
  {    
      var parameters = new DynamicParameters(procParameters);
      using var conn = new SqlConnection(_options.Connection);


      var result =  conn.Query(procName, parameters, commandType: CommandType.StoredProcedure).ToList();

      return result;
  }

With swagger hooked up in my project, I’m testing out my API by passing in my proc name and parameters with a sample call looking like: https://localhost:7009/StoredProc/MyProcThatIWantToRun?ParameterName=ParameterValue

When I run this, I’m getting the following error:

The member Comparer of type
System.Collections.Generic.IEqualityComparer`1[[System.String,
System.Private.CoreLib, Version=6.0.0.0, Culture=neutral,
PublicKeyToken=7cec85d7bea7798e]] cannot be used as a parameter value

I’ve tried changing my dictionary to be Dictionary<string, object> but no value is passed in when i run this.

What is it that I’m doing wrong / not understanding in this?

>Solution :

It appears that the dictionary values’ conversion to DynamicParameters is the root of the problem. When using DynamicParameters, a DictionaryStringObjectComparer is attempted to be used internally for comparison. It appears from the error that this comparer is not appropriate for your situation.

You might need to use DynamicParameters to cycle through the dictionary and add each parameter one at a time rather than supplying the complete dictionary as parameters at once. Make sure the arguments in the URL correspond to the expected parameter names in your saved method as well.

[HttpPost("{procName}")]
public List<dynamic> RunProcWithParameters(string procName, [FromQuery] 
Dictionary<string, string> procParameters)
{    
var parameters = new DynamicParameters();

// Iterate through the dictionary and add each parameter individually
foreach (var kvp in procParameters)
{
    parameters.Add(kvp.Key, kvp.Value);
}

using var conn = new SqlConnection(_options.Connection);

var result =  conn.Query(procName, parameters, commandType: CommandType.StoredProcedure).ToList();

return result;
}

With this change, you can be assured that every parameter is added to DynamicParameters separately. Additionally, confirm that the values are of the right type and that the parameter names in the URL correspond to the expected parameter names of your stored method.