Microsoft.Practices.EnterpriseLibrary.Data: execute multiple statements in one transaction

Question

i recently started with an existing project and it works with the Microsoft.Practices.EnterpriseLibrary.Data objects.

Now i want to execute multiple stored procedures in one transaction (1:n insert which have to all fail or succeed)

But i don't know how....

Can anyone help me out?

Typical code to execute a sp in this project looks like this:

Database oDatabase = DatabaseFactory.CreateDatabase(CONNECTION_STRING_KEY);
DbCommand oDbCommand = oDatabase.GetStoredProcCommand("upCustomer_Insert");

Int32 iCustomerKey = 0;
oDatabase.AddInParameter(oDbCommand, "Firstname", DbType.String, p_oCustomer.FirstName);
oDatabase.AddInParameter(oDbCommand, "Lastname", DbType.String, p_oCustomer.LastName);

oDatabase.ExecuteNonQuery(oDbCommand);

Answer 1

You need to make use of a DbTransaction:

using (DbConnection connection = db.CreateConnection())
{
    connection.Open();
    DbTransaction transaction = connection.BeginTransaction();

    try
    {
        db.ExecuteNonQuery(transaction, sp1);
        db.ExecuteNonQuery(transaction, sp2);
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

Notice how the first parameter to ExecuteNonQuery is the transaction to use.

More info here.

Answer 2

Transaction scope is not thread safe though. You can not use it for multi-thread applications is what I've read. This is a real PITA overall. MS still seems to not understand how to adequately scale software systems.