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.

Solution 1

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

Project Properties http://img442.imageshack.us/img442/1843/propsa.jpg

Solution 2

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.

Solution 3

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.

Solution 4

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.

Solution 5

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.

View more solutions

Comments

[Admin]

I think this is a good suggestion. Can you elaborate on how to add a precompile condition to a VS-2008 project? I am not using MSBuild or any other build process. I simply clean/rebuild and deploy from within Visual Studio.

[Jeffrey L Whitledge]

+1 I like the idea of changing something on the server itself. This seems less error-prone than the other approaches.

[Admin]

Thank you for this suggestion. It is perhaps the most practical and convenient.

[Admin]

While I don't think your answer deserves a downvote, I really don't want to go the DSN route.

[jjxtra]

The only downside is that if you have a lot of production servers in a big web farm, you have to make sure to set the Environment variable, although if you have an image you just slam on it's less of an issue.

[Admin]

Thank you friend. Your method and sample code made short work of this task.

[BornToCode]

The downside of this method is that the project with the Web.config will be determined as not 'up to date' every time the solution is built (since one of the files of the project - Web.config - was modified) causing that project to always be built when the solution is compiled, even if it wasn't really changed.

[AxelEckenberger]

The solution was OK by 2010 standards. Nowadays, however, there is support for this built into VisualStudio. If you using a studio that does not support the feature, you can look into the Slow Cheetah extension.