Implementing a Custom IConfiguration Provider with MS SQL Server in C#

• Download source - 12 KB
• Download latest source (GitHub)

Introduction

In the realm of modern software development, configuration management plays a pivotal role in ensuring flexibility, scalability, and maintainability of applications. Microsoft's .NET ecosystem offers a robust configuration framework through the IConfiguration interface, allowing developers to access configuration data from various sources. However, when dealing with complex systems or enterprise-grade applications, a need may arise for a custom configuration provider tailored to specific requirements.

In this article, we'll explore the implementation of a custom IConfiguration provider leveraging MS SQL Server to store configuration data. This solution offers the advantage of centralizing configuration settings in a database, enabling dynamic updates without requiring application restarts.

Background

Configuration providers allow developers to retrieve configuration data from various sources, such as JSON files, environment variables, and Azure Key Vault. These configuration sources are abstracted behind a unified API.

Useful link:

https://learn.microsoft.com/en-us/dotnet/core/extensions/custom-configuration-provider

Setting Up the Project

For demonstration purposes, let's consider an empty default ASP.NET project. We'll implement our custom configuration provider, named SQLConfigurationProvider, within this project. To interact with the SQL database, we'll utilize Dapper, a lightweight Object-Relational Mapping (ORM) library for .NET.

namespace SQLConfigurationProvider;

public class SqlServerConfigurationProvider : ConfigurationProvider, IDisposable
{
    private SqlServerConfigurationSource _source { get; }

    private readonly Timer? _timer;

    public SqlServerConfigurationProvider(SqlServerConfigurationSource source)
    {
        _source = source;

        if (_source.ReloadPeriodically)
        {
            _timer = new Timer
            (
                callback: ReloadSettings,
                dueTime: TimeSpan.FromSeconds(10),
                period: TimeSpan.FromSeconds(_source.PeriodInSeconds),
                state: null
            );
        }
    }

    public override void Load()
    {
        using var connection = new SqlConnection(_source.ConnectionString);

        //TODO Just a sample of query and set prop
        var myConfigurationOnSQL = connection.Query("select * from 1").ToDictionary(x => (string)x.Key, x => (string)x.Value);
        Data = myConfigurationOnSQL;

    }

    private void ReloadSettings(object? state)
    {
        Load();
        OnReload();
    }

    public void Dispose()
    {
        _timer?.Dispose();
    }
}

Reloading Configuration Data

One of the key features of a robust configuration provider is the ability to reload configuration data without restarting the application. To achieve this with our SQLConfigurationProvider, we had implement a mechanism to periodically refresh the configuration data from the database. 

Conclusion

In this article, we've explored the implementation of a custom IConfiguration provider using MS SQL Server as the storage backend. By leveraging Dapper for database interaction, we've created a flexible solution that allows dynamic configuration updates without application restarts. This approach can be particularly beneficial for enterprise-grade applications with complex configuration requirements.