LINQ to SQL offers a quick mechanism to build a data tier in a n-Tier application. There’s a challenge when using the DBML designer in a Class Library. The designer stores the connection string in the Settings file. Although it appears that you can change it in the config file, any changes you make there will be ignored because they are actually retained in the generated Settings file.
While you could go into the DataContext’s .Designer file and change the location of the connection string, any changes you make there will be overwritten if you make changes to the DBML file. So what are you to do?
Remember, one of the nice features added to VB 9 and C# 3 was partial properties. With the DataContext, the code generators add a OnCreated method that is called as part of the context’s constructors. As a result, we can implement the partial method in a separate partial DataContext class that is not changed when the DBML is regenerated. Here’s a sample to do that for the context on this site (LinqBlogDataContext):
Imports System.Configuration Public Class LinqBlogDataContext Private Sub OnCreated() MyBase.Connection.ConnectionString = _ ConfigurationManager.ConnectionStrings("LinqBlogConnectionString") _ .ConnectionString End Sub End Class
When you do this, you can change the connection string in the app.config or web.config and it will be picked up in the business tier correctly. Realize that the design surface will still use the value in the settings in& the Class Library project instead of the config file.