ASP.NET Core 6 configuration settings stored in a SQL database and acts just like appsettings.json

Program.cs

//Snippet from Program.cs which adds the provider and sets up a Settings class to map the settings

using ErikNoren.Configuration;
using TestMvcWebApplication;

var builder = WebApplication.CreateBuilder(args);
builder.Configuration.AddSqlDatabase(config =>
{
    //We can get the connection string from previously added ConfigurationProviders to use in setting this up
    config.ConnectionString = builder.Configuration.GetConnectionString("DemoDatabase");
    config.RefreshInterval = TimeSpan.FromMinutes(1);
});

//Settings from all sources will be merged together. Since the database provider is added after the default
//providers it can be used to override settings from those other providers.
builder.Services.Configure<Settings>(builder.Configuration.GetSection("AppSettings"));

//remaining setup omitted

SqlConfigurationExtensions.cs

using Microsoft.Extensions.Configuration;

namespace ErikNoren.Configuration;

public static class SqlDatabaseConfigurationExtensions
{
    public static IConfigurationBuilder AddSqlDatabase(this IConfigurationBuilder builder, Action<SqlDatabaseConfigurationSource>? configurationSource)
        => builder.Add(configurationSource);
}

SqlConfigurationProvider.cs

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;

namespace ErikNoren.Configuration;

public class SqlDatabaseConfigurationProvider : ConfigurationProvider, IDisposable
{
    private readonly Timer? _refreshTimer = null;

    public SqlDatabaseConfigurationSource Source { get; }

    public SqlDatabaseConfigurationProvider(SqlDatabaseConfigurationSource source)
    {
        Source = source;

        if (Source.RefreshInterval.HasValue)
            _refreshTimer = new Timer(_ => ReadDatabaseSettings(true), null, Timeout.Infinite, Timeout.Infinite);
    }

    public override void Load()
    {
        if (string.IsNullOrWhiteSpace(Source.ConnectionString))
            return;

        ReadDatabaseSettings(false);

        if (_refreshTimer != null && Source.RefreshInterval.HasValue)
            _refreshTimer.Change(Source.RefreshInterval.Value, Source.RefreshInterval.Value);
    }

    private void ReadDatabaseSettings(bool isReload)
    {
        using var connection = new SqlConnection(Source.ConnectionString);
        var command = new SqlCommand("SELECT SettingKey, SettingValue FROM dbo.Settings WHERE IsActive = 1", connection);

        try
        {
            connection.Open();
            var reader = command.ExecuteReader();

            var settings = new Dictionary<string, string?>(StringComparer.OrdinalIgnoreCase);

            while(reader.Read())
            {
                try
                {
                    settings[reader.GetString(0)] = reader.GetString(1);
                }
                catch (Exception readerEx)
                {
                    System.Diagnostics.Debug.WriteLine(readerEx);
                }
            }

            reader.Close();

            if (!isReload || !SettingsMatch(Data, settings))
            {
                Data = settings;

                if (isReload)
                    OnReload();
            }
        }
        catch (Exception sqlEx)
        {
            System.Diagnostics.Debug.WriteLine(sqlEx);
        }
    }

    private bool SettingsMatch(IDictionary<string, string?> oldSettings, IDictionary<string, string?> newSettings)
    {
        if (oldSettings.Count != newSettings.Count)
            return false;

        return oldSettings
            .OrderBy(s => s.Key)
            .SequenceEqual(newSettings.OrderBy(s => s.Key));
    }

    public void Dispose()
    {
        _refreshTimer?.Change(Timeout.Infinite, Timeout.Infinite);
        _refreshTimer?.Dispose();
    }
}

SqlConfigurationSource.cs

using Microsoft.Extensions.Configuration;

namespace ErikNoren.Configuration;

public class SqlDatabaseConfigurationSource : IConfigurationSource
{
    public string? ConnectionString { get; set; }
    public TimeSpan? RefreshInterval { get; set; }

    public IConfigurationProvider Build(IConfigurationBuilder builder)
        => new SqlDatabaseConfigurationProvider(this);
}