IssacJones
asked on
multiple transactions
Hi
I want to use transactions in a vb.net application. This is the working code I have so far:
Dim sqlCommand As String = "SELECT * FROM tblMyTable"
Dim da1 As New SqlClient.SqlDataAdapter
da1 = New SqlClient.SqlDataAdapter(s qlCommand, objConnection)
Dim cmdClientsBuilder As New SqlClient.SqlCommandBuilde r
cmdClientsBuilder = New SqlClient.SqlCommandBuilde r(da1)
da1.InsertCommand = cmdClientsBuilder.GetInser tCommand()
da1.DeleteCommand = cmdClientsBuilder.GetDelet eCommand()
da1.UpdateCommand = cmdClientsBuilder.GetUpdat eCommand()
Dim txn As SqlClient.SqlTransaction = objConnection.BeginTransac tion
da1.UpdateCommand.Transact ion = txn
da1.DeleteCommand.Transact ion = txn
da1.InsertCommand.Transact ion = txn
Try
da1.Update(Me.dt1)
txn.Commit()
Console.WriteLine("Insert succeeded")
Catch ex As Exception
txn.Rollback()
MessageBox.Show(ex.Message )
Console.WriteLine("Insert Failed")
Finally
'Optional. A Finally block is always executed when execution leaves any part of the Try statement.
End Try
HOWEVER, I am puzzled how to use this if I had another update which I wanted to perform. For example, suppose I wanted to update another table as well e.g. dt2
Try
da1.Update(Me.dt1)
da2.Update(Me.dt2)
txn.Commit()
Console.WriteLine("Insert succeeded")
To do this, would I need to have two transaction objects?
Could somebody point me in the right direction, with example code, on how to do this?
Thanks in advance
John
I want to use transactions in a vb.net application. This is the working code I have so far:
Dim sqlCommand As String = "SELECT * FROM tblMyTable"
Dim da1 As New SqlClient.SqlDataAdapter
da1 = New SqlClient.SqlDataAdapter(s
Dim cmdClientsBuilder As New SqlClient.SqlCommandBuilde
cmdClientsBuilder = New SqlClient.SqlCommandBuilde
da1.InsertCommand = cmdClientsBuilder.GetInser
da1.DeleteCommand = cmdClientsBuilder.GetDelet
da1.UpdateCommand = cmdClientsBuilder.GetUpdat
Dim txn As SqlClient.SqlTransaction = objConnection.BeginTransac
da1.UpdateCommand.Transact
da1.DeleteCommand.Transact
da1.InsertCommand.Transact
Try
da1.Update(Me.dt1)
txn.Commit()
Console.WriteLine("Insert succeeded")
Catch ex As Exception
txn.Rollback()
MessageBox.Show(ex.Message
Console.WriteLine("Insert Failed")
Finally
'Optional. A Finally block is always executed when execution leaves any part of the Try statement.
End Try
HOWEVER, I am puzzled how to use this if I had another update which I wanted to perform. For example, suppose I wanted to update another table as well e.g. dt2
Try
da1.Update(Me.dt1)
da2.Update(Me.dt2)
txn.Commit()
Console.WriteLine("Insert succeeded")
To do this, would I need to have two transaction objects?
Could somebody point me in the right direction, with example code, on how to do this?
Thanks in advance
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER