LINQ to SQL connectionstring

I have an application that I want to be able to configure the connection string for my LINQ to SQL. I've tried so many different ways but cannot seem to get it working. I want to do this dynamically in the code when the 开发者_如何学运维app is run, the reason for this is that the user can change the connection settings.

If I delete the connectionString out of app.config the application still works OK (communicating) which makes me wonder where I should be changing the connection string?

I think that the best way to do it is a combination of Albin's and Rup's answers. Have a value in the config file, and then read it at run time and feed it to the context constructor, something like this:

WEB.CONFIG:

<appSettings>
<add key="ConString" Value="The connection string" />

CODE:

//read value from config
var DBConnString = System.Configuration.ConfigurationManager.AppSettings("ConString");

//open connection
var dataContext= new MyDataContext(sDBConnString)

this way you can change the connection string even at runtime and it will work and change on the running program.

You can pass an override connection string into the DataContext constructor:

var db = new MyDataContext("Data Source=Something Else;")

The DBML class (YourDataContext) has an overloaded constructor which takes ConnectionString, so try instantiating that instead of the default one.Get the connection string from app.config and use that to create the instance.

YourDataContext context = new  YourDataContext (ConfigurationManager.ConnectionStrings["ConnStringInAppConfig"].ConnectionString)

You should change it in app.config. The reason it works without is that the LINQ2SQL designer creates a fallback to the connection string used when designing the DBML. If you define a connection string in app.config that is used instead.

By Default your constructor look like this

public dbDataContext() : 
            base(global::invdb.Properties.Settings.Default.Enventory_4_0ConnectionString, mappingSource)
    {
        OnCreated();
    }

You can change return value Instead of

//Original 
public string Enventory_4_0ConnectionString {
            get {
                return ((string)(this["Enventory_4_0ConnectionString"]));
            }
        } 

this

//Modified code
public string Enventory_4_0ConnectionString {
            get {
                return (System.Configuration.ConfigurationManager.ConnectionStrings["Enventory_4_0ConnectionString"].ConnectionString);
            }
        }

Inside your dbml file designer.cs add this dynamic call to base class constructor. It will work for local, dev and prod automatically pulling from current web.config without need to pass connection every time;

    public HallLockerDataContext() : 
    base(ConfigurationManager.ConnectionStrings["MYDB1"].ConnectionString, mappingSource)
    {
        OnCreated();
    }

Usage:

    using (var db = new HallLockerDataContext())
    {

    }