LinqToSql dbml dynamically switches connection string

Question

We have two databases: DEV and STAGING. They are mostly identical. I have an application settings tag in Web.Config call it "mode" and two connection string entries.

If mode = DEV, I want to use ConnectionString 1, otherwise use ConnectionString 2. This works fine in some parts of the application, but dbml does not seem to switch connection strings. I use this function inside the 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 a variety of stored procedures in this legacy application, but dbml seems to always use the Staging connection string.

When I look at the dbml properties, I see that it is hardcoded in a Staging connectionstring, but I thought I was redefining this by changing the .vb constructor for 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 get dbml to use the correct connection string based on the Web.config entry?

Answer 1

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

Code .... I have 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 this instead of using New MyDataContext () .

Alternatively you can call

 dc = New MyDataContext(GetConnectionString()) 

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

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

Answer 2

I like this solution better. You can configure the .dbml constructor to not set an empty default constructor, and then use an incomplete class to create a default constructor. http://blogs.msdn.com/b/jongallant/archive/2007/11/25/linq-and-web-application-connection-strings.aspx