• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3248
  • Last Modified:

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!
0
mkdev2009
Asked:
mkdev2009
  • 2
  • 2
1 Solution
 
teebonCommented:
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
 
teebonCommented:
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
 
mkdev2009Author Commented:
Hi teebon, thank for your help.

It works with pass transaction object into ExecuteNonQuery
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now