CODEWITHSUNDEEP
.netdapper
peflorencio
peflorencio

Reputation: 2482

Execute a query multiple times

According to the Dapper documentation, I can execute the same command multiple times if I pass an IEnumerable parameter:

connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",
    new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
  ).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3"

I would like to do something similar but with a Query. The same query would be executed multiple times and then the result of each execution, which would be a scalar value, would be combined in a IEnumerable result. Something like this:

IEnumerable<long> ids = connection.Query(@"insert MyTable(colA, colB) values (@a, @b);
                     select case(scope_identity() as bigint);",
    new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
  );

When I try to do this, I get an InvalidOperationException with the message "An enumerable sequence of parameters (arrays, lists, etc) is not allowed in this context". Is there a way to accomplish this?

I am using Dapper 1.50.2.

Upvotes: 4

Views: 1340

Answers (1)

Nkosi
Nkosi

Reputation: 247641

The API does not provide that functionality. You would need to execute the query multiple times with each parameter argument.

var sql  = @"insert MyTable(colA, colB) values (@a, @b);
select case(scope_identity() as bigint);";
var parameters = new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }; 
List<long> ids = new List<long>();    
foreach(var param in parameters) {
    ids.AddRange(connection.Query<long>(sql, param));
}

Upvotes: 1

Related Questions