Perform CRUD operations on MySQL database using EF Core and ASP.NET Core

As an ASP.NET Core web developer you are probably quite comfortable working with Microsoft SQL Server and Entity Framework Core. However, at times you need to work with other popular data stores. One such popular database engine is MySQL. To that end this article illustrates how to connect with a MySQL database and perform CRUD operations using MySQL Entity Framework Core provider.

In order to work with the code discussed in this article you need to have the latest version of MySQL Community Server installed on your machine. You can download it here. You can also download and install MySQL database engine from a ZIP file. I also assume that you have a valid user account that you can use to connect with the database.

Once you install MySQL Community Server, start the database engine from the Visual Studio command prompt as shown below:

> mysqld --console

Make sure to run this command from the bin folder of the MySQL installation. The following figure shows a successful run of the MySQL server. 

Now, open the MySQL command line interface (CLI) by issuing this command:

> mysql -u root -p

Then create a new database for our testing purpose called MyTestDb:

> create database MyTestDb;

Also create a new table in the MyTestDb database by issuing this command:

> USE MyTestDb;
> CREATE TABLE IF NOT EXISTS Customers (
>  CustomerID INT AUTO_INCREMENT,
>  CompanyName TEXT NOT NULL,
>  PRIMARY KEY(CustomerID)
> );

Here, you first switched to the MyTestDb database and then created a table named Customers using CREATE TABLE statement. The Customers table has two columns namely CustomerID and CompanyName. I won't go into too much details of the SQL syntax. You can see the official product documentation for more details.

You can EXIT from the MySQL CLI. But keep the MySQL server running in the background and proceed to create a new ASP.NET Core MVC application using Visual Studio.

Once the project is created add the NuGet package for MySql.Data.EntityFrameworkCore.

This package provides EF Core style data access to MySQL database.

Next, create the Customer entity class and a custom DbContext. The Customer class is shown below:

[Table("Customers")]
public class Customer
{
    [Column("CustomerID")]
    [Key]
    [DatabaseGenerated
(DatabaseGeneratedOption.Identity)]
    [Required]
    public int CustomerID { get; set; }

    [Column("CompanyName")]
    [Required]
    [StringLength(100)]
    public string CompanyName { get; set; }
}

And the custom DbContext class - AppDbContext - looks like this:

public class AppDbContext:DbContext
{
    public AppDbContext(
DbContextOptions<AppDbContext> 
options) : base(options)
    {
    }

    public DbSet<Customer> Customers { get; set; }
}

Once these classes are ready, open the Startup class and add this line in the ConfigureServices() method.

public void ConfigureServices
(IServiceCollection services)
{
    services.AddControllersWithViews();

    services.AddDbContext<AppDbContext>
(o => o.UseMySQL(Configuration.
GetConnectionString("MyDb")));
}

As you can see, the AddDbContext() method registers custom DbContext with the DI container. The UseMySQL() extension method plays an important role here and accepts MySQL database connection string.

The MySQL database connection string is stored in the appsettings.json file and is shown below:

"ConnectionStrings": {
  "MyDb": "Server=localhost;
  Database=MyTestDb;
  Uid=root;
  Pwd=YOUR_PWD_HERE"
}

The above connection string indicates that your MySQL server is running on localhost, the database name is MyTestDb. And you are connecting with root user account and the associated password. Make sure to substitute your password in the above connection string.

Now that you have registered AppDbContext, you can inject it in any controller and perform CRUD operations. This is quite similar to what you do with Microsoft SQL Server and EF Core.

The following code shows how the AppDbContext is injected into HomeController.

public class HomeController : Controller
{
    private readonly AppDbContext db;

    public HomeController(AppDbContext db)
    {
        this.db = db;
    }
}

Once injected you can use db to perform CRUD operations as shown below:

public IActionResult Index()
{
    // insert
    Customer cust1 = new Customer()
    {
        CustomerID = 100,
        CompanyName = "Company Name 1"
    };
    Customer cust2 = new Customer()
    {
        CustomerID = 200,
        CompanyName = "Company Name 2"
    };
    db.Customers.Add(cust1); 
    db.Customers.Add(cust2);
    db.SaveChanges();

    // update

    Customer custUpdt = db.Customers.Find(100);
    custUpdt.CompanyName = "Company Name Changed";
    db.SaveChanges();

    // delete

    Customer custDel = db.Customers.Find(200);
    db.Customers.Remove(custDel);
    db.SaveChanges();

    return View(db.Customers.ToList());
}

You will find this code quite similar to the EF Core code you use with Microsoft SQL Server. This time, however, SaveChanges() is taking the changes to a MySQL database, MytestDb in this case. The code first adds two Customer entities, then modifies one of them, and then deletes one of them. The final list of customers is sent to the Index view for display purposes.

The Index view that displays a list if customers is shown below:

@model List<MySQLDemo.DataAccess.Customer>

<h1>List of Customers</h1>

<table border="1" cellpadding="11">
    @foreach(var item in Model)
    {
        <tr>
            <td>@item.CustomerID</td>
            <td>@item.CompanyName</td>
        </tr>
    }
</table>

The following figure shows a sample output in the browser.

I hope you got some idea about performing CRUD operations using MySQL and EF Core. In this example you used a local installation of MySQL Community Server. Azure also supports cloud hosted MySQL database that you can use instead of a local engine. To read more go here and here.

That's it for now! Keep coding!!