Transactions commit and rollback with Entity Framework, Mysql and LINQ to SQL in C#

Question

My problem is that the transaction is not working properly it should not save the data for one table if an exception occurs during the trascation

When all the table is correct then only save data.

Consider the following:

databaseEntites objEntites = null;  
using (objEntites = new databaseEntites())  
{
     objEntites.Connection.Open();  
     using (System.Data.Common.DbTransaction transaction = 
         objEntites.Connection.BeginTransaction())
     {
         try
         {  
             customer objcust=new customer();  
             objcust.id=id;  
             objcust.name="test1";  
             objcust.email="[email protected]";  
             objEntites.customer.AddObject(objcust);  

             order objorder=new order();  
             objorder.custid=objcust.id;  
             objorder.amount=500;  
             objEntites.order.AddObject(objorder);  

             objEntites.SaveChanges();  
             transaction.Commit();  
         }  
         catch()  
         {  
             transaction.Rollback();  
         }  
     }  
}

In this my second table column name is not correct and on SaveChanges() giving the exception.

When i see the database and found that it saving the data for customer table which is wrong i want data will go in the customer table when all table is correct and this savechanges either save for all table or not save for any.

For this i have also try the TransactionScope

using (TransactionScope tscope = 
    new TransactionScope(TransactionScopeOption.RequiresNew))
{
......all the code here....
    objEntites.SaveChanges(false);

    tscope.Complete();
    objEntites.AcceptAllChanges();
}

But its giving the same issue as described above.

Thanks in advance.

Solution

You can use database transaction or EF TransactionScope. For using database transaction it is enough to do as below:

using (var dbContextTransaction = context.Database.BeginTransaction()) 
{
  try
  {
    //Some stuff
    dbContextTransaction.Commit(); 
  } 
  catch (Exception) 
  { 
    dbContextTransaction.Rollback(); 
  }
} 

And for using second way that EF TransactionScope just use easily as below:

using (var scope = new TransactionScope(TransactionScopeOption.Required)) 
    {  
      try
      {
        //Some stuff
        scope.Complete(); 
      } 
      catch (Exception) 
      { 
         //Don't need call any rollback like method
      }
    } 

The point is no Rollback() method exist in the TransactionScope (against the normal ADO.NET Transaction) and Unless you call the Complete() method, the transaction do not complete and all the changes are rolled back automatically. You can see MSDN for better understand: http://msdn.microsoft.com/en-us/data/dn456843.aspx

Hope this help