35

Which is the proper way of using BeginTransaction() with IDbConnection in Dapper ?

I have created a method in which i have to use BeginTransaction(). Here is the code.

using (IDbConnection cn = DBConnection) { var oTransaction = cn.BeginTransaction(); try { // SAVE BASIC CONSULT DETAIL var oPara = new DynamicParameters(); oPara.Add("@PatientID", iPatientID, dbType: DbType.Int32); ..........blah......blah............ } catch (Exception ex) { oTransaction.Rollback(); return new SaveResponse { Success = false, ResponseString = ex.Message }; } }

When i executed above method - i got an exception -

Invalid operation. The connection is closed.

This is because you can't begin a transaction before the connection is opened. So when i add this line: cn.Open();, the error gets resolved. But i have read somewhere that manually opening the connection is bad practice!! Dapper opens a connection only when it needs to.

In Entity framework you can handle a transaction using a TransactionScope.

So my question is what is a good practice to handle transaction without adding the line cn.Open()... in Dapper ? I guess there should be some proper way for this.

Improve this question

4 Answers 4

Reset to default
74

Manually opening a connection is not "bad practice"; dapper works with open or closed connections as a convenience, nothing more. A common gotcha is people having connections that are left open, unused, for too long without ever releasing them to the pool - however, this isn't a problem in most cases, and you can certainly do:

using(var cn = CreateConnection()) { cn.Open(); using(var tran = cn.BeginTransaction()) { try { // multiple operations involving cn and tran here tran.Commit(); } catch { tran.Rollback(); throw; } } }

Note that dapper has an optional parameter to pass in the transaction, for example:

cn.Execute(sql, args, transaction: tran);

I am actually tempted to make extension methods on IDbTransaction that work similarly, since a transaction always exposes .Connection; this would allow:

tran.Execute(sql, args);

But this does not exist today.

TransactionScope is another option, but has different semantics: this could involve the LTM or DTC, depending on ... well, luck, mainly. It is also tempting to create a wrapper around IDbTransaction that doesn't need the try/catch - more like how TransactionScope works; something like (this also does not exist):

using(var cn = CreateConnection()) using(var tran = cn.SimpleTransaction()) { tran.Execute(...); tran.Execute(...); tran.Complete(); }
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

FFR: this was suggested but rejected as a PR :( github.com/StackExchange/dapper-dot-net/pull/429 Marc also participated in the discussion. It was rejected mainly because there is already duplication between sync/async - adding extension methods for transactions would result in all methods being duplicated 4 times.
@marc-gravell - In the case of rollback, do you have to explicitly call tran.RollBack? is the transaction not rolled back automatically on dispose?
This solution work fine, thank you @};-
6

You should not call 

cn.Close();

because the using block will try to close too. For the transaction part, yes you can use TransactionScope as well, since it is not an Entity Framework related technique. Have a look at this SO answer: https://stackoverflow.com/a/6874617/566608 It explain how to enlist your connection in the transaction scope. The important aspect is: connection are automatically enlisted in the transaction IIF you open the connection inside the scope.

Improve this answer

2 Comments

Yes, you are right, sorry i forgot to remove it. So the link you provided said that you can use TransactionScope with Dapper but you have to write this code - con.Open(). So is it a good practice ??
of course you have to open the connection before using it
5

Take a look at Tim Schreiber solution which is simple yet powerful and implemented using repository pattern and has Dapper Transactions in mind.

The Commit() in the code below shows it.

public class UnitOfWork : IUnitOfWork { private IDbConnection _connection; private IDbTransaction _transaction; private IBreedRepository _breedRepository; private ICatRepository _catRepository; private bool _disposed; public UnitOfWork(string connectionString) { _connection = new SqlConnection(connectionString); _connection.Open(); _transaction = _connection.BeginTransaction(); } public IBreedRepository BreedRepository { get { return _breedRepository ?? (_breedRepository = new BreedRepository(_transaction)); } } public ICatRepository CatRepository { get { return _catRepository ?? (_catRepository = new CatRepository(_transaction)); } } public void Commit() { try { _transaction.Commit(); } catch { _transaction.Rollback(); throw; } finally { _transaction.Dispose(); _transaction = _connection.BeginTransaction(); resetRepositories(); } } private void resetRepositories() { _breedRepository = null; _catRepository = null; } public void Dispose() { dispose(true); GC.SuppressFinalize(this); } private void dispose(bool disposing) { if (!_disposed) { if(disposing) { if (_transaction != null) { _transaction.Dispose(); _transaction = null; } if(_connection != null) { _connection.Dispose(); _connection = null; } } _disposed = true; } } ~UnitOfWork() { dispose(false); } }
Improve this answer

8 Comments

Its nice. Have several questions about the solution. What if dont wanna use transactions lets say for usual select queries? So, as I understood, sql will generate code for transactions after commit() or what? Why do I need do BeginTransaction() if I will not use it in query? Can it affect perfomance for queries where I dont need transactions? Please, dont understand me wrong. I just wanna clarify all things before I start use this in production.
So, I think that better is to add flag (useTransaction = false). In that case, creating instance of unitOfWork we can chose strategy that we need. Am I right?
You don't need to commit() when your query is just SELECT. So don't worry about performance!. your idea about adding a flag is nice but in fact, it is not necessary. I use it this way and it works like a charm.
Could you explain why _transaction is disposed in the finally block although _transaction.RollBack() has been called in the catch block?
@EfeZaladin finally block will run for sure, so disposing of the object is neccessary either way. In case of successful try, the _transaction should dispose and if something goes wrong, _transaction should be rollbacked and in both scenarios, it will be disposed of finally.
|
0

There are two intended ways to use transactions with Dapper.

  1. Pass your IDbTranasction to your normal Dapper call.

    Before:

    var affectedRows = connection.Execute(sql, new {CustomerName = "Mark"});

    After:

    var affectedRows = connection.Execute(sql, new {CustomerName = "Mark"}, transaction=tx);

  2. Use the new .Execute extension method that Dapper adds to IDbTransaction itself:

    tx.Execute(sql, new {CustomerName = "Mark"});

Note: the variable tx comes from IDbTransaction tx = connection.BeginTransaction();

This is how you're supposed to use transactions with Dapper; neither of them are TranasctionScope.

Bonus Reading

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.