Managing Dynamic Website Settings Persisted in a Database

Question

I am trying to create something to hold global site-wide settings in our ASP.NET website - things such as site name, google analytics account number, facebook url etc... The site can have multiple ‘brands’ or sub-sites associated with it, hence the sitguid column, we would also like to have the option to put them into groups, hence the group column – e.g. TEST and PRODUCTION (set via web.config appsetting).

I do not want any of the KEY’s to be hardcoded anywhere, but I would like to be able to reference them as simply as possible in code, e.g. SiteSetting.getSetting(“SiteName”) (these may be used in templates (masterpages and such) that more junior devs will create)

I would also like to be able to administer the existing settings in our admin console and to be able to create new settings.

The datatype column is for the edit form so that the correct input element can be used, e.g. checkbox for bit types, text box for varchar etc...

SiteSettings database table currently:

  [sts_sitGuid] [uniqueidentifier] NOT NULL, -- tells us which site the setting is for
  [sts_group] [nvarchar](50) NOT NULL, -- used to group settings e.g. test/live
  [sts_name] [nvarchar](max) NULL, -- the display name of the setting, for edit forms
  [sts_alias] [nvarchar](50) NOT NULL, -- the name for the setting
  [sts_value] [nvarchar](max) NOT NULL, -- the settings value
  [sts_dataType] [nvarchar](50) NULL, -- indicates the control to render on edit form
  [sts_ord] [tinyint] NULL,  -- The order they will appear in on the admin form

I am part way through having this working at the moment, but I am not happy with the way I have done it and would like any advice people here have that might help find the ‘right’ solution! I'm sure people have done this before me. (I would share what I have so far, but do not want to skew the answers in any particular way) All i'm looking for is an overview of how this might be best done, not looking for anyone to write it for me ;)

I’ve done quite a bit of searching both here and Google and have not really found what I’m looking for, especially the ability to add new setting ‘definitions’ as well as editing the settings that exist.

The system runs on ASP.NET using webforms, it's all written in c# and uses MSSQL 2008.

As always, any help is very much appreciated!

EDIT: To clarify I am going to explain what I have built so far. I am dead set on storing all of this in SQL as we don't want web.config or other xml files or another database floating around since it'll give us more to do when we rollout the app to other customers.

So far I have a SiteSettings class, this has a method GetSetting which i can call with GetSetting("SettingAlias") to get the value for "SettingAlias" in the DB. This class's constructor fetches all the settings for the current site from the database and stores those in a dictionary, GetSetting reads from that dictionary. All of that part I am happy with so far.

The part I am struggling with is generating the edit form. The previous version of this used a webservice to get/set the settings and I am trying to continue using something similar to save work, but they were all defined in the code, such as GoogleAnalyticsID, Sitename etc... and each had a column in the database, the change I am making is to store these settings as ROWS instead (since then it's easier to add more, no need to change the schema & all of the sitesettings class) Currently my SiteSettings class has a SiteSettingsEditForm method which grabs all the info from the db, creates a bunch of controls for the form elements, puts that in a temporary page and executes that, then passes the HTML generated to our management system via ajax. This feels wrong and is a bit clunky, and is the reason for posting it here, I am having trouble figuring out how to save this stuff back via the webservice, but more importantly generating a bunch of HTML by executing a page containing a load of form controls just feels like the wrong way to do it.

So in summary I (think i) want to write a class to be able to cache & read a handful of rows from a database table, and also give me an edit form (or give data to something else to generate the form) that is dynamic based on the contents of the same database table (e.g. where my type column is 'bit' I want a checkbox, where it is 'text' I want a text input)

Answer 1

Sometimes this kind of problem is easier to visualize if you start off with the data model. If you want a setting per row, then two tables would probably be the best way to store this:

Site:

SiteId    SiteKey     SiteName
1         XYGZ4345    Client Site 1
2         AZT43752    Client Site 2

This would define the list of sites you have config for. I'd use a SiteKey as you'd put this in your web.config and it's better to abstract this away into a random string or GUID (to make it harder to accidentally load someone else's config), the client can change their name and you don't get confused in the future as you didn't use their old name as a key etc etc.

The config table itself is also simple, if we treat every setting as a string:

SiteSetting:

SettingId    SiteId    SettingName    SettingValue
1            1         Brand          KrustyBrand
2            1         GoogleId       MSFTSUX0R
3            2         Brand          ConfigMasters(TM)

You can then load all the config quite simply:

SELECT * FROM SiteSetting INNER JOIN Site ON (SiteSetting.SiteId = Site.SiteId) WHERE Site.SiteKey = 'XYGZ4345'

Now we have a list of key value pairs you could then store in a class like:

public class SiteSetting
{
    public Site Site {
        get; set; //Site would just be a simple class consisiting of Id, Key and Name to match the database table
    }

    protected Dictionary<String, String> Settings { get; set; } //Simple key value pairs

}

So this is a very simple solution. However, we can take it further - things to consider:

1) Can we add an environment to this somewhere?

We could either add a site per environment

OR

Add an environment to the SiteSetting table. The advantage of this is that you could define enironment = 'ALL' to avoid duplication.

OR

The database the configuration is loaded from defines the environment; so you change the config connection string in the app config. Of course, to connect to a different environment you have to change app.config, but you would potentially have to do that anyway to change the client key and/or environment.

2) Add the concept of user defineable settings - some settings you are going to want to change, some you are going to want to lock. A bit column containing "UserDefinable" would allow you to sort this out

3) Typing of settings.

This is slightly more difficult. You might have something like:

PropertyId    PropertyName        PropertyType    Format          UserDefined
1             Brand               String          NULL            1
2             DatePresentation    DateTime        "yyyy-MM-dd"    1

The Settings table then only defines a value, and a PropertyId. The advantage of this is that you can then start to increase the information about each setting you are storing, and reuse this information as the design is more normalized. The Settings class then changes like so:

public List<PropertyValue> { get; set; } //replacing the dictionary

PropertyValue then looks something like:

public class PropertyValue
{
    int Id { get; set; }

    public string Name { get; set; }

    public string Value { get; set; }

    public string PVType { get; set; } //Could be an enum

    public string DisplayFormat { get; set;

    private string _RawValue;

    public string Value{
        get{
          switch(PVType){
            case "DateTime":
                    return Convert.ToDateTime(_RawValue).ToString(DisplayFormat);
            break;
            case "Double":
                    return Convert.ToDouble(_RawValue).ToString(DisplayFormat);
            break;
            default:
                    return _RawValue;
          }
        }
        set{
            _RawValue = value;
        }
    }

}

Things like the Value method need to be improved to support robust error handling (you could also investigate using Convert.ChangeType to simplify the switch block)

This topic is as simple or as complicated as you choose to make it ;-)

Editing

As regards maintaining them; a very simple GUI would allow the user to see all of their properties in a tabular format. You might consider having rows where UserDefinable = 0 as readonly, otherwise the user can edit and add rows. You'd need to validate, especially for duplicate Setting Names for example.

The easiest way to do this is to use the DataGrid; a simple mockup might look something like this:

And a more sophisticated approach might look something like this

Generating the form is therefore as simple as databinding a collection of PropertyValue objects to your chosen grid solution.

Answer 2

As you probably found, there are a variety of ways that you can do this, ranging from Microsoft-recommended to 100% roll-your-own. You're currently on the roll-your-own end of the spectrum, which I think is usually better anyway.

Anyway, basically what you're looking for is a StateBag. Something loosely typed and highly configurable, which doesn't really lend itself very well to an RDBMS, but if you already have the SQL Server in place, it's probably going to be easiest to just use that.

Option 1: Redis

However, another option is to run something like Redis for site configurations. You can store it as a collection of key/value pairs in a Redis object, pull it in a JSON object, and then either convert that to a Hashtable, where lookups will be very fast, or some other hashable collection. A simple wrapper class around the collection would suffice.

Redis is very light and in your case wouldn't require a lot of configuration or maintenance.

Option 2: SQL Server

The way you've mentioned is actually a pretty good way of doing it, but rather than query the database every time, put a strongly typed collection or Hashtable in the cache and let it expire every few hours.

If you go with essentially what you have now, you could store it like this:

Setting
    SettingId Int PK,
    Key NVarchar(50),
    Name NVarchar(50),
    Description NVarchar(1000),
    Type NVarchar(50),
    Priority Int

Configuration
    SiteId Int,
    SettingId Int FK(Setting),
    SettingValue NVarchar(1000)

Don't store things like live/test/dev in the database. You need to have completely separate databases and servers to distinguish between live/test/dev/stage/QA etc.