Best ASP.NET ConfigSection to DB Schema

Question

Previously, settings for deployments of an ASP.NET application were stored in multiple configuration files under the Web.config config sections using a KEY/VALUE format. We are moving these 'site module options' to the database for a variety of reasons.

Here are the two options we are mulling over at the moment:
1. A single table with the applicationId, moduleId, and key as a Primary Key with a Value field.
Pros:
- This mimics the file access.
- It is easy to select entire sections to cache in hashtables/value objects.
Cons:
- More difficult to update since each key needs to be updated individually.
- Must cast each value if it's not a string.

2. Individual tables for each section which separate stored procedures, classes, etc.
Pros:
- Data is guaranteed to be consistent since the column and object types are typed.
- Updating is done in one trip to the database through an explicit interface.
Cons:
- Must change the application interface to access the
- Must update the objects, database tables, and stored procedures each time something changes.

Do either of these sound like good ideas or is there another way I may have overlooked?

Answer 1

If I understand what you are proposing correctly. I would do the first approach. It leverages what you have already built. I would use the hash tables for caching inside of wrapper classes that can provide stongly typed interfaces for the properties.

For example:

/// <summary>
/// The time passwords expire, in days, if ExpirePasswords is on
/// </summary>
public int PasswordExpirationDays {
 get { return ParseUtils.ParseInt(this["PasswordExpirationDays"], PW_MAX_AGE);}
 set { this["PasswordExpirationDays"] = value.ToString(); }
}

Answer 2

Another option is to group like settings together into their own classes, and then use XML serialization/deserialization to store and retrieve instances of these settings classes to and from the database.

This doesn't specifically provide advantages above and beyond a key/value pair other than you don't have to yourself perform any type conversions (this is done behind the scenes as part of the serialization/deserialization process - so it still does happen). I find this sort of approach ideally suited for solving configuration issues such as you are facing. Its clean, quick to implement, very easy to expand, and very easy to test. You don't have to spend time creating a feature rich API to get at your settings, especially if you've already got your configuration subclassed out.

Also in a pinch you can direct your settings to come from database tables or the file system without altering your serialization/deserialization code (this is very nice during development).

Finally if you are using SQL Server (and likely Oracle, though I have no experience with Oracle and XML) and you think about the design of your settings class up front, you can define an XML schema for your serialized configuration object instances so you can use XQuery to quickly get a configuration setting's value without having to fully deserialize.

Answer 3

This is how we did it - Click Here

We were more concerned with the fact that different environments (Dev, Test, QA and Prod), had different values for the same key. Now we have only 2 keys in a WebEnvironment.Config file that never gets promoted. The first key is which environment are you in and the second one is the connection string.

The table gets loaded up once to a dictionary and then we can use it in our code like this:

  cApp.AppSettings["MySetting"];