Change Database during runtime in Entity Framework, without changing the Connection

Question

I have a server that hosts 50 databases with identical schemas, and I want to start using Entity Framework in our next version.

I don't need a new connection for each of those databases. The privileges of the one connection can talk to all of the 50 databases, and for data management and speed (this is a WebAPI application) I don't want to instantiate a new EF context every time I talk to each of the databases if I don't have to, unless of course if this occurs each time a request comes to the server then no big deal.

All I really need is the ability to change the USE [databasename] command, which I assume eventually gets sent to the server from EF.

Is there a way to accomplish this in code? Does EF maintain a read/write property in the Context that refers to the database name that could be changed on the fly before calling SaveChanges(), etc.??

Thank you!!!

bob

score 14 · Answer 1 · answered Apr 15 '15 at 21:01

Don't Work hard, work smart !!!!

MYContext localhostContext = new MYContext();
MYContext LiveContext = new MYContext();
//If your databases in different servers
LiveContext.Database.Connection.ConnectionString = LiveContext.Database.Connection.ConnectionString.Replace("localhost", "Live");
//If your databases have different Names
LiveContext.Database.Connection.ConnectionString = LiveContext.Database.Connection.ConnectionString.Replace("DBName-Localhost", "DBName-Live");

the structure for databases should be the same ;)

score 5 · Accepted Answer · edited May 23 '17 at 10:31

5

You can take a look at:

SO question about passing existing SQL Connection to EntityFramework Context
and at this article describing how to change database on existing connection.

Please let me know if any additional help is needed.

Edited
Updated 2nd link to point to SqlConnection.ChangeDatabase method.
So eventually code would look similarly to the following:

MetadataWorkspace workspace = new MetadataWorkspace(
  new string[] { "res://*/" }, 
  new Assembly[] { Assembly.GetExecutingAssembly() });

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
using (EntityConnection entityConnection = new EntityConnection(workspace, sqlConnection))
using (NorthwindEntities context = new NorthwindEntities(entityConnection))
{
  // do whatever on default database
  foreach (var product in context.Products)
  {
    Console.WriteLine(product.ProductName);
  }

  // switch database
  sqlConnection.ChangeDatabase("Northwind");
  Console.WriteLine("Database: {0}", connection.Database);
}

edited May 23 '17 at 10:31

Community

1
1

answered Sep 17 '13 at 05:06

alex.b

4,547
1
31
52

Second link is dead. Anywhere I can get a gist of this article please? – Lionet Chen Jun 23 '16 at 01:56
1

Note that `ChangeDatabase` requires that the connection is already open before it is called, otherwise it will throw an `InvalidOperationException`. – Josh Jan 04 '17 at 19:04
Also, at least with SQL Azure and probably other systems, the `USE` syntax (which is what is done behind the scenes) can be disabled. If that is the case an exception will be thrown when `ChangeDatabase` is called. – Josh Jan 04 '17 at 19:10
Shouldn't the last `Console.WriteLine` use `sqlConnection.Database` instead of `connection.Database` ? – drigoangelo Mar 08 '19 at 14:07

score 5 · Answer 3 · edited May 29 '14 at 21:04

It is very simple

I had

public WMSEntities() : base("name=WMSEntities") //WMSEntities is conection string name in web.config also the name of EntityFramework
{
}

already in autogenerated Model.Context.cs of edmx folder.

To connect to multiple database in runtime, I created another constructor that takes connection string as parameter like below in same file Model.Context.cs

public WMSEntities(string connStringName)
            : base("name=" + connStringName)
{
}

Now, I added other connection string in Web.Config for example

<add name="WMSEntities31" connectionString="data source=TESTDBSERVER_NAME;initial catalog=TESTDB;userid=TestUser;password=TestUserPW/>

<add name="WMSEntities" connectionString="data source=TESTDBSERVER_NAME12;initial catalog=TESTDB12;userid=TestUser12;password=TestUserPW12/>

Then, when connecting to database I call below method passing connectionString name as parameter

public static List<v_POVendor> GetPOVendorList(string connectionStringName)
{

   using (WMSEntities db = new WMSEntities(connectionStringName))
   {               
       vendorList = db.v_POVendor.ToList();
   }
}

score 1 · Answer 4 · answered Jul 07 '15 at 16:03

Here's my solution for just changing the database name. Simply pull the string from the web or app.config file, modify it, and then instantiate:

        string yourConnection = ConfigurationManager.ConnectionStrings["MyEntities"].ConnectionString.Replace("MyDatabase", yourDatabaseName);
        dcon = new MyEntities(yourConnection);

score 0 · Answer 5 · answered Sep 17 '13 at 11:24

I have implemented this in my current project in which we have a common security database and different database for every client in the project. So our security database has a table that contain connection string for every other database. We just pass client id and get the connection string of the client database..

For this add two EDMX one for the common database and other for common schema databases. When user login or what might be your scenario to choose database go to common databse and get the connection string and create object of the needed database. Here is Code sample any, if any quer let me know..

You can keep connection string regarding every other database in a table in a a common database shared by all the other database.

EntityInstance_ReviewEntities.GetContext(GetConnectionString(ClientId));


private string GetConnectionString(int TenantId)
        {
            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
            ISecurityRepository objSecurity = new SecurityRepository();
            string tenantConnectionString = objSecurity.GetClientConnectionString(TenantId);
            entityBuilder.ProviderConnectionString = tenantConnectionString;
            entityBuilder.Provider = "System.Data.SqlClient";
            entityBuilder.Metadata = @"res://*/ClientEntity.YourEntity.csdl|res://*/ClientEntity.ADBClientEntity.ssdl|res://*/ClientEntity.YourEntity.msl";
            return entityBuilder.ToString();
        }

score 0 · Answer 6 · answered Jul 09 '14 at 15:35

EntityConnection.ChangeDatabase method is not supported, but SqlConnection.ChangeDatabase works fine.

So you have to use SqlConnection in entity framework database's constructor:

using MvcMyDefaultDatabase.Models;
using System.Data.Metadata.Edm;
using System.Data.SqlClient;
using System.Data.EntityClient;
using System.Configuration;
using System.Reflection;

    public ActionResult List(string Schema)
    {
        SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);

        MetadataWorkspace workspace = new MetadataWorkspace(new string[] { "res://*/" }, new Assembly[] { Assembly.GetExecutingAssembly() });

        EntityConnection entityConnection = new EntityConnection(workspace, sqlConnection);

        sqlConnection.Open();

        sqlConnection.ChangeDatabase(Schema);

        Models.MyEntities db = new MyEntities(entityConnection);

        List<MyTableRecords> MyTableRecordsList = db.MyTableRecords.ToList();

        return View(MyTableRecordsList);
    }

With this code you can read the tables with the same format (same table name and same fields) of several schema passing the database name in the "Schema" string.

score 0 · Answer 7 · answered Jul 22 '21 at 05:54

For SQL Server, if you want to change only the database, not a connection, try:

public class XXXXDbContext : DbContext
{

    public string databaseName
    {
        set
        {
            Database.GetDbConnection().Open();
            Database.GetDbConnection().ChangeDatabase(value);
        }
    }
}

Change Database during runtime in Entity Framework, without changing the Connection

7 Answers7

Linked