Setting connection string for Entity Framework at runtime


I have a class made with Entity Framework designer.

I have a test and production database that's identical.

I want to switch between test and production at runtime by changing the connection string.

How can I do this in ?
David DAsked:
Who is Participating?
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
When you create an instance of your context, you can pass your connection string but it is not a regular connection string. I use this method in my application to build my EF connection string:
  Public Shared Function EFBDConnectionString(ByVal pMetaData As String, ByVal pDatabaseName As String) As String
        'Specify the provider name, server and database.
        Const kStrProviderName As String = "System.Data.SqlClient"
        'Const strDatabaseName As String = "FGLMainData"

        Dim strServerName As String = GetValueFromConnectionString("Data Source")

        'Initialize the connection string builder for the underlying provider.
        Dim sqlBuilder As New SqlConnectionStringBuilder()

        'Set the properties for the data source.
        sqlBuilder.DataSource = strServerName
        sqlBuilder.InitialCatalog = pDatabaseName
        sqlBuilder.ApplicationName = GetValueFromConnectionString("Application Name") + " (EF)"

        If String.IsNullOrEmpty(GetValueFromConnectionString("Integrated Security")) Then
            sqlBuilder.UserID = GetValueFromConnectionString("UID")
            sqlBuilder.Password = GetValueFromConnectionString("PWD")
            sqlBuilder.IntegratedSecurity = True
        End If

        'Build the SqlConnection connection string.
        Dim strProviderString = sqlBuilder.ToString() + ";MultipleActiveResultSets=true;"

        'Initialize the EntityConnectionStringBuilder.
        Dim entityBuilder = New EntityConnectionStringBuilder()
        'Set the provider name.
        entityBuilder.Provider = kStrProviderName

        'Set the provider-specific connection string.
        entityBuilder.ProviderConnectionString = strProviderString

        'Set the Metadata location.
        entityBuilder.Metadata = String.Format("res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl", pMetaData)

        Return entityBuilder.ToString()
    End Function

Open in new window

David DAuthor Commented:
Hi Eric,

The trouble is that I'm using the class generated by the EF Designer.

There is no way I can see to use the connection string as a parameter to that class.
Éric MoreauSenior .Net ConsultantCommented:
it is in your app.config file
David DAuthor Commented:
Yes, I know.

But it doesnt help much on runtime.

Anyways I found a solution myself about this.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.