Moving AppSettings to a Database Table.

Moving AppSettings to a Database Table.

by JBrooks 25. February 2009 11:38

In one of our ASP.Net application we wanted to change where the AppSettings were stored from a config file to a database table.   Since this is a very large application (over 670 .cs files), the first requirement was that the majority of the changes needed to be done with a search and replace.   In the end the change looked like this:


Would be replaced by:


We had app settings in web.config and the environment specific settings were in an WebEnvironment.config file that was referenced by the web.config file.  So our appSettings section in our web.config looked like:
    <appSettings file="WebEnvironment.config">
        <add key="AppVersion" value="13.9"/>


The problem with this is that we have to do a release to change one of the settings.  Whenever we were changing a setting in the WebEnvironment.config  we had to give the deployment team a different file for each environment and have them rename the correct file depending on where they were deploying to.   So they had a WebEnvironment.configTEST, WebEnvironment.configQA, WebEnvironment.configPROD and WebEnvironment.configDR, this is a little awkward and prone to errors.

We also liked the fact that if the app settings lived in a table then we could give ourselves a maintenance page where we could change the values without doing a deployment.

First I needed the table to hold the app setting.  This was simple enough:
    create table dbo.Config
    (Environment varchar(4),
    [Key] varchar(255),
    Value varchar(8000),
    Comment varchar(255),
    LastUpdated datetime default getdate())

Second a stored proc to retrieve the values depending on the environment. So this is:

    create proc dbo.GetConfig(@Environment varchar(4))

select [key], value
from AppConfig with(nolock)
where environment = @Environment
union all
select [key], value
from AppConfig with(nolock)
where environment = 'All'
and [key] not in
            (select env.[key]
            from AppConfig env with(nolock)
            where env.environment = @Environment
order by [key]


Now insert some sample data:
insert into Config
(Environment, [Key], Value, Comment)
values ('Dev','AppVersion','13.9', 'Version of the application')

insert into Config
(Environment, [Key], Value, Comment)
values ('Dev','key1','value1', 'Test data')

insert into Config
(Environment, [Key], Value, Comment)
values ('Prod','key1','value1prod', 'Test data')

Now I need my new class that will hold the data, this will only load once.

using System.Data;
using System.Collections.Generic;

public class cConfig
    public Dictionary<string, string> AppSettings = new Dictionary<string,string>();

    public cConfig(string environment)
        // the cApp.DAL is our data access layer and this just calls the stored proc and returns a table.
        foreach (DataRow dr in cApp.DAL.CommonData.GetConfig(environment).Rows)
            AppSettings.Add(dr["Key"].ToString(), dr["Value"].ToString());



Now we make the data available globally within the application and define it in such a way that it only insantiates the cConfig object once in a tread safe way.

using System;
using System.Collections.Generic;
using System.Configuration;

  public sealed class cApp
        // This is the only time System.Configuration.ConfigurationManager.AppSettings is called.
        // The appSetting ApplicationEnv is in machine.config and will be one of the values “Dev”, “Test”, “QA”, “Prod” or “DR”
        static readonly cConfig _config = new cConfig(System.Configuration.ConfigurationManager.AppSettings["ApplicationEnv"]));

        public static Dictionary<string, string> AppSettings
            get {
                return _config.AppSettings;

Now I can use this anywhere in my code by using the following:


Development | SQL

Comments (3) -

Dan Kelley
Dan Kelley United States
3/12/2009 9:52:33 PM #

This seems like such a logical approach for large scale apps.  I've found that the AppSetting gets out of control quickly and becomes difficult to manage.  This looks like a simple approach which solves a lot of my issues.

5/30/2009 1:09:48 AM #

I am looking for an example application with the above approach. I would appreciate if you send me the sample application to my email.


Steve Canada
3/3/2010 6:44:58 AM #

Paul or anyone.. If this is available in sample can you please send to my email.

Thanks in Advance.

One Script

OneScript Automated Database Releases Smartly combines SQL scripts from version control into a single release script. Now the release of incremental database changes can be automated and a part of a continuous integration process.