Common Knowledge: Adaptive Connection Strings for Windows Forms Applications

As a developer working with small businesses, I do a lot of Windows Forms based development. One of the great things about this platform is the ease of deployment. A traditional installation program can be created in a matter of minutes. Better yet, with one-click deployment it's easy to update a client's machine at a remote location with the touch of a button.

In a small-scale custom development context, it's very common that simple updates are urgently needed. This could be because of an undiscovered bug, but more commonly it is the result of an unknown or poorly understood business rule that emerges at the worst possible moment. For example, during invoicing it is discovered that no one told the developer about new discount rules. In this scenario a simple fix at the code level is usually possible, and extensive acceptance testing is counterproductive. This is where quick and easy deployment makes everyone's life easier.

But there is one fly in the deployment ointment: the database connection string for the production environment is always different than the development environment. That means that the last step in any deployment is resetting the database connection string. There are several problems with this, including:

If you forget to change the connection or make a mistake in resetting the string, your already cranky client gets an immediate connection error after the deployment.
There's no obvious automatic way to make sure you don't make a mistake when you reset the string.
There's no way to test within the development environment to make sure you've got the production connection right. If you develop this way, every build you deploy is in fact an untested build, because it can't run successfully in the development testing environment.

What we really need is an application feature that will manage the connection string for us, that will make sure the correct connection string is always used. It must be possible to set up the connection configuration once, test it in all environments, and then leave it alone. This would allow us to deploy our changes without any changes to the connection string. The feature should be valid for both application assemblies and DLLs that are deployed as components. I call this feature, "Adaptive Connection Strings" and the rest of this post describes how to make it happen.

Getting Control of the Connection String

Almost all useful business applications have at least one database connection. Whether you're using hand-coded ADO.NET Connection objects, table adapters, LINQ to SQL, or the ADO.NET Entity Framework to connect to your database, the most convenient and flexible option for managing database connection strings is to create connection string setting in the application settings class. Visual Studio designers do this for you when you choose the save this connection option. But application settings of type connection string are always application level read-only properties, so it is not possible to build run-time logic to modify them.

But are connection string properties really read only? Let's take a quick look at a connection string property definition in the Settings.Designer.cs class:

        public string MyAppConnectionString {        
    get {         
        return ((string)(this["MyAppConnectionString"]));         
    }         
}

You can see that the property is clearly defined as read only. But you can also see that the connection string property is really just an alias for a call to a indexer defined by the settings class. The indexer used here is defined in System.Configuration.ApplicationSettingsBase to provide a reference to the Properties member collection. It turns out that all application settings are accessible in read/write mode by using this indexer directly. The following code demonstrates how to modify the connection string using this approach:

//change the application connection string Properties.Settings.Default["MyAppConnectionString"] = strCnString; //debug:verify that this really did work Console.WriteLine(Properties.Settings.Default.MyAppConnectionString);

So we can set up the connection string at run time, as long as we have an effective place to put this code. For example, we could put this in the constructor of the main form of an application. But how do we determine which connection string to use?

Finding the Right Connection

The whole point of this technique is to make the connection adaptive; we want the application database connection to automatically adapt based on the context in which it is running. This can be done in a number of different ways, but in most cases you will find the Environment class very helpful. The static instance of this class can be used to collect information about the host of the current process. I usually use the MachineName property to identify development workstations. The code looks something like this:

string ServerName; string DatabaseName; switch (System.Environment.MachineName) { case "DEV1": case "DEV2": ServerName = @"DEV\SQL2008"; DatabaseName = "Test"; break; default: ServerName = @"CONTOSO\BIGBOY"; DatabaseName = "Production"; break; } System.Data.SqlClient.SqlConnectionStringBuilder bld = new System.Data.SqlClient.SqlConnectionStringBuilder(); bld.DataSource = ServerName; bld.InitialCatalog = DatabaseName; bld.IntegratedSecurity = true; Properties.Settings.Default["MyAppConnectionString"] = bld.ConnectionString;

This approach works well in small scale settings when you can be sure that there won't be duplicate machine names in different domains. For a more scalable approach, you can also parse the domain name from the UserDomainName property of the static Environment instance.

At this point we're in pretty good shape. We have a way to control the connection string at runtime, and we have a programmatic method to determine which connection string to use. But that still leaves one important issue: we don't have a standard way to include this code in our projects that will guarantee the connection will be initialized before any data components are constructed. For example, if we are building a DLL that exposes multiple public methods, there is no single entry point at which we can perform the initialization. Wouldn't it be nice if we could simply handle a "load" event for the assembly settings? It turns out that we can do that, and use the same technique whether we are building a stand-alone application or component.

Hooking Settings Initialization

In order to hook the load event for your assembly settings, you must first create a code behind file for the settings class. To do this, select <app name> properties from the Project menu in Visual Studio, then click the settings tab. This is the same screen that is used to modify connection string properties:

Note the View Code button, which is highlighted by the red circle. When this button is clicked, Visual Studio creates a Settings.cs file in the main directory of your application (you would expect this file to appear in the Properties directory, but it goes into the main directory instead).

When this file is created, it contains two pre-wired events: SettingChanging and SettingsSaving. These events are of no use for our purposes. In order to hook the SettingsLoaded event, you must manually add an event handler, as shown below:

public Settings() { // // To add event handlers for saving and changing settings, // uncomment the lines below: // // this.SettingChanging += this.SettingChangingEventHandler; // // this.SettingsSaving += this.SettingsSavingEventHandler; // this.SettingsLoaded += new System.Configuration.SettingsLoadedEventHandler(Settings_SettingsLoaded); } void Settings_SettingsLoaded(object sender, System.Configuration.SettingsLoadedEventArgs e) { // Connection string initialization goes here // ... }

You can now move the connection string setting code into the SettingsLoaded event. Since you are in the code behind file for the settings class, you can replace references to Properties.Settings.Default with the this keyword:

this["MyAppConnectionString"] = bld.ConnectionString;

The connection string initialization is a good candidate for a code snippet. In most cases the only difference in this code from project to project will be the server and database settings.

It should be noted that if you're using SQL Server Authentication or another connection type that requires a user name and password, you won't want to hook the SettingsLoaded event. In this case, you'll have to display a login dialog to retrieve the authentication information. But you'll still be able to use the technique described above to control the connection string properties of your application.

Conclusion

This post demonstrates a technique for adaptive connections strings that can be used for all Windows Forms application and component projects. I've used this technique at my company, PJPM, to deploy patches and updates to clients very quickly and easily. It definitely takes an important pain point out of the deployment process.