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

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

But what to do when I'm in the Page_Load, and need to have the grid point to a valid LqDS? At this stage the Context_Creating has not yet been set! So I get an error and an empty grid!

I recall a solution where the MyLinq2Ds.cs which is under the MyLinq2Ds.dbml was changed, after setting it to none. The automatic Visual Studio designer changed that when I changed a field in the DB, and now cannot find how I did it.

The solution to change the DAL Settings.cs (created by clicking the project properties/Settings and then "View Code", and overloading the SettingsLoaded event handler, didn't work for me either, because I want the connection string comming in from the using web project, and not directly in the DAL project!

What should I do?

Answer 4

If you dont want to add the event listener to every page you can follow this link http://goneale.com/2009/03/26/untie-linq-to-sql-connection-string-from-application-settings/