Ado.Net Error Handling

Robust error handling is essential when working with databases. Ado.Net provides a set of exceptions that allow you to respond to problems such as connection failures, command timeouts, and constraint violations.

Common Exceptions

• SqlException – Thrown for SQL Server‑specific errors.
• InvalidOperationException – Occurs when the connection state is invalid for the requested operation.
• DbException – Base class for database‑related exceptions.
• TimeoutException – Raised when a command exceeds its CommandTimeout.

Try‑Catch Pattern

Wrap database calls in a try/catch block and inspect the exception for details.

using System;
using System.Data.SqlClient;

public class ErrorHandlingDemo
{
    private const string ConnString = "Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=True";

    public void Run()
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                conn.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT * FROM InvalidTable", conn))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader[0]);
                        }
                    }
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"SQL Error {ex.Number}: {ex.Message}");
            // Log the error, rollback transactions, etc.
        }
        catch (InvalidOperationException ex)
        {
            Console.WriteLine($"Invalid Operation: {ex.Message}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Unexpected error: {ex.Message}");
        }
    }
}

Using using for Automatic Disposal

Ensuring that connections and commands are disposed correctly reduces the likelihood of resource leaks that can cause exceptions.

Handling Transaction Errors

When using transactions, roll back on failure to maintain data integrity.

using (SqlConnection conn = new SqlConnection(ConnString))
{
    conn.Open();
    SqlTransaction tx = conn.BeginTransaction();

    try
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Orders ...", conn, tx))
        {
            cmd.ExecuteNonQuery();
        }

        // Additional commands...

        tx.Commit();
    }
    catch (Exception)
    {
        tx.Rollback();
        throw;
    }
}

Best Practices

• Log full exception details (stack trace, error code).
• Do not expose internal error messages to end users.
• Implement retry logic for transient failures (e.g., using Polly).
• Validate inputs before executing commands to avoid SQL injection.