If you use LINQ in a data-access component, and are looking to manage connection strings in various projects and environments, this article is for you…

When you create a new ER mapping in LINQ (as a dbml), you may end up with the connection string in the app.config file of your DA component. When your website or other client project uses this component, it can get quite confusing to know where the connection string is picked up from, and you most likely have pulled out your hair out in that process.
It is likely that the connection string is actually hard-coded in your Settings.Designer.cs file in the Properties folder in our project. There is a default setting attribute for the connection key property in your settings class, and if the required key is not found in your configs at run-time, this default value will be used.
So, what are the best practices to specify connection strings for LINQ? Here is my solution…

The key is to have the right settings key (no pun intended) in the connectionStrings section in your client config file.
Something like:

  <add name=”NamespaceFoo.SettingFoo.ConnectionFoo” connectionString=”DataSource=xxx;blah-blah”
    providerName=”System.Data.SqlClient” />

To figure out the precise key name, open your Settings.Designer.cs file. Note the Namespace, the partial class name and the name of the related connection string Property. Concatenate these together with periods and you have your config key. e.g. Outerspace.Settings.EarthDBConnection

Add this setting to the config file of your calling project, e.g. web.config of your main website. At run-time, the connection string value for that key will be used by your LINQ data context.

As you’re aware, different environments like dev, integration, qa, staging, production, etc. have different connection strings. You could change the value of the connection string but how do you maintain the various values? In the past, I have had these all in the config files and commented and uncommented them as required, but that was clunky and exposed them to everybody.
A good solution to this is to have different config files for your environments with the required security privileges and to specify which one to use in your config, like so:

<connectionStrings configSource=”dev.config” />

Create a dev.config in the same folder and specify the value in there. Note, that it should be the entire xml node

  <add name=”xxx” connectionString=”yyy”
    providerName=”System.Data.SqlClient” />

For QA, create another file qa.config with the required settings, and simply change the configSource value to point to qa.config. That way, you just need to change this value in a post-deployment step. Of course, your constraints for security and who sees the config files may be different but you have all the building blocks to adapt to your modus-operandi.
Hope that helps…