Provider setup and connection string in EntityFramework for MySql

Question

I am using Entity Framework and there are 9 projects in my solution and it will expand. My problem is that the connection string is in the .config file. When I liked this, I had to specify a connection string for 4-5 projects, and when I want to change my connection, changing ConnectionStrings will be mandatory for 4 or 5 projects. I want to set the connection string in the DbContext constructor. DbContext can provide me this opportunity, but I cannot determine the name of the provider. So the context dbconnection automatically uses SqlClient, but I want to use the MySql provider. My connection string:

"Server=localhost;Database=xxx;Uid=auth_windows;Persist Security Info=True;User=root;Password=yyyyyy;" 

Also, I can’t provide the provider name in the connection string, for example, “Provider = MySql.Data.MySqlClient”. This eliminates the exception. Vendor keyword is not supported.

I am changing my question to a more understandable one.

In short, I want this.

 public class XxContext : DbContext { public XxContext() { this.Database.Connection.Provider = "MySql.Data.MySqlClient"; this.Database.Connection.ConnectionString = "Server=localhost bla bla bla"; } } 

But I do not know how to indicate that you should use MySql.Data.MySqlClient (without a configuration file). Is it possible? If so, how can I do this?

Answer 1

Yours is the one used with SqlConnection objects . Since these objects only support SQL Server, you cannot use the Provider keyword. Moreover, since you are using EF, you need to specify a different connection string in order to use your database model, context, and the Provider keyword. Typical EF connection string :

 <connectionStrings> <add name="AdventureWorksEntities" connectionString="metadata=.\AdventureWorks.csdl|.\AdventureWorks.ssdl|.\AdventureWorks.msl; provider=System.Data.SqlClient;provider connection string='Data Source=localhost; Initial Catalog=AdventureWorks;Integrated Security=True;Connection Timeout=60; multipleactiveresultsets=true'" providerName="System.Data.EntityClient" /> </connectionStrings> 

To use the MySQL provider, review these steps in this answer :

Entity Framework 6 offers some convenient subtle changes that help both in working with MySQL, as well as creating dynamic connections to the database. Getting MySQL to work with Entity Framework 6

First, at the time of my answer to this question, the only .Net connector drivers compatible with EF6 is MySQL.Net Connectior 6.8.1 (beta version), which can be found on the official MySQL website here.

After installation, refer to 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 available for the project at build time, for example, a directory.

Then you need to add some elements to your Web.config (or App.config if on the desktop).

Connection string:

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

Also add a provider, inside and nodes, optional (this is an absolute obligation in the second part of my answer, dealing with dynamically defined databases), you can change the value of 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 changed defaultConnectionFactory from the sql server's default connection, be sure to remove the nodes that are nested in the defaultConnectionFactory node. MysqlConnectionFactory does not accept any parameters for its constructor and will not work if the parameters still exist.

Answer 2

You can use DbProviderFacory , which is registered in the configuration file (see MSDN and SO articles ).

Your configuration file should have something like this

 <DbProviderFactories> <remove invariant="MySql.Data.MySqlClient" /> <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" /> </DbProviderFactories> 

And your implementation of DbContext and IDbContextFactory looks something like this ...

Dbcontext

 public class MyDbContext : DbContext { public MyDbContext(DbConnection connection, bool contextOwnsConnection) : base(connection, contextOwnsConnection) { } } 

IDbContextFactory

 public class MyDbContextFactory : IDbContextFactory<MyDbContext> { private readonly string _connectionStringName; public MyDbContextFactory(string connectionStringName) { Contract.Requires<NullReferenceException>( !string.IsNullOrEmpty(connectionStringName), "connectionStringName"); _connectionStringName = connectionStringName; } public MyDbContext Create() { var connectionStringSettings = ConfigurationManager .ConnectionStrings[_connectionStringName]; var connection = DbProviderFactories .GetFactory(connectionStringSettings.ProviderName) .CreateConnection(); if (connection == null) { var message = string.Format( "Provider '{0}' could not be used", connectionStringSettings.ProviderName); throw new NullReferenceException(message); } connection.ConnectionString = connectionStringSettings .ConnectionString; return new MyDbContext(connection, true); } } 

Associates with a context constructor that can programmatically include DbProvider . The context is configured for its own connection, so that it can open upon first use and close when deleted.