ADO.NET Transactions

Transactions are a fundamental concept in database management, ensuring data integrity and consistency. In ADO.NET, you can manage transactions to group a set of operations so that they are treated as a single unit of work. If any operation within the transaction fails, all operations can be rolled back, leaving the database in its original state. Conversely, if all operations succeed, the transaction can be committed.

Understanding Transactions

A transaction has the following ACID properties:

• Atomicity: All operations within the transaction are completed successfully, or none are.
• Consistency: The transaction brings the database from one valid state to another.
• Isolation: The intermediate state of the database during a transaction is not visible to other transactions.
• Durability: Once a transaction is committed, its changes are permanent, even in the event of system failure.

Managing Transactions in ADO.NET

ADO.NET provides the System.Data.Common.DbTransaction class (and its provider-specific implementations like System.Data.SqlClient.SqlTransaction) to manage transactions. The general workflow involves:

1. Establishing a database connection.
2. Beginning a transaction.
3. Executing one or more commands within the transaction.
4. Committing the transaction if all operations are successful, or rolling it back if any operation fails.

Beginning a Transaction

You can start a transaction by calling the BeginTransaction method on your DbConnection object. This method returns a DbTransaction object that you will use to control the transaction.

using (DbConnection connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    DbTransaction transaction = connection.BeginTransaction();
    // ... execute commands using this transaction ...
}

Executing Commands within a Transaction

To ensure that a command is executed as part of a transaction, you need to associate the DbCommand object with the transaction. This is done by setting the Transaction property of the DbCommand object to the DbTransaction object.

using (DbCommand command = connection.CreateCommand())
{
    command.CommandText = "INSERT INTO Products (ProductName, Price) VALUES (@Name, @Price)";
    command.Transaction = transaction; // Associate the command with the transaction

    DbParameter nameParam = command.CreateParameter();
    nameParam.ParameterName = "@Name";
    nameParam.Value = "Widget";
    command.Parameters.Add(nameParam);

    DbParameter priceParam = command.CreateParameter();
    priceParam.ParameterName = "@Price";
    priceParam.Value = 19.99;
    command.Parameters.Add(priceParam);

    command.ExecuteNonQuery();
}

Committing and Rolling Back Transactions

Once all operations are complete, you can either commit the transaction using the Commit method or roll it back using the Rollback method.

using (DbConnection connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    DbTransaction transaction = connection.BeginTransaction();
    try
    {
        // Execute first command
        using (DbCommand cmd1 = connection.CreateCommand())
        {
            cmd1.Transaction = transaction;
            cmd1.CommandText = "UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1";
            cmd1.ExecuteNonQuery();
        }

        // Execute second command
        using (DbCommand cmd2 = connection.CreateCommand())
        {
            cmd2.Transaction = transaction;
            cmd2.CommandText = "UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2";
            cmd2.ExecuteNonQuery();
        }

        // If both commands succeeded, commit the transaction
        transaction.Commit();
        // Optional: Log successful transaction
    }
    catch (Exception ex)
    {
        // If an error occurred, roll back the transaction
        if (transaction != null)
        {
            transaction.Rollback();
        }
        // Optional: Log the error and rollback
        throw; // Re-throw the exception to be handled by the caller
    }
}

Transaction Isolation Levels

ADO.NET allows you to specify the isolation level for a transaction. This controls how locks are applied and how data consistency is maintained between concurrent transactions. Common isolation levels include:

• ReadUncommitted: Lowest level, allows dirty 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: Highest level, prevents all concurrency anomalies.

You can specify the isolation level when calling BeginTransaction:

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

Best Practices

• Keep transactions as short as possible to minimize blocking of other operations.
• Handle exceptions gracefully and always ensure that Rollback is called if an error occurs.
• Consider the appropriate isolation level for your application's needs.
• Use parameterized queries to prevent SQL injection vulnerabilities, especially within transactions.