Can Dapper batch a set of stored proc calls?

.net c# dapper

Question

Can Dapper batch a set of stored proc calls? I see it supports Multiple Results in the documentation but I'm not sure if you can execute multiple stored proc calls using Dapper.

Accepted Answer

Dapper supports batching commands for stored procs:

connection.Execute("create table #t (i int)");
connection.Execute("create proc #spInsert @i int as insert #t values (@i)");
connection.Execute("#spInsert", new[] { new { i = 1 }, new {i = 2}, new {i = 3} }, 
    commandType: CommandType.StoredProcedure);

var nums = connection.Query<int>("select * from #t order by i").ToList();

nums[0].IsEqualTo(1);
nums[1].IsEqualTo(2);
nums[2].IsEqualTo(3);

The code above reuses the IDbCommand with the text #spInsert, 3 times. This makes batching inserts a bit more efficient.

In general if you worry about perf at this level you would wrap the batch call in a transaction.

Additionally Dapper supports whatever batch you decide to send it:

connection.Execute(@"
    exec #spInsert @i = @one 
    exec #spInsert @i = @two 
    exec #spInsert @i = @three",
    new { one = 1, two = 2, three = 3 });

Which would cause three rows to be inserted.

Further more, if #spInsert returned a result set you could use QueryMultiple to execute the batch which would give you 3 record sets to iterate through.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why