Dapper dynamic parameter

Question

I am using dapper and dynamicParameters to get data from SQL Server in a Web API project (.NET 5).

It works most of the time without any problems, but sometimes I get the following error

Procedure or function MySP has too many arguments specified.

I suspect due to simultaneous requests, the parameters are duplicated. Is there a solution to this problem?

string sp = "MySP";

if (ADOFunctions.IsConncetionReadyToOpen(Connection))
{
    await Connection.OpenAsync();
}

var queryParameters = new DynamicParameters();
queryParameters.Add("@Date", dateTime.Date);
queryParameters.Add("@ClientRef", clientRef);
queryParameters.Add("@orderRefs", orderRefList);

var countLimitation = await Connection.QueryAsync<UnityExhibitionOrdersInfo>
   (sql: sp, param: queryParameters, commandType: System.Data.CommandType.StoredProcedure);

foreach (var item in countLimitation)
{
    if (item.Sold > item.Limit)
    {
        returnVal.Message += $"error ";
        returnVal.Result = false;
    }
}

score 2 · Accepted Answer · answered Apr 12 '21 at 06:48

Firstly, it should be noted that you don't even need DynamicParameters here; a simple:

new { dateTime.Date, ClientRef = clientRef, orderRefs = orderRefList }

should be more than sufficient for the parameters, since this is a simple usage, but: either way, your queryParameters object is local and not shared (which is good). This should work absolutely fine - my suspicion, then, would be: "is Connection somehow shared between requests / concurrent async flows on the same request?" (it absolutely must not be; ADO.NET connections are not thread-safe and are not intended for concurrent access).

Dapper dynamic parameter

1 Answers1