In this article, we shall learn how to use MySql database with Entity Framework in ASP.NET MVC application.
Introduction
MySql is a very favorite database for most of the developers and companies at their initial stages. The main reason behind this is that it is free and easy to use; on top of it the performance of MySql is also not bad.
In fact, the 1st release of this website DotNetFunda.com was also backed with MySql database and it remain on MySql for couple of years.
I must accept that I had enjoyed working with MySql. As time passes, new releases and enhancements are added to MySql and it is now a good choice for .NET developers also to back their application with MySql.
In this article, we shall learn how to use MySql database with Entity Framework in ASP.NET MVC application.
Installing MySql package for Entity Framework
The first step we need to take is to install the MySql.Data.Entities package to our application using Manage NuGet Packages. To do this, right click the project Solution and select Manage NuGet Packages and then write MySql in the search box under Browse tab as displayed in the picture below.
This shows below search result, select MySQL.Data.Entities and then click Install to install this package.
Successful installation should place two .dlls into the project as shown below.
Configuring web.config file
The next step is to configure the web.config file.
1. - ADD
First, add a connection string, in our case it is MySqlConnection. Note that the providerName is MySql.Data.MySqlClient.
<connectionStrings>
<add name="MySqlConnection" connectionString="Server=dotnetfunda;Database=dotnetfunda;Uid=dotnetfunda;Pwd=Dotnetfunda;"
providerName="MySql.Data.MySqlClient" />
</connectionStrings>2. - CHECK
Next, Double check that following configuration is added to the last of the web.config (before </configuration>) file when the MySql package was getting installed by NuGet packages. If it is not, add following yourself.
<system.data>
<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.8.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>3. ADD
Next, add a separate provider for MySql, note that the type attribute 2nd value after MySqlProviderServiecs must match with the .dll name as displayed in the 2nd picture above.
<providers>
<provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"/>
</providers>Look at below picture where the names of the .dll has been highlighted.
Configuring the IdentityModel for MySql connection
Now, open IdentityModel.cs file from Models folder and change the connection string name as shown below. Remember that the connection string name should exactly match as written in the web.config file under <connectionStrings> tag.
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
public ApplicationDbContext()
: base("MySqlConnection", throwIfV1Schema: false)
{
}
public static ApplicationDbContext Create()
{
return new ApplicationDbContext();
}
}Now, we are done. It is time to test whether our configurations are working properly or not.
Testing application using MySql with Entity Framework
Following codes are specific to this demo scenario. To test the application, we have created a model corresponding to a sample table in our database.
[Table("categoryparent")]
public class categoryparent
{
[Key]
public int AutoID { get; set; }
public string CategoryParent { get; set; }
public bool Active { get; set; }
}Now,
create a controller and try to run the application and see if it works and below is the result.
Hurrah!, we have done this successfully.
Thanks for reading the article, do write your feedback or comments that will help me to improve forthcoming articles.