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.

[rvandervalk]

I have a similar issue. But, since I already know the connection strings for all my databases, and my testing database, I just keep them in a text file (basically an ini file). When my application gets to the logon screen there is a "change database" dropdown that lists all the possible database connection strings (loaded from the ini file). I simply click on one and that is the database that opens.
Now, in order to do it automatically you can use a technique that I use to automatically fill in a logon id when I'm in development mode. I simply check for whether or not the debugger is attached. If it is you can load your test database, and if it is not (I assume the users are not running under debug mode) simply load the production db.

I know this many not be exactly what you were asking, but I hope it may give you an idea of how you might solve your issue.

[ThatSamIAm]

rvandervalk,

Thanks for the suggestion. I will look into it.