asked on

Wrapping INsert statements in ado.net in sql transactions

In my ASP.NET / VB.NET application , i create a transaction which consists of 3 steps:
1) insert transactionheader row
2) insert transactiondetail rows
3) insert transaction payment rows.
HOw do I wrapp all these into a transaction so, if the steps 2 and steps 3 are not creatd properly then step 1 will be rolled back too. Thanks please help

Dim dtTransaction As New dsTransaction.TransactionsDataTable
Dim drTransaction As dsTransaction.TransactionsRow = dtTransaction.NewRow
With drTransaction
.UserID = intUserID
.CustomerID = intCustomerID
'assign all values here
End With
dtTransaction.AddTransactionsRow(drTransaction)
Dim intRtn As Integer = taTran1.Update(dtTransaction)
drTransaction = dtTransaction.Rows(0)
Dim dtTRansactioneNtry As DataTable = 'someho get dataable
Dim drentry As DataRow
For Each drentry In dtTRansactioneNtry.Rows

taEntry.Insert(drTransaction.ID, Convert.ToInt32(drentry("ItemID")),all other fields here)
Next
Dim dtPayments As DataTable = 'getdt

For Each drPayment In dtPayments.Rows
taPayment.Insert(drTransaction.ID, Convert.ToInt32(drPayment("PaymentTypeID")), decAmount, strRef)
Next

Return drTransaction.ID

Nasir Razzaq

You are using DataAdapters to insert these rows. It would be easier if you use SQLCommand object directly and execute insert statements so you can use transactions easily.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

TrialUser

ASKER

I cannot change the existing code due to other reasons. Is there a way to do it with the dataadapters and the way it has already been implemented. Thanks

TrialUser

ASKER

Is there anywy to do it from code, using javascript or vb.net, so the setting is only specific to my application. Thanks

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial