How does dapper execute multiple times the same command? As I have written below insert sql, and invoke Dapper's execute method with the list parameters, so I will insert 3 objects.
The question: does dapper make 3 round trips to Sql Server to execute 3 inserts, or send it somehow as 1 round trip to invoke 3 inserts?
I just wonder if using it like this has any advantages over just foreach and invoke one insert by one or if its just the same.
Version 1.
var sqlInsert = $"INSERT INTO Book (Id, Title) Values (@Id, @Title)";
var parameters = new List<Book> { new Book("Damian Book", 123), new Book("XXX Book", 156), new Book("Zonk BOok", 167) };
connection.Execute(sqlInsert, parameters);
Version 2.
foreach (var book in parameters)
{
connection.Execute(sqlInsert, book)
}
Is it version1 and version2 after all the same or not?
When you pass IEnumerable to Execute for inserting multiple items, each item is inserted independently. That said, there is no much difference in the two scenarios you presented.
Dapper just facilitates the insertion of multiple items by accepting IEnumerable. It does not internally implement something like bulk insert.
Have a look at this article:
But this approach sends every command as a single, stand-alone transaction, which may cause inconsistencies in case of error while executing one or more statements. The workaround here is to use an IDBTransaction object to create an explicit transaction that covers all the executions. Performances and scalability will be worse than executing just one command passing an array of objects (due to network latency and thus longer transactions), but at least consistency will be guaranteed.
But since Dapper supports SQL Server’s Table-Valued-Parameters and also JSON my recommendation is to use one of those if you need to pass an array of values to a parameter. I’ll discuss about them in future articles, so stay tuned.
Now, what about if you have to pass an array of, say, 10.000 values or more? The right choice, here, is to use a bulk load, and more specifically with SQL Server the BULK INSERT command, which is, unfortunately, not supported by Dapper natively. The workaround is to just use the regular SqlBulkCopy class here and you’re done.
Alternatively, you may consider using tool like Dapper Plus - Bulk Insert.
You may read more about bulk insert and a hack to achieve this here. Also, Dapper supports TVPs. If your RDBMS does, use it.
To answer your comment:
If I create transaction, and invoke multiples inserts within it, does the transaction make 1 roundtrip to database to invoke all of them? Or inserts will be still executed one by one ?
Transaction have nothing to do with bulk insert. If you wrap your code block (any version you mention in question) in transaction, result will not change....well except one. Entire transaction will either commit or rollback. Without transaction, you may encounter consistency issues if operation fails in-between somewhere. By wrapping your code in transaction, you simply avoid this issue. Your core issue - Bulk Insert stays as is.