How to set the connection string in your LINQ dbml file dynamically (based on web.config)

I've used this technique in my last 3 projects. Ideally, you want your LINQ database connection strings defined outside your compiled code and to be held in the Web.Config instead.

Unfortunately, by default, they are added to your Settings.cs file which is then compiled away into your code. This does the following:

1. reduces your ability to easily configure your applications for different environments (ie you would need to have different compiled dlls for dev, test and production).


2. It is also LESS secure because you can easily use reflection to examine your dlls for passwords - whereas you can encrypt the web.config so it is only viewable to people who have permissions to the IIS console.This is more difficult to break.

The recommendations to remedy this issue in the following MSDN blog http://blogs.msdn.com/jongallant/archive/2007/11/25/linq-and-web-application-connection-strings.aspx is slightly wrong. He suggests that you remove the default constructor in the designer. This is bad because you would have to fix up the file every time you regenerate your dbml file. Instead, you should use what is provided to you and set the property on the designer for "Application Settings" to false.

This then allows you to define a default constructor in your own partial class that extends your dbml context designer classes:

// 

// Copyright (C) 2007 ddkonline

// All rights reserved.

// http://www.ddkonline.com

//

// File - InvestmentManagementDb.cs

// Author – David Klein

// Date Created – 18/02/2008

//

// History of changes

// ---------------------------------------------------------------------------------------

// Version        Author                      Date                Jira No.

// 1.1            David Klein                 18/02/2008          LPP-59

// 

// Description of changes

// ---------------------------------------------------------------------------------------

// Version    Description

// 1.1        DBML now reads from Web.Config via ConfigManager.ConnectionStrings

// ---------------------------------------------------------------------------------------



using System.Configuration;

using ddkonline.InvestmentManagement.Domain.Properties;

using System;



namespace ddkonline.InvestmentManagement.Domain.Entity

{

    public partial class InvestmentManagementDataContext

    {

        public InvestmentManagementDataContext()

            : base(ConfigurationManager.ConnectionStrings["InvestmentManagementConnectionString"].ConnectionString, mappingSource)

        {



        }







    }

}