C# – Using SQL transactions with Dapper

When you want to execute multiple SQL commands with Dapper in a transaction, using TransactionScope is the simplest option. Here’s an example of how to use it:

using System.Transactions;

using (var trxScope = new TransactionScope())
{
	movieRepo.Insert(newMovie);
	movieRepo.Delete(movieToDelete);

	//Commits the transaction
	trxScope.Complete();
};
//Rolls back the transaction if Complete() wasn't called
Code language: C# (cs)

When you call TransactionScope.Complete(), it commits the transaction. If you don’t call Complete(), it’ll rollback the transaction once it leaves the TransactionScope block.

This keeps the code nice and clean and works well with the Repository pattern. Here are the Insert()/Delete() methods in the repository class. These methods use Dapper to insert and delete a record. Notice that it doesn’t have to deal with transactions at all?

using Dapper;
using System.Data.SqlClient;

public class MovieRepository
{
	public void Insert(Movie movie)
	{
		using (var con = new SqlConnection(connectionString))
		{
			con.Execute(INSERT_SQL, param: movie);
		}
	}
	public void Delete(Movie movie)
	{
		using (var con = new SqlConnection(connectionString))
		{
			con.Execute(DELETE_SQL,
				param: new { id = movie.Id });
		}
	}
	//rest of class
}
Code language: C# (cs)

Note: Using .NET 5 against a SQL Server 2016 database.

Any connection that is opened within the TransactionScope block gets automatically enlisted into the transaction.

Distributed transactions

When a transaction is created, it starts off as a local transaction. Under certain conditions, it gets escalated to a distributed transaction that requires the Distributed Transaction Coordinator (MSDTC) service to be running. There are two main conditions that cause transactions to escalate:

• Explicitly opening two connections in the transaction scope at the same time.
• Using different connection strings (like if you’re connecting to a different server).

The database engine / version you’re using also plays a role. It’s best to figure out early in the development process if you’re going to need to deal with distributed transactions or not. This is because they can be an architectural roadblock. Ideally, try to avoid distributed transactions.

Distributed transactions not supported in .NET Core

Distributed transactions are currently not supported in cross-platform versions of .NET (.NET Core and above). It’s possible Microsoft will add support for it eventually. When you do something that triggers transaction escalation, you get the following exception:

System.PlatformNotSupportedException: This platform does not support distributed transactions.

If you’re migrating to .NET Core and you need distributed transactions, this is a major blocker that would require a redesign to eliminate needing distributed transactions.

Note: You may get the ‘MSDTC is unavailable’ error if the MSDTC service not running, which is confusing because it’s irrelevant. If MSDTC is running, you’ll get the ‘platform not supported’ exception.

Distributed transactions in .NET Framework

Distributed transactions are supported in .NET Framework, and requires the Distributed Transaction Coordinator (MSDTC) service to be running. When a transaction is escalated and you don’t have the MSDTC service running, you get the error:

System.Data.SqlClient.SqlException: MSDTC on server ‘SQLSERVER’ is unavailable.

Make sure the MSDTC service is running and set to start automatically.

Avoiding transaction escalation when connecting to different databases on the same server

Different connection strings trigger transaction escalation, even if you’re connecting to different databases on the same server. For example, the following code (executed within a TransactionScope) triggers transaction escalation and fails with the ‘platform not supported’ exception (in cross-platform .NET):

public void Insert(Movie movie)
{
	using (var con = new SqlConnection("Server=MAKOLYTE;Database=MoviesDbNew;Integrated Security=true"))
	{
		con.Execute(INSERT_SQL, param: movie);
	}
}
public void Delete(Movie movie)
{
	using (var con = new SqlConnection("Server=MAKOLYTE;Database=MoviesDbOld;Integrated Security=true"))
	{
		con.Execute(DELETE_SQL,
			param: new { id = movie.Id });
}
Code language: C# (cs)

One way to avoid the transaction escalation is to use the same connection string and switch to the target database with USE <DbName>:

public void Insert(Movie movie)
{
	using (var con = new SqlConnection("Server=MAKOLYTE;Database=MoviesDbNew;Integrated Security=true"))
	{
		con.Execute(INSERT_SQL, param: movie);
	}
}
public void Delete(Movie movie)
{
	using (var con = new SqlConnection("Server=MAKOLYTE;Database=MoviesDbNew;Integrated Security=true"))
	{
		con.Execute("USE MoviesDbOld");
		con.Execute(DELETE_SQL,
			param: new { id = movie.Id });
	}
}
Code language: C# (cs)

Because it’s the same connection string, it doesn’t escalate the transaction.

Note: This is the same as calling con.Open() + con.ChangeDatabase(“MoviesDbOld”), just simpler because I prefer to let Dapper open the connection.

Alternative to TransactionScope – Connection.BeginTransaction()

If you prefer to have more explicit control over the transaction, you can use the Connection.BeginTransaction() style instead of using TransactionScope. Here’s an example:

using Dapper;
using System.Data.SqlClient;

using(var con = new SqlConnection(connectionString))
{
	con.Open();
	using(var trx= con.BeginTransaction())
	{
		con.Execute(INSERT_SQL, param: movieToInsert, transaction: trx);
		con.Execute(DELETE_SQL, param: new { movieToDelete.Id }, transaction: trx);

		trx.Commit();
	}
}
Code language: C# (cs)

One thing to notice is the connection must be opened before calling BeginTransaction().

If you don’t call Commit(), it’ll rollback the transaction automatically when it leaves the BeginTransaction using block. You can also call Rollback() yourself.