How to use a SQL connection string with ADO.NET Entity Data Model

I have done this before, just pass the connection string in one of the overloaded constructor of the auto generated DbContext derived class.

Entity contexts have constructors that take the connection string as an argument. I believe you can either provide the string itself, or the name of a string from your connectionStrings element in your config files.

So the trick is to avoid creating your contexts by just saying new EntityContext(). One simple way to do this is to use a factory.

public class ContextFactory : IContextFactory
{
    public EntityContext Get()
    {
        var connectionString = ... // Do some logic to get the connection string
        return new EntityContext(connectionString);
    }
}

...

using (var context = new ContextFactory().Get())
{
    ...
}

The ideal way to use this is in connection with dependency injection, so that you're not even tightly coupled with the new ContextFactory() implementation:

public class MyRepository : IMyRepository
{
    private readonly IContextFactory _contextFactory;
    public MyRepository(IContextFactory contextFactory)
    {
        _contextFactory = contextFactory;
    }
    public GetObjects()
    {
        using(var context = _contextFactory.Get())
        {
        }
    }
}

This gives you ultimate flexibility when it comes to deciding how your contexts will be instantiated.

Try this:

In web.confige, create a copy of your existing connection string and give it a different name. Change the connection string attributes for this new connection string. e.g. If you previously had a connection string declared as

   <add name="MyEntities" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MySQL1;initial catalog=MyDB;user id=sa;password=password123;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Add another one giving it a new name

<add name="MyEntities2" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MySQL2;initial catalog=MyDB;user id=sa;password=password987;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Then in your code files, add the "using System.Data.EntityClient;" Namespace. Declare an entityConnection object and assign the default server.

EntityConnection econn = new EntityConnection("name=MyEntities");

YOu can access the default DB using the following code

using (var _context = new MyEntities(econn))
        { .......}

Later in code you can switch between the servers using the following code:

econn = new EntityConnection("name=MyEntities2");

YOu can access the second DB using the following code

using (var _context = new MyEntities(econn))  <== This will point to second server now.