LinqToSql dbml dynamically switch connectionstring

connection-string, linq-to-sql, web-config

Solution

I would use a factory method in a partial class for the DataContext. Keep in mind that a connection string for a DataContext is different from a regular ADO.NET connection string.

Code.... I've never used VB.NET, but it should be something like this:

Partial Public Class MyDataContext

    ' GetConnectionString code here
    '

    Public Shared Function Create() As MyDataContext
        Return New MyDataContext(GetConnectionString())
    End Function
End Class

Use that instead of using New MyDataContext().

Alternatively, you could call

dc = New MyDataContext(GetConnectionString())

everywhere you get a new instance, but I prefer the factory method.

The basic idea is the same as your subclassing, but without the need for a confusing extra class name. Partial classes are very useful when it comes to Entity Framework (or any code generation tools). You can add business logic methods to your Entity Framework generated classes, etc.

Problem

We have two databases, DEV and STAGING. They are for the most part identical. I have an app settings tag in the Web.Config call it "mode", and two connectionstring entries. If mode=DEV I want to use ConnectionString 1 otherwise use ConnectionString 2. This works fine in some parts of the app, but the dbml doesn't seem to be switching the connection strings. I am using this function inside a Utilities class ``` Public Function GetConnectionString() As String Dim connectionStringToGet = String.Empty Select Case GetCurrentApplicationMode() Case "DEV" connectionStringToGet = "Dev" Case "STAG" connectionStringToGet = "Staging" Case "PROD" connectionStringToGet = "Production" End Select Return ConfigurationManager.ConnectionStrings(connectionStringToGet).ConnectionString End Function ``` This works for the myriad of stored procs in this legacy app, but the dbml, seems to always use the Staging connection string. When I view the properties of the dbml, I see that it is hard coded to the Staging connectionstring, but I thought I was overridding this by changing the designer.vb for the dbml like this ``` Public Sub New() MyBase.New(Utilities.GetConnectionString(), mappingSource) OnCreated End Sub Public Sub New(ByVal connection As String) MyBase.New(connection, mappingSource) OnCreated End Sub Public Sub New(ByVal connection As System.Data.IDbConnection) MyBase.New(connection, mappingSource) OnCreated End Sub Public Sub New(ByVal connection As String, ByVal mappingSource As System.Data.Linq.Mapping.MappingSource) MyBase.New(connection, mappingSource) OnCreated End Sub Public Sub New(ByVal connection As System.Data.IDbConnection, ByVal mappingSource As System.Data.Linq.Mapping.MappingSource) MyBase.New(connection, mappingSource) OnCreated End Sub ``` Is there anything I can do to force the dbml to use the correct connectionstring based on the Web.config entry?

Original source