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.
