Centralizing SQL Connection Handling with a DatabaseHelper Class

For applications that rely on SQL Server connectivity for real-time data processing and high-volume data operations, transient connection failures can occur due to network issues, server load, timeouts, etc. Implementing a custom SQL connection retry mechanism ensures that these temporary disruptions do not compromise application stability.

In this write-up, I look at a custom GetResilientConnection method in ADO.NET when connecting to SQL Server using SqlClient to ensure my application handles connection disruptions gracefully by implementing automated custom retry logic that allow my applications to attempt reconnections with controlled delays and limits.

Transient Connection Failures
These faults are temporary connectivity issues, often due to network instability or database server connectivity issues. While these errors are typically resolved once the network or server recovers, they can disrupt an application's flow unless handled properly. Some general transient connection examples can include:

• Network-related errors (e.g., error code 40613)
• SQL Server unavailability (e.g., error code 10928)

Implementing Custom Retry Logic in ADO.NET
A simple yet effective way to enhance resilience is by implementing retry logic. When a transient error occurs, the application should wait for a brief period before attempting to reconnect. This code defines a class named DatabaseHelper, which provides a method called GetResilientConnection. This method is responsible for handling transient database connection failures by implementing a retry mechanism.

Sample Retry Pattern Code Example

using System;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net.Sockets;
using System.Threading;

public class DatabaseHelper
{
  public static SqlConnection GetResilientConnection(string connectionString)
  {
    int maxRetries = 3;  // maximum retry attempts
    int delay = 2000;    // initial delay (2 seconds)

    for (int i = 0; i < maxRetries; i++)
    {
      try
      {
        // attempt to establish a SQL connection
        var connection = new SqlConnection(connectionString);
        connection.Open(); // open the connection
        return connection; // successfully connected, return the connection
      }
      catch (Exception ex) when (IsTransientError(ex)) // catch broader transient errors
      {
        Console.WriteLine($"Transient error encountered: {ex.Message}");
        Console.WriteLine($"Retrying in {delay / 1000} seconds...");

        Thread.Sleep(delay); // wait before retrying (exponential backoff)
        delay *= 2; // double the delay for the next retry
      }
    }

    // if all retries fail, throw an exception indicating failure
    throw new Exception("Max retry attempts exceeded.");
  }

  private static bool IsTransientError(Exception ex)
  {
    // some common SQL server transient error codes
    int[] transientErrors = { 40613, 10928, 40197, 40501, 49918, 1205, 233, -2 };

    if (ex is SqlException sqlEx)
    {
      // check if any of the SQL error numbers match the transientErrors errors array
      if (sqlEx.Errors.Cast<SqlError>().Any(e => transientErrors.Contains(e.Number)))
      {
        return true;
      }
    }

    // none of the erros codes in the transientErrors errors array were found
    // so look at these other types 
    // additional transient errors based on exception types
    return ex is TimeoutException  // SQL command timeout
      || ex is IOException       // general network failure
      || ex is InvalidOperationException // connection unexpectedly closed
      || ex is SocketException; // low-level socket failure
  }
}

This GetResilientConnection example method does the following:
• Attempts to establish a SQL Server connection using the provided connection string.
• Retries up to 3 times if a transient failure occurs, with an increasing wait time between attempts - waits (2s → 4s → 8s) before retrying which can improve application server recovery chances. Retries up to 3 times before throwing an exception.
• Uses IsTransientError to detect common transient errors, including SQL timeouts, deadlocks, and network issues. Detects timeouts, network failures, and connection drops.
• Logs the failure and waits before retrying, using exponential backoff (doubling the delay each time).
• Throws an exception if all retry attempts fail.
• Encapsulated in a reusable helper class and can be called from multiple parts of the application for consistent connection handling.

Example of GetResilientConnection Usage
Instead of rewriting SQL connectivity retry logic everywhere in my application where I need to make a SQL connection, I can simply call DatabaseHelper.GetResilientConnection. When another part of my application references DatabaseHelper, it is using this class as a utility to establish SQL connections with built-in resilience.

using (var connection = DatabaseHelper.GetResilientConnection(connectionString))
{
  Console.WriteLine("Connected successfully!");

  // use the connection here
  // and do your data processing stuff
  // in the context of the connection
}

This approach promotes code maintainability and follows the Single Responsibility Principle (SRP) and ensures consistent handling of connection failures across my application.

The Encapsulation Part
I created the custom DatabaseHelper class to centralize database logic and avoid repeating connection code throughout my application. By keeping (encapsulating) this logic in one place, I improve code maintainability and make it easier to update connection handling in the future (only one place if I want to add functionality or fix bugs).

Doing this also ensures that database connection logic remains separate from the rest of my application logic, which helps with making the codebase cleaner and more modular.

Within this class, the IsTransientError method is marked private, meaning transient error detection is only accessible inside DatabaseHelper, which ensures that SQL connectivity error handling is consistent and can only be leveraged by referencing the DatabaseHelper class.

Basically - since DatabaseHelper.GetResilientConnection(connectionString) is public and static, any part of my application that needs a resilient SQL connection can simply call it. This prevents duplicate code and ensures consistent retry logic across the application.

Why Use It
• Improves database connection reliability in case of temporary issues.
• Ensures automatic retry instead of failing immediately.
• Centralized connection handling reduces redundant code across my application.

Conclusion
By creating and using GetResilientConnection, my application can gracefully handle transient failures and improve reliability without requiring manual intervention and ensures that application database connectivity remains stable(ish). Proper logging and exception handling within the retry mechanism further help diagnose persistent issues, making it a valuable strategy for robust database interactions.

Comments

[Spyros Ponaris]

Thanks for sharing! I’m really happy to see people using ADO.NET and knowing how to work with the good old SqlConnection. Unfortunately, many newer developers aren’t aware of the underlying benefits it provides.
If I may, I’d like to recommend a couple of small changes to your code. For example, avoiding the use of static and use Dependency Injection instead , or perhaps creating an extension method for better reusability. Additionally, using Task to make it asynchronous might be a nice improvement.
Using a CancellationToken would also add more flexibility and control.

for (int attempt = 0; attempt < maxRetries; attempt++)
{
cancellationToken.ThrowIfCancellationRequested();
try
{
var connection = new SqlConnection(connectionString);
await connection.OpenAsync(cancellationToken); // Open the connection asynchronously
return connection; // Successfully connected, return the connection
}
catch (Exception ex) when (IsTransientError(ex)) // Handle transient errors
{
await Task.Delay(delay);
delay *= 2;
}
}