实体框架的动态MySQL数据库连接6 [英] Dynamic MySQL database connection for Entity Framework 6
问题描述
我希望将动态连接字符串传递给实体框架上下文。我有超过150个模式是相同的（每个帐户一个），我想选择这样的连接：
ApplicationDbContext db = new ApplicationDbContext（dbName）;
理论上这将是相当容易的，因为我可以创建一个connectionString并将其作为参数传递构造函数，例如：
public ApplicationDbContext（string dbName）：base（GetConnectionString（dbName））
{
}
public static string GetConnectionString（string dbName）
{
//传递的connectionString类似于：
// Server = localhost; Database = {0}; Uid = username; Pwd = password
var connString = ConfigurationManager
.ConnectionStrings [MyDatabase]
.ConnectionString
.ToString（）;
返回String.Format（connString，dbName）;
}
当我传递连接字符串名称时，我可以连接成功，但不能我动态生成它如下。我现在意识到这是因为web.config中的连接字符串中有 providerName =MySql.Data.MySqlClient属性。
当我将实际的连接字符串动态传递给连接时，它假定需要连接到SQL Server而不是MySQL，并且由于连接字符串无效而失败。
问题是，如果我正在动态创建连接字符串，我该如何传递提供者名称？
实体框架6提供了一些方便的微妙变化，有助于使MySQL工作，并创建动态数据库连接。
使用Entity Framework 6获取MySQL < h2>
首先，在我回答这个问题的日期，唯一与EF6兼容的.Net连接器驱动程序是MySQL .Net Connectior 6.8.1（Beta开发版本），可以是发现在这里的官方MySQL网站。
安装后，从Visual Studio解决方案中引用以下文件： p>
- Mysql.Data.dll
- Mysql.Data.Entity.EF6.dll
您还需要将这些文件复制到构建期间可以访问项目的位置，例如bin目录。
接下来，您需要添加一些项目到您的Web.config（或App.config，如果在基于桌面的）文件。
连接字符串：
< connectionStrings>
< add name =mysqlCon
connectionString =Server = localhost; Database = dbName; Uid = username; Pwd = password
providerName =MySql.Data.MySqlClient/> ;
< / connectionStrings>
还可以在< entityFramework /> 和< providers /> 节点，可选地（在处理动态定义的数据库时，这是我答复的第二部分绝对必须的）可以更改< defaultConnectionFactory /> 节点：
的EntityFramework>
< defaultConnectionFactory type =MySql.Data.Entity.MySqlConnectionFactory，MySql.Data.Entity.EF6/>
< providers>
< provider invariantName =MySql.Data.MySqlClienttype =MySql.Data.MySqlClient.MySqlProviderServices，MySql.Data.Entity.EF6/>
< / providers>
< / entityFramework>
如果将defaultConnectionFactory从默认的sql server连接更改，请不要忘记删除嵌套在defaultConnectionFactory节点中的< parameter> 节点。 MysqlConnectionFactory不会为其构造函数采用任何参数，如果参数仍然存在，将失败。
在这个阶段，连接到MySQL很容易与实体，您可以通过名称参考上面的connectionString。请注意，如果通过名称进行连接，即使 defaultConnectionFactory 节点仍然指向SQL Server（默认情况下），这将起作用。
public class ApplicationDbContext：DbContext
{
public ApplicationDbContext（）：base（mysqlCon）
{
}
}
这只是一个正常连接的问题：
ApplicationDbContext db = ApplicationDbContext（）;
连接到动态选择的数据库名称
在这一点上，很容易连接到一个可以作为参数传递的数据库，但有一些我们需要做的事情。
重要说明
如果还没有，如果您想连接到，请务必更改Web.config中的defaultConnectionFactory MySQL
动态。由于我们将直接将连接字符串传递给
的上下文构造函数，所以它不会知道使用哪个提供程序，
将转到其默认连接工厂，除非在
web.config中指定。
您可以将连接字符串手动传递给上下文，如下所示：
public ApplicationDbContext（）：base（Server：localhost; ...）
{
}
但是为了让它更容易一些，我们可以在设置mySQL时对上面提到的连接字符串进行一个小的更改。只需添加如下所示的占位符：
< add name =mysqlConconnectionString =Server = localhost; Database = { 0}; Uid = username; Pwd = passwordproviderName =MySql.Data.MySqlClient/>
现在我们可以构建一个帮助方法并更改ApplicationDbContext类，如下所示：
public class ApplicationDbContext：DbContext
{
public ApplicationDbContext（string dbName）：base（GetConnectionString（dbName））
{
}
public static string GetConnectionString（string dbName）
{
// Server = localhost; Database = {0}; Uid = username; Pwd =密码
var connString =
ConfigurationManager.ConnectionStrings [mysqlCon]。ConnectionString.ToString（）;
返回String.Format（connString，dbName）;
}
}
如果您正在使用数据库迁移，则以下步骤重要的
如果您正在使用迁移，您将发现ApplicationDbContext将被框架传递给您的Seed方法，它将失败，因为它不会传入参数我们放入数据库名称。
将下面的类添加到上下文类的底部（或任何地方）来解决这个问题。
public class MigrationsContextFactory：IDbContextFactory< ApplicationDbContext>
{
public ApplicationDbContext Create（）
{
返回新的ApplicationDbContext（developmentdb）;
}
}
您的代码优先迁移和种子方法现在将定位您的MySQL数据库中的 developmentdb 架构。
希望这有助于某人：）
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");
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);
}
I can connect successfully when I just pass the connection string name, but not when I generate it dynamically as below. I realize now that it's because the connection string in web.config has the providerName="MySql.Data.MySqlClient" attribute in it.
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?
Entity Framework 6 offers some handy subtle changes which aid in both getting MySQL working and also creating dynamic database connections.
Getting MySQL working with Entity Framework 6
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.
After installing, reference the following files from your Visual Studio solution:
- Mysql.Data.dll
- Mysql.Data.Entity.EF6.dll
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.
Next, you need to add some items to your Web.config (or App.config if on desktop based) file.
A connection string:
<connectionStrings>
<add name="mysqlCon"
connectionString="Server=localhost;Database=dbName;Uid=username;Pwd=password"
providerName="MySql.Data.MySqlClient" />
</connectionStrings>
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>
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.
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.
Important Note
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;...")
{
}
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" />
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);
}
}
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");
}
}
Your code-first migrations and seed methods will now target the developmentdb schema in your MySQL database.
Hope this helps someone :)
这篇关于实体框架的动态MySQL数据库连接6的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
