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);

score 7 · Accepted Answer · answered Nov 19 '10 at 08:46

7

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.

answered Nov 19 '10 at 08:46

RPM1984

72,246
58
225
350

by the way, the page in the link shows this: 'Retired Content'. Is this version of the entlib (or the entire entlib?) not supported anymore? – Michel Nov 19 '10 at 08:51
No problems - let us know how you get on. – RPM1984 Nov 19 '10 at 08:51
1

Entlib is still supported, but that particular version (3.1) is outdated. We just shipped version 5.0 last May. The current doc page is here: http://msdn.microsoft.com/en-us/library/bb748697(v=PandP.50).aspx although it's not any different. By the way, I'd strongly recommend using TransactionScope instead of explicit transactions these days. It's just easier to manage and much more composable. – Chris Tavares Nov 19 '10 at 19:02

score 2 · Answer 2 · answered Jul 02 '14 at 00:12

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.

answered Jul 02 '14 at 00:12

Grwon

61
2

score 0 · Answer 3 · answered Nov 19 '10 at 08:49

0

You could wrap the calls inside a transactionscope, see: http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

answered Nov 19 '10 at 08:49

Shiraz Bhaiji

64,065
34
143
252

Yes, but that's regular ADO.NET - when using Enterprise Library, you should make use of Enterprise Library objects, of which `DbTransaction` is one. Can't believe people still use Enterprise Library BTW. – RPM1984 Nov 19 '10 at 08:51
3

Bzzt! Wrong answer! DbTransaction isn't an Entlib object, it's an ADO.NET object. The best approach these days is to use TransactionScope; it's integrated into more parts of the framework, and it's implicit nature means it's much easier. Entlib fully supports TransactionScope as of Entlib 3. – Chris Tavares Nov 19 '10 at 18:53

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

3 Answers3