C# How to get the Linq To SQL DBML file to read the connection string from your web.config or app.config

You know the drill. You start a new project and drag and drop the DBML file to a project to connect to a SQL (or one of the supported databases).

Only to find  that the DBML file REFUSES to read changes to the connection string specified in your app.config or web.config file.

I recently figured out a clean and effective way to do this and wanted to put it out there – primarily for my own sake.

Considering you are working on a web project and have restricted your data-access layer to a DLL project – this is the worst case scenario. It is easier to have the DBML file inside of your web project and change the connection string.

Anyways, here goes. This should probably work for all scenarios.

Step 1: Add the connection string to your web.config
For example:

<connectionStrings>
 <add name="DefaultConnection" 
      connectionString="Server=.\SQLEXPRESS;Database=my-awesome-db;Trusted_Connection=True;" 
      providerName="System.Data.SqlClient" /> 
</connectionStrings>

Step 2: Create your DBML file and set it up.

In this example, I am naming the file: Db.dbml and have put in a Dll Project\Db\ folder so that we have the file at location: Project\Dll\Db\Db.dbml

01-Dbml structure

On expanding the file, you’ll get two additional files: Db.dbml.layout and Db.designer.cs

Step 3: Set the Connection String to (None) for the dbml file

If you double-click on the Db.dbml file in the solution explorer, the designer window opens up.
Right click on the white space of the designer and select Properties

You will see something like this in your Properties inspector

02-ConnectionStrings

This next step is important as it will let you create a new Empty Constructor to plug in the value from your web.config

Select the drop down next to the Connection property and select (None)

Save your changes.

03-ConnectionString-None

Step 4: Create a new partial class to plug in the connection string from the web.config

Add a new class to the Db folder. I call mine Db.cs which is the same as the dbml file. Though the name is not important.

Open up the class file and define an empty constructor like this:

Of course, depending on your project and dbml file name, the name of the constructor, namespace and class will change.

namespace Project.Db
{
 public partial class YourDbDataContext : System.Data.Linq.DataContext
 {
     public YourDbmlFilenameDataContext() :
         base(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString, mappingSource)
     {
         OnCreated();
     }
 }
}

My web.config contains the connection inside the DefaultConnection property.
Please add the property name from your web config inside the base() shown above and you should be good to go.

Questions? Concerns? Better ways to do this? Please let me know in the comments below.
Thanks.