Avoiding Transaction Promotion with Multiple Connections - Improvements in System.Data and SQL Server 2008

  • Article

Great news! The new updates added to System.Data and SQL Server 2008 finally allow multiple Open/Close connections to the same SQL Server without promoting the transaction to MSDTC.

This was by far the most requested feature for the System.Transactions/System.Data/SQL Server combination.

There is no more the need to write workarounds like ConnectionScope to avoid promotions.

Now I can write as many Open/Close, Open/Close as I need to, without worrying about promotion:

 transacted(()=>
{
   using (SqlConnection connection1 = new SqlConnection(connectionString))
   {
      Connection1.Open();

      SqlCommand command1 = new SqlCommand(commandString1, connection1);
      command1.ExecuteNonQuery();

      SqlCommand command2 = new SqlCommand(commandString2, connection1);
      command2.ExecuteNonQuery();

      connection1.Close();
   }

   ...

   using (SqlConnection connection2 = new SqlConnection(connectionString))
   {
      Connection2.Open();

      SqlCommand command1 = new SqlCommand(commandString1, connection2);
      command1.ExecuteNonQuery();

      SqlCommand command2 = new SqlCommand(commandString2, connection2);
      command2.ExecuteNonQuery();

      connection2.Close();
   }
});

More details on the updates at the ADO.Net team blog.