Configuration providers in .NET

One interesting feature of the .NET ecosystem is the ability to configure the application using Microsoft.Extensions.Options library. It allows developers to easily manage and inject application settings from different sources, such as appsettings.json files, environment variables, command-line arguments, or even custom sources.

Using a SQL database as an example, in this article I’m going to explain how to create a custom provider for Microsoft.Extensions.Options that reads key-valued configurations from a table that also ensure values are refreshed in-memory if the table content changes.

If you want to use an approach for which you don’t have a provider available, like getting configurations from some custom API inside your company, you can easily use this example as a template to implement whatever requirements you may have.

Solution setup

For starters, we’ll need a SQL database with a table to store the application settings. In this example, which I have available on GitHub, I’m going to use SQL Server (feel free to use your favorite database) and create an ApplicationSettings table with two columns to store the keys and corresponding values.

create table ApplicationSettings(
  [Key] nvarchar(256) not null primary key,
  [Value] nvarchar(256) not null
)

To keep things simple, we are going to create a console application and use the NuGet Microsoft.Extensions.Hosting to setup the host (dependency injection, logging and configurations) but this code is fully compatible with any application using ASP.NET Core 2 or later.

Create a new console application and register the following NuGets:

• Microsoft.Extensions.Hosting — for hosting setup;
• Microsoft.data.SqlClient — to access our SQL Server database (or another provider if a different database);
• Dapper — optional, just to make it easier to map SQL results to C# entities;

You can also configure the *.csproj file to include the application settings files when publishing.

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <None Include="*.config;*.json" CopyToOutputDirectory="PreserveNewest" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.1.24" />
    <PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.2" />
    <PackageReference Include="Microsoft.Extensions.Hosting" Version="8.0.0" />
  </ItemGroup>

</Project>

Add a new appsettings.json file to the project and include some options, like the connection string to the SQL Server database and some custom settings — in this example we are going to output a simple message to the console.

{
  "ConnectionStrings": {
    "ArticleDotNetConfiguration": "Data Source=localhost;Database=ArticleDotNetConfiguration;User Id=sa;Password=abcd1234;Encrypt=false;"
  },
  "Example": {
    "Message": "Hello world, from appsettings.json!"
  }
}

Create a class representing the example options.

public record ExampleOptions
{
    public string Message { get; init; }
}

Open the Program.cs file, create a host with pre-configured defaults, configure the ExampleOptions and output the message that was loaded from the appsettings.json file.

var builder = Host.CreateApplicationBuilder(args);

builder.Services.Configure<ExampleOptions>(
    builder.Configuration.GetSection("Example")
);

using var host = builder.Build();

var options = host.Services.GetRequiredService<IOptions<ExampleOptions>>().Value;
Console.WriteLine(options.Message);

Running the application, the message stored in the appsettings.json file should be displayed.

Configuration provider

To create a custom configuration provider you need to implement two interfaces:

• IConfigurationSource — will be added to the configuration manager and is used to store options (like a connection string) and build provider instances.
• IConfigurationProvider — knows how to load the application settings and has methods to get or set configuration values by a given key. Providers usually extend the class ConfigurationProvider which makes it easier to store settings in-memory.

Create a new class SqlServerConfigurationSource, add a property for the connection string and implement the interface IConfigurationSource without any logic, for now.

public class SqlServerConfigurationSource : IConfigurationSource
{
    public string ConnectionString { get; set; }

    public IConfigurationProvider Build(IConfigurationBuilder builder) => throw new NotImplementedException();
}

Create a new class SqlServerConfigurationProvider, passing the configuration source as a constructor parameter and extending the class ConfigurationProvider.

Override the Load method, reading application settings from the SQL table and store the results into the Data dictionary.

public class SqlServerConfigurationProvider(
    SqlServerConfigurationSource source
) : ConfigurationProvider
{
    public override void Load()
    {
        using var connection = new SqlConnection(source.ConnectionString);
        connection.Open();

        Data = connection.Query<(string Key, string Value)>(@"
select 
    [Key],
    [Value]
from ApplicationSettings").ToDictionary(e => e.Key, e => e.Value);
    }
}

Reopen the SqlServerConfigurationSource.cs file and implement the Build method, returning a new instance of the provider.

public class SqlServerConfigurationSource : IConfigurationSource
{
    public string ConnectionString { get; set; }

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

Open the Program.cs file and add the SQL Server configuration source to the host builder. Don’t forget to read the connection string from the existing sources.

var builder = Host.CreateApplicationBuilder(args);

var connectionString = builder.Configuration.GetConnectionString("ArticleDotNetConfiguration");
builder.Configuration.Add<SqlServerConfigurationSource>(source =>
{
    source.ConnectionString = connectionString;
});

builder.Services.Configure<ExampleOptions>(
    builder.Configuration.GetSection("Example")
);

// ...

Since the SQL Server provider was the last source added to the configuration manager, it will have priority over the existing ones. Let’s try to override the message by adding the key Example:Message with a value like Hello world, from database!.

insert into ApplicationSettings values (
  'Example:Message',
  'Hello world, from database!'
)

Running the application, the message stored in the ApplicationSettings table should be displayed.

Reloading data

In our previous code, we were getting an IOptions from the container to get the application settings. This is fine but what if the developer is using an IOptionsMonitor to always get the latest configurations?

The current provider implementation never updates the Data property after the initial load, so we let’s change that.

There are multiple ways to detect data changes from a SQL Server database, but for simplicity we are going to implement a simple worker that will be constantly loading application settings, compare what’s in memory and if changes are detected, invoke the base method OnReload that will trigger a refresh in all listeners.

Let’s start by changing the Program.cs file and create a task that will be writing the message at intervals so we’ll see a different message when we update the table. Don’t forget to use an IOptionsMonitor instead of an IOptions.

var builder = Host.CreateApplicationBuilder(args);

var connectionString = builder.Configuration.GetConnectionString("ArticleDotNetConfiguration");
builder.Configuration.Add<SqlServerConfigurationSource>(source =>
{
    source.ConnectionString = connectionString;
});

builder.Services.Configure<ExampleOptions>(
    builder.Configuration.GetSection("Example")
);

using var host = builder.Build();

var appOptions = host.Services.GetRequiredService<IOptionsMonitor<ExampleOptions>>();

await Task.Run(async () =>
{
    do
    {
        var options = appOptions.CurrentValue;

        Console.WriteLine(options.Message);
        await Task.Delay(5_000);
    } while (true);
});

Open the provider class and do the following changes:

• Add a private CancellationTokenSource variable that will be used to cancel the worker execution;
• Add a private Task variable that will be the responsible to refresh the application settings when they change;
• Extract the logic that loads the data into a private method, so both Load method and worker can use that logic — change it to asynchronous code and do a comparison with previous loaded data, so method OnReload can be triggered;
• Change the Load method to use the extracted logic and create a worker that will refresh data at intervals — don’t forget to add some exception handling code;
• Implement the disposable pattern to release unmanaged resources;

The SqlServerConfigurationProvider should now be as follows:

public class SqlServerConfigurationProvider(
    SqlServerConfigurationSource source
) : ConfigurationProvider, IDisposable
{
    private CancellationTokenSource _cts;
    private Task _refreshWorker;

    #region IDisposable

    ~SqlServerConfigurationProvider() => Dispose(false);

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing)
    {
        if (disposing)
        {
            _cts?.Cancel();

            if (_refreshWorker is not null)
            {
                try
                {
                    _refreshWorker.ConfigureAwait(false)
                        .GetAwaiter().GetResult();
                }
                catch (OperationCanceledException)
                {
                    // expected exception due to cancellation
                }
                catch (Exception e)
                {
                    Debug.WriteLine($"Unhandled exception when waiting for the worker to stop: {e}");
                }
            }

            _cts?.Dispose();
        }

        _refreshWorker = null;
        _cts = null;
    }

    #endregion

    public override void Load()
    {
        LoadAsync(CancellationToken.None).ConfigureAwait(false)
            .GetAwaiter().GetResult();

        if (_cts is not null) 
            return;

        _cts = new CancellationTokenSource();

        var ct = _cts.Token;
        _refreshWorker ??= Task.Run(async () =>
        {
            do
            {
                await Task.Delay(15_000, ct);
                try
                {
                    await LoadAsync(ct);
                }
                catch (Exception e)
                {
                    Debug.WriteLine($"Unhandled exception when refreshing database settings: {e}");
                }
            } while (!ct.IsCancellationRequested);
        }, ct);
    }

    private async Task LoadAsync(CancellationToken ct)
    {
        Dictionary<string, string> currentData;
        await using (var connection = new SqlConnection(source.ConnectionString))
        {
            await connection.OpenAsync(ct);

            currentData = (await connection.QueryAsync<(string Key, string Value)>(new CommandDefinition(@"
select 
    [Key],
    [Value]
from ApplicationSettings", cancellationToken: ct))).ToDictionary(e => e.Key, e => e.Value);
        }

        if (HasSameData(currentData))
            return;

        Data = currentData;

        OnReload();
    }

    private bool HasSameData(Dictionary<string, string> currentData)
    {
        if (Data.Count != currentData.Count)
            return false;

        foreach (var (key, value) in currentData)
        {
            if (!Data.TryGetValue(key, out var previousValue) || previousValue != value)
                return false;
        }

        return true;
    }
}

If you now start the application and update or delete the value while running, you should see the task writing different messages.

-- update the settings
update ApplicationSettings
set
  [Value] = 'Hello world, from DB!'
where
  [Key] = 'Example:Message';

-- or delete them
delete ApplicationSettings
where
  [Key] = 'Example:Message';

Reusing the provider

We now have a provider ready to read application settings from a SQL Server database, but I’m sure you are thinking it clearly could be more generic and just work with any SQL database.

Your line of thought is correct, we can easily do that just by changing the options from a connection string to having a connection factory, adding a property for the SQL code and even a TimeSpan for refresh interval. We should also add an exception handling callback (instead of writing into the debugger) and create extension methods to make registration into the configuration manager more readable.

The final code, which is available on GitHub, could be the as follows:

public class SqlExceptionContext
{
    public Exception Exception { get; set; }

    public SqlConfigurationProvider Provider { get; set; }
}

public static class SqlConfigurationBuilderExtensions
{
    private const string SqlExceptionHandlerKey = "SqlExceptionHandler";

    public static IConfigurationBuilder AddSql(
        this IConfigurationBuilder builder,
        Func<DbConnection> connectionFactory,
        string sql = null,
        TimeSpan? refreshInterval = null
    )
    {
        ArgumentNullException.ThrowIfNull(builder);
        ArgumentNullException.ThrowIfNull(connectionFactory);

        return builder.Add<SqlConfigurationSource>(source =>
        {
            source.ConnectionFactory = connectionFactory;

            if(sql is not null)
                source.Sql = sql;

            if (refreshInterval is not null)
                source.RefreshInterval = refreshInterval.Value;
        });
    }

    public static IConfigurationBuilder SetSqlExceptionHandler(
        this IConfigurationBuilder builder,
        Action<SqlExceptionContext> handler
    )
    {
        ArgumentNullException.ThrowIfNull(builder);

        builder.Properties[SqlExceptionHandlerKey] = handler;

        return builder;
    }

    public static Action<SqlExceptionContext> GetSqlExceptionHandler(this IConfigurationBuilder builder)
    {
        ArgumentNullException.ThrowIfNull(builder);

        return builder.Properties.TryGetValue(SqlExceptionHandlerKey, out var value)
            ? value as Action<SqlExceptionContext>
            : null;
    }
}

public class SqlConfigurationSource : IConfigurationSource
{
    public Func<DbConnection> ConnectionFactory { get; set; }

    public string Sql { get; set; } = @"
select 
    [Key],
    [Value]
from ApplicationSettings";

    public TimeSpan RefreshInterval { get; set; } = TimeSpan.FromSeconds(15);

    public IConfigurationProvider Build(IConfigurationBuilder builder)
    {
        var exceptionHandler = builder.GetSqlExceptionHandler() ?? (ctx =>
        {
            Debug.WriteLine($"Unhandled SQL exception: {ctx.Exception}");
        });
        return new SqlConfigurationProvider(this, exceptionHandler);
    }
}

public class SqlConfigurationProvider(
    SqlConfigurationSource source,
    Action<SqlExceptionContext> exceptionHandler
) : ConfigurationProvider, IDisposable
{
    private CancellationTokenSource _cts;
    private Task _refreshWorker;

    #region IDisposable

    ~SqlConfigurationProvider() => Dispose(false);

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing)
    {
        if (disposing)
        {
            _cts?.Cancel();

            if (_refreshWorker is not null)
            {
                try
                {
                    _refreshWorker.ConfigureAwait(false)
                        .GetAwaiter().GetResult();
                }
                catch (OperationCanceledException)
                {
                    // expected exception due to cancellation
                }
                catch (Exception e)
                {
                    exceptionHandler(new SqlExceptionContext
                    {
                        Exception = e,
                        Provider = this
                    });
                }
            }

            _cts?.Dispose();
        }

        _refreshWorker = null;
        _cts = null;
    }

    #endregion

    public override void Load()
    {
        LoadAsync(CancellationToken.None).ConfigureAwait(false)
            .GetAwaiter().GetResult();

        if (_cts is not null)
            return;

        _cts = new CancellationTokenSource();

        var ct = _cts.Token;
        _refreshWorker ??= Task.Run(async () =>
        {
            do
            {
                await Task.Delay(source.RefreshInterval, ct);
                try
                {
                    await LoadAsync(ct);
                }
                catch (Exception e)
                {
                    exceptionHandler(new SqlExceptionContext
                    {
                        Exception = e,
                        Provider = this
                    });
                }
            } while (!ct.IsCancellationRequested);
        }, ct);
    }

    private async Task LoadAsync(CancellationToken ct)
    {
        Dictionary<string, string> currentData;
        await using (var connection = source.ConnectionFactory())
        {
            await connection.OpenAsync(ct);

            currentData = (await connection.QueryAsync<(string Key, string Value)>(
                new CommandDefinition(source.Sql, cancellationToken: ct)
            )).ToDictionary(e => e.Key, e => e.Value);
        }

        if (HasSameData(currentData))
            return;

        Data = currentData;

        OnReload();
    }

    private bool HasSameData(Dictionary<string, string> currentData)
    {
        if (Data.Count != currentData.Count)
            return false;

        foreach (var (key, value) in currentData)
        {
            if (!Data.TryGetValue(key, out var previousValue) || previousValue != value)
                return false;
        }

        return true;
    }
}

Conclusion

In this article I explained how to create a custom configuration provider for the Microsoft.Extensions.Options library, that could be used in any .NET application that uses Microsoft.Extensions.Hosting, including ASP.NET Core applications.

I used a SQL Server database as an example, but could be easily anything, like getting settings from an internal API and even Windows registry.