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

What if book name is [bobby tables](https://xkcd.com/327/) ? ... anyway obviously with you current code you cant do this as you need the same to pass same connection and transaction somewhere — Selvin, Mar 26 '20 at 13:29
See if [Unit Of Work with Dapper](https://stackoverflow.com/a/45029588/5779732) helps you. — Amit Joshi, Mar 26 '20 at 13:31
Important: note that your SQL is a huge injection hole; Dapper makes it *painless* to parameterize queries - make use of it! — Marc Gravell, Mar 26 '20 at 14:11

Marc Gravell · Answer 1 · 2020-03-26T14:16:53.130

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 });

Hi Marc, nice tips you provide. But please why you don't use catch block with `trans.Rollback()`? Like in some website of Dapper tutorials show https://riptutorial.com/dapper/example/22536/using-a-transaction#example Does its optional? Thanks — deveton, Jun 17 '21 at 13:18

score 3 · Answer 2 · answered Mar 26 '20 at 13:39

3

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

answered Mar 26 '20 at 13:39

mm8

163,881
10
57
88

5

I usually advise *against* `TransactionScope` unless there's a very good reason - and I don't think this is a very good reason; also, note that that website is not official dapper guidance - it has nothing whatsoever to do with the dapper authors/project – Marc Gravell Mar 26 '20 at 14:05

Transaction with Dapper .NET

2 Answers2