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