CODEWITHSUNDEEP
c#sql-serverdapperdynamicparameters
M.Saeed Palideh
M.Saeed Palideh

Reputation: 191

Dapper dynamic parameter

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;
    }
}

Upvotes: 2

Views: 2025

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1064204

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

Upvotes: 2

Related Questions