TransactionScope: When connection will be escalated to MSDTC

Question

I am using TransactionScope in data layer and to avoid MSDTC I keep one connection open till transaction scope completes. I am finding this approach is problematic as it keeps the connection open and occupied for full transaction process. Method that are called from within the transaction scope may take longer time (may be 1 min.). So, I have modified my approach to not use one connection but open and close connection to same database when required.

Like this:

using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using(SqlCommand sqlc = new SqlCommand(statement, Connection, transaction))
    {
        //Execute some commands
    }
    connection.Close();
}
SomeLongRunningTask();
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using(SqlCommand sqlc = new SqlCommand(statement, Connection, transaction))
    {
        //Execute some commands
    }
    connection.Close();
}
...
...
...
scope.Complete();
}

Can any one suggest, if this code can still escalate TransactionScope to MSDTC. Objective of this change is to keep the connection open for minimum time.

Do you see any disadvantage in this approach?

muszeo muszeo · Accepted Answer · 2016-05-04T09:35:45

You should be ok I think so long as you are using SQL 2K8 or above. Just don't open more that one connection at a time and don't open the second connection to some other server than the first. See TransactionScope automatically escalating to MSDTC on some machines?

TransactionScope: When connection will be escalated to MSDTC

1 Answers