ADO.NET Transactions

Transactions are a fundamental concept in database management, ensuring data integrity by allowing a sequence of operations to be treated as a single, atomic unit. In ADO.NET, the DbTransaction class and its derived classes (like SqlTransaction for SQL Server) provide the functionality to manage database transactions.

Understanding Transactions

A transaction guarantees the ACID properties:

• Atomicity: All operations within the transaction are completed successfully, or none of them are.
• Consistency: The database is left in a valid state after the transaction.
• 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 in ADO.NET

You manage transactions by obtaining a DbTransaction object from a DbConnection and then executing commands within the scope of that transaction.

Starting a Transaction

To start a transaction, you call the BeginTransaction() method on your DbConnection object. This method returns a DbTransaction object.

using System.Data;
using System.Data.Common; // For DbConnection and DbTransaction

// Assume conn is an established and open DbConnection object
DbTransaction transaction = conn.BeginTransaction();
            

Executing Commands within a Transaction

When creating a DbCommand object, you associate it with the active transaction. This ensures that any data modifications performed by the command are part of the transaction.

DbCommand command = conn.CreateCommand();
command.CommandText = "UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;";
command.Transaction = transaction; // Associate the command with the transaction
command.ExecuteNonQuery();

command.CommandText = "UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;";
command.Transaction = transaction; // Associate the command with the transaction
command.ExecuteNonQuery();
            

Committing a Transaction

If all operations within the transaction are successful, you can commit the transaction using the Commit() method of the DbTransaction object. This makes all changes permanent.

transaction.Commit();
            

Rolling Back a Transaction

If an error occurs or if you decide to cancel the transaction, you can roll back all changes made within the transaction using the Rollback() method. This returns the database to the state it was in before the transaction began.

try
{
    // ... perform database operations ...
    transaction.Commit();
}
catch (Exception ex)
{
    // Log the exception details
    Console.WriteLine($"Error: {ex.Message}");
    if (transaction != null)
    {
        transaction.Rollback(); // Roll back changes if an error occurs
    }
}
finally
{
    if (conn != null && conn.State == ConnectionState.Open)
    {
        // Dispose of the transaction and connection
        if (transaction != null)
        {
            transaction.Dispose();
        }
        conn.Close();
        conn.Dispose();
    }
}
            

Transaction Isolation Levels

ADO.NET supports various transaction isolation levels, which determine how transactions interact with each other. These levels can be specified when calling BeginTransaction(). Common levels include:

• ReadUncommitted: Allows transactions to read rows that have been modified by other transactions but not yet committed.
• ReadCommitted: Prevents dirty reads, meaning transactions can only read rows that have been committed.
• RepeatableRead: Guarantees that if a transaction reads a row multiple times, it will see the same data each time.
• Serializable: The highest level of isolation, ensuring that transactions are executed as if they were run one after another.

// Example: Starting a transaction with ReadCommitted isolation level
DbTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            

Best Practices

• Always wrap your transaction code in a try-catch-finally block.
• Call Commit() only when all operations are successful.
• Call Rollback() in the catch block or when an operation fails.
• Ensure the DbTransaction and DbConnection objects are properly disposed of in the finally block.
• Keep transactions as short as possible to minimize blocking and improve concurrency.
• Use the specific transaction class for your database provider (e.g., SqlTransaction for SQL Server) for maximum efficiency and access to provider-specific features.