DB Transaction Using Microsoft.Practices.EnterpriseLibrary

Posted on 2009-12-29
Last Modified: 2012-08-13
hi all,

i recently change to use Microsoft.Practices.EnterpriseLibrary.Data to access my database instead of using

and what i need now is to insert multiple insert statement to Database.

Below is my code,

Database db = DatabaseFactory.CreateDatabase();
DbConnection dbConnection = db.CreateConnection();
DbTransaction dbTran = dbConnection.BeginTransaction();

DbCommand dbGroup =db.GetStoredProcCommand("insGroup");
dbGroup.CommandType = CommandType.StoredProcedure;
dbGroup.Transaction = dbTran;

DbCommand dbDet =db.GetStoredProcCommand("insGroupDet");
dbDet.CommandType = CommandType.StoredProcedure;
dbDet.Transaction = dbTran;

When my code reach on the db.ExecuteNonQuery(dbGroup);

my try block catch an error :

The transaction is either not associated with the current connection or has been completed.

i found many forums with the same issue but there are answer for this question yet.

Please help!
Question by:mkdev2009
    LVL 9

    Expert Comment

    Hi mkdev2009,

    You might want to try to use DBCommandWrapper:
    Private creditCommandWrapper As DBCommandWrapper = db.GetStoredProcCommandWrapper("CreditAccount")creditCommandWrapper.AddInParameter("@AccountID", DbType.Int32, sourceAccount)
    creditCommandWrapper.AddInParameter("@Amount", DbType.Int32, transactionAmount)
    Dim debitCommandWrapper As DBCommandWrapper = db.GetStoredProcCommandWrapper("DebitAccount")debitCommandWrapper.AddInParameter("@AccountID", DbType.Int32, destinationAccount)
    debitCommandWrapper.AddInParameter("@Amount", DbType.Int32, transactionAmount)
    'using (IDbConnection connection = db.GetConnection())
    Dim connection As IDbConnection = db.GetConnection()
    Dim transaction As IDbTransaction = connection.BeginTransaction()
    db.ExecuteNonQuery(creditCommandWrapper, transaction) ' Credit the first account
    db.ExecuteNonQuery(debitCommandWrapper, transaction) ' Debit the second account
    transaction.Commit() ' Commit the transaction
    transaction.Rollback() ' Rollback transaction
    End Try
    CType(connection, IDisposable).Dispose()
    End Try

    Open in new window


    Author Comment

    hi teebon,

    thank for your reply, but i where do i actually to find the DBCommandWrapper object?

    i try to use

    using Microsoft.Practices.EnterpriseLibrary.Common;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System.Data.Common;

    but it cant works, can you please advice?
    LVL 9

    Accepted Solution

    Hi mkdev2009,

    What is your version of EnterpriseLibrary?
    I think maybe you are having a different version of EnterpriseLibrary.

    Can you try add an extra attribute to your ExecuteNonQuery method as follows and see whether it works:

    db.ExecuteNonQuery(dbGroup, dbTran);

    db.ExecuteNonQuery(dbDet, dbTran);

    Author Comment

    Hi teebon, thank for your help.

    It works with pass transaction object into ExecuteNonQuery

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now