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(sqlCommand, objConnection)
Dim cmdClientsBuilder As New SqlClient.SqlCommandBuilder
cmdClientsBuilder = New SqlClient.SqlCommandBuilder(da1)
da1.InsertCommand = cmdClientsBuilder.GetInsertCommand()
da1.DeleteCommand = cmdClientsBuilder.GetDeleteCommand()
da1.UpdateCommand = cmdClientsBuilder.GetUpdateCommand()
Dim txn As SqlClient.SqlTransaction = objConnection.BeginTransaction
da1.UpdateCommand.Transaction = txn
da1.DeleteCommand.Transaction = txn
da1.InsertCommand.Transaction = txn
Catch ex As Exception
'Optional. A Finally block is always executed when execution leaves any part of the Try statement.
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
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