ADO.NET Core Distributed Transactions

Back to: ADO.NET Core Tutorial For Beginners and Professionals

ADO.NET Core Distributed Transactions

In this article, I will discuss how to implement ADO.NET core distributed transactions that involve multiple databases with examples. Please read our previous article discussing ADO.NET Core Implicit vs Explicit Transactions.

What is a Transaction in ADO.NET Core?

A Transaction in ADO.NET Core, similar to other database management systems transactions, is a sequence of operations performed as a single logical unit of work. A transaction must be completed in its entirety or not executed at all, ensuring data integrity and consistency. This is crucial for maintaining the accuracy and reliability of data within a database, especially in environments where multiple users or applications may interact with the database concurrently.

In the context of ADO.NET Core, which is part of the broader .NET ecosystem designed to provide access to data sources like SQL Server, Oracle, MySQL, and others, transactions are used to manage database operations in a way that either fully completes all operations or rolls back all changes in the event of an error or failure. This ensures that the database remains consistent.

What are Distributed Transactions in ADO.NET Core?

Distributed Transactions in ADO.NET Core refer to a method of managing transactions across multiple databases or systems, ensuring that all parts of the transaction are completed successfully or that all are rolled back in case of failure. This concept is crucial for maintaining data integrity across different systems in an application that interacts with multiple databases or services that need to be updated in a consistent manner.

In the context of ADO.NET Core, a Distributed Transaction typically involves coordinating transactions across different database connections or different resource managers, like SQL Server, Oracle, file systems, or services. The coordination is often managed by a transaction coordinator, with Microsoft Distributed Transaction Coordinator (MSDTC) being a common example in Windows environments.

Example to Understand Distributed Transactions using ADO.NET Core:

Let us understand Distributed Transactions with one real-time example. Let us say we have two databases, i.e., AXISBankDB and ICICIBankDB. As you can see in the image below, the structure of both databases is the same, but if you want, you can change the schema. The Accounts table will hold the customer’s available balance, and whenever the customer deposits, withdraws or transfers any money, that information is stored in the transaction details table.

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

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', '2022-11-23');
INSERT INTO TransactionDetails VALUES(1001, 2000, 'Withdraw', 'DR', '2022-11-23');
INSERT INTO TransactionDetails VALUES(1002, 9000, 'Deposited', 'CR', '2022-11-23');
INSERT INTO TransactionDetails VALUES(1002, 3000, 'Withdraw', 'DR', '2022-11-23');
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', '2022-11-23');
INSERT INTO TransactionDetails VALUES(50001, 5000, 'Withdraw', 'DR', '2022-11-23');
INSERT INTO TransactionDetails VALUES(50002, 26000,'Deposited', 'CR', '2022-11-23');
INSERT INTO TransactionDetails VALUES(50002, 10000, 'Withdraw', 'DR', '2022-11-23');
GO

Now, we want to implement the Money Transfer functionality. For example, one customer from AXIS Bank wants to transfer money to an account at ICICI Bank. As we know, transferring money is not a single operation. First, we need to deduct the money from the sender’s account and then store the information in the Transaction Details table. Then again, we need to add the amount to the receiver’s account and 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 operations fail, then the transaction fails. But here, we have to implement the transaction between two different databases, which is nothing but a distributed transaction. Now, let us proceed and see how to implement distributed transactions in ADO.NET Core.

How Do We Implement Distributed Transactions in ADO.NET Core?

To implement distributed transactions in ADO.NET Core, you might use the System.Transactions namespace, which provides classes and interfaces to support transactions, including distributed ones. Here’s a brief overview of how you might work with distributed transactions in ADO.NET Core:

• TransactionScope: This class defines a block of code that participates in a transaction. When you work within a TransactionScope, if any part of the transaction fails, the entire transaction can be rolled back to maintain data consistency.
• Committing Transactions: When all operations within the TransactionScope are completed successfully, the transaction is committed, meaning all changes are permanently applied.
• Rolling Back Transactions: If an operation within the TransactionScope fails, the transaction can be rolled back, undoing all changes made during the transaction.
• Distributed Transaction Coordinators: When a transaction spans multiple resource managers, a distributed transaction coordinator, like MSDTC, manages the commit or rollback operations across all involved resources to ensure atomicity.

It’s important to note that while distributed transactions are powerful, they also come with overhead and complexity. They can impact performance due to the coordination and communication required between different systems.

What is TransactionScope in ADO.NET Core?

TransactionScope in ADO.NET Core is a powerful feature used to manage transactions in your .NET applications, mainly when working with database operations. It provides a simple yet effective way to ensure that a series of operations, either all succeed or all fail, which is fundamental for maintaining data integrity. Here’s a brief overview of how TransactionScope works:

• Scope Creation: When you create a TransactionScope instance, you’re defining a block of code that should be executed within a transaction. This doesn’t start a database transaction immediately but prepares the environment for transactional execution.
• Automatic Transaction Management: As you perform database operations within the TransactionScope, it automatically manages the transaction for you. If you’re working with a single database connection, it enlists that connection in the transaction. If you open multiple connections to the same database or different databases, TransactionScope can coordinate the transactions across these connections using a transaction manager.
• Committing or Rolling Back: At the end of the TransactionScope block, you must explicitly call Complete() to commit the transaction, indicating that all operations within the scope were successful. If Complete() is not called (for example, if an exception occurs), the transaction is automatically rolled back when the TransactionScope is disposed. This rollback undoes all operations performed within the scope, ensuring that your data remains consistent.
• Distributed Transactions: For operations that span multiple databases or even different types of data stores, TransactionScope can automatically escalate to a distributed transaction, using a transaction manager like Microsoft Distributed Transaction Coordinator (MSDTC) to ensure atomicity across all involved resources. You can enable Distributed Transactions using TransactionManager.ImplicitDistributedTransactions = true;

Here’s a simple example to illustrate its usage:

TransactionManager.ImplicitDistributedTransactions = true;
using (TransactionScope scope = new TransactionScope())
{
    // Perform database operations here
    
    // If everything was successful
    scope.Complete();
}
// If scope.Complete() was not called, the transaction is rolled back.

Example to Understand ADO.NET Core Distributed Transaction:

Please modify the Program class as follows. The following example is self-explained, so please go through the comment lines for a better understanding.

using Microsoft.Data.SqlClient;
using System.Transactions;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //Connection String Pointing to AXISBankDB
            string connStringAXIXBank = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=AXISBankDB;Trusted_Connection=True;TrustServerCertificate=True;";

            //Connection String Pointing to ICICIBankDB
            string connStringICICIBank = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=ICICIBankDB;Trusted_Connection=True;TrustServerCertificate=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

            // Enable implicit distributed transactions
            TransactionManager.ImplicitDistributedTransactions = true;

            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.
            Console.ReadKey();
        }
    }
}

Output:

Now, you can verify the database tables in both databases and see the data as expected. Now, if any of the DML operations fail 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 using ADO.NET Core.

When Should We Use Distributed Transactions in ADO.NET Core?

Distributed transactions in ADO.NET Core are used when your application needs to perform operations that span multiple transactional resources and ensure that these operations either all succeed or fail. This means if any part of the transaction fails, the entire set of operations is rolled back to maintain data integrity across disparate systems or resources. Here are some specific scenarios when to use distributed transactions:

• Multiple Databases: When you need to update data across multiple databases in a single transaction. For example, if your application needs to update customer information in one database and their order details in another, a distributed transaction ensures that both updates are completed successfully or none at all.
• Different Types of Data Stores: When your transaction involves different types of data stores, such as a SQL database and a NoSQL database or a file system and a database. Distributed transactions can coordinate these diverse resources.
• Inter-Service Communication: In microservices architectures, when an operation spans multiple services that each manage their own data, distributed transactions can ensure data consistency across these services. This is particularly useful in scenarios where compensating transactions are not feasible or would be too complex to implement.
• Integrating Legacy Systems: When integrating with legacy systems that might use different transaction management systems. Distributed transactions can help unify these systems under a single transaction umbrella.

In the next article, I will discuss ADO.NET Core SqlBulkCopy with Examples. In this article, I explain ADO.NET Core Distributed Transaction with Examples. I would like to have your feedback. Please post your feedback, questions, or comments about this ADO.NET Core Distributed Transaction article.