Manual Transaction with a plugable DAL

Manual Transaction with a plugable DAL

Old forum URL:

paul_rockerdale posted on Wednesday, July 27, 2011


I'm stuck with a SQL 2000 DB but I'm using CSLA4. Because the database is old I'm going to have to use manual transactions right? Well I don't think transactionscope will work well a SQL 2000DB?

I've read that I can use the TransactionManager object to help me reuse a connection and an existing transaction. What I'm trying to figure out is how I can start a transaction in my dataportal class library class and pick this up in my DAL classes for parent and child dals.

Could I start transaction using the transaction manager inside my dal manager; were the dal manager is created in the dataportal method; and then use the Transaction manager using block in the DAL's CRUD methods?

Here's my dataportal method on my root object

protected override void DataPortal_Update()
 using (var dalManager = DataAccess.DalFactory.GetManager())

                var dal = dalManager.GetProvider<DataAccess.IPersonDal>();
                using (BypassPropertyChecks)
                    dal.Update(Id, FirstName, LastName, TitleId);


 Here and dal method

public void Update(int id, string firstName, string lastName, int TitleId)
 using (var ctx = TransactionManager<SqlConnection, SqlTransaction>.GetManager("LocalDb"))
  using (var cm = ctx.Connection.CreateCommand())
                    cm.CommandType = System.Data.CommandType.Text;
                    cm.CommandText = "UPDATE CslaPerson SET FirstName=@firstName, LastName=@lastName, TitleId=@TitleId WHERE Id=@id";
                    cm.Parameters.AddWithValue("@id", id);
                    cm.Parameters.AddWithValue("@firstName", firstName);
                    cm.Parameters.AddWithValue("@lastName", lastName);
                    cm.Parameters.AddWithValue("@TitleId", TitleId);
                    var rowsAffected = cm.ExecuteNonQuery();
                    if (rowsAffected == 0)
                        throw new DataNotFoundException("Person");


PS. I'm using the EncapsulatedInvoke technique.

RockfordLhotka replied on Wednesday, July 27, 2011

Yes, the DAL manager for your concrete DAL implementation can open the connection and start the transaction with the transaction manager. All other code using the transaction manager on that thread will get the same transaction.

You don't want your DP_XYZ code calling begin/commit/rollback though. That prevents composition of your business types.

rsbaker0 replied on Wednesday, July 27, 2011



You don't want your DP_XYZ code calling begin/commit/rollback though. That prevents composition of your business types.

We used manual transaction management since we started.

I solved the composition issue by using a concept of a "transaction context" being managed at the thread level (only one context with an open transaction per thread at any one time).  Typically, the "server-side" methods pass the context around explicitly as a parameter, but if you call a client-side method (e.g. "Save"), the DP_XYZ method will automatically enlist itself in the currently open transaction if there is one, otherwise it will begin a new one.

It has really worked out well -- we can compose our BO's arbitrarily without explicit regard to transaction boundaries.

RockfordLhotka replied on Wednesday, July 27, 2011

Yes, Csla.Data includes a transaction manager type for this purpose.

But that type of detail belongs to the DAL, not the DP_XYZ methods.

One DAL might use ADO.NET transactions. Another might use TransactionScope or something. A mock DAL will probably have no transaction concept. The DP_XYZ methods should be neutral to what technology (if any) is used.

paul_rockerdale replied on Thursday, July 28, 2011

I suppose were I'm struggle is visualising the nesting of manual transactions. So this is how I see it.

I've a Root object that contains a child list. I've created two DAL classes; one class for the root and one for child. The update method in the root DAL wraps the update statement with ......  using (var ctx = ConnectionManager<SqlCeConnection>.GetManager("LocalDb")). In the child DAL I use a separate using statement. These two using statements; one in the root DAL; and one in the child DAL; don't appear to be nested.

However, in the root DataPortal_XYZ method I create a DAL Manager and in its constructor I create a variable to hold a transaction manager object. I've created a DAL manager in my dataportal root with a using statement..... using (var dalManager = DataAccess.DalFactory.GetManager())..... So I'm assuming whilst in this using block all calls to the DAL will be nested within one transaction (if all calls are on the same thread). So in my root DAL I can open a connection and set a transaction isolation level. But where would I commit the transaction? Or will it simply commit when the dataportal using statement for the DalManager completes, assuming no errors occur.

RockfordLhotka replied on Thursday, July 28, 2011

You wouldn't use a ConnectionManager anymore - instead you'd use a TransactionManager.

ADO.NET transaction objects have a Connection property, so you really only need to ensure that the transaction is set up when the DalManager is created - and everyone can use that transaction, and the connection to which it is associated.

paul_rockerdale replied on Wednesday, August 03, 2011

Ok to recap. I create my Root DAL manager inside my parent DP_XYZ method by doing ....using (var dalManager = DataAccess.DalFactory.GetManager()). My Root DAL Manager constructor looks like the code below. The Root manager DAL controls the start of the transaction, were I will have control over what isolation level I want for the transaction. All OrderDal, OrderItemDal and OrderPersonItem can all use the same transaction by using the TransactionManager. Great....

But now to the problem... The commit variable in the transaction manager can only be set to True when the refcount equals 1. So the commit has to be handled by the root DAL Manager. But where can I do the commit? I can't do the commit via the DP_XYZ as this will effect composition. There's not much point doing it via the OrderDal because the TransactionManager refcount will equal 2. So I thought about doing it in the dispose method of the root DAL Manager, but surely that can't be the correct place to call a Commit, can it? It feels like all avenues to commit the transaction have been close off... this has lead me to the conclusion that I must be doing something fundamentally wrong :-( 

        public DalManager()

            ConnectionManager = ConnectionManager<SqlConnection>.GetManager("LocalDb");

            //if we in the root begin a transaction
            if (ConnectionManager.RefCount == 1)
                ConnectionManager.Connection.BeginTransaction(Serializable); //TO DO ensure fetches don't use Serializable

            TransactionManager = TransactionManager<SqlConnection, SqlTransaction>.GetManager("LocalDb");

        public void Dispose()
            TransactionManager = null;

            ConnectionManager = null;




RockfordLhotka replied on Wednesday, August 03, 2011

The TransactionManager type is designed primarily to be used within a series of nested using blocks. At the end of each using block, right before the using block is exited, Commit is called. That way, if any exception occurs within the using block, Commit isn't called, so the transaction is rolled back.

Because your DAL code isn't directly using the TransactionManager, you need to elevate that behavior to the object that the code is using: your DalManager.

In other words, your DalManager needs a Commit method, that just delegates to the TransactionManager. And in your DAL code, at the bottom of each DalManager using block you should call Commit - that will achieve the same result.

paul_rockerdale replied on Wednesday, August 03, 2011

Thanks for you help.

My mistake was my misinterpretion of "You don't want your DP_XYZ code calling begin/commit/rollback though. That prevents composition of your business types." I took the first part to mean I can't do DALManager.commit; were the commit was going to delegated to whatever concreate implementation is required.

Now with that done I can start doing my T4 code gen of my CSLA classes.... exciting times Smile

stiv replied on Wednesday, June 19, 2013

Hello Paul,

Do you have an example of your implementation of the business objects root and child using the TransactionManager, i´m trying to achieve the same solution.

Thanks in advance,



karlcraft replied on Thursday, August 04, 2011

Hi Rocky and Paul,

I too have been looking at the TransactionManager, my only concern is that I couldn't see a way to set the transaction Isolation level that the transaction manager will use. Ideally we want to use Serializable as the isolation level for our updates.

I noted in your Using CSLA 4 chapter 3 Data Access book (pg 44) that you state the following regarding the use of TransactionScope :-

The TransactionScope created by the data portal uses default values for all settings, including the isolation level and timeout value. This means the transaction is Required, has an isolation level of Serializable, and has a one minute timeout. If you need different values from the defaults you will need to use manual transactions and create your own TransactionScope object.

I guess my question is this, why does the TransactionScope use Serializable, but the Transaction Manager does not?

I have reflected on the code in the Transaction Manager Class and it doesn't set the isolation level,

    private TransactionManager(string connectionString, string label) 
        this._label = label;
        this._connectionString = connectionString;
        this._connection = (default(C) == null) ? Activator.CreateInstance<C>() : default(C);
        this._connection.ConnectionString = connectionString;
        this._transaction = (T) this._connection.BeginTransaction();


RockfordLhotka replied on Thursday, August 04, 2011

That may be a documentation error. When I wrote that bit in the book, I based it on an MSDN article - in terms of the defaults for TransactionScope. Perhaps the article was out of date, or wrong?

What is the default isolation level?

paul_rockerdale replied on Thursday, August 04, 2011

msdn does say that the default isolation level for a transaction scope is serializable.

For Connection.BeginTransaction msdn says it just uses the default... Which for an sql 2000 database the default is read commited

When I used the transaction manager the isolation level is set to readcommited in my database... i veiwed this in profiler.

I tried to be sneaky in my code by kicking of a transaction via the connection manager with the hope that the transaction manager will pick up the same connection and transaction when Transaction Manager comes into play. Well it didn't work... worth a try though!

I suppose all I really need is for the Transaction Manager to be serializable too. Can this be changed?


RockfordLhotka replied on Thursday, August 04, 2011

TransactionManager can't unilaterally change to a different isolation level. What should happen, is that TransactionManager should have a static property that allows you to supply a "transaction provider" so you create the transaction instead of TransactionManager creating the transaction.

I'll add this to the wish list.

sergeyb replied on Thursday, August 04, 2011

How about the same for Transactional attribute based on Transaciton scope?

RockfordLhotka replied on Thursday, August 04, 2011


How about the same for Transactional attribute based on Transaciton scope?

Yes, that is a good idea as well.

Copyright (c) Marimer LLC