C# 在实体框架运行时更改数据库，而不更改连接

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

提问by user2197022

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

我有一台托管 50 个具有相同架构的数据库的服务器，我想在我们的下一个版本中开始使用实体框架。

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.

我不需要为每个数据库建立新的连接。一个连接的权限可以与所有 50 个数据库对话，并且为了数据管理和速度（这是一个 WebAPI 应用程序），如果我每次与每个数据库对话，我都不想实例化一个新的 EF 上下文不必，当然，除非每次请求到达服务器时都发生这种情况，否则没什么大不了的。

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

我真正需要的是更改 USE [databasename] 命令的能力，我假设它最终会从 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.??

有没有办法在代码中实现这一点？EF 是否在 Context 中维护了一个读/写属性，该属性指的是在调用 SaveChanges() 等之前可以动态更改的数据库名称？？

Thank you!!!

谢谢！！！

bob

鲍勃

采纳答案by alex.b

You can take a look at:

你可以看看：

• SO questionabout passing existing SQL Connection to EntityFramework Context
• and at this articledescribing how to change database on existing connection.

• SO问题有关将现有的SQL连接到的EntityFramework上下文
• 并在这篇文章中描述了如何更改现有连接上的数据库。

Please let me know if any additional help is needed.

如果需要任何其他帮助，请告诉我。

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

编辑
更新第二个链接指向SqlConnection.ChangeDatabase方法。
所以最终代码看起来类似于以下内容：

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);
}

回答by Anirudh Agarwal

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..

我已经在我当前的项目中实现了这一点，其中我们为项目中的每个客户端都有一个通用的安全数据库和不同的数据库。所以我们的安全数据库有一个表，其中包含每个其他数据库的连接字符串。我们只是传递客户端 id 并获取客户端数据库的连接字符串..

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..

为此，添加两个 EDMX，一个用于公共数据库，另一个用于公共模式数据库。当用户登录或您选择数据库的场景时，请转到公共数据库并获取连接字符串并创建所需数据库的对象。这是任何代码示例，如果有任何问题请告诉我..

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();
        }

回答by Sakhu

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.

已经在 edmx 文件夹的自动生成的 Model.Context.cs 中。

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

为了在运行时连接到多个数据库，我在同一个文件 Model.Context.cs 中创建了另一个将连接字符串作为参数的构造函数，如下所示

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

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

现在，我在 Web.Config 中添加了其他连接字符串，例如

<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

然后，当连接到数据库时，我调用下面的方法将 connectionString 名称作为参数传递

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

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

回答by Gian Maria

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

不支持 EntityConnection.ChangeDatabase 方法，但 SqlConnection.ChangeDatabase 工作正常。

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

所以你必须在实体框架数据库的构造函数中使用 SqlConnection：

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.

使用此代码，您可以读取具有相同格式（相同表名和相同字段）的多个表，并在“架构”字符串中传递数据库名称。

回答by Zeyad Qunees

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 ;)

数据库的结构应该相同;)

回答by Paul D

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:

这是我仅更改数据库名称的解决方案。只需从 web 或 app.config 文件中提取字符串，修改它，然后实例化：

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