How to use transactions with dapper.net?

Question

I would like to run multiple insert statements for multiple tables. I am using dapper.net. I do not see the ability to process transactions using dapper.net.

Share your ideas on how to use transactions with dapper.net.

Answer 1

Here's the code snippet:

using System.Transactions; .... using (var transactionScope = new TransactionScope()) { DoYourDapperWork(); transactionScope.Complete(); } 

Please note that you need to add a reference to the System.Transactions assembly, because by default it does not reference.

Answer 2

I preferred to use a more intuitive approach, getting the transaction directly from the connection:

 // This called method will get a connection, and open it if it not yet open. using (var connection = GetOpenConnection()) using (var transaction = connection.BeginTransaction()) { connection.Execute( "INSERT INTO data(Foo, Bar) values (@Foo, @Bar);", listOf5000Items, transaction); transaction.Commit(); } 

Answer 3

You should be able to use TransactionScope since Dapper only runs ADO.NET commands.

 using (var scope = new TransactionScope()) { // insert // insert scope.Complete(); } 

Answer 4

Given that all your tables are in the same database, I do not agree with the TransactionScope solution proposed in some answers here. Refer to this answer.

1. TransactionScope commonly used for distributed transactions; Transactions spanning different databases can be on different systems. This requires some configurations in the operating system and SQL Server, without which it will not work. This is not recommended if all your queries relate to the same database instance.
   But with a single database, this can be useful when you need to include code in a transaction that is not under your control. With a single database, it also does not require special settings.

2. connection.BeginTransaction is ADO.NET syntax for implementing a transaction (in C #, VB.NET, etc.) for a single database. This does not work in multiple databases.

So connection.BeginTransaction() is the best way.

An even better way to process a transaction is to implement UnitOfWork as described in this answer.

Answer 5

Daniel answered as expected for me. For completeness, here is a snippet demonstrating commit and rollback using a transaction scope and dapper:

 using System.Transactions; // _sqlConnection has been opened elsewhere in preceeding code using (var transactionScope = new TransactionScope()) { try { long result = _sqlConnection.ExecuteScalar<long>(sqlString, new {Param1 = 1, Param2 = "string"}); transactionScope.Complete(); } catch (Exception exception) { // Logger initialized elsewhere in code _logger.Error(exception, $"Error encountered whilst executing SQL: {sqlString}, Message: {exception.Message}") // re-throw to let the caller know throw; } } // This is where Dispose is called