Determining the prod/dev connection string during run-time
Language(s):C#
Category(s):appSettings, ASP.Net, Connection String, connectionString, MySQL, web.config      
Dynamically Determining the Connection String for Prod/Dev Servers

When developing an ASP.Net application that connects to a database, it is usually the case that the connection string for the production server will be different than the connection string for the development server. Normally one must remember to change the web.config file before switching between the two environments. This is a pain and forgetting to do so can lead to sometimes frustrating results.

A better way to do this is to take advantage of the MachineName property of the HttpContext.Current.Server object as follows:

if (HttpContext.Current.Server.MachineName == "YourDevMachine")

    return (new MySqlConnection(

ConfigurationManager.ConnectionStrings["YourDevEntry"].ToString()));

else

    return (new MySqlConnection(

ConfigurationManager.ConnectionStrings["YourProdEntry"].ToString()));

 

Note that I am using a MySQL database for this example. The same method can be used for OleDB, SQL Server, Oracle or any other ADO.Net database.

The above example assumes you have set up the following entries in the connectionStrings section of web.config:

<connectionStrings>

  <add name="ProdServer" connectionString="Server=???;Database=???;UID=???;Pwd=???;" />

  <add name="DevServer" connectionString="Server=???;Database=???;UID=???;Pwd=???;" />

</connectionStrings>

 

This works great if your developers are all using the same dev machine, but a better solution would be to place the name of the dev machine in the web.config file as well under the appSettings:

<appSettings>

  <add key="DevMachine" value="???"/>

</appSettings>

 

So, now the code would look like this:

string strDevMachineName = ConfigurationSettings.AppSettings["DevMachine"];

if (HttpContext.Current.Server.MachineName == strDevMachineName)

    return (new MySqlConnection(

ConfigurationManager.ConnectionStrings["DevServer"].ToString()));

else

    return (new MySqlConnection(

ConfigurationManager.ConnectionStrings["ProdServer"].ToString()));

 

Since you may want to expose the ConnectionString and DevMachineName properties to other processes as well, we can go one step further. In the code example below, we provide the additional properties. Note that the properties and methods are declared as static so it is not necessary to instantiate the DataServices object explicitly:

   

/// <summary>

/// CreateConnection - Retuns a database connection

/// </summary>

/// <returns></returns>

public static MySql.Data.MySqlClient.MySqlConnection CreateConnection()

{

    return (new MySqlConnection(DataServices.ConnectionString));

}

 

/// <summary>

/// DevMachine - Returns the Development Machine Name

/// </summary>

public static string DevMachine

{

    get

    {

        return (ConfigurationSettings.AppSettings["DevMachine"]);

    }

}

 

/// <summary>

/// ConnectionString - Returns the ConnectionString based on the MachineName

/// </summary>

public static string ConnectionString

{

    get

    {

        if (HttpContext.Current.Server.MachineName

                == DataServices.DevMachine)

            return (

ConfigurationManager.ConnectionStrings["DevServer"].ToString());

        else

            return (

ConfigurationManager.ConnectionStrings["ProdServer"].ToString());

    }

}

 

The calling program can then instantiate the connection as follows:

MySqlConnection con = DataServices.CreateConnection();

The entire sample project may be downloaded from the link above. You will need to enter the appropriate machine name and connection strings for your site. 

This article has been viewed 10490 times.
The examples on this page are presented "as is". They may be used in code as long as credit is given to the original author. Contents of this page may not be reproduced or published in any other manner what so ever without written permission from Idioma Software Inc.