Note: The examples are for VB.NET.

I have a separate assembly that hosts the layer containing my LINQ to SQL model. If you're going to use the L2S designer, you need to let L2S store the connectionstring in the settings.settings file, as shown here.

This is great, until you move your website to staging/production.  You need to be able to change this connectionstring by way of a web.config setting.  This method assumes that your L2Q data context has the following settings:

Connection: ExampleConnectionString (MySettings)
Connection String: xxx
Application Settings: True
Settings Property Name: ExampleConnectionString

It turns out that it’s really simple.  Just add a connectionstring entry to override the one in the assembly containing the L2S connectionstring.  Here’s mine for the example above:

  <connectionStrings>
    <add name="App.My.MySettings.ExampleConnectionString"
      connectionString="server=svrprod; uid=secureuser;password=xxx; initial catalog=db"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

The key is the name.  You have to prefix your connection string’s name with “RootNamespaceForYourAssembly.My.MySettings.”.  That’s it.  Your web.config settings will now override the assembly’s settings when you view the website, and yet the designer will continue to use the settings in the assembly!

John

ps.  I do recommend you verify your changes before moving this to production to make sure your web.config connectionstring is being used.  To do this, simply make your production web.config connectionstring point to an invalid server and make sure you get the error on your website.  That way you’ll know for sure that it’s reading it correctly.