IDbTransaction spanning several methods

  • Thread starter Thread starter Ola Strandberg
  • Start date Start date
O

Ola Strandberg

I am building a multi-tier app (similar to the design of MS PetShop). My
original intention was to use MS DTC and declarative/automatic transactions,
but having run into a million problems and having read some performance
stats, I am at least investigating an alternative path:

The BLL (Business Logic Layer) that is the transaction boundary, but would
contain no transaction code other than [AutoComplete] attributes, etc, now
has some knowledge of transactions. Each DAL (Data Access Layer) method has
a ref IDbTransaction parameter, which they test for null and initialize if
they are.

This results in DAL code similar to:
public void MyMethod(...., ref IDbTransaction trans)
{
OracleConnection conn = null;
if ( trans == null )
{
conn = new OracleConnection(CONN_STRING);
trans = conn.BeginTransaction();
}
else
{
conn = (OracleConnection)trans.Connection;
}
:
OracleCommand cmd = ...
cmd.Connection = conn;
cmd.Transaction = (OracleTransaction)trans;
:
}

and BLL code similar to:

IDbTransaction trans = null;
try
{
DALFactory.Create().MyMethod(..., trans);
DALFactory.Create().MyOtherMethod(..., trans);
}
catch ( Exception e )
{
IDbConnection conn = trans.Connection;
trans.Rollback();
conn.Close();
throw e;
}
IDbConnection conn = trans.Connection;
trans.Commit();
conn.Close();

Not very elegant.... I can swallow the transaction stuff in the BLL code,
but I would really prefer it if I could end each DAL method with a
conn.Close() as I would if I was using EnterpriseServices.

Is there a more elegant solution? What happens if I call conn.Close() with a
running transaction? I suppose I could try it, but I am still interested in
input and suggestions on what to do.

Cheers,
Ola
 
Ola Strandberg said:
I am building a multi-tier app (similar to the design of MS PetShop). My
original intention was to use MS DTC and declarative/automatic transactions,
but having run into a million problems and having read some performance
stats, I am at least investigating an alternative path:

The BLL (Business Logic Layer) that is the transaction boundary, but would
contain no transaction code other than [AutoComplete] attributes, etc, now
has some knowledge of transactions. Each DAL (Data Access Layer) method has
a ref IDbTransaction parameter, which they test for null and initialize if
they are.

This results in DAL code similar to:
public void MyMethod(...., ref IDbTransaction trans)
{
OracleConnection conn = null;
if ( trans == null )
{
conn = new OracleConnection(CONN_STRING);
trans = conn.BeginTransaction();
}
else
{
conn = (OracleConnection)trans.Connection;
}
:
OracleCommand cmd = ...
cmd.Connection = conn;
cmd.Transaction = (OracleTransaction)trans;
:
}

and BLL code similar to:

IDbTransaction trans = null;
try
{
DALFactory.Create().MyMethod(..., trans);
DALFactory.Create().MyOtherMethod(..., trans);
}
catch ( Exception e )
{
IDbConnection conn = trans.Connection;
trans.Rollback();
conn.Close();
throw e;
}
IDbConnection conn = trans.Connection;
trans.Commit();
conn.Close();

Not very elegant.... I can swallow the transaction stuff in the BLL code,
but I would really prefer it if I could end each DAL method with a
conn.Close() as I would if I was using EnterpriseServices.

Is there a more elegant solution? What happens if I call conn.Close() with a
running transaction? I suppose I could try it, but I am still interested in
input and suggestions on what to do.
Click to expand...

For explicit transactions you should make your DAL object stateful and have
it own the connection and the transaction. So your source looks like:

class DAL : IDisposable
{
IDbConnection conn;
IDbTransation trans;
public DAL()
{
conn = new OracleConnection(...);
}
public void BeginTransaction()
{
trans = conn.BeginTransaction();
}
public void CommitTransaction()
{
trans.CommitTransaction();
trans = null;
}
public void RollbackTransaction()
{
trans.RollbackTransaction();
trans = null;
}
public void Dispose()
{
if (trans != null)
{
CommitTransaction();
}
conn.Close();
}

public void MyMethod(...., ref IDbTransaction trans)
{
OracleCommand cmd = ...
cmd.Connection = conn;
if ( trans != null )
{
cmd.Transaction = (OracleTransaction)trans;
}

:
}

}

then your application code for a multi-step transaction is:

DAL dbConn = DALFactory.Create();
try
{
dbConn.MyMethod(...);
dbConn.MyMethod2(...);
...
}
catch (Exception ex)
{
dbConn.RollbackTransaction();
throw;
}
finally
{
dbConn.Dispose();
}

and for a single step transaction:


using (DAL dbConn = DALFactory.Create())
{
dbConn.MyMethod(...);
}

David
 
Hi David,
and thanks. In theory, however, a BLL call could span multiple DAL objects
(having said that, we could always design the system so that there is a 1-1
mapping between BLL and DAL, but if we do, we could just as well move the
transaction boundary to the DAL method scope).

What I am considering is creating our own class to hold the connection and
transaction state and pass such an object around across DAL calls. It would
at least make the code a little more elegant.

Cheers,
Ola

David Browne said:
Ola Strandberg said:
I am building a multi-tier app (similar to the design of MS PetShop). My
original intention was to use MS DTC and declarative/automatic transactions,
but having run into a million problems and having read some performance
stats, I am at least investigating an alternative path:

The BLL (Business Logic Layer) that is the transaction boundary, but would
contain no transaction code other than [AutoComplete] attributes, etc, now
has some knowledge of transactions. Each DAL (Data Access Layer) method has
a ref IDbTransaction parameter, which they test for null and initialize if
they are.

This results in DAL code similar to:
public void MyMethod(...., ref IDbTransaction trans)
{
OracleConnection conn = null;
if ( trans == null )
{
conn = new OracleConnection(CONN_STRING);
trans = conn.BeginTransaction();
}
else
{
conn = (OracleConnection)trans.Connection;
}
:
OracleCommand cmd = ...
cmd.Connection = conn;
cmd.Transaction = (OracleTransaction)trans;
:
}

and BLL code similar to:

IDbTransaction trans = null;
try
{
DALFactory.Create().MyMethod(..., trans);
DALFactory.Create().MyOtherMethod(..., trans);
}
catch ( Exception e )
{
IDbConnection conn = trans.Connection;
trans.Rollback();
conn.Close();
throw e;
}
IDbConnection conn = trans.Connection;
trans.Commit();
conn.Close();

Not very elegant.... I can swallow the transaction stuff in the BLL code,
but I would really prefer it if I could end each DAL method with a
conn.Close() as I would if I was using EnterpriseServices.

Is there a more elegant solution? What happens if I call conn.Close()
Click to expand...
with
a
running transaction? I suppose I could try it, but I am still interested in
input and suggestions on what to do.
Click to expand...

For explicit transactions you should make your DAL object stateful and have
it own the connection and the transaction. So your source looks like:

class DAL : IDisposable
{
IDbConnection conn;
IDbTransation trans;
public DAL()
{
conn = new OracleConnection(...);
}
public void BeginTransaction()
{
trans = conn.BeginTransaction();
}
public void CommitTransaction()
{
trans.CommitTransaction();
trans = null;
}
public void RollbackTransaction()
{
trans.RollbackTransaction();
trans = null;
}
public void Dispose()
{
if (trans != null)
{
CommitTransaction();
}
conn.Close();
}

public void MyMethod(...., ref IDbTransaction trans)
{
OracleCommand cmd = ...
cmd.Connection = conn;
if ( trans != null )
{
cmd.Transaction = (OracleTransaction)trans;
}

:
}

}

then your application code for a multi-step transaction is:

DAL dbConn = DALFactory.Create();
try
{
dbConn.MyMethod(...);
dbConn.MyMethod2(...);
...
}
catch (Exception ex)
{
dbConn.RollbackTransaction();
throw;
}
finally
{
dbConn.Dispose();
}

and for a single step transaction:


using (DAL dbConn = DALFactory.Create())
{
dbConn.MyMethod(...);
}

David
Click to expand...
 
Back
Top