This question is related to my question: SQL Server and TransactionScope (with MSDTC): Sporadically can't get connection

I'm doing some transaction programming using the .net TransactionScope class. If I understand correctly, I can do some SQL operations within a transaction by wrapping the SQL calls inside a using ts as new TransactionScope() block, or by using new TransactionScope() and then TransactionScope.Dispose() at the end.

To commit the transaction, MSDN says to use TransactionScope.Commit(). Supposing that I want to rollback the tra开发者_开发百科nsaction under certain circumstances, is it sufficient to simply call TransactionScope.Dispose() without calling the Commit method first? Is that good practice, or is this supposed to be done some other way?

if you know you want to rollback then do that explicitly. You are not guaranteed that Dispose will rollback (in the case where complete has been called, the transaction will be committed when you call Dispose)

to your question about using or new/Dispose they are not equivalent

using(var ts = new TransactionScope())
{
}

is equivalent to

TransactionScope ts;
try
{
  ts = new TransactionScope();
}
finally
{
  ts.Dispose();
}

to answer your follow up question no if you call Dispose you will not have your transaction "hanging around" it will either commit or rollback. However if you use the new/dispose as you wrote it (no finally block) you can have a situation where dispose isn't called when you expect it to be (in the case of an exception)

If you call TransactionScope.Dispose() (either by a using block or by calling the Dispose method yourself), it will rollback the transaction unless you tell it t commit first. By putting in a Transaction.Rollback, you are explicitly telling other programmers that is what you intend on doing.

I would probably add it for sake of clarity that this action was intended in that circumstance. The other thing about not explicitly adding the rollback command is that you are assuming that the Dispose method will always behave in this manner. In reality this will probably be the case, but making that assumption is risky. It's always better to explicitly roll it back instead of hoping that it will be done for you.

The intent of the TransactionScope class, as I see it, is to make transactions as foolproof as possible to the application developer, and to this end, make the recommended means of aborting a transaction be the one with the least extra thought. I believe (based on the documentation of TransactionScope) a simple Dispose is the recommended means of ending the transaction, and if it hasn't been committed, it will be rolled back.

I've always encased the TransactionScope in a Using block, since that automatically calls the Dispose if there's an unhandled exception that prevents the transaction from completing successfully.

I'll provide an answer, and an implementation I used using EF, Unit of Work, and Repository Pattern.

Answer

From the documentation:

If you want to rollback a transaction, you should not call the Complete method within the transaction scope. For example, you can throw an exception within the scope. The transaction in which it participates in will be rolled back.

Take a look to the examples they share in the links. Those are highly illustrative (I'll copy one of the examples down below, just in case).

Implementation Example

My Implementation

This is what I had to save into the DB, from an MVC project:

What is the best way to rollback a .net transaction?

I faced this problem as follows, implementing the method:

void SaveTheThing(tracker, driver, manager, destination, transportation);

Read the comments:

// Create the TransactionScope to execute the commands, guaranteeing
// that both commands can commit or roll back as a single unit of work.
using (TransactionScope scope = new TransactionScope())
{
    // Step 1
    int driverId = SavePerson(driver);
    int destinationId = SaveDestination(destination);

    // Step 2
    transportation.id_person_driver = driverId;
    transportation.id_destination = destinationId;
    int transportationId = SaveTransportation(transportation);

    // Step 3
    int managerId = SavePerson(manager);

    // Step 4
    tracker.id_person_manager = managerId;
    tracker.id_transportation = transportationId;
    SaveTracker(tracker);

    // Step 5
    // The Complete method commits explicitly the transaction.
    // If an exception has been thrown, then Complete is not
    // called and the transaction is rolled back.

    /* If I add an exception here, the transaction will roll-back */
    throw new Exception("Roll it back!");

    // This code becomes unreachable until you delete the Exception
    scope.Complete(); // If we get here things are looking good.
    _unitOfWork.Save(); // If we get here it is save to accept all changes.

}
// Implicitly rolls back the transaction to the DB if something goes wrong.
// Scope is disposed. Entity(ies) are not committed.

Inside, SaveWhatever(whatever), there's methods that use SaveChanges() and other DbContext and DbSet related methods.

And in controller (this is an MVC project):

try
{
    SaveTheThing(tracker, driver, manager, destination, transportation);
}
catch (TransactionAbortedException taEx)
{
    // If something wrong happens while committing the transaction,
    // it will rollback and throw this exception.
    Console.WriteLine(taEx.Message);
    return View("Edit", vm);
}
catch (Exception ex)
{
    // The thrown Exception is catched here.
    Console.WriteLine(ex.Message);
    return View("Edit", vm);
}

Keep in mind that this approach presents some issues that I'm still to investigate: There's some persistence in EF or in my repositories, that remains while the connections is open (I'm working on it, I'll update when I fix it). But yes, the DB does not receive the data and the roll-back is actually made.

Readings

TransactionScope Class
Implementing an Implicit Transaction using Transaction Scope
TransactionScope.Complete

Example (From Documentation)

// This function takes arguments for 2 connection strings and commands to create a transaction 
// involving two SQL Servers. It returns a value > 0 if the transaction is committed, 0 if the 
// transaction is rolled back. To test this code, you can connect to two different databases 
// on the same server by altering the connection string, or to another 3rd party RDBMS by 
// altering the code in the connection2 code block.
static public int CreateTransactionScope(
    string connectString1, string connectString2,
    string commandText1, string commandText2)
{
    // Initialize the return value to zero and create a StringWriter to display results.
    int returnValue = 0;
    System.IO.StringWriter writer = new System.IO.StringWriter();

    try
    {
        // Create the TransactionScope to execute the commands, guaranteeing
        // that both commands can commit or roll back as a single unit of work.
        using (TransactionScope scope = new TransactionScope())
        {
            using (SqlConnection connection1 = new SqlConnection(connectString1))
            {
                // Opening the connection automatically enlists it in the 
                // TransactionScope as a lightweight transaction.
                connection1.Open();

                // Create the SqlCommand object and execute the first command.
                SqlCommand command1 = new SqlCommand(commandText1, connection1);
                returnValue = command1.ExecuteNonQuery();
                writer.WriteLine("Rows to be affected by command1: {0}", returnValue);

                // If you get here, this means that command1 succeeded. By nesting
                // the using block for connection2 inside that of connection1, you
                // conserve server and network resources as connection2 is opened
                // only when there is a chance that the transaction can commit.   
                using (SqlConnection connection2 = new SqlConnection(connectString2))
                {
                    // The transaction is escalated to a full distributed
                    // transaction when connection2 is opened.
                    connection2.Open();

                    // Execute the second command in the second database.
                    returnValue = 0;
                    SqlCommand command2 = new SqlCommand(commandText2, connection2);
                    returnValue = command2.ExecuteNonQuery();
                    writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
                }
            }

            // The Complete method commits the transaction. If an exception has been thrown,
            // Complete is not  called and the transaction is rolled back.
            scope.Complete();
        }
    }
    catch (TransactionAbortedException ex)
    {
        writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
    }

    // Display messages.
    Console.WriteLine(writer.ToString());

    return returnValue;
}