How to use transactions with dapper.net?

I would like to run multiple insert statements on multiple tables. I am using dapper.net. I don't see any way to handle transactions with dapper.net.

Please share your ideas on how to use transactions with dapper.net.

Here the code snippet:

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

Note that you need to add reference to System.Transactions assembly because it is not referenced by default.

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

// This called method will get a connection, and open it if it's 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();
}

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

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

There are 3 approaches to doing transactions in Dapper.

1. Simple Transaction
2. Transaction from Transaction Scope
3. Using Dapper Transaction (additional nuget package and most favored approach)

You can find out more about these transaction approaches from the official tutorial website here

For reference here's a breakdown of the transaction approaches

1. Simple Transaction

In this example, you create a transaction on an existing db connection, and then pass in the transaction to the Execute method on dapper (which is an optional parameter).

Once you've done all your work, simply commit the transaction.

string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
	connection.Open();
	
	using (var transaction = connection.BeginTransaction())
	{
		connection.Execute(sql, new {CustomerName = "Mark"}, transaction: transaction);
        connection.Execute(sql, new {CustomerName = "Sam"}, transaction: transaction);
        connection.Execute(sql, new {CustomerName = "John"}, transaction: transaction);
		
		transaction.Commit();
	}
}

2. Transaction from Transaction Scope

If you'd like to create a transaction scope, you will need to do this before the db connection is created. Once you've created the transaction scope, you can simply perform all your operations and then do a single call to complete the transaction, which will then commit all the commands

using (var transaction = new TransactionScope())
{
	var sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";

	using (var connection = My.ConnectionFactory())
	{
		connection.Open();

		connection.Execute(sql, new {CustomerName = "Mark"});
		connection.Execute(sql, new {CustomerName = "Sam"});
		connection.Execute(sql, new {CustomerName = "John"});
	}

	transaction.Complete();
}

3. Using Dapper Transaction

This is the most favorable approach to achieve transaction in code, because it makes the code easy to read and easy to implement. There is an extended implementation of SQL Transaction called Dapper Transaction (which you can find here), which allows you to run the SQL executes off the transactions directly.

string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
	connection.Open();
	
	using (var transaction = connection.BeginTransaction())
	{
		transaction.Execute(sql, new {CustomerName = "Mark"});
		transaction.Execute(sql, new {CustomerName = "Sam"});
		transaction.Execute(sql, new {CustomerName = "John"});

		transaction.Commit();
	}
}

Considering all your tables are in single database, I disagree with TransactionScope solution suggested in some answers here. Refer this answer.

1. TransactionScope is generally used for distributed transactions; transaction spanning different databases may be on different system. This needs some configurations on operating system and SQL Server without which this will not work. This is not recommended if all your queries are against single instance of database.
   But, with single database this may be useful when you need to include the code in transaction that is not under your control. With single database, it does not need special configurations either.

2. connection.BeginTransaction is ADO.NET syntax to implement transaction (in C#, VB.NET etc.) against single database. This does not work across multiple databases.

So, connection.BeginTransaction() is better way to go.

Even the better way to handle the transaction is to implement UnitOfWork as explained in this answer.

Daniel's answer worked as expected for me. For completeness, here's a snippet that demonstrates 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