Enterprise library: Roll back multiple transactions

I'm currently using Microsoft Enterprise Library 5.0, and I'm wondering if the cod开发者_JAVA百科e below is an accepted way of handling transactions.

I've simplified the scenario a bit, but the essence is that I want to perform multiple inserts in different databases within the same transaction.

If either of the inserts fail, all transactions should be rolled back.

I've looked at TransactionScope, but I want to know if I could manage without it.

public void InsertStuff_AcrossDbs()
{
    //Create a ref to 2 different Db's on the same server
    Database db_a = DatabaseFactory.CreateDatabase("Data Source=localhost;Initial Catalog=db_a"); 
    Database db_b = DatabaseFactory.CreateDatabase("Data Source=localhost;Initial Catalog=db_b");

    //Create Connections for the 2 db's
    using (DbConnection connection_db_a = db_a.CreateConnection())
    using (DbConnection connection_db_b = db_b.CreateConnection())
    {
        //Create DbTransactions for the 2 db's
        DbTransaction transaction_dbA = connection_db_a.BeginTransaction();
        DbTransaction transaction_dbB = connection_db_b.BeginTransaction();

        try
        {
            //Insert into DbA.Person and get the PK
            DbCommand dbCmd_dbA_insert = db_a.GetSqlStringCommand("Insert INTO Person(Name,Age)Values('test',23); SET @pkReturnId= SCOPE_IDENTITY() ");
            db_a.AddOutParameter(dbCmd_dbA_insert, "pkReturnId", DbType.Int32, 0);
            db_a.ExecuteNonQuery(dbCmd_dbA_insert, transaction_dbA);
            int personID = Convert.ToInt32(db_a.GetParameterValue(dbCmd_dbA_insert, "@pkReturnId"));

            //Insert 'personId' into dbB.Employee  (a different table in a different db)
            DbCommand dbCmd_dbB_delete = db_a.GetSqlStringCommand("Insert INTO Employee(personId) VALUES(" + personID + ")");
            db_a.ExecuteNonQuery(dbCmd_dbB_delete, transaction_dbB);

            //try to commit both transactions
            transaction_dbA.Commit();
            transaction_dbB.Commit();
        }
        catch (Exception ex)
        {
            //If either transactions fails, roll back both
            try
            {
                transaction_dbA.Rollback();
            }
            catch { }

            try
            {
                transaction_dbB.Rollback();
            }
            catch { }

            throw ex;
        }
        finally
        {
            connection_db_a.Close();
            connection_db_b.Close();
        }

    }

}

Is this code considered OK, or will there be scenarios where not all of the transactions will be rolled back if one of them should throw an error?

---

Your code is not OK. There are scenarios where the two databases will not be consistent.

In your code you are creating two local transactions: one in database A and one in database B. This isn't what you want since you want both operations to occur within one transaction in order to maintain consistency. To do this you need to use a distributed transaction and, as you mention, TransactionScope is the best way to do this. It will also make the code more readable.

e.g.

Database db_a = DatabaseFactory.CreateDatabase("Data Source=localhost;Initial Catalog=db_a"); 
Database db_b = DatabaseFactory.CreateDatabase("Data Source=localhost;Initial Catalog=db_b");

using (TransactionScope scope = new TransactionScope())
{
    using (DbConnection connA = db_a.CreateConnection())
    {
        // ...
    }

    using (DbConnection connB = db_b.CreateConnection())
    {
        // ...
    }

    scope.Complete();
}

I realize your code is a simplified scenario so I'm not sure if these apply to your actual code but here are some other comments:

DbCommand dbCmd_dbB_delete = 
    db_a.GetSqlStringCommand("Insert INTO Employee(personId) VALUES(" + personID + ")");

Dynamic SQL strings should be avoided. They are vulnerable to SQL injection attacks and also lower performance since the database execution plan will not be reused for other requests with different parameters.

finally
{
    connection_db_a.Close();
    connection_db_b.Close();
}

You don't need to manually close your connections since you are disposing the connections via your using statements and Close and Dispose are functionally equivalent.