Public Sub RunSqlTransaction(myConnString As String)
Dim myConnection As New SqlConnection(myConnString)
myConnection.Open()
Dim myCommand As New SqlCommand()
Dim myTrans As SqlTransaction
' Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted, "SampleTransaction")
' Must assign both transaction object and connection
' to Command object for a pending local transaction
myCommand.Connection = myConnection
myCommand.Transaction = myTrans
Try
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
myCommand.ExecuteNonQuery()
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"
myCommand.ExecuteNonQuery()
myTrans.Commit()
Console.WriteLine("Both records are written to database.")
Catch e As Exception
myTrans.Rollback("SampleTransaction")
Console.WriteLine(e.ToString())
Console.WriteLine("Neither record was written to database.")
Finally
myConnection.Close()
End Try
End Sub 'RunSqlTransaction