Skip to content

Instantly share code, notes, and snippets.

@HamedFathi
Forked from ErikNoren/Program.cs
Created April 27, 2023 10:21
Show Gist options
  • Save HamedFathi/f83bc4e00f4f902ff5522ad91b2d4470 to your computer and use it in GitHub Desktop.
Save HamedFathi/f83bc4e00f4f902ff5522ad91b2d4470 to your computer and use it in GitHub Desktop.
ASP.NET Core 6 configuration settings stored in a SQL database and acts just like appsettings.json
//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
using Microsoft.Extensions.Configuration;
namespace ErikNoren.Configuration;
public static class SqlDatabaseConfigurationExtensions
{
public static IConfigurationBuilder AddSqlDatabase(this IConfigurationBuilder builder, Action<SqlDatabaseConfigurationSource>? configurationSource)
=> builder.Add(configurationSource);
}
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();
}
}
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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment