Commit/Rollback Multiple database transactions calls from business layer to repository layer

Question

I have Presentation Layer/ Business Layer/ Repository Layer. All the database calls like select/update/insert/delete are done at Repository Layer. Business layers checks for the rules and makes the call according to Repository layer to interact with Database. The issue that I am having is Rolling back the transactions at business layer whenever something goes wrong in any of the calls.

I did refer to some of the articles like this and this but I am getting the System.Transactions.TransactionException error using transactionscope.

EDIT:-

This is how I am making the calls from the business layers to repository layer.

public bool CreatePerson(Model AddPersonToSchool)
    {
        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromSeconds(120)))
        {
            try
            {
                //Check if the person is already created, if not assign him the next Roll call and details

                CreatePersonDetails(Person); //Repository call where SaveChanges() are called.
                //Check the preReq for the subjects

                CreatePersonSubjects(Person); //Repository call where SaveChanges() are called.

                //Check for the Availability of the seats in classes 
                CreateTimeTableForClasses(Person);  //Repository call where SaveChanges() are called.

                LogDetails(Person);

                scope.Complete();
                return true;
            }
            catch (Exception ex)
            {
                scope.Dispose();
                RecordErrorException(ex);
                return false;
            }
        }
    }

This is the error message I am getting.

Exception type: System.Transactions.TransactionException
Message       : The operation is not valid for the state of the transaction.
Stacktrace:
   at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
   at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.<Open>b__38(DbConnection t, DbConnectionInterceptionContext c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.EntityConnection.<Open>b__2()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)
   at System.Data.Entity.Core.EntityClient.EntityConnection.Open()

The fix could be as simple as changing the [Transaction](https://msdn.microsoft.com/en-us/library/system.transactions.transaction(v=vs.110).aspx) type value to something else but we need to know the exact error message that you're getting and how the different layers call each other. An mcve would really help anyone checking your question. — Dealdiane, Aug 25 '15 at 21:06
If you need `TransactionScope` with regular repositories something is fundamentally wrong. It should be possible to commit nearly all business transactions by one `SaveChanges` call (irrespective of how deeply it's buried under repository/UoW layers). That said, if you have a problem with code, *show* the code. This question is unanswerable. — Gert Arnold, Aug 25 '15 at 21:14
Look at this: http://stackoverflow.com/q/193154/861716. Your code doesn't reveal any relevant detail for us to be able to diagnose the problem. — Gert Arnold, Aug 26 '15 at 22:40

Commit/Rollback Multiple database transactions calls from business layer to repository layer

0 Answers0