.Net Core 3 - Part 2 - MySQL

Continuing the .Net Core series, in this chapter, we will connect our web API with a remote MySQL server.
The full example can be downloaded in my GitHub repository: NetCore3 MySQL.

Technologies

• .Net Core 3
• MySQL
• MySQL Workbench
• Dapper

Topics

• MySQL
• .Net Core 3

MySQL

Let's create a remote MySQL database and manipulate it with MySQL Workbench.

First, access this website:

https://remotemysql.com/

At the home page, click on "Login" and, in the next page, on the "Create Account" tab:

Create an account and you will receive an e-mail to confirm. Login in the website and create a database:

These are your connection data. Remember the password!

Now download the MySQL Workbench through this link.
After the installation process, open the program and click on "Manage Connections...":

Click on "New" and set the Hostname, Username, Password and Default Schema fields with the data from the remote database:

Test the connection:

Now, connect to the database:

We are ready to create our first table.
Run the following script inside the "Query 1" tab:

CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name varchar(100)
);

Add some lines:

INSERT INTO user (name) VALUES ('Luciano');
INSERT INTO user (name) VALUES ('Sousa');
INSERT INTO user (name) VALUES ('Pereira');

The table structure will be visible:

.Net Core 3

Download the project sample from the part 1.

Inside the appsettings.json, add a new object called ConnectionStrings containing the MySQL connection string:

Create a folder named Models in the root with two classes: ConnectionStrings and User.

To be able to use the connection string anywhere in the code, it's necessary to create a singleton of its model inside the Startup class.

Create another controller called UserController with two methods: POST and GET.

Dapper is an ORM that allow an easy connection with any database. Install it through Nuget.

MySqlConnector is another package that you need to install.

Create a constructor inside the UserController injecting the ConnectionStrings singleton in a local variable.

Implement the Dapper inside the GET and POST methods:

Run the API and see the result in the URL:

http://localhost:53000/swagger/index.html

Test the GET method:

The result will be:

Test the POST method:

The result will be:

Run the GET method again and a new user will appear:

Conclusion

In order to easily connect with a database we have used the Dapper and MySqlConnector packages.
Model classes were created to reflect the table and the connection string.
In the next chapter, we will deploy our web API to Heroku.