Entity Framework using BeginTransaction()

Question

I am trying to implement Entity Framework in my application and i should be able to commit and rollback the changes manually.

First time when i execute the update statement it updates the table successfully and i am able to rollback the changes. This is correct

But second time when i execute the update statement, it updates the table successfully and also commits the changes. So I am unable to rollback manually. This is wrong

Please let me know why it is happening and how to solve this issue.

The below code is just sample to reproduce my problem.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;

namespace EFTest
{
    public class DBOperations
    {
        NorthwindEntities NorthwindContext;
        DbTransaction transObject;

        public DBOperations()
        {
        }

        public void ConnectDB()
        {
            try
            {
                if (NorthwindContext == null)
                {
                    NorthwindContext = new NorthwindEntities();
                    if (NorthwindContext != null && NorthwindContext.Connection.State != ConnectionState.Open)
                    {
                        NorthwindContext.Connection.Open();
                        transObject = NorthwindContext.Connection.BeginTransaction(IsolationLevel.ReadUncommitted);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Database Error " + ex.Message);
            }
        }

        public int disconnect()
        {
            if (NorthwindContext != null && transObject != null)
            {
                try
                {
                    transObject.Rollback();
                }
                catch (Exception)
                {
                }
                transObject.Dispose();
                NorthwindContext.Connection.Close();
                NorthwindContext.Dispose();
            }

            return 0;
        }

        public void CommitTransaction()
        {
            if (NorthwindContext != null && transObject != null)
            {
                try
                {
                    transObject.Commit();
                }
                catch (Exception)
                {
                }
            }
        }

        public void RollbackTransaction()
        {
            if (NorthwindContext != null && transObject != null)
            {
                try
                {
                    transObject.Rollback();
                }
                catch (Exception)
                {
                }
            }
        }

        public int UpdateDB()
        {
            int _returnVal = 0;


            try
            {
                NorthwindContext.ExecuteStoreCommand("UPDATE Orders SET OrderDate = GETDATE() WHERE OrderID = '10248'");
            }
            catch (Exception ex)
            {
                throw new Exception("Database Error " + ex.Message);
            }

            return _returnVal;
        }
    }

    public class program
    {
        public program()
        {
            //Establishing the connection.
            DBOperations _DBOperations = new DBOperations();
            _DBOperations.ConnectDB();

            //Update the datebase
            _DBOperations.UpdateDB();                           //Update the database but it doesn't commit the changes.                       

            //Issue Rollback to rollback the transaction.
            _DBOperations.RollbackTransaction();                //Successfully Rollbacks the database changes.


            //Again Update the datebase
            _DBOperations.UpdateDB();                           //Update the database it commits the changes. 

            //Issue Rollback to rollback the transaction.
            _DBOperations.RollbackTransaction();                //Rollback fails.

        }
    }
}

Answer 1

You need to assign new transaction after commit or rollback of transaction .

public program()
{
    //Establishing the connection.
    DBOperations _DBOperations = new DBOperations();
    _DBOperations.ConnectDB();

    //Update the datebase
    _DBOperations.UpdateDB();    //Update the database but it doesn't commit the changes.

    //Issue Rollback to rollback the transaction.
    _DBOperations.RollbackTransaction();    //Successfully Rollbacks the database changes.

    _DBOperations.ConnectDB(); //you need to assign new transaction because your last 
                               //transaction is over when you commit or roll back 

    _DBOperations.UpdateDB();    //Update the database it commits the changes.

    //Issue Rollback to rollback the transaction.
    _DBOperations.RollbackTransaction();    //Rollback fails.
}

Answer 2

With TransactionScope Your DbOperations could look like this:

public class DBOperations : IDisposable
{
    NorthwindEntities _context;
    private TransactionScope _transactionScope;

    public DBOperations()
    {
        this.Initialize();
    }

    private void Initialize()
    {
        try
        {
            this.Dispose();
            this._transactionScope = new TransactionScope();
            this._context = new NorthwindEntities();
            // no need to open connection. Let EF manage that.
        }
        catch (Exception ex)
        {
            throw new Exception("Database Error " + ex.Message);
        }
    }

    public void RollbackTransaction()
    {
            try
            {
                this._transactionScope.Dispose();
                this._transactionScope = null;
                this.Dispose();
                this.Initialize();
            }
            catch (Exception)
            {
                // TODO
            }
    }

    public int UpdateDB()
    {
        int _returnVal = 0;
        try
        {
            this._context.ExecuteStoreCommand("UPDATE Orders SET OrderDate = GETDATE() WHERE OrderID = '10248'");
        }
        catch (Exception ex)
        {
            throw new Exception("Database Error " + ex.Message);
        }
        return _returnVal;
    }

    public void Dispose()
    {
        if (this._transactionScope != null)
        {
            this._transactionScope.Complete();
            this._transactionScope.Dispose();
        }
        if (this._context != null) this._context.Dispose();
    }
}

And the program:

public class program
{
    public program()
    {
        using (DBOperations dbOperations = new DBOperations())
        {
            dbOperations.UpdateDB(); // Update the database no commit.

            dbOperations.RollbackTransaction(); // Rollback.

            dbOperations.UpdateDB(); // Update the database no commit.

            dbOperations.RollbackTransaction(); // Rollback.
        } // Commit on Dispose.
    }
}

A connection that is opened within the scope of a TransactionScope automatically enlists in the transaction. The transaction is only committed by calling Commplete(). Disposing or unhandled exceptions will cause a rollback.

If you do more than just a store command, as in changing objects and relying on the context's change tracking, you may to implement a retry mechanism in stead of just discarding the context and the changes.