在多个数据库上使用实体框架

I am writing a payroll system that will integrate with a pre-existing system. The original system had a master database that handled user management and some global configuration, below that there are multiple databases each identical in structure, basically each database is one companies payroll database, all these are tied to the main database because it belongs to a parent company who has many subsidiaries each with their own HR department.

我正在编写一个与预先存在的系统集成的工资单系统。原始系统有一个处理用户管理和一些全局配置的主数据库，下面有多个数据库，每个数据库在结构上相同，基本上每个数据库都是一个公司工资单数据库，所有这些数据库都绑定到主数据库，因为它属于父数据库公司拥有多家子公司，每家公司都有自己的人力资源部

What I was wondering is if there is any way that I can, based on either a cookie or another method that stores what company they wish to connect to, dynamically change the entity frameworks target database based on their input using a filter?

我想知道的是，如果有任何方法，我可以根据cookie或其他方法存储他们希望连接的公司，使用过滤器根据输入动态更改实体框架目标数据库？

Here's an example:

这是一个例子：

User A logs in to the site, page loads with available companies that the user has permission to access, user will then select a company, they have admin privileges in that company, they add an employee, before that action is run, asp.net will switch the connection string to the appropriate database then add the record.

用户A登录到站点，页面加载用户有权访问的可用公司，然后用户将选择公司，他们在该公司拥有管理员权限，他们添加员工，在该操作运行之前，asp.net将连接字符串切换到适当的数据库，然后添加记录。

2 个解决方案

#1

EF6 has better support for multiple DB access from Same context. Here is a snippet from EF5. Managing the database initializer setting prior is important. You may not want to trigger ANY migrations. i.e, use this before

EF6更好地支持来自Same上下文的多个DB访问。这是EF5的一个片段。之前管理数据库初始化程序设置很重要。您可能不想触发任何迁移。即，之前使用它

Database.SetInitializer(new ContextInitializerNone<MyDbContext>());

Database.SetInitializer（new ContextInitializerNone （））;

but to answer the question: Yes you can

但要回答这个问题：是的，你可以

var conn = GetSqlConn4DbName(dataSource,dbName );
var ctx = new MyDbContext(conn,true);



public DbConnection GetSqlConn4DbName(string dataSource, string dbName) {
        var sqlConnStringBuilder = new SqlConnectionStringBuilder();
        sqlConnStringBuilder.DataSource = String.IsNullOrEmpty(dataSource) ? DefaultDataSource : dataSource;
        sqlConnStringBuilder.IntegratedSecurity = true;
        sqlConnStringBuilder.MultipleActiveResultSets = true;

        var sqlConnFact = new SqlConnectionFactory(sqlConnStringBuilder.ConnectionString);
        var sqlConn = sqlConnFact.CreateConnection(dbName);
        return sqlConn;
    }


 public class ContextInitializerNone<TContext> : IDatabaseInitializer<TContext> where TContext : DbContext
{
    public void InitializeDatabase(TContext context) {  }
}

Also see * answer using migration, sample code, and dynamic db connection

另请参阅使用迁移，示例代码和动态数据库连接的*应答

#2

It is very simple. I have:

这很简单。我有：

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

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 connetionString name as parameter

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

public static List<v_POVendor> GetPOVendorList(string connectionStringName)
{
    using (WMSEntities db = new WMSEntities(connectionStringName))
    {               
        vendorList = db.v_POVendor.ToList();                 
    }
}

#1