Thursday, April 10, 2014

How to use connection string from config file in LINQ To SQL

Most of the time when we are working with Linq To SQL We come across a situation when our application takes the connection string of the database which we used while dragging tables or procedures on dbml designers.But this is not the right way to do.So what we can do in such situation??
Possible answer may be change the connection string in dbml.designer.cs.
But there is another work around and possibly the better approach.We should not modify our dbml designer.cs file manually(the reason is that it will be rewritten when we edit/add a table.Now what is to be done next--

1) Right click on the empty space in designer and view the properties and Set the connection property of your .dbml file to “none”



 2)Now add a separate partial class with same name as in dbml file(in the above example it will be "DataClassesDataContext" with a default parameter less constructor.

public partial class DataClassesDataContext
{
  public DataClassesDataContext() : base(ConfigurationManager.ConnectionStrings["ConnectionString Name"].ConnectionString)
  {
    OnCreated();
  }
}

3) Set the connection string in web.config file

<connectionStrings>
  <add name="democonnection"
  connectionString="Data Source=Rajeev-PC;Initial Catalog=testdb;Persist Security Info=True;Integrated Security="true"
  providerName="System.Data.SqlClient" />

</connectionStrings>

Note:The down side to this approach is that you have to set the Connection property to none every time  you add new objects to the dbml file, but it's not as bad as having to manage the connection string in multiple places.

No comments :

Post a Comment