ADO.NET Transactions

Transactions are crucial for maintaining data integrity in multi-step database operations. In ADO.NET, you can manage transactions to ensure that a series of database commands either all succeed or all fail together, preventing inconsistent data states.

Understanding Database Transactions

A transaction is a unit of work that comprises one or more database operations. The key properties of a transaction are often referred to by the acronym ACID:

• Atomicity: Ensures that all operations within the transaction are completed successfully. If any operation fails, the entire transaction is rolled back, and the database remains in its original state.
• Consistency: Guarantees that a transaction brings the database from one valid state to another, preserving data integrity rules.
• Isolation: Ensures that 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 to represent a transaction. The specific implementation of this interface varies depending on the data provider you are using (e.g., SqlTransaction for SQL Server, OracleTransaction for Oracle).

Starting a Transaction

To start a transaction, you typically use the BeginTransaction() method on a connection object. This method returns a transaction object that you can then associate with your commands.

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

    // Commands will be executed within this transaction
}
            

Associating Commands with a Transaction

When you create a command object (e.g., SqlCommand), you can assign the transaction object to its Transaction property. This ensures that the command is executed as part of the transaction.

SqlCommand command1 = connection.CreateCommand();
command1.Transaction = transaction;
command1.CommandText = "UPDATE Products SET Price = Price * 1.1 WHERE ProductID = 10;";
command1.ExecuteNonQuery();

SqlCommand command2 = connection.CreateCommand();
command2.Transaction = transaction;
command2.CommandText = "INSERT INTO OrderHistory (OrderID, ProductID, Quantity) VALUES (101, 10, 5);";
command2.ExecuteNonQuery();
            

Committing and Rolling Back Transactions

After executing all the commands within the transaction, you have two options:

• Commit: If all operations are successful, you call the Commit() method on the transaction object to make the changes permanent.
• Rollback: If any operation fails, or if you decide to cancel the transaction, you call the Rollback() method. This undoes all changes made since the transaction began.

It's good practice to wrap transaction logic in a try-catch block. If an exception occurs, you can catch it and perform a rollback.

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

    try
    {
        transaction = connection.BeginTransaction();

        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();

        // If both commands succeed, commit the transaction
        transaction.Commit();
        Console.WriteLine("Transaction committed successfully.");
    }
    catch (Exception ex)
    {
        // If an error occurs, roll back the transaction
        if (transaction != null)
        {
            transaction.Rollback();
            Console.WriteLine($"Transaction rolled back: {ex.Message}");
        }
    }
}
            

Transaction Isolation Levels

Isolation levels control how transaction locking is applied, affecting how data modifications made by other transactions are visible to your transaction. Common isolation levels include:

• Read Uncommitted: Lowest level. Allows dirty reads (reading uncommitted data).
• Read Committed: Prevents dirty reads. Data is read only after it has been committed by other transactions.
• Repeatable Read: Guarantees that if a transaction reads a row multiple times, it will see the same data each time.
• Serializable: Highest level. Prevents dirty reads, non-repeatable reads, and phantom reads. It ensures transactions are serialized.

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

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

Benefits of Using Transactions

• Data Integrity: Ensures that database operations are atomic, preventing partial updates.
• Reliability: Provides a mechanism to recover from errors by rolling back changes.
• Concurrency Control: Helps manage simultaneous access to data, though isolation levels need careful consideration.

By mastering ADO.NET transactions, you can build more robust and reliable data-driven applications.