I have a few methods which gets called sequentially and they all execute queries into a MySQL database, sequentially:
UpdateInvoice() - > UpdateOrderItems(connection)
|---------- > UpdateGrandTotal(connection)
|---------- > UpdateAdvances(connection)
I have a using block for connection and transaction as follows:
using (var connection = ConnectionManager.GetConnection()){
using (var transaction = connection.BeginTransaction(){
UpdateOrderItems(connection)
UpdateGrandTotal(connection)
UpdateAdvances(connection)
}
}
My question is, once I have created a transaction from connection.BeginTransaction(), do I need to pass the transaction object around to get it to work atomically? According to my knowledge since BeginTransaction() was called upon the connection, it is in transaction mode, and that a single connection can have only one transaction at a time.
Am i getting something wrong?
P.S. I am using dapper to execute queries inside these methods
You can only have 1 transaction per connection (regardless of IsolationLevel if you are wondering). However you can nest multiple transactions by using System.Transactions.TransactionScope.
I hope this shed some light.