In the last lesson, we created a database for our to do list using MySQL Workbench. In this lesson, we'll connect the database to our application using a tool called MySqlConnector. Once connected, we'll be able to interact with our database and its tables as C# objects in our application.

First, let's think about how we'll refactor our to do list to use a database.

To Do List Database Refactor Plan

---

The rest of the weekend homework will focus on building database methods from scratch. This is much more labor-intensive than using an ORM tool like Entity and involves a lot of tedious boilerplate code. Our goal is to get an understanding of what is really going on under the hood before we start using Entity.

Project Structure & Configuration

---

Let's start refactoring our to do list to use a MySQL database. After following along with last section's lessons, our to do list should look like this:

ToDoList.Solution
├── ToDoList
│   ├── Controllers
│   │   ├── CategoriesController.cs
│   │   ├── HomeController.cs
│   │   └── ItemsController.cs
│   ├── Models
│   │   ├── Category.cs
│   │   └── Item.cs
│   ├── Program.cs
│   ├── Startup.cs
│   ├── ToDoList.csproj
│   └── Views
│       ├── Categories
│       │   ├── Index.cshtml
│       │   ├── New.cshtml
│       │   └── Show.cshtml
│       ├── Home
│       │   └── Index.cshtml
│       └── Items
│           ├── DeleteAll.cshtml
│           ├── New.cshtml
│           └── Show.cshtml
└── ToDoList.Tests
    ├── ModelTests
    │   ├── CategoryTests.cs
    │   └── ItemTests.cs
    └── ToDoList.Tests.csproj

Your project directory may be slightly different if you added a Shared directory with a _Layout.cshtml file or if you included static assets (a wwwroot directory with related subdirectories).

We recommend referencing this sample repository throughout the section: To Do List Example Repo. You are also welcome and encouraged to use your own previous to do list project as well and keep it updated in Github. Note that the commits will be named based on the lesson that they reference, and represent the code at the end of that particular lesson.

If you'd like to see the code at its current state, please reference this commit.

MySqlConnector Installation & Configuration

---

We'll begin by walking through how to set up a database in a project using MySqlConnector. Let's update our .csproj file to include MySqlConnector.

Installing the MySqlConnector Package

We can use the following command in the production directory of our project (ToDoList.Solution/ToDoList) to add this package:

$ dotnet add package MySqlConnector -v 0.69.0

This command should also automatically restore the packages in our application, but if you encounter any errors, run the following command to manually restore packages:

$ dotnet restore

Configuring the Database Connection

Let's use our new dependency to connect to our database. We'll add the following code to Startup.cs. It should go after the closing bracket of the existing Startup class, but before the closing bracket of the ToDoList namespace declaration:

...

namespace ToDoList
{
  public class Startup
  {
    ...
  }

  public static class DBConfiguration
  {
    public static string ConnectionString = "server=localhost;user id=root;password=epicodus;port=3306;database=to_do_list;";
  }
}

This declares a DBConfiguration class. It contains a single connection string that tells our application where to find its database. The connection string includes five distinct parts:

• server identifies our database's server. We list localhost because our application is running on a server local to our machine, not online.

• user id identifies our database's user.

• password provides the password for the given user. For now, our databases won't hold sensitive information so we won't worry about securing them yet.

• port identifies the port MySQL is running on. The default port number for a MySQL server is 3306.

• database is the database name. In our case, this is the to_do_list database we created a few lessons ago.

Creating the Database Object

Next, we need a DB class. It will be responsible for managing the database's presence in our application. In the Models folder, create a Database.cs and add the following:

using System;
using MySql.Data.MySqlClient;
using ToDoList;

namespace ToDoList.Models
{
  public class DB
  {
    public static MySqlConnection Connection()
    {
      MySqlConnection conn = new MySqlConnection(DBConfiguration.ConnectionString);
      return conn;
    }
  }
}

• We declare we're using the MySql.Data.MySqlClient namespace imported by the MySqlConnector package.

• We import the ToDoList namespace so we can access the DBConfiguration class in Startup.cs that contains our connection string.

• We create a new MySqlConnection object named conn. It uses the connection string we defined in Startup.cs.

• This MySqlConnection class tells our app how to connect to our database.

Communicating with the Database

---

We can now call DB.Connection() from anywhere in our application to communicate with our database. Calling this method instantiates and returns a connection to our database named conn. We can call further methods upon conn to interact with our database. We'll see exactly how this works in the next lesson.

Repository Reference

Follow the link below to view how a sample version of the project should look at this point. Note that this is a link to a specific commit in the repository.

Example GitHub Repo for To Do List