CODEWITHSUNDEEP
c#sql
RegularNormalDayGuy
RegularNormalDayGuy

Reputation: 735

Clarification on sqlTransaction operations

My understanding of a transaction is that it is one or multiple SQL commands that will all be executed as a group.

Now, I'm reading Microsoft's documentation and I do not understand the goal of the first try-catch exception block. Why would we try to rollback the transaction if it fails. What I mean is that if it fails, then shouldn't we be positive that no changes were done in the DB (since that's the goal of a transaction) ? What am I not understanding ?

private static void ExecuteSqlTransaction(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // Start a local transaction.
        transaction = connection.BeginTransaction("SampleTransaction");

        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

        try
        {
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
            command.ExecuteNonQuery();
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
            command.ExecuteNonQuery();

            // Attempt to commit the transaction.
            transaction.Commit();
            Console.WriteLine("Both records are written to database.");
        }
        catch (Exception ex)
        {
            // Logging exception details
            // Attempt to roll back the transaction.
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // Logging exception details
            }
        }
    }
}

Upvotes: 0

Views: 58

Answers (4)

Polyfun
Polyfun

Reputation: 9639

According to the Microsoft documentation that you linked: "The transaction is rolled back on any error, or if it is disposed without first being committed." [My emphasis]. So I would expect the following to work too:

private static void ExecuteSqlTransaction(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (SqlCommand command = connection.CreateCommand())
        using (SqlTransaction transaction connection.BeginTransaction("SampleTransaction"))
        {
            // Must assign both transaction object and connection
            // to Command object for a pending local transaction
            command.Connection = connection;
            command.Transaction = transaction;

            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
            command.ExecuteNonQuery();
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
            command.ExecuteNonQuery();

            // Attempt to commit the transaction.
            transaction.Commit();
            Console.WriteLine("Both records are written to database.");
        }
    }
}

Note that there is no explicit Rollback call here.

Because I have put the SqlTransaction into a using a block, it will always get disposed, even if an exception is thrown. If execution proceeds normally, the "transaction.Commit()" will commit the transaction. However, if an exception is thrown and we don't get to the Commit() call, then the transaction won't be committed, but it will still be disposed (on leaving the scope of the using block). And we know from the documentation that the transaction will be rolled back if it is disposed without first being committed, or indeed, on any error.

There is no need to catch any exception here, because we cannot usefully do anything with the exception. I would expect the exception to be handled somewhere higher up in the calling chain, e.g., where it might be logged and a friendly error message displayed to the user.

To conclude--the Microsoft sample code in the documentation is not always the best.

Upvotes: 1

Jon Roberts
Jon Roberts

Reputation: 2282

Alternatively check out system.transactions.transactionscope

it can be used inside a Using and if not completed will rollback without the need for a rollback command.

Upvotes: 2

Jan Heikel
Jan Heikel

Reputation: 11

You have to end the transaction with either rollback or commit. If the commit fails you have to rollback, so that the transaction isn't left open, which can naturally cause a multitude of problems.

Upvotes: 1

Jason Wadsworth
Jason Wadsworth

Reputation: 8885

A rollback of a transaction will undo anything that you did as a part of that transaction. So if you start a transaction, insert a few records into a table, do an update of a record and that update fails the rollback would remove the inserted records as well.

Upvotes: 2

Related Questions