Change Entity connection string at runtime

Question

Hello !

I'm using entity framework 6.02 and Vb.net with Sql server databases.

I have 4 identical databases on 4 different servers.I create Entity through wizard with one of the databases.Now I want to modify the connection string  on runtime , to connect with other databases.

This is how I do this change :

This is the connection string on app.config inside visual studio  ( I have changed server name with {SERVER} and database name with {DATABASE}

<connectionStrings>
    <add name="MyEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source={SERVER};initial catalog={DATABASE};integrated security=True;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

This is the code to change the connection string :

Public Cnstring as string

Dim connstringtemplate As String = System.Configuration.ConfigurationManager.ConnectionStrings(1).ConnectionString.ToString
 
Cnstring = connstringtemplate.Replace("{DATABASE}", "MyDB2").Replace("{SERVER},"PC2").Tostring

This is My Entity class to accept connection string as parameter :

Partial Public Class MyEntities
    Inherits DbContext  
Public Sub New(connectionString As String)
        If String.IsNullOrWhiteSpace(connectionString) Then
            Throw New ArgumentNullException("connectionString")
        End If
        Database.Connection.ConnectionString = connectionString
    End Sub

 

This is Entity declaration :
context = New MyEntities(cnstring)

But now on runtime i get an error on the line

Database.Connection.ConnectionString = connectionString

An unhandled exception of type 'System.ArgumentException' occurred in System.Data.dll
 
Additional information: Keyword not supported: 'metadata'.

What's wrong with this code ?

Thank you !

Answer 1

Hi. You don't need the metadata on the connection string. something like below should work

<connectionStrings>
    <add name="myEntities" connectionString="Data Source={SERVER};initial catalog={DATABASE};integrated security=True;multipleactiveresultsets=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

.

Answer 2

Thank you !

But the connection string that I wrote and the entire app.config file is created automatically by visual basic during the wizard for creation of entity.So if I don't need metadata , why the wizard included them on connection string ?

Thank you !

Answer 3

Hello,

>>So if I don't need metadata , why the wizard included them on connection string ?

The "Database.Connection.ConnectionString" accepts a standard ADO.NET connection string wothout metadata.

>>But the connection string that I wrote and the entire app.config file is created automatically by visual basic during the wizard for creation of entity.

The automatically generated connection string by the model in the .config file is Entity Framework connection string wihich needs the metadata and provider name and the standard connection string for ADO.NET.

For more information regarding EF connection, there is a description:
http://msdn.microsoft.com/en-us/library/cc716756(v=vs.110).aspx

Regards.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 4

Hi, not sure you could directly use it in VB (C# code) but it can give you ideas

EntityConnectionStringBuilder entityConnBuilder = new EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["YouAppConfigConnectionStringName"].ConnectionString);
SqlConnectionStringBuilder sqlConnBuilder = new SqlConnectionStringBuilder(entityConnBuilder.ProviderConnectionString);
sqlConnBuilder.InitialCatalog = MyDBName;
entityConnBuilder.ProviderConnectionString = sqlConnBuilder.ConnectionString;
return entityConnBuilder.ConnectionString;

you just have to pass your needed parameters to construct your own connection string to the fly
you can totally reconstruct your connection string, I test it for multi tenant applications and it works fine