set connection string in LINQ dbml file Default Constructor from web.config

 How Do I? Change Connection String of datacontext default constructor from web.config

Using Ling to Sql makes it really easy to easily create a data layer but not planing properly cost at later stage of development when time is critical and things are not working out as they suppose to be.

while doing maintenance in a website i noticed that the initial design or architect decision was taken or without any decision , Ling to SQL generator default constructor was used to handle the database connection string.

when working with Ling to SQL, it generates many constructors. Default constructor of dbml file will always use the data string that it generates and stored in settings class. If different database will be used in development, it will record all database connection strings and will use the last database connection string. while this works out for development time, on deployment in staging or live, it always give trouble to beginners as it is using connection string from the settings class and that points to local system.

the initial response to this error is to hard code the staging or live connection string in dbml default constructor and deploy it. while this works out perfectly, every now change will have to be recompiled as dbml file will always discard changes.

solution to this problem is to always create a connection helper class that will return you the datacontext object and it will help you in future to use the load balancing and fail over techniques.

but to solve this problem of realization after deveopment there is a way out. dbml datacontext class is a partial class and every function of CRUD it generates against each table in this class is a partial function. To learn more about Partial class and Partial function click here.

Simple we can add more functionaliy in the partial class and we can implement the partial function. In our case if we examine the default constructor of dbml file, it shows a call to OnCreated partial function. we can give definition to this function and this will solve our problem. here is the default constructor of a sample dbml

public partial class DataLayerDataContext : System.Data.Linq.DataContext
    {
        
        private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
        
    #region Extensibility Method Definitions
    partial void OnCreated();
}

when a default constructor will be called, our implementation of OnCreated() will be called which is residing in a different class file inside the project. Then we can change the connection string from the web.config file. here is the implementation of partial function OnCreated.

NOTE: you have to add reference to System.Web.Configuration to access WebConfigurationManager class

public partial class DataLayerDataContext : System.Data.Linq.DataContext
    {
        partial void OnCreated()
        {
            string connectionString = WebConfigurationManager.ConnectionStrings["AnswersExpertsConnectionString"].ToString();
            this.Connection.ConnectionString = connectionString;
        }
    }

Hope this will help beginners and are facing such problem.