Is it possible to use distributed transactions with multiple unitOfWork instances? Here is some sample code...

            using (TransactionScope scope = new TransactionScope())
            {
                using (var adapter = new CatalogA.DatabaseAccessAdapter())
                {
                    UnitOfWorkA.Commit(adapter, true);
                }

                using (var adapter = new CatalogB.DatabaseAccessAdapter())
                {
                    UnitOfWorkB.Commit(adapter, true);
                }

                using (var adapter = new CatalogC.DatabaseAccessAdapter())
                {
                    UnitOfWorkC.Commit(adapter, true);
                }

                scope.Complete();
            }

vhtas wrote:

Is it possible to use distributed transactions with multiple unitOfWork instances? Here is some sample code...

            using (TransactionScope scope = new TransactionScope())
            {
                using (var adapter = new CatalogA.DatabaseAccessAdapter())
                {
                    UnitOfWorkA.Commit(adapter, true);
                }

                using (var adapter = new CatalogB.DatabaseAccessAdapter())
                {
                    UnitOfWorkB.Commit(adapter, true);
                }

                using (var adapter = new CatalogC.DatabaseAccessAdapter())
                {
                    UnitOfWorkC.Commit(adapter, true);
                }

                scope.Complete();
            }

I thought if you passed true in the auto commit for the UnitOfWork2 it will commit your transaction immediately. You want to pass false so that the transaction isn't committed early. Then each UnitOfWork will create a transaction on its designated source and only when scope.Complete is called will the transaction will be persisted.

Thanks Brandt. For some reason I had assumed the default was false.

The goal is to be able to have a transaction that span across multiple catalogs. I was able to get it to work using the code below. Is this an appropriate use of UnitOfWork?

            
            using (var adapterA = new CatalogA.DatabaseAccessAdapter())
            using (var adapterB = new CatalogB.DatabaseAccessAdapter())
            using (var adapterC = new CatalogC.DatabaseAccessAdapter())
            {
                try
                {
                    UnitOfWorkA.Commit(adapterA, false);
                    UnitOfWorkB.Commit(adapterB, false);
                    UnitOfWorkC.Commit(adapterC, false);
                }
                catch (Exception ex)
                {
                    adapterA.Rollback();
                    adapterB.Rollback();
                    adapterC.Rollback();
                }
            }

vhtas wrote:

Thanks Brandt. For some reason I had assumed the default was false.

The goal is to be able to have a transaction that span across multiple catalogs. I was able to get it to work using the code below. Is this an appropriate use of UnitOfWork?

            
            using (var adapterA = new CatalogA.DatabaseAccessAdapter())
            using (var adapterB = new CatalogB.DatabaseAccessAdapter())
            using (var adapterC = new CatalogC.DatabaseAccessAdapter())
            {
                try
                {
                    UnitOfWorkA.Commit(adapterA, false);
                    UnitOfWorkB.Commit(adapterB, false);
                    UnitOfWorkC.Commit(adapterC, false);
                }
                catch (Exception ex)
                {
                    adapterA.Rollback();
                    adapterB.Rollback();
                    adapterC.Rollback();
                }
            }

Actually the way you did it first was the way to do it. I personally think using transaction scope is the best way. You just don''t want to pass 'true' to the UnitOfWork for autocommit because it will commit the transcation right after its work isdone. If you look at the UnitOfWork code it checks to see if true was passed and commits immediately if it is. It doesn't check to see if it was a borrowed transaction. If it doesn't commit it is your responsiblity to commit the transaction on the dataaccessadapter or the transactionscope.

So in your code above you must commit the transaction on each of your adapter(s)* in the try block or your changes will not be saved.

This is how i would do it

    


            using (var adapterA = new CatalogA.DatabaseAccessAdapter())
            using (var adapterB = new CatalogB.DatabaseAccessAdapter())
            using (var adapterC = new CatalogC.DatabaseAccessAdapter())
            using (TransactionScope scope = new TransactionScope())
            {
                try
                {
                    UnitOfWorkA.Commit(adapterA, false);
                    UnitOfWorkB.Commit(adapterB, false);
                    UnitOfWorkC.Commit(adapterC, false);
                    scope.Complete()
                }
                catch (Exception ex)
                {
                    adapterA.Rollback();
                    adapterB.Rollback();
                    adapterC.Rollback();
                }
            }
    


            

I took your advice and added the TransactionScope as you suggested. Rollbacks work fine since I'm explicitly rolling back each adapter. However, calling scope.Complete() does not seem to commit the changes. In doing this does this require DTC at the database?

Adapters signal to the scope that they are committed or rolledback and the scope then rollsback/commits the transaction.

So the complete call should either rollback the complete transaction or commit it. However, it might be that this takes a couple of milliseconds, as the commit is asynchronous. So if you directly check if the commit succeeded, you might be mistaken. In one of our unittests we wait for 2 seconds before testing the results because of this, could you check if the results are indeed committed after, say, a second?

Also, you do see activity on the MS DTC?

I got things working using the code below. However, I'm sure I'm not in a pure atomic transaction. There is no activity in MS DTC. As soon as I introduce a TransactionScope it doesn't work. Specifically, nothing gets rolled back.

I'm very much a beginner when it comes to distributed transactions. Do I need to have support at the DB level for this to work? I'm using Sybase ASE.

Here's the code that works but I don't think I'm in a distributed transaction:

                using (var adapterA = new CatalogA.DataAccessAdapter)
                using (var adapterB = new CatalogB.DataAccessAdapter)
                using (var adapterC = new CatalogC.DataAccessAdapter)
                {
                    try
                    {
                        UnitOfWorkA.Commit(adapterA , false);
                        UnitOfWorkB.Commit(adapterB , false);
                        UnitOfWorkC.Commit(adapterC , false);

                        adapterA .Commit();
                        adapterB .Commit();
                        adapterC .Commit();
                    }
                    catch (Exception ex)
                    {
                        adapterA .Rollback();
                        adapterB .Rollback();
                        adapterC .Rollback();
                        throw ex;
                    }
                }

Here's how I think it should work but it doesn't:

                
          using (var scope = new TransactionScope())
          { 
                using (var adapterA = new CatalogA.DataAccessAdapter)
                using (var adapterB = new CatalogB.DataAccessAdapter)
                using (var adapterC = new CatalogC.DataAccessAdapter)
                {
                    try
                    {
                        UnitOfWorkA.Commit(adapterA , false);
                        UnitOfWorkB.Commit(adapterB , false);
                        UnitOfWorkC.Commit(adapterC , false);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }

                scope.Complete();
           }

yes you need support on the DB level for MS DTC. If that's not available, you can't use distributed transactions (as that's coordinated by MS DTC). Furthermore, if the DB doesn't support lightweight transaction support (sqlserver 2005/2008, oracle 11g DO support it), the distributed transaction is always propagated to a full MS DTC transaction, so slower than lightweight transactions).

i.o.w.: as you're using sybase ase, I think you should stick with normal ADO transactions.

If I use normal ADO.Net transactions is it possible to wrap all three adapters into a single transaction?

vhtas wrote:

If I use normal ADO.Net transactions is it possible to wrap all three adapters into a single transaction?

Not out of the box, you then have to pass the same adapter to all unit of works.

You can make your own subclass of the DataAccessAdapter and override CreateNewPhysicalTransaction to pass a transaction object you passed into the adapter through a property of some sort instead of creating a new one. Keep in mind that you also have to share a connection in that case. It's easier to simply pass the adapter around than adding this logic I think.

So basicly do: - collect work in unit of work objects - commit them with 1 adapter

As you don't need an adapter when unitofwork objects are created / used, only when they're committed.

But the adapters are catalog specific. How would I be able to commit entities from the multiple catalogs if I'm using only one adapter?

vhtas wrote:

But the adapters are catalog specific. How would I be able to commit entities from the multiple catalogs if I'm using only one adapter?

Are these catalogs located on the same server or on different servers? If they're on different servers, I don't think it's possible without MS DTC support to have a single transaction spanning multiple servers.

They are on the same server.

So is there a way to wrap multiple adapters from different catalogs in a single transaction without the use of MS DTC?

Why don't use one LLBLGen Project for both catalogs? You can have entities from more than one catalog into the same project, this will make dealing with them later much easier.

Otherwise I think MS DTS is the way to go.

Walaa wrote:

Why don't use one LLBLGen Project for both catalogs? You can have entities from more than one catalog into the same project, this will make dealing with them later much easier.

I was under the impression that this is not possible with Sybase ASE. Is this incorrect?

I don't know, would this link be useful: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21335675.html

vhtas wrote:

Walaa wrote:

Why don't use one LLBLGen Project for both catalogs? You can have entities from more than one catalog into the same project, this will make dealing with them later much easier.

I was under the impression that this is not possible with Sybase ASE. I this incorrect?

Indeed, sorry for the confusion we've caused.

So this leaves you one option: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=25336&ThreadID=4670

It's a fairly old thread but shows what's to be done. In short: you have multiple adapter instances, one creates a physical transaction, the other ones receive that created transaction. To be able to use a shared transaction in adapter 2, 3, etc., you have to share the IDbConnection AND the transaction object. As adapter 2, 3, etc. call CreateNewPhysicalConnection and CreateNewPhysicalTransaction, you can override these in the derived class of DataAccessAdapter and either create a new instance (by simply calling base.<methodname>) or by returning the shared instance the adapter received from adapter 1. Please read the whole thread linked above for further details.

If you run into problems, don't worry: in that case please post your derived adapter class and call code and which error you run into and we'll look into it.

I'll read through the thread. Thanks for all the help!

Just to be clear, I assume I still have the option of MS DTC if I enable it at the database, correct?

vhtas wrote:

I'll read through the thread. Thanks for all the help!

Just to be clear, I assume I still have the option of MS DTC if I enable it at the database, correct?

if Sybase ASE supports it, then you can use TransactionScope which works with MS DTC and takes care of propagating the transaction over the several connections. Though it will likely be slower than sharing the ado.net transaction.

I'm working on subclassing DataAccessAdapter and had a quick question...

Do I need to call StartTransaction on all adapters even though it will use the same transaction? Also, I assume I would only need to call commit on the first adapter, correct?

Start transaction has to be called on all adapters, commit only on the first one.

Thanks for all the help guys! Extending the data access adapter works.