How to Run MySQL Connector in ASP.NET Core

This tutorial shows how to use MySQL database with MySQL Connector in Asp.net Core.

Before we start it, please make sure you have installed MySQL 5.7, .NET Core, Visual Studio, and also Sakila sample database.

Let’s Get Started

1. The first step is to create ASP.NET Core Web Application and you can name it. For example, we use MvcSakilaCore.

2. Select Web Application template and No Authentication.

3. You will see the project below:

4. Press F5 to run the application:

5. Create a new folder named “Models” where it will store the database access:

6. Installing MySQL Connector/NET Core Package

7. Add Connection string like below

8. Adding data modal classes.

For this example a Film class will be used. It contains the database fields as properties we want to show in our application.

using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MvcSakilaCore.Models
{
  public class Film
  {
    private SakilaContext context;

    public int FilmId { get; set; }

    public string Title { get; set; }

    public string Description { get; set; }

    public int ReleaseYear { get; set; }

    public int Length { get; set; }

    public string Rating { get; set; }
  }
}

Create a new SakilaContext class that will contains the connections and Sakila database entities:

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;

namespace MvcSakilaCore.Models
{
  public class SakilaContext
  {
    public string ConnectionString { get; set; }

    public SakilaContext(string connectionString)
    {
      this.ConnectionString = connectionString;
    }

    private MySqlConnection GetConnection()
    {
      return new MySqlConnection(ConnectionString);
    }

    public List<Film> GetAllFilms()
    {
      List<Film> list = new List<Film>();

      using (MySqlConnection conn = GetConnection())
      {
        conn.Open();
        MySqlCommand cmd = new MySqlCommand("SELECT * FROM film", conn);
        using (MySqlDataReader reader = cmd.ExecuteReader())
        {
          while (reader.Read())
          {
            list.Add(new Film()
            {
              FilmId = reader.GetInt32("film_id"),
              Title = reader.GetString("title"),
              Description = reader.GetString("description"),
              ReleaseYear = reader.GetInt32("release_year"),
              Length = reader.GetInt32("length"),
              Rating = reader.GetString("rating")
            });
          }
        }
      }

      return list;
    }
  }
}

In order to be able to use our SakilaContext it’s required to register the instance as a service in our application. To do this add the code line in the Startup.cs file:

9. Adding Film Controller

In Solution Explorer, right-click Controllers > Add > New Item… > MVC Controller Class. Name the controller FilmsController:

Change the FilmsController code to this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using MvcSakilaCore.Models;

// For more information on enabling MVC for empty projects, visit http://go.microsoft.com/fwlink/?LinkID=397860

namespace MvcSakilaCore.Controllers
{
  public class FilmsController : Controller
  {
    // GET: /<controller>/
    public IActionResult Index()
    {
      SakilaContext context = HttpContext.RequestServices.GetService(typeof(MvcSakilaCore.Models.SakilaContext)) as SakilaContext;

      return View(context.GetAllFilms());
    }
  }
}

10. Creating the Films View

Start creating the Films folder under Views:

In Solution Explorer, right click Views > Films > Add > New Item… > ASP.NET > MVC View Page

Add the following code into the new Index.cshtml view file:

@model IEnumerable<MvcSakilaCore.Models.Film>
@{
  ViewBag.Title = "Films";
}
<h2>Films</h2>
<table class="table">
  <tr>
    <th>Film ID</th>
    <th>Title</th>
    <th>Description</th>
    <th>Release Year</th>
    <th>Length</th>
    <th>Rating</th>
  </tr>
  @foreach (var item in Model)
  {
    <tr>
      <td>
        @Html.DisplayFor(modelItem => item.FilmId)
      </td>
      <td>
        @Html.DisplayFor(modelItem => item.Title)
      </td>
      <td>
        @Html.DisplayFor(modelItem => item.Description)
      </td>
      <td>
        @Html.DisplayFor(modelItem => item.ReleaseYear)
      </td>
      <td>
        @Html.DisplayFor(modelItem => item.Length)
      </td>
      <td>
        @Html.DisplayFor(modelItem => item.Rating)
      </td>
    </tr>
  }
</table>

Before run the application, add the Films path to the running url.

In Solution Explorer, right click MvcSakilaCore > Properties > Debug > Launch URL > Films:

Run the application (press F5) and the Films list should be displayed: