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?

Thorarin · Accepted Answer · 2009-07-10 19:42:35Z

9

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.

edited Jul 10, 2009 at 19:42

answered Jul 10, 2009 at 19:27

Thorarin

48.8k12 gold badges77 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hcabnettek Over a year ago

I don't use VB by choice. I am a C# guy. This particular app was coded in VB.NET and .NET 1.1. We proposed rewriting it in C# and due to time constraints that was shot down. :( Thanks for your answer.

Hcabnettek Over a year ago

This works superb!!! I went the factory methods and updated the everything to Dim db as myDataContext = myDataContext.Create I guess shared is similiar to c# Static as the method shows up in Intellisense. Thanks for the great solution!!

Thorarin Over a year ago

This was actually converted from some C# code. My intention was to create a static method in VB.NET yes :)

m4tt1mus · Accepted Answer · 2011-07-06 20:34:32Z

0

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

answered Jul 6, 2011 at 20:34

m4tt1mus

1,63514 silver badges25 bronze badges

Collectives™ on Stack Overflow

LinqToSql dbml dynamically switch connectionstring

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related