ADO.NET Transactions

Transactions are a fundamental concept in database management, ensuring data integrity by allowing a series of operations to be treated as a single, indivisible unit. In ADO.NET, you can manage database transactions to guarantee that either all operations within the transaction are successfully completed, or none of them are. This prevents partial updates and maintains a consistent state of your data.

Understanding Transactions

A transaction is defined by three key properties, often referred to as ACID:

• Atomicity: All operations within a transaction are completed successfully, or the transaction is rolled back, leaving the database in its original state.
• Consistency: A transaction brings the database from one valid state to another.
• Isolation: Concurrent transactions do not interfere with each other. Each transaction appears to execute in isolation.
• Durability: Once a transaction is committed, its changes are permanent, even in the event of system failures.

Managing Transactions in ADO.NET

ADO.NET provides the System.Data.IDbTransaction interface for managing transactions. You typically obtain a transaction object by calling the BeginTransaction() method on a connection object.

Starting a Transaction

To start a transaction, you need an active IDbConnection. You can then call BeginTransaction():

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

    try
    {
        // Perform database operations here...
    }
    catch (Exception ex)
    {
        // Handle exceptions and roll back
        transaction.Rollback();
        // Log or re-throw the exception
    }
    finally
    {
        // Commit if no exceptions occurred
        // This should be inside the try block if successful
    }
}
        

Performing Operations within a Transaction

When executing commands within a transaction, you must associate the IDbCommand object with the transaction. This is done by setting the Transaction property of the command object:

SqlCommand command1 = connection.CreateCommand();
command1.Transaction = transaction;
command1.CommandText = "UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1";
command1.ExecuteNonQuery();

SqlCommand command2 = connection.CreateCommand();
command2.Transaction = transaction;
command2.CommandText = "UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2";
command2.ExecuteNonQuery();
        

Committing and Rolling Back

If all operations within the transaction complete successfully, you commit the transaction using the Commit() method:

transaction.Commit();
        

If any error occurs or you need to cancel the transaction, you roll it back using the Rollback() method:

transaction.Rollback();
        

Transaction Isolation Levels

Isolation levels define the degree to which one transaction must be isolated from the data modifications made by other concurrent transactions. ADO.NET supports various isolation levels, including:

• ReadUncommitted: Allows dirty reads, non-repeatable reads, and phantom reads.
• ReadCommitted: Prevents dirty reads but allows non-repeatable reads and phantom reads.
• RepeatableRead: Prevents dirty reads and non-repeatable reads but allows phantom reads.
• Serializable: The highest isolation level, preventing dirty reads, non-repeatable reads, and phantom reads.

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

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

Example: Transferring Funds

A classic example of transaction usage is transferring funds between two accounts. This operation must be atomic: either both the debit and credit operations succeed, or neither does.

public void TransferFunds(string connectionString, int fromAccountId, int toAccountId, decimal amount)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlTransaction transaction = connection.BeginTransaction();

        try
        {
            // Debit from account
            using (SqlCommand debitCommand = connection.CreateCommand())
            {
                debitCommand.Transaction = transaction;
                debitCommand.CommandText = "UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountId = @FromAccountId AND Balance >= @Amount";
                debitCommand.Parameters.AddWithValue("@Amount", amount);
                debitCommand.Parameters.AddWithValue("@FromAccountId", fromAccountId);

                int rowsAffected = debitCommand.ExecuteNonQuery();
                if (rowsAffected == 0)
                {
                    throw new Exception("Insufficient funds or invalid source account.");
                }
            }

            // Credit to account
            using (SqlCommand creditCommand = connection.CreateCommand())
            {
                creditCommand.Transaction = transaction;
                creditCommand.CommandText = "UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountId = @ToAccountId";
                creditCommand.Parameters.AddWithValue("@Amount", amount);
                creditCommand.Parameters.AddWithValue("@ToAccountId", toAccountId);

                creditCommand.ExecuteNonQuery();
            }

            // If both operations succeed, commit the transaction
            transaction.Commit();
            Console.WriteLine("Fund transfer successful.");
        }
        catch (Exception ex)
        {
            // If any error occurs, roll back the transaction
            transaction.Rollback();
            Console.WriteLine($"Fund transfer failed: {ex.Message}");
            // Log the error or re-throw
            throw;
        }
    }
}
        

By effectively using ADO.NET transactions, you can significantly enhance the reliability and integrity of your data access operations.