Here you learn transaction handling in Ado.net, How to handle transaction in Ado.net Class
In ADO.NET DbTransaction object supports single database transaction as well as distributed transactions.
Single database transaction model is implemented using the .NET managed providers for Transaction and Connection classes, which resides under System.Data namespace.
For Distributed transaction model you need to use classes from System.Transactions namespace.
There are different ways we can implement Transaction using Ado.Net
Single database transaction example using Ado.Net object
Here we are trying to update multiple tables using two different SqlCommand object, and putting them under one transaction, so either all will be successful or all will fail
using System.Data; using System.Data.SqlClient; public void LoadDataDbCommand() { SqlTransaction objTrans = null; using (SqlConnection con = new SqlConnection(Util.ConnectionString)) { con.Open(); objTrans = con.BeginTransaction(); try { SqlCommand _sCommand = con.CreateCommand(); _sCommand.CommandText = "Update tbOrder set discount=30"; _sCommand.CommandType = CommandType.Text; SqlCommand _sCommand2 = con.CreateCommand(); _sCommand2.CommandText = "usp_SpName"; _sCommand2.CommandType = CommandType.StoredProcedure; _sCommand.ExecuteNonQuery(); _sCommand2.ExecuteNonQuery(); objTrans.Commit(); } catch { objTrans.Rollback(); } finally { con.Close(); } } }
Like SQL Transaction here in Ado.Net Transaction has all four ACID properties ( Atomic, Consistent, Isolated, and Durable)
We can setup IsolationLevel while creating a transaction object from connection object, also transaction object can be assigned to each command object, let’s look at the example below, how things can be implemented differently
SqlTransaction objTrans = null; SqlConnection conSQL = new SqlConnection(Util.ConnectionStringSQL); conSQL.Open(); objTrans = conSQL.BeginTransaction(IsolationLevel.ReadCommitted); SqlCommand _sCommand = conSQL.CreateCommand(); _sCommand.CommandText = "Select * from tbOrder"; _sCommand.CommandType = CommandType.Text; _sCommand.Transaction = objTrans; SqlCommand _sCommand2 = conSQL.CreateCommand(); _sCommand2.CommandText = "usp_SpName"; _sCommand2.CommandType = CommandType.StoredProcedure; _sCommand2.Transaction = objTrans; try { _sCommand.ExecuteNonQuery(); _sCommand2.ExecuteNonQuery(); objTrans.Commit(); } catch { objTrans.Rollback(); } finally { conSQL.Close(); }
In case we want to execute transaction against multiple distributed data sources, then Microsoft Distributed Transaction Coordinator (MSDTC) can be used
Distributed Transaction model under System.Transactions Example: In this example you will learn how to implement transaction between multiple databases; we will be updating tables in different databases under single transaction
In this example we will be using TransactionScope and set IsolationLevel from System.Transactions
using System; using System.Transactions; TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = new TimeSpan(0, 4, 0); using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, options)) { transactionScope.Complete(); }
Ado.Net Distributed Transaction using ServicedComponent Example
You also can create a ServicedComponent, then write a method that will update multiple databases, and everything will happen under one transaction.
In all DBTransaction there is a EnlistDistributedTransaction method, which accept all different type of DbTransaction and set them under one Transaction, you need to add reference of System.EnterpriseServices
using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.EnterpriseServices; using System.Transactions; namespace WebTrainingRoom { [Transaction(TransactionOption.RequiresNew)] class DistributedTransactionExample2 : ServicedComponent { public void UpdateCreditCardDetails() { try { SqlConnection con = new SqlConnection(Util.ConnectionStringSQL); con.Open(); con.EnlistDistributedTransaction((ITransaction)ContextUtil.Transaction); SqlCommand _sCommand2 = con.CreateCommand(); _sCommand2.CommandText = "usp_SpName"; _sCommand2.CommandType = CommandType.StoredProcedure; _sCommand2.ExecuteNonQuery(); OleDbConnection conOle = new OleDbConnection(Util.ConnectionStringOleDb); conOle.Open(); conOle.EnlistDistributedTransaction((ITransaction)ContextUtil.Transaction); OleDbCommand _sCommand1 = conOle.CreateCommand(); _sCommand1.CommandText = "usp_SpName_Client"; _sCommand1.CommandType = CommandType.StoredProcedure; _sCommand1.ExecuteNonQuery(); // Commit Transaction ContextUtil.SetComplete(); con.Close(); conOle.Close(); } catch (Exception ex) { throw ex; } } } }
In above example we are updating credit card details in two different databases.
Note: here we have not implemented any log or exception handling, just wanted to explain the distributed transaction part
if you want to see how transaction or exception logging can be done, you can look at this logging in .net example.