How to change connection string of a dbml file ( LINQ 2 SQL ) dynamically using app.config.

Linq 2 Sql : INQ to SQL is an ORM (object relational mapping) implementation, which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it. LINQ to SQL fully supports transactions, views, and stored procedures.


I wanted my Linq 2 Sql’s connection string to be defined outside by compiled code, so that I could easily change between connection strings without having to recompile my code. There are many ways discussed on blogs, few of them are as listed below :

but none of them are as effective and easy to maintain as this one.


So, here is how you do it.

Step 1 : Set the connection property of your .dbml file to “none”.

Step 2 : Create a new separate partial class with the same name as that of the existing partial class for the .dbml file. And set the connectionString property by using the parameterless constructor.

public partial class DataClassesDataContext  
public DataClassesDataContext() : base(ConfigurationManager.ConnectionStrings["Dev-connString"].ConnectionString)

Step 3 : Almost Done ! Lastly you need to define your connectionString in your app.config file, as shown below.

<?xml version="1.0" encoding="utf-8"?>  
connectionString="Data Source=yasser-home;Initial Catalog=pp;Persist Security Info=True;User ID=sa;Password=gogole"
providerName="System.Data.SqlClient" />
You can now easily change the connectionString from the app.config file without having to re-compile your code, which would be the case otherwise.
Why did I create a seperate partial class ? Can't I edit the existing Dbml.designer.cs file ?
Don't modify Dbml.designer.cs file manually, because it will be rewritten when you add/edit/delete a table, stored proc etc.
Hope this helps :)