Linq to Sql – Set connection string dynamically based on environment variable

I need to set my connection string for Linq to Sql based on an environment variable. I have a function which will return the connection string from the web.config based on the environment variable, but how do I get Linq to always use this “dynamically created” connection string (preferably without having to specify it every time)?

I know I can specify the connection string using the constructor, but how does that work when using the datacontext in a LinqDataSource?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Use:

MyDataClassesDataContext db = new MyDataClassesDataContext(dynamicConnString);

For a LinqDataSource, intercept the ContextCreating event and create the DataContext manually as above:

protected void LinqDataSource_ContextCreating(object sender, LinqDataSourceContextEventArgs e)
{
    e.ObjectInstance = new MyDataClassesDataContext (dynamicConnString);
}

From MSDN:

By default, the LinqDataSource control
creates an instance of the type that
is specified in the ContextTypeName
property. The LinqDataSource control
calls the default constructor of the
data context object to create an
instance of the object. It is possible
that you have to use a non-default
constructor or you have to create an
object that differs from the one
specified in the ContextTypeName
property. In that case, you must
handle the ContextCreating event and
manually create the data context
object.

Method 2

• Open up the LINQ to SQL designer, and open the Properties tab of the designer (the schema itself), expand Connection and set Application Settings to False. Save.
• Close that down and open up your DataContext designer file (dbml_name.designer.cs) and alter the DataContext constructor. You will immediately notice how your connection string decided to jump in here as you turned off application wide settings. So the part to focus on here is altering the base() inheritor. Renaming ConnString” below to suit your own. I also noticed a DatabaseAttribute on the class which I don’t think plays a big part and has any implications on the connection settings. You will also need a reference to System.Configuration:

  public dbDataContext() : base(ConfigurationManager.ConnectionStrings[“MyConnString”].ConnectionString, mappingSource)

• Open the App.config or Web.config featured in the project where your LINQ to SQL classes reside, and rename the connection string to what you defined as “MyConnString“.
• You now must Cut the entire entry with name change and Paste it into either the App.config or Web.config of the application which is to access the data, such as a web application, Silverlight, WPF, WCF etc. It is important that you alter the configuration file of the calling application which is to access the data, as the ConfigurationManager defined in your LINQ to SQL classes will look for the .config file from where the calling application is executing from, no matter where your LINQ to SQL classes have been Defined. As you can see, it works a little differently from before.
• Now Right Click and open the Properties on your DAL or project containing your LINQ to SQL classes and remove the connection string “Application Setting” reference on the Settings tab.
• Rebuild. You’re all done, now just do a Find in Files check for perhaps your database name that you know was featured in the connection string to check for any stragglers, there shouldn’t be any.

Method 3

The DataContext class has a constructor that takes in a connection string.

Method 4

you can change the connection string dynamically if you will implement the OnCreated() function. This function is a partial function and it can be implemented in seperate file other than where you dbml exists.

for detail please see this article

http://aspilham.blogspot.com/2011/01/how-do-i-set-connection-string-in-linq.html

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0