MSDN Documentation

Handling Data Transactions in ADO.NET

Last updated: October 26, 2023

ADO.NET provides robust mechanisms for managing data transactions, ensuring data integrity and consistency across database operations. Transactions allow you to group a series of database operations into a single logical unit of work. If any operation within the transaction fails, the entire transaction can be rolled back, leaving the database in its original state. Conversely, if all operations succeed, the transaction can be committed.

What is a Transaction?

A transaction is a sequence of operations performed as a single, atomic unit of work. It adheres to the ACID properties:

• Atomicity: All operations within the transaction are completed successfully, or none of them are.
• Consistency: The transaction brings the database from one valid state to another.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Once a transaction is committed, its changes are permanent, even in the event of system failures.

Managing Transactions with System.Data.Common.DbTransaction

ADO.NET uses the DbTransaction class (or its provider-specific implementations like SqlTransaction for SQL Server) to manage transactions.

Starting a Transaction

You can start a transaction by calling the BeginTransaction() method on your DbConnection object. This method returns a DbTransaction object that represents the ongoing transaction.

using System.Data.Common;

// Assuming 'connection' is an established DbConnection object
DbTransaction transaction = null;
try
{
    transaction = connection.BeginTransaction();
    // ... perform database operations ...
}
catch (Exception ex)
{
    // Handle exceptions
}
finally
{
    if (connection != null && connection.State == System.Data.ConnectionState.Open)
    {
        // If transaction was started but not committed/rolled back, roll it back
        if (transaction != null)
        {
            transaction.Rollback();
        }
        connection.Close();
    }
}
                

Performing Operations within a Transaction

When executing commands within a transaction, you must associate them with the transaction object. This is typically done by setting the Transaction property of the DbCommand object.

// Inside the try block where 'transaction' is active
using (DbCommand command = connection.CreateCommand())
{
    command.Transaction = transaction;

    command.CommandText = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price)";
    command.Parameters.AddWithValue("@Name", "Gadget");
    command.Parameters.AddWithValue("@Price", 199.99);
    command.ExecuteNonQuery();

    command.CommandText = "UPDATE Stock SET Quantity = Quantity - 1 WHERE ProductId = @ProductId";
    command.Parameters.AddWithValue("@ProductId", 123);
    command.ExecuteNonQuery();
}
                

Committing a Transaction

If all operations within the transaction complete successfully, you can commit the transaction using the Commit() method on the DbTransaction object. This makes all changes permanent.

if (transaction != null)
{
    transaction.Commit();
    Console.WriteLine("Transaction committed successfully.");
}
                

Rolling Back a Transaction

If any error occurs during the transaction, or if you decide to cancel the operations, you can roll back the transaction using the Rollback() method. This undoes all changes made since the transaction began.

if (transaction != null)
{
    transaction.Rollback();
    Console.WriteLine("Transaction rolled back due to an error.");
}
                

Transaction Isolation Levels

Isolation levels control how transactions interact with each other. ADO.NET allows you to specify the isolation level when starting a transaction. Common levels include:

• ReadUncommitted: Lowest level, allows reading uncommitted data.
• ReadCommitted: Prevents reading uncommitted data.
• RepeatableRead: Ensures that if a transaction rereads a row, it will see the same data.
• Serializable: Highest level, ensures complete isolation.

The default isolation level is usually determined by the database. You can specify it using the overload of BeginTransaction() that accepts an IsolationLevel enum value.

DbTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                

Example: Transferring Funds

Consider a scenario where you need to transfer funds from one account to another. This operation must be atomic: either both the debit and credit happen, or neither does.

public void TransferFunds(int fromAccountId, int toAccountId, decimal amount)
{
    using (DbConnection connection = new SqlConnection("YourConnectionString")) // Example using SqlConnection
    {
        connection.Open();
        DbTransaction transaction = null;
        try
        {
            transaction = connection.BeginTransaction();

            using (DbCommand debitCommand = connection.CreateCommand())
            {
                debitCommand.Transaction = transaction;
                debitCommand.CommandText = "UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountId = @AccountId";
                debitCommand.Parameters.AddWithValue("@Amount", amount);
                debitCommand.Parameters.AddWithValue("@AccountId", fromAccountId);
                debitCommand.ExecuteNonQuery();
            }

            using (DbCommand creditCommand = connection.CreateCommand())
            {
                creditCommand.Transaction = transaction;
                creditCommand.CommandText = "UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountId = @AccountId";
                creditCommand.Parameters.AddWithValue("@Amount", amount);
                creditCommand.Parameters.AddWithValue("@AccountId", toAccountId);
                creditCommand.ExecuteNonQuery();
            }

            transaction.Commit();
            Console.WriteLine("Funds transferred successfully.");
        }
        catch (Exception ex)
        {
            if (transaction != null)
            {
                transaction.Rollback();
            }
            Console.WriteLine($"An error occurred: {ex.Message}");
            throw; // Re-throw the exception for further handling
        }
    }
}
                

By using transactions, you ensure that your application maintains data integrity even in the face of errors or concurrent operations. This is a fundamental aspect of robust database application development with ADO.NET.