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

ADO.NET Core Transactions with Examples

In this article, I will discuss Transactions in ADO.NET Core with Examples. Please read our previous article discussing How to Perform CRUD Operations using ADO.NET Core with Stored Procedures.

What is a Transaction in a Database?

A transaction in a database is a sequence of one or more SQL operations (such as INSERT, UPDATE, DELETE) that are executed as a single unit of work. These operations can be anything from inserting, updating, or deleting records. Transactions ensure data integrity and consistency by making sure either all operations within a transaction are completed successfully (commit) or none of them are applied (rollback). This ensures that all the changes succeed together or none at all, preventing partial updates that can leave the database in an inconsistent state.

For example, if an e-commerce platform processes a customer’s order, the system needs to:

• Deduct the quantity from the inventory.
• Update the customer’s order history.
• Create a record for shipping details.

Although these are three different steps at the database level, they must be treated logically as one atomic operation. If any of these steps fails (e.g., inventory update fails), the entire transaction is rolled back to maintain consistency. This atomic behaviour ensures data reliability and integrity.

ACID Properties of a Transaction

Transactions are governed by the ACID properties: Atomicity, Consistency, Isolation, and Durability.

Atomicity:

• All operations within a transaction either complete entirely or not at all. If any part of the transaction fails, the entire transaction is rolled back, and the database is returned to its state before the transaction begins.
• For example, imagine transferring money between bank accounts. If debiting the sender’s account succeeds but crediting the receiver’s account fails, the whole transaction is canceled so that no money is lost or duplicated.

Consistency:

• A transaction transforms the database from one valid state to another. If the transaction fails, the database remains unchanged and consistent. All business rules or constraints are preserved once the transaction completes.
• For example, when transferring money from one bank account to another, consistency ensures that the amount deducted from the sender’s account matches the amount credited to the receiver’s account. If something goes wrong, no money is moved, keeping balances accurate.

Isolation:

• Transactions are independent of each other. Changes made by one transaction are invisible to others until they are completed. So, they work in isolation until the transaction is completed.
• For example, if you and someone else are simultaneously transferring funds from the same account, each of your transactions should only see the final, consistent state once the other person’s transaction is committed. You should not see partial updates made by the other person’s transaction.

Durability:

• Once a transaction is committed, the changes are permanent, even in the event of a system crash.
• For example, once you receive a message from the ATM saying Transaction Successful, even if a power failure occurs a moment later or if the bank’s server crashes, the balances remain correct.

Why Do We Need Database Transactions?

Let us understand why we need a Transaction with an example. Consider a money transfer between two bank accounts (Account A and Account B). The transaction involves at least two operations:

• Operation 1: Debit (withdraw) money from Account A.
• Operation 2: Credit (deposit) money into Account B.

Without Database Transactions

Scenario: Operation 1 succeeds (money is deducted), but Operation 2 fails (money is not credited) due to an error or system crash.

Result: The money leaves Account A and is essentially lost because it never shows up in Account B. Data inconsistency arises, leading to serious accounting errors and customer trust issues.

With Database Transactions

Scenario: Both operations (debit and credit) are wrapped in a single transaction.

Result:

• If both steps are completed successfully, the transaction is committed, and the changes become permanent.
• If one operation fails (e.g., credit operation), the transaction is rolled back, ensuring that Account A’s funds are restored to their original state.

So, to maintain data integrity and consistency in a database, we should use transactions.

What is ADO.NET Core Transaction?

ADO.NET Core Transactions provide a programmatic way to manage transactions when interacting with a relational database from a .NET Core application. We need transactions in ADO.NET Core when multiple database operations must succeed or fail as a group for data integrity. They ensure that either all operations are completed successfully or none of them are completed.

This is especially useful in real-time applications where data consistency and integrity are mandatory, such as banking systems, e-commerce order processing, or any system dealing with financial transactions.

Why We Need Transactions in ADO.NET Core?

We need Transactions in ADO.NET Core for the following reasons:

• Ensuring Data Integrity and Consistency: If an operation like order placement involves multiple database updates (e.g., inventory, order, payment records), a transaction ensures that these updates are applied together or not at all.
• Preventing Partial Updates: Without transactions, failures in one step can lead to partial data updates, causing inconsistencies that are difficult to detect and resolve.
• Easing Recovery: Transactions provide a mechanism for automatically rolling back changes in case of errors, simplifying error handling and ensuring the database remains in a valid state.
• Complex Operations: Crucial when a business operation involves multiple steps that must all be completed successfully.

How Do We Implement Transactions in ADO.NET Core?

To Implement Transactions in ADO.NET Core, we need to follow the below steps:

• Create and Open a Connection: Create a connection to your SQL Server database using SqlConnection.
• Begin a Transaction: Start the transaction by calling BeginTransaction on the connection object.
• Associate Commands with the Transaction: When creating SQL commands (using SqlCommand), assign the transaction object to each command’s Transaction property.
• Execute Database Operations: Perform your SQL operations (insert/update/delete) within the transaction scope.
• Commit or Roll Back: Use Commit if all operations complete successfully; otherwise, if any error occurs, call Rollback to undo all changes.
• Dispose of the Transaction and Connection Object: Once the operation is completed, properly dispose of connection and transaction objects using the using statement.

Syntax:

The syntax to use a transaction in ADO.NET Core is given below:

Understanding the Flow:

The above snippet demonstrates the basic structure of implementing a transaction in ADO.NET Core.

• The connection is opened, and the transaction is started.
• Commands are executed within the transaction context.
• In case of an error, the transaction will roll back, ensuring no partial updates persist.

Database Setup in SQL Server

The following SQL script creates a SQL Server database named OrderManagementDB with tables typical of an order processing system with five tables: Customers, Products, Orders, OrderItems, and Payments. The tables include proper constraints, relationships, and sample data for Customers and Products. The tables Orders, OrderItems, and Payments are designed to be populated via application transactions.

-- Create OrderManagementDB Database
CREATE DATABASE OrderManagementDB;
GO

-- Switch to OrderManagementDB database
USE OrderManagementDB;
GO

-- Create Customers table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL UNIQUE,
    Phone VARCHAR(20),
    CreatedDate DATETIME DEFAULT GETDATE()
);
GO

-- Create Products table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) NOT NULL,
    Stock INT NOT NULL,
    CreatedDate DATETIME DEFAULT GETDATE()
);
GO

-- Create Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    CustomerID INT NOT NULL,
    OrderDate DATETIME DEFAULT GETDATE(),
    TotalAmount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
GO

-- Create OrderItems table
CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY IDENTITY(1,1),
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10,2) NOT NULL,
 FOREIGN KEY (OrderId) REFERENCES Orders(OrderId),
    FOREIGN KEY (ProductId) REFERENCES Products(ProductId)
);
GO

-- Create Payments table
CREATE TABLE Payments (
    PaymentID INT PRIMARY KEY IDENTITY(1,1),
    OrderID INT NOT NULL,
    PaymentDate DATETIME DEFAULT GETDATE(),
    Amount DECIMAL(10,2) NOT NULL,
    PaymentMethod VARCHAR(50),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
GO

-- Insert sample data for Customers
INSERT INTO Customers (FirstName, LastName, Email, Phone)
VALUES 
('John', 'Doe', 'john.doe@example.com', '1234567890'),
('Jane', 'Smith', 'jane.smith@example.com', '0987654321');
GO

-- Insert sample data for Products
INSERT INTO Products (ProductName, Price, Stock)
VALUES 
('Laptop', 999.99, 10),
('Smartphone', 499.99, 20),
('Headphones', 89.99, 50);
GO

.NET Core Console Application: Order Booking Operation

We will develop a .NET Core Console Application to demonstrate how to implement an order booking operation using ADO.NET Core transactions. The application simulates the following steps:

• Create an order for a customer.
• Deduct product quantities from the stock.
• Insert records into Orders, OrderItems, and Payments tables.

We will wrap all operations inside a transaction to ensure that either all operations succeed (data consistency) or all fail (data remains unchanged in case of any error). So, we need to use ADO.NET Core transactions to ensure that the entire operation rolls back if any step fails, maintaining data integrity and consistency.

The following console application simulates an order booking process: an order is inserted into the Orders table, corresponding items are inserted into the OrderItems table, and a payment is recorded in the Payments table. The application then updates the stock quantity. The entire operation is executed within an ADO.NET Core transaction to ensure data consistency. The following example code is self-explained, so please read the comment lines for a better understanding.

using Microsoft.Data.SqlClient;

namespace OrderBookingApp
{
    public class Program
    {
        // Connection string to your SQL Server OrderManagementDB database
        public static string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderManagementDB;Trusted_Connection=True;TrustServerCertificate=True;";

        // Simulate either success or failure scenario
        // Change to 'true' to test rollback scenario.
        public static bool simulateFailure = false;

        static void Main(string[] args)
        {
            Console.WriteLine("Starting Order Booking Operation...");

            // Wrap entire booking operation in a try-catch to manage any failures
            try
            {
                // Call the method to book an order
                BookOrder();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Order booking failed: " + ex.Message);
            }

            Console.ReadKey();
        }

        // This method simulates the order booking process within a transaction.
        // It demonstrates the atomicity of ADO.NET Core transactions.
        static void BookOrder()
        {
            // Define the order details (normally these would come from user input or business logic)
            int customerId = 1;
            int productId = 1;
            int orderQuantity = 2;
            decimal unitPrice = 999.99m;
            decimal totalAmount = unitPrice * orderQuantity;
            decimal paymentAmount = totalAmount;

            // Establish connection to the database
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Begin the transaction
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 1. Insert into Orders table and obtain the generated OrderID
                        int orderId;
                        string InsertOrdersQuery = "INSERT INTO Orders (CustomerID, TotalAmount) OUTPUT INSERTED.OrderID VALUES (@CustomerID, @TotalAmount)";
                        using (SqlCommand cmd = new SqlCommand(InsertOrdersQuery, connection, transaction))
                        {
                            cmd.Parameters.AddWithValue("@CustomerID", customerId);
                            cmd.Parameters.AddWithValue("@TotalAmount", totalAmount);
                            orderId = (int)cmd.ExecuteScalar(); // Capture the OrderID for use in OrderItems and Payments
                            Console.WriteLine("Order created successfully with OrderID: " + orderId);
                        }

                        // 2. Insert into OrderItems table
                        string InsertOrderItemsQuery = "INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice) VALUES (@OrderID, @ProductID, @Quantity, @UnitPrice)";
                        using (SqlCommand cmd = new SqlCommand(InsertOrderItemsQuery, connection, transaction))
                        {
                            cmd.Parameters.AddWithValue("@OrderID", orderId);
                            cmd.Parameters.AddWithValue("@ProductID", productId);
                            cmd.Parameters.AddWithValue("@Quantity", orderQuantity);
                            cmd.Parameters.AddWithValue("@UnitPrice", unitPrice);
                            cmd.ExecuteNonQuery();
                            Console.WriteLine("Order item inserted successfully.");
                        }

                        // 3. Insert into Payments table
                        string InsertPaymentsQuery = "INSERT INTO Payments (OrderID, Amount, PaymentMethod) VALUES (@OrderID, @Amount, @PaymentMethod)";
                        using (SqlCommand cmd = new SqlCommand(InsertPaymentsQuery, connection, transaction))
                        {
                            cmd.Parameters.AddWithValue("@OrderID", orderId);
                            cmd.Parameters.AddWithValue("@Amount", paymentAmount);
                            cmd.Parameters.AddWithValue("@PaymentMethod", "CreditCard");
                            cmd.ExecuteNonQuery();
                            Console.WriteLine("Payment recorded successfully.");
                        }

                        // 4. Update Product Stock (simulate stock deduction)
                        string UpdateProductsQuery = "UPDATE Products SET Stock = Stock - @OrderQuantity WHERE ProductID = @ProductID";
                        using (SqlCommand cmd = new SqlCommand(UpdateProductsQuery, connection, transaction))
                        {
                            cmd.Parameters.AddWithValue("@OrderQuantity", orderQuantity);
                            cmd.Parameters.AddWithValue("@ProductID", productId);
                            cmd.ExecuteNonQuery();
                            Console.WriteLine("Product stock updated successfully.");
                        }

                        // Intentionally cause an error if simulateFailure = true
                        if (simulateFailure)
                        {
                            throw new Exception("Simulated exception to test rollback behavior.");
                        }

                        // Commit the transaction when all operations complete successfully.
                        transaction.Commit();
                        Console.WriteLine("Transaction committed successfully. Data consistency maintained.");
                    }
                    catch (Exception ex)
                    {
                        // In case any operation fails, roll back the entire transaction.
                        transaction.Rollback();
                        Console.WriteLine("Transaction rolled back due to an error: " + ex.Message);
                    }
                }
            }
        }
    }
}

Code Explanation:

• Connection and Transaction Setup: The code opens a connection to the SQL Server using the provided connection string. It then begins a transaction to ensure that all subsequent operations are atomic.
• Scenarios: The boolean flag simulateFailure is set to false by default. If you change it to true, it will throw an exception after inserting the data, triggering a rollback.
• Order Insertion: An order is created in the Orders table. The OUTPUT INSERTED.OrderID clause returns the new OrderID, which is used to relate the order to its items and payment.
• OrderItems and Payments: Using the captured OrderID, the code inserts a corresponding order item and a payment record. Each command is executed as part of the transaction.
• Stock Update: The product stock is updated to reflect the deduction due to the order. If any command fails along the way, the exception handler rolls back the transaction.
• Commit vs. Rollback: If all operations are successful, the transaction is committed, ensuring data consistency (Case 1). If any operation fails (Case 2), the rollback is triggered, leaving the database unchanged.

Case 1: All Operations Succeed

Set simulateFailure = false. Run the application. You will see the following messages:

Case 2: One Operation Fails

Set simulateFailure = true. Run the application. You will see the following messages:

ADO.NET Core with Asynchronous Transaction

Using asynchronous programming (async/await pattern) can improve the scalability and responsiveness of .NET Core applications, especially when dealing with I/O-bound operations such as database calls. The overall approach is the same; however, you use asynchronous methods like OpenAsync(), ExecuteNonQueryAsync(), ExecuteScalarAsync(), CommitAsync(), RollbackAsync(), etc. The following Key Changes we need to do for Asynchronous Operations:

• Async Main Method: The Main method is marked with async and returns Task to allow asynchronous operations.
• Open Connection Asynchronously: OpenAsync is used to open the database connection asynchronously.
• Begin Transaction Asynchronously: BeginTransactionAsync starts a transaction asynchronously.
• Execute Commands Asynchronously: ExecuteScalarAsync, ExecuteNonQueryAsync, and other command execution methods are used with await to perform database operations without blocking the thread.
• Commit and Rollback Asynchronously: Transactions are committed or rolled back using CommitAsync and RollbackAsync, respectively.

The following example is the asynchronous version of the previous example.

using Microsoft.Data.SqlClient;

namespace OrderBookingApp
{
    public class Program
    {
        // Connection string to your SQL Server OrderManagementDB database
        public static string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=OrderManagementDB;Trusted_Connection=True;TrustServerCertificate=True;";

        // Simulate either success or failure scenario
        // Change to 'true' to test rollback scenario.
        public static bool simulateFailure = false;

        static async Task Main(string[] args)
        {
            Console.WriteLine("Starting Order Booking Operation...");

            // Wrap entire booking operation in a try-catch to manage any failures
            try
            {
                // Call the method to book an order
                await BookOrderAsync();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Order booking failed: " + ex.Message);
            }

            Console.ReadKey();
        }

        // Asynchronously executes the order booking process within a transaction.
        static async Task BookOrderAsync()
        {
            // Define the order details (normally these would come from user input or business logic)
            int customerId = 1;
            int productId = 1;
            int orderQuantity = 2;
            decimal unitPrice = 999.99m;
            decimal totalAmount = unitPrice * orderQuantity;
            decimal paymentAmount = totalAmount;

            // Establish connection to the database
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();

                // Begin Transaction Asynchronously
                using (SqlTransaction transaction = (SqlTransaction)await connection.BeginTransactionAsync())
                {
                    try
                    {
                        // Insert into Orders table and get OrderID asynchronously.
                        int orderId;
                        string InsertOrdersQuery = "INSERT INTO Orders (CustomerID, TotalAmount) OUTPUT INSERTED.OrderID VALUES (@CustomerID, @TotalAmount)";
                        using (SqlCommand cmd = new SqlCommand(InsertOrdersQuery, connection, transaction))
                        {
                            cmd.Parameters.AddWithValue("@CustomerID", customerId);
                            cmd.Parameters.AddWithValue("@TotalAmount", totalAmount);
                            // Execute the command asynchronously and retrieve the new OrderId.
                            orderId = Convert.ToInt32(await cmd.ExecuteScalarAsync());
                            Console.WriteLine("Order created successfully with OrderID: " + orderId);
                        }

                        // 2. Insert into OrderItems table
                        string InsertOrderItemsQuery = "INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice) VALUES (@OrderID, @ProductID, @Quantity, @UnitPrice)";
                        using (SqlCommand cmd = new SqlCommand(InsertOrderItemsQuery, connection, transaction))
                        {
                            cmd.Parameters.AddWithValue("@OrderID", orderId);
                            cmd.Parameters.AddWithValue("@ProductID", productId);
                            cmd.Parameters.AddWithValue("@Quantity", orderQuantity);
                            cmd.Parameters.AddWithValue("@UnitPrice", unitPrice);
                            await cmd.ExecuteNonQueryAsync();
                            Console.WriteLine("Order item inserted successfully.");
                        }

                        // 3. Insert into Payments table
                        string InsertPaymentsQuery = "INSERT INTO Payments (OrderID, Amount, PaymentMethod) VALUES (@OrderID, @Amount, @PaymentMethod)";
                        using (SqlCommand cmd = new SqlCommand(InsertPaymentsQuery, connection, transaction))
                        {
                            cmd.Parameters.AddWithValue("@OrderID", orderId);
                            cmd.Parameters.AddWithValue("@Amount", paymentAmount);
                            cmd.Parameters.AddWithValue("@PaymentMethod", "CreditCard");
                            await cmd.ExecuteNonQueryAsync();
                            Console.WriteLine("Payment recorded successfully.");
                        }

                        // 4. Update Product Stock (simulate stock deduction)
                        string UpdateProductsQuery = "UPDATE Products SET Stock = Stock - @OrderQuantity WHERE ProductID = @ProductID";
                        using (SqlCommand cmd = new SqlCommand(UpdateProductsQuery, connection, transaction))
                        {
                            cmd.Parameters.AddWithValue("@OrderQuantity", orderQuantity);
                            cmd.Parameters.AddWithValue("@ProductID", productId);
                            await cmd.ExecuteNonQueryAsync();
                            Console.WriteLine("Product stock updated successfully.");
                        }

                        // Intentionally cause an error if simulateFailure = true
                        if (simulateFailure)
                        {
                            throw new Exception("Simulated exception to test rollback behavior.");
                        }

                        // Commit the transaction when all operations complete successfully.
                        await transaction.CommitAsync();
                        Console.WriteLine("Transaction committed successfully. Data consistency maintained.");
                    }
                    catch (Exception ex)
                    {
                        // In case any operation fails, roll back the entire transaction.
                        await transaction.RollbackAsync();
                        Console.WriteLine("Transaction rolled back due to an error: " + ex.Message);
                    }
                }
            }
        }
    }
}

Output:

When Should We Use Transactions with ADO.NET Core in Real-Time Applications?

Transactions in ADO.NET Core should be used when multiple interdependent database operations need to execute as a single, atomic unit. Typical scenarios include:

• Financial and Banking Systems: Ensuring that funds are neither lost nor duplicated in transfers.
• E-commerce Platforms: Guaranteeing that orders, inventory updates, and payments remain consistent.
• Multi-step Business Processes: Any situation where failing one step should cancel all preceding operations to maintain data integrity.
• Concurrent Access: When multiple users or processes are modifying data concurrently, isolation must be maintained to avoid race conditions or data corruption.

A database transaction is crucial to ensure all-or-nothing behavior for a set of database operations. By applying ACID properties, you can maintain data integrity and consistency. ADO.NET Core provides robust and straightforward APIs to handle transactions synchronously and asynchronously.

In the next article, I will discuss the ADO.NET Core Transaction Isolation Level with Examples. In this article, I explain how to Implement Transactions in ADO.NET Core with Examples. I would like to have your feedback. Please post your feedback, questions, or comments about this How to Implement Transactions in ADO.NET Core with Examples article.