Transaction with Dapper .NET

Question

I currently have two classes in one layer, which perform the inclusion of data in the database:

using Dapper;
using System;
using System.Data.SqlClient;
using System.Linq;

namespace repositories
{
    public class DAOBook
    {
        private readonly string _connection;

        public DAOBook(string databaseConnection)
        {
            _connection = databaseConnection;
        }

        public bool IncludeBook(string title)
        {
            try
            {
                using (var connection = new SqlConnection(_connection))
                {
                    var sql = $@"
                                INSERT INTO books
                                  (title)
                                VALUES
                                  ('{title}' ";

                    var result = connection.Execute(sql);

                    return result != 0;
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"{ex.Message}", ex);
            }
        }
    }
}

---

using Dapper;
using System;
using System.Data.SqlClient;
using System.Linq;

namespace repositories
{
    public class DAOBookTag
    {
        private readonly string _connection;

        public DAOBookTag(string databaseConnection)
        {
            _connection = databaseConnection;
        }

        public bool IncludeBookTag(string tag, int userid)
        {
            try
            {
                using (var connection = new SqlConnection(_connection))
                {
                    var sql = $@"
                                INSERT INTO bookTag
                                  (tag, userid)
                                VALUES
                                  ('{tag}', {userid} ";

                    var result = connection.Execute(sql);

                    return result != 0;
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"{ex.Message}", ex);
            }
        }
    }
}

In my service layer, I can call these two classes normally, and they insert them into the database.

try
{
    var connectionString = "<my_connection_string>";
    var daoBook = new DAOBook(connectionString);
    var daoBookTag = new DAOBookTag(connectionString);

    dao.IncludeBook("Alice");
    dao.IncludeBookTag("Romance", 1);
}
catch (Exception ex)
{
    throw new Exception($"{ex.Message}", ex);
}

However, I want to place a transaction control, so that in case of an error in the insertion of the second class, it undoes the transaction in catch, something like this:

try
{
    var connectionString = "<my_connection_string>";
    var daoBook = new DAOBook(connectionString);
    var daoBookTag = new DAOBookTag(connectionString);

    // begin transaction    
    dao.IncludeBook("Alice");
    dao.IncludeBookTag("Romance", 1);
    // commit
}
catch (Exception ex)
{
    // rollback
    throw new Exception($"{ex.Message}", ex);
}

I know it must be a beginner's question, but I can't seem to find a way for the two persistence classes to share the same transaction.

I saw an example of implementing Dapper's transaction control, but I don't know how I could implement it in my service layer (instead of the persistence layer). https://riptutorial.com/dapper/example/22536/using-a-transaction

Thank you

Answer 1

There are two ways of handling transactions in ADO.NET; the usually preferred mechanism is an ADO.NET transaction, i.e. BeginTransaction. This has limitations, but is very efficient and maps natively into most providers. The key restriction of an ADO.NET transaction is that it only spans one connection, and your connection must last at least as long as the transaction.

In terms of Dapper usage, you must also pass the transaction into the call; for example:

using (var conn = new SqlConnection(connectionString))
{
    connection.Open();
    using (var tran = connection.BeginTransaction())
    {
        // ... your work
        tran.Commit();
    }
}

where "your work" here effectively uses the same conn and tran instances, using:

var result = conn.Execute(sql, args, transaction: tran);

---

The much lazier way is to use TransactionScope. This is simpler to use, but more more involved. I usually advise against it, but it works.

---

You should also parameterize:

var sql = @"
INSERT INTO bookTag (tag, userid)
VALUES (@tag, @userId)";
var result = connection.Execute(sql, new { tag, userId });

Answer 2

Use a TransactionScope:

using (var transactionScope = new TransactionScope())
{
    var connectionString = "<my_connection_string>";
    var daoBook = new DAOBook(connectionString);
    var daoBookTag = new DAOBookTag(connectionString);

    // begin transaction    
    dao.IncludeBook("Alice");
    dao.IncludeBookTag("Romance", 1);
    //commit
    transactionScope.Complete();
}

https://dapper-tutorial.net/transaction