MSDN Documentation

Transactions in ADO.NET

Transactions are a fundamental concept in database management, ensuring data integrity by allowing a sequence of database operations to be treated as a single, indivisible unit. In ADO.NET, the System.Data.IDbTransaction interface and its implementations provide the framework for managing these operations.

Why Use Transactions?

Transactions are essential for scenarios where multiple database modifications must succeed or fail together. Consider a simple fund transfer between two bank accounts:

• Debit the source account.
• Credit the destination account.

If the debit operation succeeds but the credit operation fails, the database would be in an inconsistent state. A transaction guarantees that either both operations complete successfully, or if any part fails, all changes are rolled back, leaving the database as it was before the transaction began.

Key Concepts

• Atomicity: All operations within a transaction are completed as a single unit.
• Consistency: A transaction brings the database from one valid state to another.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Once a transaction is committed, its changes are permanent.

Working with Transactions in ADO.NET

The process typically involves the following steps:

1. Establish a Connection: Obtain an open IDbConnection object.
2. Begin the Transaction: Call the BeginTransaction() method on the connection object. This returns an IDbTransaction object.
3. Execute Commands: Associate your IDbCommand objects with the transaction by setting their Transaction property.
4. Commit or Rollback:
   • If all operations are successful, call the Commit() method on the transaction object.
   • If any operation fails, call the Rollback() method on the transaction object.
5. Dispose Resources: Ensure all connection and command objects are properly disposed of.

Example: Using Transactions with SQL Server

This example demonstrates a simple fund transfer using ADO.NET with SQL Server.

using System;
using System.Data;
using System.Data.SqlClient;

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

            try
            {
                // Begin the transaction
                transaction = connection.BeginTransaction();

                // Command to debit the source account
                using (SqlCommand debitCommand = new SqlCommand())
                {
                    debitCommand.Connection = connection;
                    debitCommand.Transaction = transaction;
                    debitCommand.CommandText = "UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountId = @AccountId";
                    debitCommand.Parameters.AddWithValue("@Amount", amount);
                    debitCommand.Parameters.AddWithValue("@AccountId", fromAccountId);
                    debitCommand.ExecuteNonQuery();
                }

                // Command to credit the destination account
                using (SqlCommand creditCommand = new SqlCommand())
                {
                    creditCommand.Connection = connection;
                    creditCommand.Transaction = transaction;
                    creditCommand.CommandText = "UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountId = @AccountId";
                    creditCommand.Parameters.AddWithValue("@Amount", amount);
                    creditCommand.Parameters.AddWithValue("@AccountId", toAccountId);
                    creditCommand.ExecuteNonQuery();
                }

                // If all commands executed successfully, commit the transaction
                transaction.Commit();
                Console.WriteLine("Transfer successful. Transaction committed.");
            }
            catch (Exception ex)
            {
                // If any error occurs, rollback the transaction
                if (transaction != null)
                {
                    transaction.Rollback();
                    Console.WriteLine($"Transfer failed: {ex.Message}. Transaction rolled back.");
                }
                else
                {
                    Console.WriteLine($"An unexpected error occurred: {ex.Message}");
                }
            }
            finally
            {
                // The 'using' statement takes care of closing and disposing the connection.
                // Ensure transaction is disposed if not null.
                if (transaction != null)
                {
                    transaction.Dispose();
                }
            }
        }
    }
}
            

Transaction Isolation Levels

ADO.NET allows you to specify the isolation level for a transaction, which controls how transactions are isolated from each other. Common isolation levels include:

• ReadUncommitted: Lowest isolation level.
• ReadCommitted: Reads are guaranteed to be committed.
• RepeatableRead: Guarantees that if a row is read twice, the data will be the same.
• Serializable: Highest isolation level. Transactions are executed serially.

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

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

Nested Transactions

While some database systems support true nested transactions, ADO.NET's IDbTransaction interface does not directly support them. However, you can achieve a similar effect using savepoints, which are checkpoints within a transaction that allow you to rollback to a specific point without aborting the entire transaction.

The exact implementation for savepoints varies by database provider. For SQL Server, you can use commands like:

// Within an existing transaction
command.CommandText = "SAVE TRANSACTION MySavePoint;";
command.ExecuteNonQuery();

// To rollback to the savepoint
command.CommandText = "ROLLBACK TRANSACTION MySavePoint;";
command.ExecuteNonQuery();
            

Conclusion

Transactions are critical for maintaining data integrity in database applications. ADO.NET provides robust support for managing transactions, enabling developers to ensure that complex data operations are performed reliably and consistently.