Proper way to manage test/prod connection strings in .NET web.config file

Question

I have the source code of an application that I maintain. In my web.config file, I have connection strings for both my test and production databases as follows:

<add name="conn" connectionString="Data Source=TestDBServer; (etc...)">

<add name="conn" connectionString="Data Source=ProdDBServer; (etc...)">

Whenever I am in test, I just comment out the production connection string and vice-versa. For example, when I finish testing, I comment out the test connection string and uncomment the production connection string and then deploy.

I am very early on in my career as a developer, but it seems to me that there must be a better or more standard way to handle this. What should I do?

Edit

Guilherme Lofrano Corneto linked an identical question that completely answers my own question. I have since marked mine as a duplicate. Here is the link:

Change connection string from development to production when publishing

See this: http://stackoverflow.com/questions/32389101/change-connection-string-from-development-to-production-when-publishing — Guilherme Lofrano Corneto, Apr 10 '17 at 12:38
There are lots of ways to manage this, and it really comes down to what works best for your situation. Not really something anyone can tell you to do because we don't know your full situation. However, one thing to keep in mind is that having manual steps increases the likelihood that you'll screw up and accidentally connect to the production database and screw up some data. — mason, Apr 10 '17 at 13:01
If you're going to have both connection strings in the file, then I would name them differently and use code to determine which is appropriate to use. On production server, use Production connection string. Every other server should use the test connection string. — mason, Apr 10 '17 at 13:01
Long term though, it's probably better to move to an automated release system such as using TFS or Octopus to deploy your code and inject the correct connection strings. — mason, Apr 10 '17 at 13:03

score 2 · Accepted Answer · answered Apr 10 '17 at 12:44

Visual Studio can automatically transform your web.config based on your currently active build configuration. So you should have a build configuration "Test", "Dev", "Prod" etc. (whatever you need for your workflow).

Then you can right-click on your web.config and click "Add transform". This creates a new config like web.Prod.config where you can overwrite your config values. When building with a specific build configuration, visual studio will automatically overwrite the default web.config with your corresponding transformation.

If you need to apply such transformations to other files than the web.config you might want to check extensions like "SlowCheetah - XML Transforms"

S.Dav · Answer 2 · 2017-04-10T13:26:46.310

You can follow the following steps to transform web config file during deployment.

Here is a short summary:

1- Set your development connection string in the web.config file. Should look similar to this:

 <connectionStrings>
    <add name="ConnStringDb1"  connectionString="Data Source=TestDBServer; (etc...)" />
  </connectionStrings>

2- Expand your web.config file and open web.release.config

3- Use the Replace function to replace the connection string with the one for you want for deployment. You can use xdt:Locator="Match(name)" attribute to match it with the name of the connection string (ConnStringDb1) in this example:

<connectionStrings>
    <add name="ConnStringDb1"  xdt:Transform="Replace" xdt:Locator="Match(name)" 
         connectionString="Data Source=ProdDBServer; (etc...)" />
  </connectionStrings>

More information on transformation syntax in web.config.

score 0 · Answer 3 · answered Apr 10 '17 at 13:14

My team usually keeps the "dev" setup as the one in TFS as the default. It helps if all team members install the same SqlServer "instance" so if does not differ from dev to dev.

Example : "localhost\SqlServ2014Stand

or something like that.

For deployments to QA, Staging, UAT, Production (whatever you call them)......we write wix installer(s) (one per product) and we handle the xml-transforms in the wix project. This is what will change the connection string (or anything else) for the different environments.
You may want to look at a CI server like CruiseControl.NET or Jenkins or TFS or something. This is what will build your code in a consistent way. This takes a little time to setup. If time if the main factor, I would try Jenkins.

Proper way to manage test/prod connection strings in .NET web.config file

Edit

3 Answers3