How to Dynamically Create Connection String?

[ThatSamiam]

Hi,

I need to switch back and forth between using a development database and a production database. Rather than edit the config file (and forget to change it back), I would like change the connection string to use the development database while I am developing.

I am pretty close:

Goal:
Config. File: metadata=res://*/MyDbEntities.csdl|res://*/MyDbEntities.ssdl|res://*/MyDbEntities.msl;provider=System.Data.SqlClient;provider connection string="Data Source=MySource; Initial Catalog=MyDb;Integrated Security=True;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient"
Code Attempt: metadata=res://*/MyDbEntities.csdl|res://*/MyDbEntities.ssdl|res://*/MyDbEntities.msl;provider=System.Data.SqlClient;provider connection string="Data Source=MySource;Initial Catalog=MyDb;Integrated Security=True;MultipleActiveResultSets=True"

Private Function CreateConnectionString(ByVal server As String, ByVal database As String) As String
    Dim builder = New EntityConnectionStringBuilder
    Dim sqlBuilder As New SqlConnectionStringBuilder With { _
       .DataSource = server, _
       .InitialCatalog = database, _
       .IntegratedSecurity = True, _
       .MultipleActiveResultSets = True}

    ' Initialize the EntityConnectionStringBuilder.
    Dim entityBuilder As New EntityConnectionStringBuilder With { _
        .Provider = "System.Data.SqlClient", _
        .ProviderConnectionString = sqlBuilder.ToString, _
        .Metadata = String.Format("res://*/{0}Entities.csdl|res://*/{0}Entities.ssdl|res://*/{0}Entities.msl", database)}

    Return entityBuilder.ToString
  End Function

My request is for help on how to include providerName="System.Data.EntityClient" in the connection string.

Thanks.

[jmcilhinney]

You can create separate config files for Debug and Release and have them merged at build. You should look into that.