Part 18 - Handling ChangeConflictException

Suggested Videos
Part 15 - Identity Cache in Linq to SQL
Part 16 - Difference between optimistic and pessimistic concurrency control
Part 17 - Concurrency in Linq to SQL



This is continuation to Part 17. Please watch Part 17 before proceeding. In this video we will discuss how to handle ChangeConflictException. 



There are 3 options available to handle ChangeConflictException. RefreshMode enum values define how to handle optimistic concurrency conflicts. This enum has 3 values

KeepCurrentValues - Keeps all the current changes made by the current user in the DataContext object. SubmitChanges() method will save all changes made by the current user, overwriting any changes made by other users after the data was loaded by the current user.

KeepChanges - Keeps the current values that have been changed, but updates the other values with the database values. SubmitChanges() method will save any changes made by the current user and will preserve any changes made by other users. If another user changed the same value as the current user, the current user's change will overwrite it.

OverwriteCurrentValues - Updates the DataContext with the current database values, which means that all changes made by the current user will be discarded.

Example : To handle the exception include the Linq to Sql code in try/catch block. Modify the code in btnDeposit_Click() as shown below. 

protected void btnDeposit_Click(object sender, EventArgs e)

{

    using (SampleDataContext db = new SampleDataContext())

    {

        try

        {

            Account account = db.Accounts.First(x => x.AccountNumber == 1);

            account.AccountBalance = account.AccountBalance + 500;

            db.SubmitChanges();

            GetAccountData();

        }

        catch (ChangeConflictException)

        {

            db.ChangeConflicts.ResolveAll(RefreshMode.OverwriteCurrentValues);

            foreach (ObjectChangeConflict objectChangeConflict

                in db.ChangeConflicts)

            {

                foreach (MemberChangeConflict memberChangeConflict

                    in objectChangeConflict.MemberConflicts)

                {

                    Response.Write("Current Value = " +

                        memberChangeConflict.CurrentValue.ToString() + "<br/>");

                    Response.Write("Original Value = " +

                        memberChangeConflict.OriginalValue.ToString() + "<br/>");

                    Response.Write("Database Value = " +

                        memberChangeConflict.DatabaseValue.ToString() + "<br/>");

                }

            }

            db.SubmitChanges();

            GetAccountData();

        }

    }

}

With the above changes
Step 1 : Throw a break point on the line where we call db.SubmitChanges(); in btnDeposit_Click() method.

Step 2. Run the application in Debug mode, and click "Deposit $500" button. The processing should stop just before we call SubmitChanges() method.

Step 3 : At this point open SQL Server Management Studio, and execute the following query

Update Accounts set AccountBalance = AccountBalance - 300

Where AccountNumber = 1

Step 4 : Now come back to Visual Studio and press F5 to continue the execution. Notice that "ChangeConflictException" is thrown and handled. Also, the balance gets overwritten with the current value.

Now change the following line
db.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
TO
db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);

Run the application again in Debug mode, and click "Deposit $500" button. When the application excution stops at the break point. Execute the following UPDATE statement from SQL Server Management Studio. The SQL statement has updated 2 columns (AccountName and AccountBalance)

Update Accounts set AccountBalance = AccountBalance - 300,

AccountName = 'John-Mary' Where AccountNumber = 1

Now come back to Visual Studio and press F5 to continue the execution. Notice that "ChangeConflictException" is thrown and handled. The balance gets overwritten with the current value, but the AccountName is not. This is because RefreshMode.KeepChanges option will keep and update only the values that have been changed by the current user since the data is loaded into the DataContext. This means SubmitChanges() will save only changes made by the current user and will preserve any changes made by other users. 

Now change the following line
db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
TO
db.ChangeConflicts.ResolveAll(RefreshMode.OverwriteCurrentValues);

Run the application again in Debug mode, and click "Deposit $500" button. When the application excution stops at the break point. Execute the following UPDATE statement from SQL Server Management Studio. The SQL statement has changed AccountName and AccountBalance.

Update Accounts set AccountBalance = AccountBalance - 300,

AccountName = 'Mary John' Where AccountNumber = 1

Now come back to Visual Studio and press F5 to continue the execution. Notice that "ChangeConflictException" is thrown and handled. Notice that the changes made by the other user are preserved. So, RefreshMode.OverwriteCurrentValues will update the DataContext with the current database values, which means that all changes made by the current user will be discarded.