Manage Connection Strings In Multiple Environments

Have you ever had to move or deploy applications to different servers and forgotten to change connection strings settings? Maybe you were developing against a local database and when you push to production your web.config (or wherever you store your connection strings) stayed pointing to your local instead of production. Another common pain is when moving an application throughout the various stages of the software development lifecycle; perhaps from a development environment, to testing server(s), staging server(s), and finally to production? In multi-environment development, deployment can become a real headache.

There are several different approaches to making this easier, including creating post build scripts, creating a deployment project (which I will probably cover in a future blog post), and simply implementing manual processes to follow. However, I’ve found in my own recreational programming, that creating a ConnectionManagement class is a pretty slick and easy way to go that garners all kinds of added benefits.

A Two Environment Example

Let’s take the example of developing against a local SQL database (or database project), and deploying directly to production; something which is typically done in very small projects.

We want to create a single class that we statically call to retrieve the appropriate connection string, but still maintain the ability to house the connection strings inside an easily configurable file (such as web.config). In this example, we’re not particularly concerned if the seperate environments have knowledge of the other environments’ connection strings (this is a potential security risk if you have several developers who have access to web.config via dev server/test server and should not have access to production server, though you can encrypt or take additional measures to protect the connection strings).

I create this class in my DataAccess project where the rest of my data logic resides. Let’s set this class up like the following:

ConnectionManager Class

public class ConnectionManager
    {
        public static string GetConnectionString()
        {
            string appMode = string.Empty;
            if (ConfigurationSettings.AppSettings["Mode"] != null)
            {
                appMode = ConfigurationSettings.AppSettings["Mode"].ToLower();
            }

            if (appMode == "prd")
            {
                return ConfigurationManager.ConnectionStrings["MyDBPrd"].ConnectionString;
            }
            else
            {
                return ConfigurationManager.ConnectionStrings["MyDBDev"].ConnectionString;
            }
        }
    }

Make sure you’ve added using System.Configuration; to the top of your class and if necessary added to your class library/project.

What we are doing here is very simple; we’re just going to get the appropriate connection string based on an app setting found in the web.config of the calling project. If the mode is “prd” (I know, I’m using hardcoded strings here…tisk, tisk) then return production, if it’s anything else return the dev connection string.

Web.Config of Consuming Project

Let’s take a look at the web.config app setting we’ve added to control the connection strings.

<appSettings>
      <!-- Possible values are: Dev, Prd-->
      <add key="Mode" value="Dev"/>
</appSettings>

App.Config of DataAccess Layer

Inside the data access layer, where the connection manager class resides, I have an app.config which houses all of the connection string information. If you don’t use multi-tiered design, this could go in your calling project (webapp?) instead.

<connectionStrings>
    <add name="MyDBDev" connectionString="Data Source=MY-PC\SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True"/>
    <add name="MyDBPrd" connectionString="server=localhost;uid=app;pwd=mysecurepassword;database=MyDB;application name=MyApp;Max pool Size=500;Connect Timeout=30;connection reset=false;connection lifetime=5;"/>
</connectionStrings>

Calling Our New ConnectionManager.GetConnectionString() Function

Ok, now that we went to all this trouble, make sure everywhere we call a connection string (which should probably only be in our data access layer), we call this method, like so:

Linq Example

public static Specy GetSpecies(int SpeciesID)
{
	//This next line creates a data context from linq and uses the ConnectionManager.GetConnectionString()
	//function to get the connection string it uses.  Even though this Linq2SQL data context has a connection
	//string associated with it, i am setting it in case we change environments.
	MasterDataContext dc = new MasterDataContext(ConnectionManager.GetConnectionString());
        var query = from a in dc.Species
        	where a.SpeciesID == SpeciesID
                select a;

        return query.SingleOrDefault();
}

Traditional ADO.NET Method

public static DataTable GetControlSpecies()
{
	//This next line creates a sql connection object and uses the ConnectionManager.GetConnectionString()
	//function to get the connection string it uses.  Using the same usage everytime and letting the
	//connection manager dictate which connection based on environment consolidates your code and allows you
	//to deploy your web applications to different database environments quickly and easily.
	using (SqlConnection connection = new SqlConnection(ConnectionManager.GetConnectionString()))
        {
        	string sql = "SELECT SpeciesID, CommonName, ScientificName FROM Species ORDER BY CommonName ASC";

                var cmd = new SqlCommand(sql, connection);

                var da = new SqlDataAdapter(cmd);
                var dt = new DataTable();
                da.Fill(dt);

                return dt;
	}

}

When you deploy to an environment now you only need to ensure one value is set correctly (the app setting Mode property we created)! And most of the time, if you’re like me, you hardly ever change the web.config after initial setup anyway; so you’re all set!

Dealing With Several Environments

In this case we only have two environments, the local copy we develop against, and production…However, what if we have several environments, or several different databases. To expand on this basic class, we can add a switch/case statement to jump through all of the possible combinations of environments such as the following:

public class ConnectionManager
    {
        public static string GetConnectionString()
        {
            string appMode = string.Empty;
            if (ConfigurationSettings.AppSettings["Mode"] != null)
            {
                appMode = ConfigurationSettings.AppSettings["Mode"].ToLower();
            }

	    string connectionString = string.Empty;
            switch(appMode)
            {
                case "prd": //Production
			connectionString = ConfigurationManager.ConnectionStrings["MyDBPrd"].ConnectionString;
			break;
		case "dev": //Development
			connectionString = ConfigurationManager.ConnectionStrings["MyDBDev"].ConnectionString;
			break;
		case "tst": //Test
			connectionString = ConfigurationManager.ConnectionStrings["MyDBTest"].ConnectionString;
			break;
		case "stg": //Staging
			connectionString = ConfigurationManager.ConnectionStrings["MyDBStaging"].ConnectionString;
			break;
		case "vendor": //Vendors
			connectionString = ConfigurationManager.ConnectionStrings["MyDBVendor"].ConnectionString;
			break;
		case "local":  //Local
			connectionString = ConfigurationManager.ConnectionStrings["MyDBLocal"].ConnectionString;
			break;
	    }

	    return connectionString;
        }
    }

Expanding on the ConnectionManager Class

Having a centralized point of entry for accessing your connection strings is helpful for other reasons as well. Perhaps you need to add custom encryption/decryption to your connection strings, or control access to specific connection strings programmatically. These, as well as many additional future enhancements can be built into your class and trickled down to all consuming calls.

One final note, it may also be helpful to create an enum of different databases to pass as a parameter to the ConnectionManager.GetConnectionString() function such as ConfigurationManager.GetConnectionString(Databases.Users) or ConfigurationManager.GetConnectionString(Databases.Transactions), etc. to use the class to access specific databases on a single server.

Thanks for reading, and hope you find this useful! Please feel free to add any comments or suggestions on managing connection strings!