ADO.NET Distributed Transactions

ADO.NET Distributed Transactions

In this article, I am going to discuss ADO.NET Distributed Transactions in C# with one Real-Time Example. Please read our previous article, where we discussed Transaction in C# using ADO.NET with Examples.

What is a Transaction in C#?

A Transaction is a set of operations (multiple DML Operations) that ensures either all the database operations succeed or all of them failed to ensure data consistency. This means the job is never half done, either all of it is done or nothing is done. So, a transaction is a unit of work and the transactions ensure the consistency and integrity of a database.

The ADO.NET supports both single database transactions as well as distributed transactions. In C#, the Single Database Transaction is implemented using BeginTransaction which belongs to System.Data namespace and the Distributed Transaction implemented using TransactionScope which belongs to System.Transactions namespace.

Note: In our previous article, we discussed how to implement Single Database Transactions in C# and ADO.NET, and in this article, we are going to discuss how to implement Distributed Transactions with one real-time example.

What are Distributed Transactions in C#?

A distributed transaction is a transaction that uses different data sources or in simple words, we can say that the transaction going to perform DML Operations on more than one database. Distributed transactions allow us to perform several operations on different systems as a single unit i.e. either succeeded or failed completely. If this is not clear at the moment don’t worry, we will try to understand this concept with one real-time example.

Example to Understand Distributed Transactions in C# using ADO.NET:

Let us understand Distributed Transactions with one real-time example. Let us say, we are having two databases i.e. AXISBankDB and ICICIBankDB. These two databases might be on the same server or might be on a different server. As you can see in the below image, the structure of both databases is the same, but if you want you can change the schema. The Accounts table is going to hold the available balance of the customer and whenever the customer deposit, withdraw or transfer any money, that information we are stored in the TransactionDetails table.

Example to Understand Distributed Transactions in C# using ADO.NET

Please use the following SQL Script to create the AXISBankDB and ICICIBankDB databases with the required data. Here, we are using SQL Server and in the same server, I am creating both databases.

CREATE DATABASE AXISBankDB;
GO

USE AXISBankDB;
GO

CREATE TABLE Accounts
(
     AccountNumber INT PRIMARY KEY,
     CustomerName VARCHAR(60),
     Balance INT
);
GO
INSERT INTO Accounts VALUES(1001, 'James', 5000);
INSERT INTO Accounts VALUES(1002, 'Smith', 6000);
GO

CREATE TABLE TransactionDetails
(
     TransactionDetailsID INT PRIMARY KEY IDENTITY(1, 1),
     AccountNumber INT FOREIGN KEY REFERENCES Accounts(AccountNumber),
     Amount INT,
  TransactionDetails VARCHAR(500),
  TransactionType VARCHAR(5),
  CreatedDate Date DEFAULT GETDATE()
);
GO

INSERT INTO TransactionDetails VALUES(1001, 7000, 'Deposited', 'CR', GETDATE());
INSERT INTO TransactionDetails VALUES(1001, 2000, 'Withdraw', 'DR', GETDATE());
INSERT INTO TransactionDetails VALUES(1002, 9000, 'Deposited', 'CR', GETDATE());
INSERT INTO TransactionDetails VALUES(1002, 3000, 'Withdraw', 'DR', GETDATE());
GO

CREATE DATABASE ICICIBankDB;
GO

USE ICICIBankDB;
GO

CREATE TABLE Accounts
(
     AccountNumber INT PRIMARY KEY,
     CustomerName VARCHAR(60),
     Balance INT
);
GO
INSERT INTO Accounts VALUES(50001, 'Sara', 15000);
INSERT INTO Accounts VALUES(50002, 'Pam', 16000);
GO

CREATE TABLE TransactionDetails
(
     TransactionDetailsID INT PRIMARY KEY IDENTITY(1, 1),
     AccountNumber INT FOREIGN KEY REFERENCES Accounts(AccountNumber),
     Amount INT,
  TransactionDetails VARCHAR(500),
  TransactionType VARCHAR(5),
  CreatedDate DATE DEFAULT GETDATE()
);
GO

INSERT INTO TransactionDetails VALUES(50001, 20000, 'Deposited', 'CR', GETDATE());
INSERT INTO TransactionDetails VALUES(50001, 5000, 'Withdraw', 'DR', GETDATE());
INSERT INTO TransactionDetails VALUES(50002, 26000,'Deposited', 'CR', GETDATE());
INSERT INTO TransactionDetails VALUES(50002, 10000, 'Withdraw', 'DR', GETDATE());
GO

Now, we want to implement the Money Transfer functionality. What is our requirement is, we want to transfer the money from the customer account which is in AXIS Bank and he wants to transfer the amount to an account which is in ICICI Bank. As we know, transferring money is not a single operation. First, we need to deduct the money from the sender account, then store the information in the TransactionDetails table. Then again, we need to add the amount to the receiver’s account as well as we need to store the transaction information in the TransactionDetails table.

The most important point is that, if all the operations are successfully completed, then the transaction is successful, and if any of the execution failed, then the transaction is failed. But here, we have to implement the transaction between two different databases which is nothing but a distributed transaction. Now, let us proceed and try to understand how to implement distributed transactions in C#.

How to Implement Distributed Transaction in C# and ADO.NET?

The System.Transactions introduce in .NET Framework 2.0 allows us to implement distributed transactions i.e. transaction across different data sources. The System.Transactions namespace provides the TransactionScope class, using which we can create and manage distributed transactions in C#.

In order to create and use distributed transactions, first of all, we need to create an instance of the TransactionScope class. It is also possible to open multiple database connections within the same transaction scope. The transaction scope will decide whether to create a local transaction or a distributed transaction. The transaction scope automatically converts a local transaction to a distributed transaction if required.

If you open a single connection then it will be a single transaction and when you open subsequent connections in the transaction scope, the transaction scope promotes the transaction to a distributed transaction.

Steps of Creating Distributed Transaction using ADO.NET:

We need to follow the following five steps to implement distributed transactions in C#.

  1. Create an Instance of TransactionScope class.
  2. Open the connection.
  3. Perform the DML (INSERT, UPDATE, and DELETE) operations as per your business requirements.
  4. If all your DML (INSERT, UPDATE, and DELETE) operations are completed successfully, then mark the transaction as completed.
  5. Dispose the TransactionScope object.

Note: If all the DML (INSERT, UPDATE, and DELETE) operations succeeded in a transaction scope, then call the Complete method on the TransactionScope object to indicate that the transaction was completed successfully.

How to terminate a Distributed Transaction in C#?

To terminate a distributed transaction in C#, we just need to call the Dispose method on the TransactionScope object. When we call the Dispose Method on the TransactionScope object, then the transaction is either committed or rolled back, depending on whether you called the Complete method or not:

  1. If you called the Complete method on the TransactionScope object before its disposal, the transaction manager commits the transaction.
  2. If you did not call the Complete method on the TransactionScope object before its disposal, the transaction manager rolls back the transaction.
What is TransactionScope in C#?

A transaction scope defines a block of code that participates in a transaction. If the code block is completed successfully, then the transaction manager commits the transaction. Otherwise, the transaction manager rolls back the transaction. To use TransactionScope, you need to add a reference to the System.Transactions assembly and then you need to import the System.Transactions namespace into your application.

Example to Understand ADO.NET Distributed Transaction in C#:

The following example is self-explained, so please go through the comment lines.

using System;
using System.Data.SqlClient;
//First Add Reference to System.Transactions DLL and then import the same
using System.Transactions;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            //Connection String Pointing to AXISBankDB
            string connStringAXIXBank = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=AXISBankDB; integrated security=TRUE";

            //Connection String Pointing to ICICIBankDB
            string connStringICICIBank = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=ICICIBankDB; integrated security=TRUE";

            //For Distributaed Transaction, we need to create an instance of TransactionScope
            //Here, we are using the using block which will dispose the transactionScope object automatically
            using (TransactionScope transactionScope = new TransactionScope())
            {
                //We need to Deduct Money from the accounts table of a user of AXIXBankDB
                //So, first connecttion object pointing to AXIXBankDB
                using (SqlConnection connectionAxisbank = new SqlConnection(connStringAXIXBank))
                {
                    //Create the command object
                    using (SqlCommand cmdAxisbank = new SqlCommand())
                    {
                        //Point the command object to execute the command in the AXIXBankDB
                        cmdAxisbank.Connection = connectionAxisbank;
                        connectionAxisbank.Open();

                        //First Update the Balance in AXIXBankDB 
                        cmdAxisbank.CommandText = "UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountNumber = 1001";
                        int rowsAffectedA = cmdAxisbank.ExecuteNonQuery();

                        //Then make an entry into the TransactionDetails table in AXIXBankDB
                        cmdAxisbank.CommandText = "INSERT INTO TransactionDetails (AccountNumber, Amount, TransactionDetails, TransactionType) VALUES(1001, 1000, 'Transafer' , 'DR')";
                        int rowsAffectedB = cmdAxisbank.ExecuteNonQuery();

                        //If First two DML Operations are succeded, then only go inside and do the rest operations
                        if (rowsAffectedA > 0 && rowsAffectedB > 0)
                        {
                            Console.WriteLine("1000 deducted from Account Number: 1001 from Axis Bank");
                            //The second connection pointing to ICICIBankDB where we are going to perform the rest operations
                            using (SqlConnection connectionICICIBank = new SqlConnection(connStringICICIBank))
                            {
                                using (SqlCommand cmdICICIBank = new SqlCommand())
                                {
                                    //Point the command object to execute the command in the ICICIBankDB
                                    cmdICICIBank.Connection = connectionICICIBank;
                                    connectionICICIBank.Open();
                                    //First Update the Balance in ICICIBankDB
                                    cmdICICIBank.CommandText = "UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountNumber = 50001";
                                    int rowsAffectedC = cmdICICIBank.ExecuteNonQuery();

                                    //Then make an entry into the TransactionDetails table of ICICIBankDB
                                    cmdICICIBank.CommandText = "INSERT INTO TransactionDetails (AccountNumber, Amount, TransactionDetails, TransactionType) VALUES(50001, 1000, 'Transafer', 'CR')";
                                    int rowsAffectedD = cmdICICIBank.ExecuteNonQuery();

                                    //If the above two DML operations are succeded, then call the Complete
                                    if (rowsAffectedC > 0 && rowsAffectedD > 0)
                                    {
                                        //The Complete() mark the transaction as completed successfully
                                        transactionScope.Complete();
                                        Console.WriteLine("1000 Deposited to Account Number: 50001 to ICICI Bank");
                                        Console.WriteLine("Transaction Completed");
                                    }
                                    else
                                    {
                                        Console.WriteLine("Transaction Failed..");
                                    }
                                } // Dispose the cmdICICIBank command object.

                            } // Dispose the connectionICICIBank connection.
                        }
                        else
                        {
                            Console.WriteLine("Transaction Failed..");
                        }
                    } // Dispose the cmdAxisbank command object.

                } // Dispose connectionAxisbank connection.

            } // Dispose TransactionScope object, to commit or rollback transaction.

            Console.ReadKey();
        }
    }
}
Output:

Example to Understand ADO.NET Distributed Transaction in C#

Now, you can verify the database tables in both databases and you will see the data as expected. Now, if any of the DML operations failed in the transaction scope, then, in that case, the Complete method will not call and the transaction will be rolled back. This is how you can implement Distributed Transactions in C# using ADO.NET.

In the next article, I am going to discuss ADO.NET Connection Pooling with Examples. Here, in this article, I try to explain ADO.NET Distributed Transactions and I hope you enjoy this Distributed Transaction in C# article.

Leave a Reply

Your email address will not be published. Required fields are marked *