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
TrialUserAsked:
Who is Participating?
 
CodeCruiserConnect With a Mentor Commented:
Next option is to use same connection with both adapters, start transaction on this connection and then assign this transaction to all commands on both adapters.
0
 
CodeCruiserCommented:
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
0
 
TrialUserAuthor Commented:
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
0
 
TrialUserAuthor Commented:
Is there anywy to do it from code, using javascript or vb.net, so the setting is only specific to my application. Thanks
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.