Set app.config file connection string dynamically

Question

I have a web application that comprises the following:

1. An MVC web project (with a web.config file containing a connection string - but no database access code in it)

2. A data access project (it is a class library, the dll of this project will be put into the above MVC project and accessing data in that) that uses LINQ-SQL classes.

What I need is I have a connection string in the web.config file of my MVC project. I have to write a function in the class library in such a way that the connection string in the app.config, settings.settings and in the dbml file should get modified according to the connection string in the MVC webcofig file. And the data should be taken from the db according to this connection..

Answer 1

Override the constructor of your DataContext like below.

public partial class MyDataContext : System.Data.Linq.DataContext
{
    //This constructor make sure that the connection string is used from the web.config file.
    public MyDataContext()
        : base(ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString, mappingSource)
    {
        OnCreated();
    }
}

Answer 2

You can set the connection string for the DBML files in the Class Library as "None", For the classes in the Class Library which performs DB operations can make use of a connection string which is set from it's constructor.

For eg:

public class DBOperations
    {
        string conn;
        public DBOperations(string connectionString)
        {
            conn = connectionString;
        }

        //Use this connection string in Your functions
    }

This connection string can be set from the web application.And it can be passed as the constructor of corresponding Data Access layer Class.