MSDN Documentation

ADO.NET Transactions

Transactions are a fundamental concept in database management, ensuring data integrity by grouping 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 is committed, making the changes permanent.

Why Use Transactions?

Transactions are crucial for maintaining atomicity, consistency, isolation, and durability (ACID properties) in your database operations. Consider a scenario where you need to transfer funds between two bank accounts. This involves two distinct operations: debiting one account and crediting another. If the debit operation succeeds but the credit operation fails, you'll have an inconsistent state. A transaction ensures that both operations either succeed or fail together.

Implementing Transactions with ADO.NET

ADO.NET provides classes to manage database transactions. The primary class involved is DbTransaction (or its specific provider-derived classes like SqlTransaction for SQL Server).

The general steps to implement a transaction are:

1. Establish a database connection using DbConnection.
2. Begin a transaction by calling the BeginTransaction() method on the connection object. This returns a DbTransaction object.
3. Associate your DbCommand objects with this transaction by setting their Transaction property.
4. Execute your commands.
5. If all commands execute successfully, call the Commit() method on the transaction object.
6. If any error occurs, call the Rollback() method on the transaction object to undo all changes made within the transaction.
7. Ensure the transaction and connection are properly disposed of, typically using a try...catch...finally block or using statements.

Example: Transferring Funds (Conceptual)

The following C# code snippet illustrates how to implement a transaction for a fund transfer. Note that this is a simplified example and error handling might be more extensive in a production environment.

using System;
using System.Data;
using System.Data.Common; // Or specific provider like System.Data.SqlClient

public void TransferFunds(DbConnection connection, decimal amount, int fromAccountId, int toAccountId)
{
    DbTransaction transaction = null;
    try
    {
        connection.Open();
        transaction = connection.BeginTransaction();

        // Create command to debit the source account
        DbCommand debitCommand = connection.CreateCommand();
        debitCommand.CommandText = "UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountId = @AccountId";
        debitCommand.Parameters.Add(debitCommand.CreateParameter("Amount", amount));
        debitCommand.Parameters.Add(debitCommand.CreateParameter("AccountId", fromAccountId));
        debitCommand.Transaction = transaction;
        debitCommand.ExecuteNonQuery();

        // Create command to credit the destination account
        DbCommand creditCommand = connection.CreateCommand();
        creditCommand.CommandText = "UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountId = @AccountId";
        creditCommand.Parameters.Add(creditCommand.CreateParameter("Amount", amount));
        creditCommand.Parameters.Add(creditCommand.CreateParameter("AccountId", toAccountId));
        creditCommand.Transaction = transaction;
        creditCommand.ExecuteNonQuery();

        // If both operations succeed, commit the transaction
        transaction.Commit();
        Console.WriteLine("Funds transferred successfully.");
    }
    catch (Exception ex)
    {
        // If any error occurs, rollback the transaction
        if (transaction != null)
        {
            transaction.Rollback();
        }
        Console.WriteLine($"An error occurred: {ex.Message}. Transaction rolled back.");
        // Rethrow or handle the exception as needed
        throw;
    }
    finally
    {
        // Ensure the connection is closed and disposed
        if (connection != null && connection.State == ConnectionState.Open)
        {
            connection.Close();
        }
        // Transaction is implicitly disposed when connection is closed/disposed if not explicitly managed.
        // Using statement for connection and transaction is preferred for robustness.
    }
}

// Example usage with SqlClient and using statements (more robust):
/*
using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    using (SqlTransaction transaction = connection.BeginTransaction())
    {
        try
        {
            // ... execute commands with transaction set ...
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
}
*/
            

Transaction Isolation Levels

Transactions can be configured with different isolation levels, which determine how and when changes made by one transaction are visible to other concurrent transactions. Common isolation levels include:

• Read Uncommitted: Allows transactions to see uncommitted changes made by other transactions. This can lead to dirty reads, non-repeatable reads, and phantom reads.
• Read Committed: Prevents dirty reads. Transactions can only see data that has been committed.
• Repeatable Read: Prevents dirty reads and non-repeatable reads. Ensures that if a transaction reads a row, subsequent reads of the same row will return the same data.
• Serializable: The highest isolation level. Prevents dirty reads, non-repeatable reads, and phantom reads. It ensures that concurrent transactions execute as if they were executed serially.

You can specify the isolation level when calling BeginTransaction():

DbTransaction transaction = connection.BeginTransaction(IsolationLevel.RepeatableRead);
            

Error Handling and Rollback

Robust error handling is paramount when working with transactions. The catch block is essential for detecting any exceptions during command execution. If an exception occurs, the Rollback() method must be called on the transaction object to ensure that no partial changes are saved to the database. The finally block is used to ensure that the database connection is always closed and disposed of, regardless of whether the transaction succeeded or failed.

Distributed Transactions

For scenarios involving multiple disparate data sources (e.g., a SQL Server database and an Oracle database), you might need to use distributed transactions. ADO.NET supports distributed transactions through the System.Transactions namespace, which provides the TransactionScope class. This class automatically coordinates transactions across different resource managers using protocols like the two-phase commit.