对于实体框架6动态MySQL数据库连接 [英] Dynamic MySQL database connection for Entity Framework 6

问题描述

我想一个动态连接字符串传递到实体框架背景。我有超过150架构这是相同的（每个帐户之一），我想选择连接这样：

I wish to pass a dynamic connection string to the entity framework context. I have over 150 schemas which are identical (one per account) and I would like to select the connection as such:

ApplicationDbContext db = new ApplicationDbContext("dbName");

在理论上，这将是相当容易的，因为我可以创建一个的connectionString，并传递作为构造函数的参数，例如：

In theory this would be fairly easy, as I can create a connectionString and pass it as the argument for the constructor, for example:

public ApplicationDbContext(string dbName) : base(GetConnectionString(dbName))
{
}

public static string GetConnectionString(string dbName)
{
    // The connectionString passed is something like:
    // Server=localhost;Database={0};Uid=username;Pwd=password
    var connString =  ConfigurationManager
                         .ConnectionStrings["MyDatabase"]
                         .ConnectionString
                         .ToString();

    return String.Format(connString, dbName);
}

我可以成功连接的时候，我传递的连接字符串的名称，而不是当我动态生成它下面。我现在认识到，这是因为在web.config中的连接字符串的的providerName =MySql.Data.MySqlClient属性在里面。

当我动态传递的实际连接字符串连接，虽然，它假定它需要连接到SQL Server而不是MySQL和失败，因为连接字符串是无效的。

When I pass the actual connection string dynamically to the connection though, it assumes that it needs to connect to SQL Server rather than MySQL and fails due to the connection string being invalid.

现在的问题是，如何通过提供者的名称，以连接字符串，如果我动态地创建呢？

The question is, how do I pass the provider name to the connection string if I am creating it dynamically?

推荐答案

实体框架6提供其帮助双方获得MySQL的工作，也是创建动态数据库连接方便一些微妙的变化。

Entity Framework 6 offers some handy subtle changes which aid in both getting MySQL working and also creating dynamic database connections.

首先，在我回答这个问题的日期，兼容的唯一的.Net连接器司机EF6是MySQL的.Net Connectior 6.8.1（测试版开发版本），它可以找到的at官方MySQL的网站这里。

First, at the date of my answering this question, the only .Net connector drivers compatible with EF6 is the MySQL .Net Connectior 6.8.1 (Beta development version) which can be found at the official MySQL website here.

安装完成后，从Visual Studio解决方案参考以下文件：

After installing, reference the following files from your Visual Studio solution:

• Mysql.Data.dll


• Mysql.Data.Entity.EF6.dll

您还需要地方复制这些文件，他们将在编译时间是项目访问，如bin目录。

You will also need to copy these files somewhere where they will be accessible to the project during build time, such as the bin directory.

接下来，你需要一些项目添加到您的Web.config（或App.config中，如果在基于桌面的）文件。

Next, you need to add some items to your Web.config (or App.config if on desktop based) file.

一个连接字符串：

<connectionStrings>
    <add name="mysqlCon"
         connectionString="Server=localhost;Database=dbName;Uid=username;Pwd=password" 
         providerName="MySql.Data.MySqlClient" />
</connectionStrings>

另外添加提供程序，＆LT内部;的EntityFramework /＆GT; 和＆LT;供应商/＆GT; 节点，任选的（这是我的回答的第二部分是绝对必须的，具有动态定义的数据库打交道时），你可以修改＆LT; defaultConnectionFactory /＆GT; 节点：

Also add the provider, inside the <entityFramework /> and <providers /> nodes, optionally (this is an absolute must in the second part of my answer, when dealing with dynamically defined databases) you may change the <defaultConnectionFactory /> node:

<entityFramework>
    <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6" />
    <providers>
        <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
    </providers>
</entityFramework>

如果您更改默认的SQL Server连接的defaultConnectionFactory，不要忘记删除＆LT;参数＆GT;后者嵌套在defaultConnectionFactory节点节点。该MysqlConnectionFactory不承担其构造任何参数，如果参数仍然会失败。

If you change the defaultConnectionFactory from the default sql server connection, don't forget to remove the <parameter> nodes which are nested in the defaultConnectionFactory node. The MysqlConnectionFactory does not take any parameters for its constructor and will fail if the parameters are still there.

在这个阶段，它很容易与实体连接到MySQL，你可以参考上面的connectionString的名字。需要注意的是，如果连接的名字，这将工作，即使 defaultConnectionFactory 节点仍然指向在SQL服务器（它在默认情况下）。

At this stage, it's quite easy to connect to MySQL with Entity, you can just refer to the connectionString above by name. Note that if connecting by name, this will work even if the defaultConnectionFactory node still points at SQL Server (which it does by default).

public class ApplicationDbContext: DbContext
{
    public ApplicationDbContext() : base("mysqlCon")
    {
    }
}

在它仅仅是一个正常的连接物：

The it is just a matter of connecting normally:

ApplicationDbContext db = ApplicationDbContext();

---

连接到动态选择的数据库名称

在这一点上可以很容易地连接到我们可以作为一个参数传递一个数据库，但有几件事情，我们需要做的。

---

Connecting to a dynamically selected database name

At this point it's easy to connect to a database which we can pass as a parameter, but there's a few things we need to do.

如果你还没有，则必须更改defaultConnectionFactory Web.config中，如果你想连接到MySQL
  动态。由于我们将直接传递一个连接字符串
  上下文构造函数，它将不知道要使用哪个提供商
  将转向它的默认连接工厂，除非指定
  web.config中。见上文关于如何做到这一点。

If you have not already, you MUST change the defaultConnectionFactory in Web.config if you wish to connect to MySQL dynamically. Since we will be passing a connection string directly to the context constructor, it will not know which provider to use and will turn to its default connection factory unless specified in web.config. See above on how to do that.

您可以手动传递一个连接字符串，这样的背景：

You could pass a connection string manually to the context like this:

public ApplicationDbContext() : base("Server:localhost;...")
{
}

但要使其稍微容易一些，我们可以做一个小改动设置MySQL时，我们上面所做的连接字符串。只需添加一个占位符，如下所示：

But to make it a little bit easier, we can make a small change to the connection string we made above when setting up mySQL. Just add a placeholder as shown below:

<add name="mysqlCon" connectionString="Server=localhost;Database={0};Uid=username;Pwd=password" providerName="MySql.Data.MySqlClient" />

现在我们可以建立一个辅助方法，改变ApplicationDbContext类，如下所示：

Now we can build a helper method and change the ApplicationDbContext class as shown below:

public class ApplicationDbContext: DbContext
{
    public ApplicationDbContext(string dbName) : base(GetConnectionString(dbName))
    {
    }

    public static string GetConnectionString(string dbName)
    {
        // Server=localhost;Database={0};Uid=username;Pwd=password
        var connString = 
            ConfigurationManager.ConnectionStrings["mysqlCon"].ConnectionString.ToString();

        return String.Format(connString, dbName);
    }
}

如果您正在使用的数据库迁移，下面的步骤很重要

如果您使用的是迁移，你会发现ApplicationDbContext将通过框架传递给你的种子的方法，它会失败，因为它不会被传入我们把数据库名称的参数。

If you are using database migrations, the following step is important

If you are using migrations, you will find that the ApplicationDbContext will be passed to your Seed method by the framework and it will fail because it will not be passing in the parameter we put in for the database name.

以下类添加到您的上下文类的底部（或任何地方真的）来解决这个问题。

Add the following class to the bottom of your context class (or anywhere really) to solve that problem.

public class MigrationsContextFactory : IDbContextFactory<ApplicationDbContext>
{
    public ApplicationDbContext Create()
    {
        return new ApplicationDbContext("developmentdb");
    }
}

您code-第一迁移和种子的方法，现在将目标锁定在 developmentdb 模式在你的MySQL数据库。

Your code-first migrations and seed methods will now target the developmentdb schema in your MySQL database.

希望这可以帮助别人：）

Hope this helps someone :)

这篇关于对于实体框架6动态MySQL数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！