Make your application settings store values in database

By Mirek on (tags: settings, categories: None)

Application settings in .net applications is a mechanism that allows you to define, modify and easily access a collection of settings. Depending on the setting scope (user or application) they are stored in a .config file deeply under your %USER_PROFILE% folder or in your application’s .exe.config or web.config file. In this post I will show a way to change that behavior and make settings be stored in database.

We will use Entity Framework Code First as our data access layer and for settings will use such a simple entity

public class SettingEntity
{
    public int Id { get; set; }
 
    public string Name { get; set; }
 
    public string Value { get; set; }
}

The rest of database context configuration and code is beyond this post, so I will limit the comment and code to just a crucial things.
No matter if this is a web, console or any other type of application, we now go to project’s properties and select tab Settings. If there are no settings yet click in the middle of the screen to generate one for the project. Here we must set the global access modifier to Public and add all required settings with User scope.

app_settings

Changing a scope of the settings to User makes the auto generated properties write able. On the other hand choosing it to be Application scope will cause the properties to have only getters so to be a read only.

To see what has been generated navigate to you solution explorer and under Properties folder expand the Settings.settings node and open Settings.Designer.cs file. You should see something like this.

[global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()]
[global::System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "12.0.0.0")]
public sealed partial class Settings : global::System.Configuration.ApplicationSettingsBase {
    
    private static Settings defaultInstance = ((Settings)(global::System.Configuration.ApplicationSettingsBase.Synchronized(new Settings())));
    
    public static Settings Default {
        get {
            return defaultInstance;
        }
    }
    
    [global::System.Configuration.UserScopedSettingAttribute()]
    [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
    [global::System.Configuration.DefaultSettingValueAttribute("default@address")]
    public string NotificationAddress {
        get {
            return ((string)(this["NotificationAddress"]));
        }
        set {
            this["NotificationAddress"] = value;
        }
    }
    
    [global::System.Configuration.UserScopedSettingAttribute()]
    [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
    [global::System.Configuration.DefaultSettingValueAttribute("help.company.com")]
    public string HelpUrl {
        get {
            return ((string)(this["HelpUrl"]));
        }
        set {
            this["HelpUrl"] = value;
        }
    }
    
    [global::System.Configuration.UserScopedSettingAttribute()]
    [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
    [global::System.Configuration.DefaultSettingValueAttribute("11")]
    public string UserLimit {
        get {
            return ((string)(this["UserLimit"]));
        }
        set {
            this["UserLimit"] = value;
        }
    }
}

As you can see for each setting defined in the designer the corresponding property wrapper has been generated with getter and setter. Now we would like to store/get the settings property in the database instead of default storage, but since this is an auto generated file we cannot modify it here. What we can do is use the fact that this class is marked as partial and create a separate file with same class marked as partial and modify it there. So lets create new file Settings.cs in our project and define such class

[SettingsProvider(typeof(DBSettingsProvider))]
public sealed partial class Settings 
{
}

We do not have to implement any member of this class now, but only decorate it with a SettingsProviderAttribute  and provide our custom implementation of SettingsProvider.

internal class DBSettingsProvider : SettingsProvider
{
   public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
   {
       base.Initialize("DBSettingsProvider", config);
   }
 
   public override string ApplicationName
   {
       get { return Assembly.GetExecutingAssembly().GetName().Name; }
       set { }
   }
 
   public override SettingsPropertyValueCollection GetPropertyValues(SettingsContext context, SettingsPropertyCollection collection)
   {
       var result = new SettingsPropertyValueCollection();
       using (var dbctx = new AppContext())
       {
           foreach (SettingsProperty sett in collection)
           {
               var settValue = new SettingsPropertyValue(sett);
               var dbsett = dbctx.Settings.SingleOrDefault(s => s.Name == sett.Name);
               if (dbsett != null)
                   settValue.PropertyValue = dbsett.Value;
               settValue.IsDirty = false;
               result.Add(settValue);
           }
       }
       return result;
   }
 
   public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection)
   {
       using (var dbctx = new AppContext())
       {
           foreach (SettingsPropertyValue sett in collection)
           {
               if (sett.IsDirty)
               {
                   var dbsett = dbctx.Settings.SingleOrDefault(s => s.Name == sett.Name);
                   if (dbsett == null)
                   {
                       dbsett = new SettingEntity { Name = sett.Name };
                       dbctx.Settings.Add(dbsett);
                   }
                   dbsett.Value = sett.PropertyValue.ToString();
               }
           }
           dbctx.SaveChanges();
       }
   }
}

The implementation is quite straight foreword. For GetPropertyValues method the database is queried for each setting and the value encapsulated in required object is returned. In SetPropertyValues new setting entity is created or if exists is updated with new setting value.
Having that in place we can simply call our settings like this

var notifAddress = Settings.Default.NotificationAddress;
Settings.Default.NotificationAddress = notifAddress + ".com";
Settings.Default.SomeCustomSetting = "other custom value";
Settings.Default.Save();

This solution has some benefits. One is fact that we can use built in settings designer to define new settings of our application. This is obviously convenient. Another advantage is a possibility to share settings among many applications. We can implement this approach in a code library that contains the rest of our database access classes and entities and reference it in other projects. Since settings are stored in a database not in a config file, we have a common shared settings that will be same no matter what application we use to access them.
Unfortunately there are also a side effects. To have the settings to be write able, not read only, we must define settings in user scope. This causes the definition of settings is stored in user.config file for each system user we use to run our application. This is not maybe a big deal to have such a “dummy” config files generated in our file system, but still. For me this is kind of dirty side effect.
For this reason I would even opt out of settings designer and implement a independent settings manager. That could also implement a read/write properties for each setting I want to use in my application. Of course I would need to do it manually but, to be frankly it is not that much code to write one property with getter and setter.  There are many options anyway and as always we must decide what is worth of using and what is not.