Stop SqlDependency in custom ASP.NET Core Configuration Provider

Question

I have written a custom configuration provider to load ASP.NET Core configuration from a database table as per the instructions here:

ASP.Net Custom Configuration Provider

My provider uses SqlDependency to reload configuration should the values in the database change.

The documentation for SqlDependency states that:

The Stop method must be called for each Start call. A given listener only shuts down fully when it receives the same number of Stop requests as Start requests.

What I'm not sure about is how to do this within a custom configuration provider for ASP.NET Core.

Here we go with the code:

DbConfigurationSource

Basically a container for IDbProvider which handles retrieving the data from the database

public class DbConfigurationSource : IConfigurationSource
{
    /// <summary>
    /// Used to access the contents of the file.
    /// </summary>
    public virtual IDbProvider DbProvider { get; set; }


    /// <summary>
    /// Determines whether the source will be loaded if the underlying data changes.
    /// </summary>
    public virtual bool ReloadOnChange { get; set; }

    /// <summary>
    /// Will be called if an uncaught exception occurs in FileConfigurationProvider.Load.
    /// </summary>
    public Action<DbLoadExceptionContext> OnLoadException { get; set; }

    public IConfigurationProvider Build(IConfigurationBuilder builder)
    {
        return new DbConfigurationProvider(this);
    }
}

DbConfigurationDataProvider

This is the class that creates and watches the SqlDependency and loads in the data from the database. This is also where the Dispose() call is where I want to Stop() the SqlDependency. Dispose() is not currently called.

public class DbConfigurationDataProvider : IDbProvider, IDisposable
{        
    private readonly string _applicationName;
    private readonly string _connectionString;

    private ConfigurationReloadToken _reloadToken;

    public DbConfigurationDataProvider(string applicationName, string connectionString)
    {
        if (string.IsNullOrWhiteSpace(applicationName))
        {
            throw new ArgumentNullException(nameof(applicationName));
        }

        if (string.IsNullOrWhiteSpace(connectionString))
        {
            throw new ArgumentNullException(nameof(connectionString));
        }

        _applicationName = applicationName;
        _connectionString = connectionString;

        _reloadToken = new ConfigurationReloadToken();

        SqlDependency.Start(_connectionString);
    }

    void OnDependencyChange(object sender, SqlNotificationEventArgs e)
    {
        var dependency = (SqlDependency)sender;
        dependency.OnChange -= OnDependencyChange;

        var previousToken = Interlocked.Exchange(
            ref _reloadToken,
            new ConfigurationReloadToken());

        previousToken.OnReload();
    }

    public IChangeToken Watch()
    {
        return _reloadToken;
    }

    public List<ApplicationSettingDto> GetData()
    {
        var settings = new List<ApplicationSettingDto>();

        var sql = "select parameter, value from dbo.settingsTable where application = @application";

        using (var connection = new SqlConnection(_connectionString))
        {
            using (var command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("application", _applicationName);

                var dependency = new SqlDependency(command);

                // Subscribe to the SqlDependency event.  
                dependency.OnChange += OnDependencyChange;

                connection.Open();

                using (var reader = command.ExecuteReader())
                {
                    var keyIndex = reader.GetOrdinal("parameter");
                    var valueIndex = reader.GetOrdinal("value");

                    while (reader.Read())
                    {
                        settings.Add(new ApplicationSettingDto
                            {Key = reader.GetString(keyIndex), Value = reader.GetString(valueIndex)});
                    }
                }
            }
        }

        Debug.WriteLine($"{DateTime.Now}: {settings.Count} settings loaded");

        return settings;
    }

    public void Dispose()
    {
        SqlDependency.Stop(_connectionString);
        Debug.WriteLine($"{nameof(WhsConfigurationProvider)} Disposed");
    }
}

DbConfigurationProvider

This class monitors the changeToken in DbConfigurationDataProvider and publishes the new configuration to the application.

public class DbConfigurationProvider : ConfigurationProvider
{
    private DbConfigurationSource Source { get; }

    public DbConfigurationProvider(DbConfigurationSource source)
    {
        Source = source ?? throw new ArgumentNullException(nameof(source));

        if (Source.ReloadOnChange && Source.DbProvider != null)
        {
            ChangeToken.OnChange(
                () => Source.DbProvider.Watch(),
                () =>
                {                        
                    Load(reload: true);
                });                
        }           
    }

    private void Load(bool reload)
    {
        // Always create new Data on reload to drop old keys
        if (reload)
        {
            Data = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
        }

        var settings = Source.DbProvider.GetData();

        try
        {
            Load(settings);
        }
        catch (Exception e)
        {
            HandleException(e);
        }

        OnReload();
    }

    public override void Load()
    {
        Load(reload: false);
    }

    public void Load(List<ApplicationSettingDto> settings)
    {
        Data = settings.ToDictionary(s => s.Key, s => s.Value, StringComparer.OrdinalIgnoreCase);                       
    }

    private void HandleException(Exception e)
    {
            // Removed for brevity
    }     
}

DbConfigurationExtensions

The extension method called to set everything up.

public static class DbConfigurationExtensions
{
    public static IConfigurationBuilder AddDbConfiguration(this IConfigurationBuilder builder, IConfiguration config, string applicationName = "")
    {
        if (string.IsNullOrWhiteSpace(applicationName))
        {
            applicationName = config.GetValue<string>("ApplicationName");
        }

        // DB Server and Catalog loaded from Environment Variables for now
        var server = config.GetValue<string>("DbConfigurationServer");
        var database = config.GetValue<string>("DbConfigurationDatabase");

        if (string.IsNullOrWhiteSpace(server))
        {
            // Removed for brevity
        }

        if (string.IsNullOrWhiteSpace(database))
        {
            // Removed for brevity
        }

        var sqlBuilder = new SqlConnectionStringBuilder
        {
            DataSource = server,
            InitialCatalog = database,
            IntegratedSecurity = true
        };

        return builder.Add(new DbConfigurationSource
        {
             DbProvider = new DbConfigurationDataProvider(applicationName, sqlBuilder.ToString()),                
             ReloadOnChange = true
        } );
    }
}

Finally, the call to set the whole thing up:

public class Program
{
    public static void Main(string[] args)
    {                        
        CreateWebHostBuilder(args).Build().Run();            
    }

    public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
        WebHost.CreateDefaultBuilder(args)
        .ConfigureAppConfiguration((hostingContext, config) =>
    {
        config.AddDbConfiguration(hostingContext.Configuration, "TestApp");            
    }).UseStartup<Startup>();
}

To summarise: How do I ensure the Dispose() method is called within the DbConfigurationDataProvider class?

The only information I have found so far is from here: https://andrewlock.net/four-ways-to-dispose-idisposables-in-asp-net-core/

Which covers how to dispose of objects:

1. Within code blocks with the using statement (Not Applicable)
2. At the end of a request (Not Applicable)
3. Using the DI container (Not Applicable - I don't think?)
4. When the application ends <-- Sounds promising

Option 4 looks like this:

public void Configure(IApplicationBuilder app, IApplicationLifetime applicationLifetime,
                        SingletonAddedManually toDispose)
{
        applicationLifetime.ApplicationStopping.Register(OnShutdown, toDispose);

         // configure middleware etc
}

private void OnShutdown(object toDispose)
{
    ((IDisposable)toDispose).Dispose();
}

SingletonAddedManually in my case would be the DbConfigurationDataProvider class, but this is very much out of scope from the Startup class.

More information on the IApplicationLifetime interface:

https://learn.microsoft.com/en-us/aspnet/core/fundamentals/host/web-host?view=aspnetcore-2.2

EDIT
This example doesn't even bother calling SqlDependency.Stop(), maybe it isn't that important?

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqldependency-in-an-aspnet-app

Answer 1

The “proper” way to do this would be to have your configuration provider be disposable and then dispose all your SqlDependency objects as part of the configuration provider disposal.

Unfortunately, in 2.x, the configuration framework does not support disposable providers. However that is subject to change as part of aspnet/Extensions#786 and aspnet/Extensions#861.

Since I was involved with the development of this, I can proudly announce that starting with 3.0, disposable configuration providers will be supported.

With Microsoft.Extensions.Configuration 3.0, disposable providers will be properly disposed when the configuration root gets disposed. And the configuration root will be disposed in ASP.NET Core 3.0 when the (web) host gets disposed. So in the end, your disposable configuration providers will be disposed properly and should no longer leak anything.