Click here to Skip to main content
15,908,111 members
Articles / Programming Languages / C#
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (7 votes)
29 Mar 2024CPOL1 min read 12.1K   59   17   2
ASP.NET API with a Custom SQL Configuration Provider
In this project, a custom configuration provider with reload functionality has been implemented. The API project is a empty default ASP.NET project, the SQL provider is realized within the project under the name: SQLConfigurationProvider, leveraging Dapper for data retrieval from SQL.

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.

C#
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Italy Italy
I'm a software engineer, specializing in backend development and distributed systems. I have extensive experience in designing and implementing resilient, responsive, scalable, and maintainable systems using C#, .NET on top of cutting-edge technologies.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA27-Mar-24 8:19
professionalȘtefan-Mihai MOGA27-Mar-24 8:19 
GeneralRe: My vote of 5 Pin
GabrieleTronchin29-Mar-24 6:23
GabrieleTronchin29-Mar-24 6:23 
Thanks for your support!

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.