Get LINQ to SQL Connection String from config file

When using Visual Studio 2008 to create LINQ to SQL DBML file, it automatically generates database connection information for us. However most of the time, we want to get the connection string from a config file. Here is how to do that:

• Add ConnectionString to config file

<configuration>
    <configSections>
    </configSections>
  <connectionStrings>
    <add name="DotNetIdeasConnectionString"
      connectionString="Data Source=MyServer;Initial Catalog=DotNetIdeas;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

• Open the DBML file in designer mode. Clear the “Connection String” in properties

• Add partial class, for example:

using System.Configuration;
namespace DotNetIdeasData
{
    partial class DotNetIdeasDataContext
    {
        public DotNetIdeasDataContext()
            : base(ConfigurationManager.ConnectionStrings["DotNetIdeasConnectionString"].ToString(), mappingSource)
        {
            OnCreated();
        }
    }
}

One thing need to be pointed out here is that every time you modify the DBML file, you have to clear that “Connection String” in properties again. Because when you drag/drop tables to the designer, it re-generate everything!