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


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.

Comments (2)

  1. mdoctor says:

    will this be available against Sql 2005 too?

  2. florinlazar says:

    To: mdoctor

    I doubt it, but to be sure, you should post the question on the ADO.NET blog.