Recommendations on managing connection strings in config files

Question

I've got three databases for a project of mine. I also have several environments that need to be managed. All developer boxes, a dev machine, and a staging box. So if I have 3 developers and three databases that means 11 connection strings to manage (as each developer has a local copy of the database). To help in managing these connection string I have several solution configuration settings.

In addition to this we have several class libraries, each with it's own unit test project. Several of these unit test projects also must specify connection strings to be used during testing.

So if I have a single web.config and four test projects with app.config files I have to have 5 copies of these connection strings.

There has GOT to be a better way than copying/pasting these connection strings between all of these config files.

Does anyone have any recommendations? I looked into the transformations that occur with web.config and web deployment projects but this doesn't help during development / test time.

Any ideas?

Thanks, Mike

Answer 1

Hi Mike,

We typically use multiple web.config files and let the build and deployment process sort out which file is relevant to which environment. So our solution will have a web.config for the local developer PC's and then a web.dev.config, web.test.config and a web.prod.config. It does result in duplication across the config files but does allow clear delineation between the environments. As our prod releases are reasonably infrequent, I'll manually check any settings added to the web.test.config have also ended up in the web.prod.config file before a release.

Answer 2

The way I've done it is to have a separate config file for each environment, usually the standard App or Web config is the local connection strings with the other configs named to have the environment pre-pended to App or Web.

I.E.

prod_Web.config

beta_App.config

You can use deployment options in the vsmdi in the solution and then select the environment to test from the test menu. That feature alone is a gem.

Answer 3

This might be just a partial solution but you can add a configSource attribute to a configuration element that will allow you to source that particular section from a different file. This can be used to isolate what is different from within a common configuration file.

See the following article and scroll down to the How to Use User Configuration Files for Database Connection Strings section

Answer 4

This is what i do:

<add key="PRODSSERVERNAME.DataBaseEnvironment"    value="PROD" />
<add key="STAGESERVERNAME.DataBaseEnvironment"    value="STAGE" />
<add key="DEVSERVERNAME.DataBaseEnvironment"    value="DEV" />

For the names, use the PC name. You can find it by running hostname from the command prompt.

You then do this:

<add key="DB,DEV"           value="Data Source=[DEV STRING] />
<add key="DB,STAGE"         value="Data Source=[STAGE STRING] />
<add key="DB,PROD"          value="Data Source=[PROD STRING] />

Then you write the function:

protected string GetConnectionString() {
    string machineName  = String.Concat(System.Environment.MachineName, .DatabaseEnvironment");
    string environment  = ConfigurationManager.AppSettings[machineName];
    string Key       = String.Concat("DB", ",", environment);
    string value       = ConfigurationManager.AppSettings[ssoKey];

    return value;
}