Is this correct usage of TransactionSope?

Question

I have decided to try using a TransactionScope, rather than the SqlTransaction class.

The following is my code, wrapped in a TransactionScope:

using (var transaction = new System.Transactions.TransactionScope())
{
    using (MySqlCommand cmd = new MySqlCommand(sql, connection))
    {
        if (listParameters != null && listParameters.Count > 0)
        {
            foreach (string currentKey in listParameters.Keys)
            {
                cmd.Parameters.Add(new MySqlParameter(currentKey, GetDictionaryValue(listParameters, currentKey)));
            }
        }

        using (MySqlDataReader reader = cmd.ExecuteReader())
        {
            dtResults.Load(reader);
        }
    }

    transaction.Complete();
}

The code works, however I am not binding the MySqlCommand cmd object with a transaction at any point. Is this a problem?

Answer 1

No, this is not the correct use.

The correct use is to create a connection after creating TransactionScope. Then the connection will detect the ambient TransactionScope and enlist itself.

using (var transaction = new System.Transactions.TransactionScope())
{
  using (var connection = new MySqlConnection())
  {
     ...
  }
}

If you create the connection before the scope, that connection will be out of that scope, even if you create the command after creating the scope.

Also note that TransactionScope defaults to Serializable level of isolation. This is the most secure level, but also the least concurrent one. You often want to explicitly set a more common isolation level:

using (var transaction = new TransactionScope(
             TransactionScopeOption.Required,
             new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted  }))
{ 

}