One basic question that I see time and time again with source control is how to manage connection strings stored in .config files within source control. Such a small thing, but I see that as a stumbling block for many projects starting up under source control especially for new developers. The problem is that on most developer machines there are differences in how connection strings - and even more specifically the server name - are referenced to access a database.

.config file differences in general are problematic under source control. In Web projects in Visual Studio there are .config transformations that can be applied that can mitigate some of this pain, but this only solves the problem for Web projects. If you have test projects or re-use components in other types of applications like services or desktop apps, .config transformations on their own can't solve that problem easily. There are workarounds described in this StackOverflow Question, but they're not built-in and require some extra effort as well.

Config File Connection Strings under Source Control

While most settings in .config files are pretty universal across configurations and can stay stable, the most common problem are connection string differences amongst users. For example some people use a full version of SQL Server on the local machine (.), others use SQL Express (.\SQLEXPRESS) or the new localDb Sql Server support (localdb) or simply run SQL Server on another machine altogether with a different server name.

If you have any divergence at all and you check in your changes to source control, and then another person with a different server requirement pulls those changes down, they'll likely get a merge conflict and accepting the change results the compiled application not running due to an invalid connection string.

Now you can fix the connection string in your project, but of course then when you push back your changes to the source server you then hose other people who are using a different string and the process repeats. Not ideal to say the least.

There are a number of different ways to address this, but I've been using one of the following two approaches to deal with this particular problem.

Server Aliases

The most common issue I've seen with connection strings is merely the server name rather than other stuff on the connection string. As the connection string is the same except for the server name for all involved, Server Aliases are an easy way to use a single connection string in all configurations.

If only the server name is different the easiest way to set up common base for all users is to set up a Server Alias for each of the users. SQL Server "Server Aliases" can be configured in the SQL Server Configuration Manager and as the name suggest let you specify a server's connection properties via a simplified alias name.  Typically this feature is meant to simplify complex connection features like TCP/IP ports and long SQL connection names, but it also works great to hide the server name from your .config files so that all users can use the same Server name regardless of the variant of SQL Server they are using.

To use it start the Sql Server Configuration Manager from the Start Menu, select the SQL Client configuration and then Aliases:

In the dialog that pops up you can configure the address of the server (. or here a local domain DbBeast server) and protocol, plus a configuration value such as the TCP/IP port (if custom) or a Named Pipe name for example.

Here I set up an Alias called CodePasteAlias that points at my local SQL server which is . or could also be my local machine name. If I were running SQL Express I'd use .\SQLEXPRESS instead for the server name.

Click OK to save and your configuration is set. Note that there are separate 32 bit and 64 bit configurations - you might have to set up the 32 bit or 64 bit or both depending how your application runs.

No SQL Server Client Tools? Use CliConfg.exe

The above assumes you have a local copy of SQL Server or at least the Sql Client Tools to configure. On a client machine that may not be available. Luckily Windows natively ships with a Sql configuration utility which you can access from the command prompt:

cliconfg.exe

Use the Alias tab there to configure the Alias.

Once you've configured the Server Alias you can now use it in a connection string in lieu of a server name.

<connectionStrings>
  <add name="CodePasteContext" 
       connectionString="server=CodePasteAlias;database=CodePasteData;integrated security=true" 
       providerName="System.Data.SqlClient" />
connectionStrings>

This approach is very easy to deal with as it's a one time config option and it will work across projects without changes required to the config files. But - and it's a big but - it only works works if the server name is the only thing that is different. It doesn't work if you have completely different connection strings or you have differing login names.

If you need more control over all of the connection string, this approach won't work.

In practice I've found that Server Aliasing is sufficient most of the time. If necessary creating a custom login as part of the database can ensure that everybody uses the same authentication as well (if integrated doesn't work universally) which is often the only other difference than server name.

External Configuration Sections

If you need more control over the differences in the connection string (or other parts of configuration files for that matter) you can externalize parts of the .config and keep the external pieces out of source control.

.NET Configuration files support the ability to externalize the content of a configuration section quite easily using a configSource attribute. This makes it possible to externalize the entire connectionStrings section for example and then keep the externalized file out of source control.

For example you can do something like this:

  <connectionStrings configSource="web_ConnectionStrings.config">    
  connectionStrings>

You can then create a web_ConnectionStrings.config file and store the following in there:

<connectionStrings>
  <add name="CodePaste" 
       connectionString="server=.;database=CodePasteData;integrated security=true" 
       providerName="System.Data.SqlClient" />
connectionStrings>

You now have externalized the database configuration settings into an external file which can be kept out of source control, so that each user has their own settings of the connection strings. But this has the unwelcome side effect that a new clone from a repository has no connection strings at all in place. They'll have to be manually added or copied in from some other location.

One Abstraction Further

One issue with this approach is that it doesn't solve the problem of multiple configurations when it comes time to deploy the app.

One hacky way of doing this is to store external configuration files for each build in a separate folder and then copy them in with a build task. To do this:

• Create a folder somewhere in your solution path root (I use /config)
• Copy one file for each configuration (ConnectionStrings.config.debug, *.release, *.deploy)
• Change each file to match your environment.
• Add post build event to copy the appropriate file from the folder into your project

The post-build task would look like this:

"copy" "$(SolutionDir)config\connectionstrings.config.$(ConfigurationName)"
       "$(ProjectDir)web_ConnectionStrings.config"

Using this approach you'd still have to instruct people to create the files inside of Solution /config folder outside of source control, but the advantage with this is that a) the project has all the files to run (even if the connection strings may be invalid you get an error message to that effect) and b) you can have multiple connection string settings files for the different configurations. So if you need to deploy you can be assured you're deploying with the correct connection string for the live site (or whatever config).

In this scenario you would set up the .debug and .release configurations to your dev setup and the .deploy (and whatever other versions like .staging) to the appropriate settings.

Hacky Stuff

This is all very ugly and just for the sake of source control. For small or personal projects it's easy to bypass this altogether by simply using the same connection strings for development and the server. But when working in even smal teams it's likely you'll run into divergence.

If only server names vary, Server Aliases can simulate a single connection string easily. This works just fine as long as connection strings only vary by server name. If this is possible this is definitely the easiest approach as nothing has to change in the project - you just need the manual connection setup once. Personally for me Server Aliasing works most of the time and it's easy enough to set up for all involved as long as the process is documented.

Back in the days of ODBC connections there was a repository of DSNs that could be declared at the system level, which in light of this particular issues seems like a good way to handle this - set up a connection globally once and then reference that remotely. Unfortunately, AFAIK the .NET universal provider doesn't recognize DSN connections.