Using TransactionScope or DbTransaction in LINQ to SQL

LINQ to SQL supports SQL transaction and in many cases calling SubmitChanges() method is enough to finalize the batch transaction. However, there are cases when you need explicit transaction handling.

Note: the following examples assume that we're connecting to remote SQL Server because when connecting to remote SQL, we sometimes have to consider MSDTC involvement for distributed transactions.

Example (1) below is the case you need explicit transaction. For testing purpose, I added if-statement in the middle and threw the exception. When an exception is thrown, if you check SQL Server table, you will notice that data is removed by sp_delete() method (this is a LINQ to SQL method that calls SQL Stored Procedure). So even if we didn't call SubmitChanges() method, data transaction can occur, so need to have explicit transaction.

//Example (1)
public void SaveWithoutTransaction()
{
   DataClasses1DataContext db = new DataClasses1DataContext();
   db.Connection.Open();

   int id = 10;
   db.sp_delete(id);

   if (id == 10) throw new ApplicationException(); //<== Testing 

   Tab1 t1 = new Tab1();
   t1.Id = id;
   t1.Name = "Tom";
   db.Tab1s.InsertOnSubmit(t1);
   db.SubmitChanges();
}

So for the explicit or manual transaction, we can use TransactionScope or ADO.NET DbTransaction. The Example (2) shows how to use TransactionScope. TransactionScope is simple and easy to use. If running this program, the whole transaction will be rolled back because of if-statement exception. If commenting out testing if-statement, the transaction is successful.

//Example (2)
public void SaveWithTransactionScope()
{
   int id = 10;
   DataClasses1DataContext db = new DataClasses1DataContext();
   using (TransactionScope trans = new TransactionScope())
   {
      db.sp_delete(id);

      if (id == 10) throw new ApplicationException(); // testing 

      Tab1 t1 = new Tab1();
      t1.Id = id;
      t1.Name = "Tom";
      db.Tab1s.InsertOnSubmit(t1);
      db.SubmitChanges();

      trans.Complete();
   }
}

And also if I run sp_lock against SQL Server to check table lock status, I can see the row is locked exclusively.

sp_lock result
55 7 2105058535 0 TAB IX
55 7 2105058535 1 KEY (d08358b1108f) X
55 7 2105058535 1 PAG 1:157 IX

now let's do some experiment here. I added another DataContext in the middle and called SubmitChanges() for the db2 DataContext.

// Example (3)
public void SaveWithTransactionScope()
{
   int id = 10;
   DataClasses1DataContext db = new DataClasses1DataContext();
   using (TransactionScope trans = new TransactionScope())
   {
      db.sp_delete(id);

// let's use another instance of DataContext
      DataClasses1DataContext db2 = new DataClasses1DataContext();
      Tab2 t2 = new Tab2();
      t2.Id = 1;
      t2.Name = "aaa";
      db2.Tab2s.InsertOnSubmit(t2);
      db2.SubmitChanges();  //<== Error occurs here

      Tab1 t1 = new Tab1();
      t1.Id = 10;
      t1.Name = "Tom";
      db.Tab1s.InsertOnSubmit(t1);
      db.SubmitChanges();

      trans.Complete();
   }
}

Then, I got the following SqlException.

     MSDTC on server 'MyServer1' is unavailable.

The reason is when you have multiple DataContexts, MSDTC is involved so you need to start MSDTC service on remote SQL Server machine. (if you are connecting to local SQL Server, you won't have the error)

Basically if you have multiple data contexts, you need to use MSDTC.
However, the restriction that you have to start MSDTC service on your remote SQL Server might not be possible in some cases. If you cannot (or don't want to) start MSDTC inevitably, you can try a kind of trick using manual transaction with DbTransaction. Here is how to. First, get DbTransaction object from Connection.BeginTransaction() and use Commit at the bottom of try block and Rollback method in the catch block. If db2 (DataClasses1DataContext object) throws an exception due to an error, catch block will rollback previously-ran db transaction.

Example (4)
public void SaveWithDbTransaction()
{         
   DbTransaction trans = null;
   DataClasses1DataContext db = new DataClasses1DataContext();
   try
   {
      db.Connection.Open();
      trans = db.Connection.BeginTransaction();
      db.Transaction = trans;

      int id = 10;
      db.sp_delete(id);

      DataClasses1DataContext db2 = new DataClasses1DataContext();
      Tab2 t2 = new Tab2();
      t2.Id = 1;
      t2.Name = "Jim";
      db2.Tab2s.InsertOnSubmit(t2);
      db2.SubmitChanges();  // if errors, it goes to catch block
            
      Tab1 t1 = new Tab1();
      t1.Id = id;
      t1.Name = "Tom";
      db.Tab1s.InsertOnSubmit(t1);
      db.SubmitChanges();

      trans.Commit();
   }
   catch (Exception ex)
   {
      if (trans != null)
      {
         trans.Rollback();
      }
      throw ex;
   }
   finally
   {
      if (db.Connection != null && 
         db.Connection.State == System.Data.ConnectionState.Open)
      {
         db.Connection.Close();
      }
   }
}