ADO - Distributed Transactions

In C#, a transaction is a sequence of operations that are performed as a single unit of work. It ensures that either all the operations within the transaction are successfully completed, or none of them are applied. Transactions provide atomicity, consistency, isolation, and durability (ACID) properties to maintain data integrity.

What are Distributed Transactions in C#?

Distributed transactions involve coordinating and managing multiple transactions across multiple data sources or systems. They are used when a transaction involves updates or operations that span multiple databases, services, or resources. Distributed transactions ensure that all participating resources are properly synchronized, and the entire transaction is either committed or rolled back as a whole.

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

using (TransactionScope scope = new TransactionScope())
{
    try
    {
        // Perform database operations using ADO.NET
        // Commit the transaction
        scope.Complete();
    }
    catch (Exception ex)
    {
        // Handle exceptions and rollback the transaction
    }
}

In the example above, a TransactionScope is used to define a distributed transaction. Within the scope, you can perform multiple database operations using ADO.NET, such as inserting, updating, or deleting records. If an exception occurs, the transaction will be rolled back. Otherwise, when the Complete() method is called, the transaction will be committed.

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

• To implement a distributed transaction in C# using ADO.NET, you can follow these steps:
• Create a new instance of the TransactionScope class.
• Perform your database operations within the scope.
• If an exception occurs, the transaction will be rolled back automatically.
• If no exception occurs, call the Complete() method to commit the transaction.

Steps of Creating Distributed Transaction using ADO.NET:

• Include the System.Transactions namespace in your code file.
• Create a new instance of the TransactionScope class to define the distributed transaction.
• Perform your database operations within the transaction scope.
• If an exception occurs, the transaction will be rolled back automatically when exiting the scope.
• If no exception occurs, call the Complete() method to commit the transaction.

How to terminate a Distributed Transaction in C#?

A distributed transaction can be terminated by either calling the Complete() method inside the TransactionScope to commit the transaction, or by simply exiting the scope without calling Complete(), which will automatically roll back the transaction.

What is TransactionScope in C#?

TransactionScope is a class provided by the System.Transactions namespace in C#. It represents a scope for a distributed transaction. It allows you to define the boundaries of a transaction and control its behavior. The TransactionScope class handles the enlistment of resources and provides an intuitive way to work with distributed transactions.

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

using (TransactionScope scope = new TransactionScope())
{
    using (SqlConnection connection1 = new SqlConnection(connectionString1))
    using (SqlConnection connection2 = new SqlConnection(connectionString2))
    {
        connection1.Open();
        connection2.Open();
        // Perform database operations using connection1 and connection2
        // Commit the transaction
        scope.Complete();
    }
}

In this example, a distributed transaction is created using the TransactionScope class. Inside the scope, two SQL connections (connection1 and connection2) are opened, and you can perform database operations on both connections. If the code executes successfully and the Complete() method is called, the changes made within the transaction will be committed to both databases. If an exception occurs or the Complete() method is not called, the changes will be rolled back.

Note: Ensure that the participating databases or resources support distributed transactions, such as Microsoft SQL Server with the appropriate configuration.

using System;
using System.Data.SqlClient;
using System.Transactions;
class Program
{
    static void Main()
    {
        string connectionString1 = "Data Source=Server1;Initial Catalog=Database1;Integrated Security=True";
        string connectionString2 = "Data Source=Server2;Initial Catalog=Database2;Integrated Security=True";
        using (TransactionScope scope = new TransactionScope())
        {
            try
            {
                // Database operation 1
                using (SqlConnection connection1 = new SqlConnection(connectionString1))
                {
                    connection1.Open();
                    
                    SqlCommand command1 = new SqlCommand("INSERT INTO Employees (Name, Department) VALUES ('John Doe', 'Sales')", connection1);
                    command1.ExecuteNonQuery();
                }
                // Database operation 2
                using (SqlConnection connection2 = new SqlConnection(connectionString2))
                {
                    connection2.Open();
                    
                    SqlCommand command2 = new SqlCommand("INSERT INTO Orders (Product, Quantity) VALUES ('Product A', 10)", connection2);
                    command2.ExecuteNonQuery();
                }
                // Commit the transaction
                scope.Complete();
                Console.WriteLine("Transaction committed successfully.");
            }
            catch (Exception ex)
            {
                // Handle exceptions and rollback the transaction
                Console.WriteLine("Error occurred. Transaction rolled back.");
            }
        }
    }
}

Explanation:

The example assumes that there are two databases, "Database1" on "Server1" and "Database2" on "Server2". Replace the connectionString1 and connectionString2 variables with your own connection strings.

Inside the using (TransactionScope scope) block, we define the distributed transaction scope. All the database operations within this scope will participate in the same transaction.

In the example, two separate database operations are performed, each using its own SqlConnection object and connection string. These operations can be performed on different databases or even different database servers.

The first database operation inserts a record into the "Employees" table in "Database1".

The second database operation inserts a record into the "Orders" table in "Database2".

If both database operations complete successfully without any exceptions, the Complete() method is called to commit the transaction. Otherwise, if an exception occurs or the Complete() method is not called, the transaction will be rolled back automatically.

After the transaction is completed or rolled back, a message is displayed indicating the outcome.

By using the TransactionScope class and performing database operations within its scope, you can ensure that multiple database operations are treated as a single distributed transaction. This allows you to maintain data integrity and consistency across multiple databases or resources.