none
Entity Framework using BeginTransaction() RRS feed

  • 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.
    
            }
        }
    }
    


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This will help other members to find the solution easily.

    Saturday, September 22, 2012 2:07 PM

Answers

  • Hi ksvimal,

    By committing or rolling back you are "finishing" the "BeginTransaction", so the transaction will no longer be used. You'll need to begin a new transaction after committing or rolling back.

    Regards,

    Tyler

    • Marked as answer by Alexander Sun Tuesday, October 9, 2012 8:53 AM
    Monday, September 24, 2012 3:27 AM