ADO.NET Core Implicit vs Explicit Transactions

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

ADO.NET Core Implicit vs Explicit Transactions

In this article, I will discuss ADO.NET Core Implicit vs Explicit Transactions with Examples. Please read our previous article discussing how to implement different Transaction Isolation Levels using ADO.NET Core.

Implicit vs Explicit Transactions in ADO.NET Core

In ADO.NET Core, handling transactions is crucial for maintaining data integrity during database operations. Transactions ensure that a sequence of operations on a database is executed as a single unit; either all operations succeed, or none do, maintaining the consistency of the database. ADO.NET Core supports both implicit and explicit transactions, which are used based on the specific requirements of the application.

Let us understand this with an example. First, execute the following SQL Script on the SQL Server database to create the EmployeeDB Database, Employee, and Address tables, which we will use in our application using ADO.NET Core:

-- Create Database EmployeeDB
CREATE DATABASE EmployeeDB;
GO

-- Use EmployeeDB Database
Use EmployeeDB;
GO

-- Create Employee Table
CREATE TABLE Employee
(
    EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100),
    Position NVARCHAR(100)
);

-- Create Address Table
CREATE TABLE Address
(
    AddressId INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeId INT,
    Street NVARCHAR(200),
    City NVARCHAR(100),
    State NVARCHAR(100),
    CONSTRAINT FK_Address_Employee FOREIGN KEY (EmployeeId) 
    REFERENCES Employee(EmployeeId) 
    ON DELETE CASCADE
);

Explanation:

• Employee Table: Contains an auto-incrementing EmployeeId as the primary key and Name and Position columns to store the employee’s name and job position, respectively.
• Address Table: Includes an auto-incrementing AddressId as the primary key, EmployeeId to link to the Employee table, and address details like Street, City, and State.
• Foreign Key Constraint (FK_Address_Employee): Establishes a relationship between the Address and Employee tables. This constraint ensures that each address must be associated with an existing employee. The ON DELETE CASCADE option means that if an employee is deleted, their associated addresses will be automatically deleted as well, maintaining referential integrity.

Example to Understand Implicit Transactions in ADO.NET Core

Implicit transactions are automatically managed by the ADO.NET Core. They are simpler to implement as they do not require explicit start, commit, or rollback commands. Implicit transactions are typically used with the using statement in C# that ensures the connection is closed and the transaction is automatically committed or rolled back when the block of code is exited. This approach is beneficial for straightforward operations where the scope of the transaction is clearly defined by the execution path of the code block.

For a better understanding, please modify the Program class as follows, which shows Implicit transactions using ADO.NET Core. In the below example, we’ll use an implicit transaction to insert a new employee and their address into the database. This approach will ensure that both inserts are treated as a single operation that either completely succeeds or fails together, maintaining data integrity without explicit transaction management.

using Microsoft.Data.SqlClient;
using System.Transactions;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

                using (TransactionScope scope = new TransactionScope())
                {
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        connection.Open();

                        // Insert a new employee
                        SqlCommand command1 = new SqlCommand("INSERT INTO Employee (Name, Position) VALUES ('John Doe', 'Software Developer'); SELECT SCOPE_IDENTITY();", connection);
                        var employeeId = (decimal)command1.ExecuteScalar();
                        Console.WriteLine($"Employee Inserted with Employee ID: {employeeId}");

                        // Insert the address for the new employee
                        SqlCommand command2 = new SqlCommand($"INSERT INTO Address (EmployeeId, Street, City, State) VALUES ({employeeId}, '123 Main St', 'Anytown', 'Anystate'); SELECT SCOPE_IDENTITY();", connection);
                        var addressId = (decimal)command2.ExecuteScalar();
                        Console.WriteLine($"Address Inserted with AddressID: {addressId}");
                    }

                    // Complete the transaction
                    scope.Complete();
                    Console.WriteLine("Transaction Completed Successfully");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }
    }
}

In this implicit transaction example, the TransactionScope is used to manage the transaction automatically. If the code executes without throwing exceptions, call scope.Complete() commits the transaction. If an exception occurs and scope.Complete() is not called, the transaction is rolled back automatically when the scope is disposed. When you run the above code, you will get the following output:

Understanding TransactionScope in ADO.NET Core

The concept of TransactionScope in ADO.NET is a powerful feature for managing transactions in your .NET applications, including those developed with .NET Core. TransactionScope allows you to wrap a code block within a transaction without needing to interact directly with the database transaction objects.

When you use TransactionScope, any database operations within the scope are automatically part of a transaction. If all operations succeed, the transaction is committed, meaning all changes are saved to the database. If an operation fails and an exception is thrown, the transaction is rolled back, undoing all changes made within the scope.

• Use TransactionScope: Wrap the database operations you want to be transactional within a TransactionScope using a using statement. This ensures that the scope is correctly disposed of, which includes committing or rolling back the transaction.
• Complete the transaction: If the operations within the scope succeed, call the Complete method on the TransactionScope object. This signals that all operations within the scope are successful, and the transaction should be committed.

Example to Understand Explicit Transactions in ADO.NET Core

Explicit transactions provide more control to the developer over when a transaction starts, commits, or rolls back. This is crucial for more complex scenarios where the decision to commit or roll back a transaction depends on multiple factors or operations spread across different methods or classes. In ADO.NET Core, explicit transactions are initiated by creating a transaction object associated with the connection and then explicitly calling commit or rollback on this object based on the success or failure of the operations within the transaction.

For a better understanding, please modify the Program class as follows, which shows Explicit transactions using ADO.NET Core. In the below example, we’ll perform the same operations but with manual transaction management. This gives us direct control over when the transaction is committed or rolled back.

using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    // Start a new transaction
                    SqlTransaction transaction = connection.BeginTransaction();

                    try
                    {
                        // Insert a new employee
                        SqlCommand command1 = new SqlCommand("INSERT INTO Employee (Name, Position) VALUES ('Jane Doe', 'Project Manager'); SELECT SCOPE_IDENTITY();", connection, transaction);
                        var employeeId = (decimal)command1.ExecuteScalar();
                        Console.WriteLine($"Employee Inserted with AddressID: {employeeId}");

                        // Insert the address for the new employee
                        SqlCommand command2 = new SqlCommand($"INSERT INTO Address (EmployeeId, Street, City, State) VALUES ({employeeId}, '123 Main St', 'Anytown', 'Anystate'); SELECT SCOPE_IDENTITY();", connection, transaction);
                        var addressId = (decimal)command2.ExecuteScalar();
                        Console.WriteLine($"Address Inserted with AddressID: {addressId}");

                        // Commit the transaction
                        transaction.Commit();
                        Console.WriteLine("Transaction Completed Successfully");
                    }
                    catch(Exception ex) 
                    {
                        // If an error occurs, roll back the transaction
                        transaction.Rollback();
                        Console.WriteLine($"Transaction Rollback: Error {ex.Message}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something went wrong: {ex.Message}");
            }
        }
    }
}

When you run the above code, you will get the following output:

Differences Between Implicit vs Explicit Transactions in ADO.NET Core

ADO.NET Core Implicit Transactions

Definition: Implicit transactions are automatically managed by the database system. When you execute a command without explicitly starting a transaction, the database system treats that command as a single transaction. This means that each individual statement is considered a complete transaction on its own.

Usage: Implicit transactions are used when the operations you are performing are simple and do not require multiple steps that must succeed or fail as a unit. For example, updating a single record in a database could be done within an implicit transaction.

Advantages:

• Simplicity: No need to manually manage transaction boundaries.
• Automatic: Each statement is automatically committed if it succeeds or rolled back if it fails.

Disadvantages:

• Limited Control: Since the transaction scope is limited to a single statement, you have less control over transactions that span multiple operations.
• Performance: For operations that involve multiple steps, managing transactions implicitly can lead to performance overhead due to the constant commit/rollback of individual statements.

When to Use:

• Simple Operations: For simple, single-step operations where transactional control is not explicitly required, implicit transactions can be sufficient. For example, inserting a single record into a database table.
• Less Complex Code: When your code does not require the granular control of transaction boundaries, implicit transactions can reduce complexity.
• Quick Prototyping: For quick prototyping or when developing applications that do not require strict transactional integrity, implicit transactions can speed up development.

ADO.NET Core Explicit Transactions

Definition: Explicit transactions are manually managed by the developer. You explicitly define the start and end of a transaction using the transaction management commands (BeginTransaction, Commit, Rollback). This allows multiple operations to be grouped into a single transaction that either completely succeeds or fails as a unit.

Usage: Explicit transactions are used when you have multiple operations that need to be executed as a single unit of work. For example, if you’re transferring money between two accounts, you would want both the debit and credit operations to succeed or fail together to maintain data integrity.

Advantages:

• Control: Provides full control over the transaction scope, allowing you to group multiple operations into a single transaction.
• Consistency: Ensures data integrity by allowing multiple related operations to succeed or fail as a unit.
• Flexibility: You can decide when to commit or rollback the transaction based on the success or failure of the operations within the transaction.

Disadvantages:

• Complexity: Requires more careful management of transaction boundaries.
• Resource Utilization: Holding a transaction open for a long time can lock resources and affect performance.

When to Use:

• Multiple Operations as a Single Unit: When you need to execute multiple operations, they must be treated as a single unit of work. For example, when inserting records into multiple tables where either all inserts succeed or none do, to maintain database integrity.
• Error Handling and Rollback: Explicit transactions are essential when you need precise control over error handling and rollback scenarios. If an error occurs in one part of the transaction, you can roll back the entire transaction to its initial state.
• Performance Considerations: Explicit transactions can be optimized for performance in scenarios where multiple operations need to be batched together. This reduces the overhead of transaction management for each operation.
• Cross-Database Transactions: For transactions that span multiple databases, explicit transactions are necessary to ensure that all operations across the databases either complete successfully or are all rolled back.

Choosing Between Implicit and Explicit Transactions

The choice between using implicit or explicit transactions depends on the complexity and requirements of the operations you are performing:

• Use implicit transactions for simple operations where the scope of the transaction is limited and straightforward, making code simpler and more readable. That is single-step operations where automatic commit/rollback behavior is sufficient.
• Use explicit transactions for complex operations that involve multiple steps or require atomicity, ensuring that all operations within the transaction block either complete successfully together or fail together without affecting the database state.

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