Transaction on multiple oracle databases

ecaldentey posted on Tuesday, November 18, 2014

Hello

I would like to know how to use TransactionManager to manage a transaction on multiple oracle databases.I use TransactionManager for Root/Child transactions but on the same database, below is an example.

I don´t know how to set a TransactionManager that hold different connections object and that is able to commit or rollback the complete transaction. I think i have to use a UoW object but i don't how to wrap the complete process with one TransactionManager object.

Any help will be appreciated, thank in advance, Esteban

protected override void DataPortal_Update()
        {
            bool cancel = false;
            OnBeforeUpdate(ref cancel, this);
            if (cancel) return;
           
            RetailDto resultRetailDto = null;
           
         using (var ctx = DalFactory.GetManager())
         {
          var dal = ctx.GetProvider<IRetailDal>();
               
                using (var ctxConn = TransactionManager<OracleConnection, OracleTransaction>.GetManager(this.BreintekManager.ConnectStringDB, false, this.BreintekManager.DBConnId.ToString()))
                {
                    Csla.ApplicationContext.LocalContext[this.BreintekManager.DBConnId.ToString()] = "TransacOK";
          
                    using (BypassPropertyChecks)
                    {
                        RetailDto retailDto = new RetailDto(this.BreintekManager.CodigoAccesoSistema, this.BreintekManager.Usuario)
                        {
                            RetailId = this.RetailId,
                            ....
                        };
                       
                        retailDto.BreintekManager = this.BreintekManager;
                        retailDto.BreintekManager.AccionDB = BreintekAccionesDB.Update;                       
                       
                        retailDto.BreintekManager.EsTransaccion = true;
                       
                  resultRetailDto = dal.Update(retailDto);
                   
                        OnAfterUpdate(ref resultRetailDto);
                       
                        if(resultRetailDto.BreintekManager.TieneError)
                        {
                            if(resultRetailDto.BreintekManager.EsTransaccion)
                            {
                                var dalTrx = ctx.GetProvider<IManejoTransaccion>();
                                dalTrx.EjecutaTransaccion(resultRetailDto.BreintekManager, AdministraError.ListaErrorTrx);
                            }
                           
                            this.BreintekManager = resultRetailDto.BreintekManager;
                            ApplicationContext.LocalContext.Remove(this.BreintekManager.DBConnId.ToString());
                            return;
                        }
                    }
                   
                    cancel = false;
                    OnBeforeUpdateChild(ref cancel,  ref resultRetailDto);
                    if (cancel) return;

                    FieldManager.UpdateChildren(this);
                   
                    OnAfterUpdateChild(this, ref resultRetailDto);
               
                    if  ((!this.BreintekManager.TieneError) && (Csla.ApplicationContext.LocalContext[this.BreintekManager.DBConnId.ToString()].ToString() == "TransacOK"))
                    {
                        this.BreintekManager.Status = 1;
                        this.BreintekManager.OpcionDB = 0;
                        ctxConn.Commit();
                    }
                    else
                    {
                        this.BreintekManager.Status = -1;
                        this.BreintekManager.StatusMessage = String.Format("Se produjeron errores en objetos hijos.{0}{1}", Environment.NewLine, this.BreintekManager.StatusMessage);
                    }

                    ApplicationContext.LocalContext.Remove(this.BreintekManager.DBConnId.ToString());               
                }
            }
        }

ajj3085 replied on Tuesday, November 18, 2014

Do you know if the Oracle client you're using supports enrolling in TransactionScope transactions?  If so, I would recommend using TransactionScope over the Transaction manager.  I suspect that would be easier than using the TransactionManager.

JonnyBee replied on Wednesday, November 19, 2014

You must check the capabilities of our Oracle client. I know Devarts Oracle provider differs in support of TransactionScope between OCI and Direct mode as well. This should all be well documented by your Oracle client. 

Using the Csla.TransactionalAttribute on the DataPortal_XYZ methods is definitely the simplest and easiest way of having support for both local and distributed transactions assuming that your Oracle client has full support for TransactionScope.