Managing A Debug and Release Connection String

Question

What is a good approach to managing a debug and release connection string in a .NET / SQLServer application?

I have two SQL Servers, a production and a build/debug and I need a method of switching between the two when my ASP.NET application is deployed.

Currently I simply store them in the web.config and comment one or the other out, however that is error prone when deploying.

Answer 1

I usually set an Environment variable on my production servers that denotes the server is a production server. I then read the correct connection string from my web.config based on whether this Environment variable exists and is set to the production value.

Answer 2

Well perhaps this is a bit outdated, but the ODBC DSN solves this problem quite well -- I still use -- with rigour -- DNS settings to differentiate between production and debug environments.

p.s., I am expecting loads of down-votes, perhaps this will be a gauge of what people think of a level of indirection for database identifiers.

Answer 3

The good news is that .NET4 has a provision for just that, you can have separate configs for each Configuration (web.Release.config, web.Debug.config).

The bad news is ... you're probably not using that yet.

Answer 4

Create a Debug and Release version of the Web.config file, e.g. Web.debug.config and Web.release.config. Then add a pre-compile condition that copies the relevant version into the web.config based upon the current Target.

Edit: To add the pre compile condition right click on you project and select "Properties" then goto the "Build Events" tab and add the code below to the precompile condition. Obviously, you will have to ammend the code to your needs, see image below.

@echo off

echo Configuring web.config pre-build event ...

if exist "$(ProjectDir)web.config" del /F / Q "$(ProjectDir)web.config"

if "$(ConfigurationName)" == "Debug Test" goto test
if "$(ConfigurationName)" == "Debug M" goto M
if "$(ConfigurationName)" == "Debug BA" goto BA
if "$(ConfigurationName)" == "Release Test" goto test
if "$(ConfigurationName)" == "Release M" goto M
if "$(ConfigurationName)" == "Release BA" goto BA

echo No web.config found for configuration $(ConfigurationName). Abort batch.
exit -1
goto :end

:test
copy /Y "$(ProjectDir)web.config.test" "$(ProjectDir)web.config"
GOTO end

:BA
copy /Y "$(ProjectDir)web.config.BA" "$(ProjectDir)web.config"
GOTO end

:M
copy /Y "$(ProjectDir)web.config.M" "$(ProjectDir)web.config"
GOTO end

:end
echo Pre-build event finished

Answer 5

Use preprocessor directives: when your project is configured to run in the debug mode, the debug connection string will be chosen, otherwise the release connection string will be chosen automatically.

In Visual studio you will notice that statements are dimmed exclusively according to the project configuration (debug or release).

Just add something like the following in your code:

string myConnectionString;
#if DEBUG
     myConnectionString = "your debug connection string";//may be read from your debug connection string from the config file
#else
     myConnectionString = "your release connection string"; //may be read from your relase connection string from the config file
#endif

for more detail,check this.

Answer 6

I use a combination of Sameh's and Obalix's method in .net 3.5.

    public static class DataConnection
{
#if LOCALDEV
    public const string Env = "Debug";
#endif
#if STAGING
    public const string Env="Staging";
#endif
#if RELEASE
    public const string Env="Release";
#endif
    private static ConnectionStringSettingsCollection _connections;
    static DataConnection()
    {
        _connections = ConfigurationManager.ConnectionStrings;

    }

    public static string BoloConnectionString
    {
        get
        {
            return _connections["DB1."+Env].ConnectionString;
        }
    }
    public static string AOAConnectionString
    {
        get
        {
            return _connections["DB2."+Env].ConnectionString;
        }
    }
    public static string DocVueConnectionString
    {
        get
        {
            return _connections["DB3."+Env].ConnectionString;
        }
    }
}

Then in my project properties, I define the right conditional compilation symbols. This way I don't have to keep my connection strings hard coded like Sameh's, but the code only looks for the string based on how it was built. This lets me have (if I need to) one config file for all the builds, but in reality I don't deploy the config files in my build process. Although the conditional app.Relase.config stuff for .net 4 looks like the right way to go in the future.