Setting DataContext Connection String in Data Tier
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.
Public Sub New()
MyBase.New(Global.System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString, mappingSource)
OnCreated
End Sub
Nat (Posted on 10/23/2009 7:18:00 AM)
Just as a FYI - the reason the settings in the class library's app.config are ignored is that it will attempt to get them from the containing application's app.config/web.config. In other words, app.config for class libraries can be seen as a 'template'. Copy the settings from that file into the containing application's own app.config or web.config and the settings will be picked up properly from the config file...
Best regards,
Kristofer
KristoferA (Posted on 10/26/2009 5:29:00 AM)
Public Sub New()
MyBase.New(Global.System.Configuration.ConfigurationManager.ConnectionStrings("NorthwindEFConnectionString").ConnectionString, mappingSource)
OnCreated
End Sub
Kristofer, I've found that if you manage the settings through the IDE, they are updated properly. However, if you simply edit the config file and don't re synchronize the Settings file properly, then those changes are ignored at runtime. This is due the way the Settings file access the values. They are pulled from the DefaultSettingValue attribute rather than from the value in the underlying config file.
<Global.System.Configuration.ApplicationScopedSettingAttribute(), _
Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
Global.System.Configuration.SpecialSettingAttribute(Global.System.Configuration.SpecialSetting.ConnectionString), _
Global.System.Configuration.DefaultSettingValueAttribute("Data Source=.;Initial Catalog=NorthwindEF;Integrated Security=True")> _
Public ReadOnly Property NorthwindEFConnectionString() As String
Get
Return CType(Me("NorthwindEFConnectionString"),String)
End Get
End Property
Jim
Jim Wooley (Posted on 10/27/2009 11:52:00 PM)