Transaction with Multiple Commands Using Try-Catch-Finally

This snippet showcases a robust transaction implementation with multiple commands and ensures proper cleanup using a try-catch-finally block to handle potential exceptions and guarantee transaction rollback or commit.

Code Snippet

This example expands on the previous one by explicitly creating `SqlConnection` and `SqlTransaction` objects outside the `using` statement. It then uses a `try-catch-finally` block to ensure that the connection is always closed, even if an exception occurs. Inside the `try` block, the code opens the connection, begins a transaction, executes two update commands, and attempts to commit the transaction. The `catch` block handles any exceptions by attempting to rollback the transaction. The `finally` block ensures that the connection is closed, regardless of whether the transaction was committed or rolled back. Using null checks prevents errors if rollback is attempted on a null transaction object.

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

public class TransactionExampleWithCleanup
{
    public static void ExecuteTransactionWithCleanup(string connectionString)
    {
        SqlConnection connection = null;
        SqlTransaction transaction = null;

        try
        {
            connection = new SqlConnection(connectionString);
            connection.Open();

            transaction = connection.BeginTransaction();

            SqlCommand command1 = new SqlCommand("UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1", connection, transaction);
            command1.ExecuteNonQuery();

            SqlCommand command2 = new SqlCommand("UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2", connection, transaction);
            command2.ExecuteNonQuery();

            transaction.Commit();
            Console.WriteLine("Transaction committed successfully.");
        }
        catch (Exception ex)
        {
            Console.WriteLine("Transaction failed: " + ex.Message);
            try
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                    Console.WriteLine("Transaction rolled back.");
                }
            }
            catch (Exception exRollback)
            {
                Console.WriteLine("Rollback failed: " + exRollback.Message);
            }
        }
        finally
        {
            if (connection != null)
            {
                connection.Close();
            }
        }
    }
}

Benefits of Try-Catch-Finally

The try-catch-finally block offers better control over resource management and error handling. Specifically, the finally block ensures the connection is closed and resources are released, preventing resource leaks even in case of exceptions.

Connection Management

Explicitly managing the SqlConnection using a try-catch-finally block ensures that the connection is always closed, releasing valuable database resources, irrespective of transaction success or failure. This helps improve application performance and scalability.

Interview Tip

When explaining transaction management, mention the importance of exception handling using try-catch and resource cleanup using finally blocks to highlight your understanding of robust and reliable data access practices.