Click here to Skip to main content
15,888,351 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
DbUpdateJob_DoWork(object sender, DoWorkEventArgs e)
{
    using (DbContextTransaction mdbtrans =  
    Context.Database.BeginTransaction(IsolationLevel.Serializable))
    {
       try
       {
            --- db operations with entityA  - 1  ( Insert records )
            DbUpdateJob.ReportProgress(20);

            --- db operations with entityB - 2 ( Insert records)
            DbUpdateJob.ReportProgress(40);

            --- db operations with entityC - 3 (Update records )
            DbUpdateJob.ReportProgress(60);

            --- db operations wiwth entityD - 4 (Insert records)
            DbUpdateJob.ReportProgress(80);

            Context.SaveChanges();
            mdbtrans.Commit();
            DbUpdateJob.ReportProgress(100);
       }
       catch(Exception ex)
       {
           mdbtrans.Rollback();
           DbUpdateJob.ReportProgress(0);
       }
   }
}

DbUpdateJob_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
   pbupdate.Value = e.ProgressPercentage;
   if (e.ProgressPercentage == 0)
      MessageBox.Show(e.Message); 
   if (e.ProgressPercentage == 100)
      MessageBox.Show("Success..!!"); 

}


Using SQL Server 2014, Entity Framework 6.0, .Net Framework 4.7.2, C# Winforms.

In the above code, if exception is generated anywhere in between it comes to the catch block and shows the error message. But the transaction does not roll back the changes done to the tables before the exception line.

What might cause this behaviour ? Please advise. Thanks in advance.

What I have tried:

Was using SqlTransaction earlier, changed to DBContextTransaction. Removed all external library references from inside the transaction block. Couldn't find a relevant solution link in Google search. Most links suggest the same kind of logic.. but it isn't working as expected for me.
Posted
Updated 27-Dec-19 19:32pm
v2
Comments
[no name] 27-Dec-19 8:38am    
And you are sure that the exception does not happens in DbUpdateJob.ReportProgress(100) which is after the commit?
Priya-Kiko 28-Dec-19 0:01am    
Thanks for your response. I have missed out some lines while copying the code here... anyways the actual code has some exception in the db Operations 1 or 2 inside the try block.

Are you defining Context somewhere else? It SHOULD be created inside DoWork, where all of your database work is going on.

If you're defining a global Context to be used everywhere in your class, you're doing it wrong and this can create problems for you that are a royal pain in the ass to track down.

Where you are doing database work, the pattern should be open your context as late as possible, do your database work, and close/dispose your context as early as possible.

Also, your try/catch block isn't reporting the error anywhere, so it's hiding the exception and the details that caused it. This makes troubleshooting much more difficult, such as the problem you're having now.

For debugging purposes, either get rid of the try/catch block or log the Exception details somewhere where you can read it.
 
Share this answer
 
Comments
Priya-Kiko 28-Dec-19 0:46am    
Thank you for throwing some light. The Context was indeed defined "Somewhere else" globally. As for the error reporting, we have an error handling routine in place. This is just a sample code.
Dave Kreskowiak 28-Dec-19 1:10am    
Your "sample code" doesn't help solve the problem.

The reason having a "global" context is a problem is because you cannot have two methods using the context at the same time. For example, having one method return records from via an enumeration and having a second method trying to use the context to modify records will not work and will throw.
Priya-Kiko 28-Dec-19 1:41am    
Ok. Would you suggest me to get rid of the global Context altogether?

Is it not right to confine the global Context for just reading operations for reporting etc., and dispose it off finally on Exit while we use another local Context object inside using() for db Insert, Update and Delete ??

Now have changed the updation code like this :

DbUpdateJob_DoWork(object sender, DoWorkEventArgs e)
{
using (MyContext dbContext = new MyContext())
{
using (DbContextTransaction mdbtrans =
dbContext.Database.BeginTransaction(IsolationLevel.Serializable))
{
try
{
--- db operations with entityA - 1 ( Insert records )
DbUpdateJob.ReportProgress(20);

--- db operations with entityB - 2 ( Insert records)
DbUpdateJob.ReportProgress(40);

--- db operations with entityC - 3 (Update records )
DbUpdateJob.ReportProgress(60);

--- db operations wiwth entityD - 4 (Insert records)
DbUpdateJob.ReportProgress(80);

dbContext.SaveChanges();
mdbtrans.Commit();
DbUpdateJob.ReportProgress(100);
}
catch(Exception ex)
{
mdbtrans.Rollback();
DbUpdateJob.ReportProgress(0);
}
}
}
}

DbUpdateJob_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
pbupdate.Value = e.ProgressPercentage;
if (e.ProgressPercentage == 0)
MessageBox.Show(e.Message);
if (e.ProgressPercentage == 100)
MessageBox.Show("Success..!!");

}

After creating a local Context the rollback is perfectly fine. Thank you once again.
Dave Kreskowiak 28-Dec-19 1:46am    
Get rid of the global context. What you're not thinking about is that your hogging a connection resource on the SQL server for the life of your BackgroundWorker object. Connection resources on the SQL server are limited. If your class is holding a connection open for the life of the instance, that's a bad thing.

ALWAYS open as late as possible, do your work as quickly as possible, and close/dispose of the context as early as possible.

Priya-Kiko 28-Dec-19 1:58am    
Ok. Thank you.
Try an example that "works", include "your" changes until it breaks. Or, rethink your approach since EF has (some) default transaction logic.

Working with Transactions - EF6 | Microsoft Docs[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900