Transaction in Ado.net Class C# Example

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

How to use DbTransaction in Ado.Net C#

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)

Setting IsolationLevel in Ado.Net Transaction

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

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.

 
DBTransaction in Ado.Net
Learn MS-SQL Development
Ado.net Interview Questions Answers
Connect SQL database using Ado.Net component from Asp.net application, learn how to use Ado.net as data access layer.
Distributed Transaction in Ado.Net
Ado.Net C# Examples | Join Asp.Net MVC Course