ADO.NET check if Rollback is possible

Question

I'm asking myself if it is possible to check if in ADO.NET the current transaction can be rolled back.

The msdn suggests the following implementation:

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)
        {
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
            Console.WriteLine("  Message: {0}", ex.Message);

            // Attempt to roll back the transaction.
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // This catch block will handle any errors that may have occurred
                // on the server that would cause the rollback to fail, such as
                // a closed connection.
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                Console.WriteLine("  Message: {0}", ex2.Message);
            }
        }
    }
}

Additionally there is the note: Try/Catch exception handling should always be used when rolling back a transaction. A Rollback generates an InvalidOperationException if the connection is terminated or if the transaction has already been rolled back on the server.

But I can't really believe that a try/catch is the recommended solution to check if a rollback is possible.

I know that in the SQL Server implementation the SQLTransaction objects returns null on the Connection property if the transaction is a "zombie".

But this is quite implementation specific and it only works with SQL Server.

So is there a db-independent way to detect if a transaction can be rolled back?

tia Martin

Answer 1

A lot of this complexity is handled by using a TransactionScope object in a "using" statement - check it out on MSDN. One caveat is that a TransactionScope will automatically "scale up" to use a distributed transaction when it is deemed necessary - sometimes this is desireable, and other times it isn't, so be careful if you are nesting TransactionScopes.

Answer 2

the problem is that in non SQL 2005 the transactionscope is promoted to a distributed transaction which is quite an overhead.