Validating Connection Strings on .NET Startup

There exist extension points in .NET that allow you to verify the starting state of your application. Validating configuration can be helpful when you know that without certain elements, there’s no reason to start the application. One of those configuration values is connection strings, but there’s a catch. You may have set the value in the configuration, but the value either is to a non-existent resource or the resource is inaccessible.

In this short post, we’ll write an extension method and a class that will allow you to test your database connection on startup and fail the application if you can’t connect.

The Initial Host Configuration

Let’s look at the final solution and work our way backward. We have our application host, and we register a connection string validator. Our validation will get the connection string, determine the provider, and attempt a connection. If successful, the app starts. If unsuccessful, the app exits.

using System.Data.Common;
using Microsoft.Data.SqlClient;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Options;
using Microsoft.IdentityModel.Tokens;
using WorkerServiceDatabase;

IHost host = Host.CreateDefaultBuilder(args)
    .ConfigureServices(services =>
    {
        services
            .ValidateConnectionStrings()
            .ValidateOnStart();
        
        services.AddHostedService<Worker>();
    })
    .Build();

host.Run();

Neat, let’s see what challenges we have to overcome.

The ConnectionString Configuration

.NET allows you to bind a configuration to a strongly-typed object, but in the case of ConnectionStrings, that’s a well-known JSON section. So how do we accomplish that? Well, let’s have a look at our working configuration.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
      "Sqlite": "Data Source=database.db",
      "SqlServer": "Data Source=localhost,11433;Initial Catalog=Northwind;User Id=sa;Password=Pass123!;Encrypt=false"
    }
}

ConnectionStrings looks like a JSON Dictionary, so we can bind to a class that inherits from Dictionary<string,string>.

public class ConnectionStrings
    : Dictionary<string,string>
{}

Awesome, but there’s a problem. None of our connection strings declare the database provider. So here’s the first decision you’ll want to make for your use case: “How do you map connection string names to their database providers?”

My approach to this issue was explicitly mapping the keys to a database provider in my ConnectionStrings constructor.

public class ConnectionStrings
    : Dictionary<string,string>
{
    public ConnectionStrings()
    {
        // these are the key names
        DbProviderFactories.RegisterFactory("Sqlite", SqliteFactory.Instance);
        DbProviderFactories.RegisterFactory("SqlServer", SqlClientFactory.Instance);
    }
}

These database factories come from the NuGet packages of Microsoft.Data.SqlClient and Microsoft.Data.Sqlite. You’ll need to add other database factories for providers like PostgreSQL, MySQL, or Oracle.

Next, let’s write our Validate method. This method will create a new connection using our connection strings and then attempt to open a connection to the database.

public class ConnectionStrings
    : Dictionary<string,string>
{
    public ConnectionStrings()
    {
        // these are the key names
        DbProviderFactories.RegisterFactory("Sqlite", SqliteFactory.Instance);
        DbProviderFactories.RegisterFactory("SqlServer", SqlClientFactory.Instance);
    }
    
    public bool Validate()
    {
        // can't inject logger :(
        var logger = LoggerFactory
            .Create(cfg => cfg.AddConsole().AddDebug())
            .CreateLogger("ConnectionStrings");
        
        List<Exception> errors = new();
        foreach (var (key, connectionString) in this)
        {
            try
            {
                var factory = DbProviderFactories.GetFactory(key);
                using var connection = factory.CreateConnection();
                if (connection is null) {
                    throw new Exception($"\"{key}\" did not have a valid database provider registered");
                }

                connection.ConnectionString = connectionString;
                connection.Open();
            }
            catch (Exception e)
            {
                var message = $"Could not connect to \"{key}\".";
                logger.LogError(message);
                errors.Add(new Exception(message, e));
            }
        }

        return errors.IsNullOrEmpty();
    }
}

The validate method loops over the values in our ConnectionString section, finding the DbProviderFactory and creating a connection.

I had to create a new logger factory so you could see the failed connection, but you could remove that if you want an all-or-nothing approach.

We have our class; now let’s wire it up.

The ConnectionString Validation Method

This section is pretty straightforward, we’ll write an extension method to bind our ConnectionStrings configuration section to our ConnectionStrings class and call the validate method.

public static class ConnectionStringExtensions
{
    public static OptionsBuilder<ConnectionStrings> 
        ValidateConnectionStrings(this IServiceCollection services)
    {
        return services
            .AddOptions<ConnectionStrings>()
            .BindConfiguration("ConnectionStrings")
            .Validate(c => c.Validate(), "Could not connect to 1 or more databases.");
    }
}

Easy peasy.

The Complete Solution

So how does this all look in one file? Where here it is.

using System.Data.Common;
using Microsoft.Data.SqlClient;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Options;
using Microsoft.IdentityModel.Tokens;
using WorkerServiceDatabase;

IHost host = Host.CreateDefaultBuilder(args)
    .ConfigureServices(services =>
    {
        services 
            // validate connection strings
            .ValidateConnectionStrings()
            .ValidateOnStart();
        
        services.AddHostedService<Worker>();
    })
    .Build();

host.Run();

public static class ConnectionStringExtensions
{
    public static OptionsBuilder<ConnectionStrings> 
        ValidateConnectionStrings(this IServiceCollection services)
    {
        return services
            .AddOptions<ConnectionStrings>()
            .BindConfiguration("ConnectionStrings")
            .Validate(c => c.Validate(), "Could not connect to 1 or more databases.");
    }
}

public class ConnectionStrings
    : Dictionary<string,string>
{
    public ConnectionStrings()
    {
        // these are the key names
        DbProviderFactories.RegisterFactory("Sqlite", SqliteFactory.Instance);
        DbProviderFactories.RegisterFactory("SqlServer", SqlClientFactory.Instance);
    }
    
    public bool Validate()
    {
        // can't inject logger :(
        var logger = LoggerFactory
            .Create(cfg => cfg.AddConsole().AddDebug())
            .CreateLogger("ConnectionStrings");
        
        List<Exception> errors = new();
        foreach (var (key, connectionString) in this)
        {
            try
            {
                var factory = DbProviderFactories.GetFactory(key);
                using var connection = factory.CreateConnection();
                if (connection is null) {
                    throw new Exception($"\"{key}\" did not have a valid database provider registered");
                }

                connection.ConnectionString = connectionString;
                connection.Open();
            }
            catch (Exception e)
            {
                var message = $"Could not connect to \"{key}\".";
                logger.LogError(message);
                errors.Add(new Exception(message, e));
            }
        }

        return errors.IsNullOrEmpty();
    }
}

There you have it.

Important. The validation process in .NET uses a ValidationHostedService, so it’s important that if you have any hosted service that relies on your configuration, then you should register them after your validation runs. If you don’t you’ll have your hosted service start before validation is completed.

Summary

Using .NET configuration extension points, we can validate the most critical resources. For example, using the DbProviderFactories class, we can register and support many database providers. However, you must make a design choice, as the ConnectionStrings section in the configuration doesn’t allow the provider to be set. Ultimately, my approach wasn’t too bad, but it could be better. Also, I found that the ConnectionStrings class instance must have a parameterless constructor and could not inject an instance of ILogger, which is why I created my own. If you are fine with a “pass/fail” approach, feel free to exclude the logger, which might lead to frustrating debugging to determine which connection string failed.

Thanks for reading, and I hope you enjoyed this post.