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 ? ... anyway obviously with you current code you cant do this as you need the same to pass same connection and transaction somewhere — Selvin
– Selvin, Commented Mar 26, 2020 at 13:29
Important: note that your SQL is a huge injection hole; Dapper makes it painless to parameterize queries - make use of it! — Marc Gravell
– Marc Gravell, Commented Mar 26, 2020 at 14:11

Marc Gravell · Accepted Answer · 2020-03-26 14:16:53Z

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 riptutorial.com/dapper/example/22536/… Does its optional? Thanks

mm8 · Accepted Answer · 2020-03-26 13:39:49Z

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

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

Collectives™ on Stack Overflow

Transaction with Dapper .NET

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related