DB Transaction Using Microsoft.Practices.EnterpriseLibrary

hi all,

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

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();
dbConnection.Open();
DbTransaction dbTran = dbConnection.BeginTransaction();

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

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


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!
mkdev2009Asked:
Who is Participating?
 
teebonConnect With a Mentor Product ManagerCommented:
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);
0
 
teebonProduct ManagerCommented:
Hi mkdev2009,

You might want to try to use DBCommandWrapper:

http://www.vbdotnetheaven.com/UploadFile/leonpere/EnterpriseLibraryvb11142005014116AM/EnterpriseLibraryvb.aspx
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()
Try
connection.Open()
Dim transaction As IDbTransaction = connection.BeginTransaction()
Try
db.ExecuteNonQuery(creditCommandWrapper, transaction) ' Credit the first account
db.ExecuteNonQuery(debitCommandWrapper, transaction) ' Debit the second account
transaction.Commit() ' Commit the transaction
Catch
transaction.Rollback() ' Rollback transaction
End Try
connection.Close()
Finally
CType(connection, IDisposable).Dispose()
End Try

Open in new window

0
 
mkdev2009Author Commented:
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?
0
 
mkdev2009Author Commented:
Hi teebon, thank for your help.

It works with pass transaction object into ExecuteNonQuery
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.