EF and change of server (connection string)

Question

Is it possible to change the name of the server the EF connect to on the fly?

I have the same database on 5 servers (different environments, Dev, Test, UAT...) and would like to write a dashboard application to extract the same information from each server in turn.

Seems a waste to have 5 models - plus the names of the tables conflict with each other.

Anyone done this?

Answer 1

Instead of using the basic constructor you can use the constructor that accepts connection name: http://msdn.microsoft.com/en-us/library/bb739017.aspx and just add numerous connection strings in your .config file

---

Please mark posts as answers/helpful if it answers your question

Answer 2

Hi Lee,

 

Welcome to ADO.NET Entity Framework and LINQ to Entities forum!

 

As Ido has suggested, if the data structures and data table names are similar among the 5 servers, we can pass different entity names to the same generated ObjectContext.  Also, we can set the default container name with the ObjectContext(string, string) constructor.  

=================================================================================
MyNewDatabaseEntities context = new MyNewDatabaseEntities("name=MyNewDatabaseEntities");

MyNewDatabaseEntities context = new MyNewDatabaseEntities("name=MyNewDatabaseEntities1");

MyNewDatabaseEntities context = new MyNewDatabaseEntities("name=MyNewDatabaseEntities2", "MyNewDatabaseEntities2");

 

<connectionStrings>

    <add name="MyNewDatabaseEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=Server1\MICSUNSERVER;Initial Catalog=MyNewDatabase;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

    <add name="MyNewDatabaseEntities1" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlServerCe.3.5;provider connection string=&quot;Data Source=D:\data.sdf&quot;" providerName="System.Data.EntityClient" />

    <add name=" MyNewDatabaseEntities2" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=Server2\MyServer;Initial Catalog=Test;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

</connectionStrings>
=================================================================================

 

Another option would be using EntityConnectionStringBuilder and ObjectContext(EntityConnection) to dynamically create an entity connection string:

=================================================================================

            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();

            sqlBuilder.DataSource = @"Server1\MySERVER";

            sqlBuilder.InitialCatalog = "MyNewDatabase";

            sqlBuilder.IntegratedSecurity = true;

            sqlBuilder.MultipleActiveResultSets = true;

           

            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();

            entityBuilder.Provider = "System.Data.SqlClient";

            entityBuilder.ProviderConnectionString = sqlBuilder.ToString();

            entityBuilder.Metadata = @"res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl";

 

            EntityConnection conn = new EntityConnection(entityBuilder.ToString());

 

            MyNewDatabaseEntities context = new MyNewDatabaseEntities(conn);
=================================================================================

Hope you have a great weekend!

 

 

Best Regards,
Lingzhi Sun

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

Hi Lingzhi Sun,

I have just come across your help re entity connection strings.

I am having difficulties working out the syntax required for the transform web.release/debug.config.

I am getting the error: Format of the initialization string does not conform to specification starting at index 129.

Can you help me with the correct syntax for the entity connection string that is required for the config transform?

Very much appreciated in advance...

Have a great day...

Regards

Michael