LinqToSql dbml dynamically switch connectionstring

Question

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?

Answer 1

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.