In the Dapper documentation, it says you can use an IEnumerable
parameter to execute a command multiple times. It gives the following example:
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"
Will this result in multiple round-trips to the database (i.e. one for each T
in the IEnumerable<T>
)? Or is Dapper smart enough to transform the multiple queries into a batch and just do one round-trip? The documentation says an example usage is batch loading, so I suspect it only does one round-trip, but I want to be sure before I use it for performance-sensitive code.
As a follow-up question, and depending on the answer to the first, I'd be curious how transactions are handled? That is, is there one transaction for the whole set of T
s, or one transaction per T
?
I finally got around to looking at this again. Looking at the source code (in \Dapper\SqlMapper.cs
), I found the following snippet in method ExecuteImpl
:
// ...
foreach (var obj in multiExec)
{
if (isFirst)
{
masterSql = cmd.CommandText;
isFirst = false;
identity = new Identity(command.CommandText, cmd.CommandType, cnn, null, obj.GetType(), null);
info = GetCacheInfo(identity, obj, command.AddToCache);
}
else
{
cmd.CommandText = masterSql; // because we do magic replaces on "in" etc
cmd.Parameters.Clear(); // current code is Add-tastic
}
info.ParamReader(cmd, obj);
total += cmd.ExecuteNonQuery();
}
// ...
The interesting part is on the second-last line where ExecuteNonQuery
is called. That method is being called on each iteration of the for
loop, so I guess it is not being batched in the sense of a set-based operation. Therefore, multiple round-trips are required. However, it is being batched in the sense that all operations are performed on the same connection, and within the same transaction if so specified.
The only way I can think of to do a set-based operation is to create a custom table-valued type (in the database) for the object of interest. Then, in the .NET code pass a DataTable
object containing matching names and types as a command parameter. If there were a way to do this without having to create a table-valued type for every object, I'd love to hear about it.