CODEWITHSUNDEEP
c#.net.net-coredapper
pantonis
pantonis

Reputation: 6467

Dapper update query with multiple parameters of different type

I have just started using Dapper and I have the following code that produces a query based on a combination of parameters (a list and a local variable)

var sqlQuery = $"UPDATE MyItem SET ItemOrder = @ItemOrder WHERE Id = @Id AND TenantId = @TenantId;";

var parameters = new DynamicParameters();
parameters.Add("@ItemOrder", myItems.Select(x=> x.ItemOrder));
parameters.Add("@Id", myItems.Select(x => x.Id));
parameters.Add("@TenantId", SessionInfo.TenantId);

await dbConnection.ExecuteAsync(sqlQuery, parameters, transaction: transaction);

and I can see in SQL Server Profiler the following query that is executed

exec sp_executesql N'UPDATE MyItem SET ItemOrder = (@ItemOrder1,@ItemOrder2,@ItemOrder3) WHERE Id = (@Id1,@Id2,@Id3) AND TenantId = @TenantId;',
N'@ItemOrder1 int,@ItemOrder2 int,@ItemOrder3 int,@Id1 bigint,@Id2 bigint,@Id3 bigint,@TenantId bigint',
@ItemOrder1=1,@ItemOrder2=2,@ItemOrder3=3,@Id1=1,@Id2=2,@Id3=5,@TenantId=1

which returns Incorrect syntax near ','

Why am I getting this error. What am I doing wrong?

Upvotes: 0

Views: 3671

Answers (1)

sri harsha
sri harsha

Reputation: 682

Try like below:

foreach(var item in myItems)
{
     var sqlQuery = $"UPDATE MyItem SET ItemOrder = @ItemOrder WHERE Id = @Id AND TenantId = @TenantId;";

     var parameters = new DynamicParameters();
     parameters.Add("@ItemOrder", item.ItemOrder);
     parameters.Add("@Id", item.Id);
     parameters.Add("@TenantId", SessionInfo.TenantId);

     await dbConnection.ExecuteAsync(sqlQuery, parameters, transaction: transaction);
}

Upvotes: 2

Related Questions