Transaction Scope

Hi Team,

I have a piece of code that sits within a transaction but appears to be commiting data I may have misunderstood how to use transactions correctly within lightspeed

  using (TransactionScope transactionScope = new TransactionScope())

            {

                foreach (GlTransactionPrimary transactionPrimary in glTransactions)

                {

                    UnitOfWorkScope.Current.Add(transactionPrimary);

                   // call save changes here because I need the id below for the stored proc

   UnitOfWorkScope.Current.SaveChanges();

                    UnitOfWorkScope.Current.GlTransactionAddTaxAdjustmentForClosedPeriod(fundingCompanyId, transactionPrimary.Id);

                }

                UnitOfWorkScope.Current.SaveChanges();

                transactionScope.Complete();

            }

GlTransactionAddTaxAdjustmentForClosedPeriod is a stored proc which requires the ID of the transactionPrimary, I noticed it was commiting data when I had another error which caused the foreach loop to crash on the second or third iteration the first transationPrimary was still commited to the database, any help is appreciated.

Thanks,

Jay

Hello Jay,

Your code looks okay and if your database supports TransactionScope then I would not expect your changes to be committed until the transaction scope is completed.

However not all databases support TransactionScope, and those that don't tend to just ignore it with no warning.  On these databases you need to use the BeginTransaction method instead:

using (var txn = UnitOfWorkScope.Current.BeginTransaction()) {
  // perform multiple SaveChanges operations
  txn.Commit();
}

What database are you on?  If it's PostgreSQL or Oracle, they definitely don't support ambient transactions; most others do I think but I can check for you.

Hi Ivan,

We are using sql 2008 r2, I tried to isolate what might be causing the problem and created the contrived example below

 public void JournalEntryInsert(IList<GlTransactionPrimary> glTransactions, GlPosting posting,  int fundingCompanyId)

        {

        int i = 0;

            using (TransactionScope transactionScope = new TransactionScope())

            {

                foreach (GlTransactionPrimary transactionPrimary in glTransactions)

                {

if (i == 1)

{

throw new NotImplementedException();

}

                    UnitOfWorkScope.Current.Add(transactionPrimary);

                UnitOfWorkScope.Current.SaveChanges();

                  //  UnitOfWorkScope.Current.GlTransactionAddTaxAdjustmentForClosedPeriod(fundingCompanyId, transactionPrimary.Id);

                i++;

                }

                UnitOfWorkScope.Current.SaveChanges();

                transactionScope.Complete();

            }

        }

Just saving one entity and commenting out the stored proc and as you can see I am throwing an error on the second iteration. The data was still being committed from the first iteration.

So I refactored the code to what you suggested

public void JournalEntryInsert(IList<GlTransactionPrimary> glTransactions, GlPosting posting,  int fundingCompanyId)

        {

            using (var txn = UnitOfWorkScope.Current.BeginTransaction())

            {

                foreach (GlTransactionPrimary transactionPrimary in glTransactions)

                {

                    UnitOfWorkScope.Current.Add(transactionPrimary);

                UnitOfWorkScope.Current.SaveChanges();

                    UnitOfWorkScope.Current.GlTransactionAddTaxAdjustmentForClosedPeriod(fundingCompanyId, transactionPrimary.Id);

                }

                UnitOfWorkScope.Current.SaveChanges();

txn.Commit();

            }

        }

And it works fine, so not sure why transactions scope isnt working but your suggestion works great thanks

Cheers,

Jay