Setting provider and connection string in EntityFramework for MySql

c# entity-framework mysql

Question

I am using Entity Framework and in my solution there are 9 projects and it will enlarge. My problem is stating connection string in .config file. When I did like this, I had to state connection string for 4-5 projects and when I wanted to change my connection, changing ConnectionStrings is becoming an obligation for 4 or 5 projects. I want to set connection string in DbContext constructor. DbContext can provide me this ability but I can't define providerName.So dbconnection of context automatically use SqlClient but I want to use MySql provider. My connection string is :

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

Also I can't specify provider name in connection string like "Provider = MySql.Data.MySqlClient". It throws exception "Provider keyword is not supported".

I am changing my question for more comprehensible.

In short I want to to 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 don't know how to state you should use MySql.Data.MySqlClient(without config file). Is it possible ? If it is, how can I do ?

1
6
9/1/2014 10:03:39 AM

Popular Answer

Yours is a connection string used with SqlConnection objects. Since these objects support only SQL Server you cannot use the Provider keyword. Moreover, since you're using EF, you need to specify a different connection string in order to use the model of your database, a context and the Provider keyword. A typical EF connection string would be:

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

In order to use the MySQL provider, consider these steps provided in this answer:

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 and nodes, optionally (this is an absolute must in the second part of my answer, when dealing with dynamically defined databases) you may change the 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 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.

4
5/23/2017 12:10:08 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow