loading...
Cover image for .Net Core 3 - Part 2 - MySQL

.Net Core 3 - Part 2 - MySQL

lucianopereira86 profile image LUCIANO DE SOUSA PEREIRA Originally published at lucianopereira.netlify.com ・3 min read

.Net Core 3 (3 Part Series)

1) .Net Core 3 - Part 1 - Swagger 2) .Net Core 3 - Part 2 - MySQL 3) .Net Core 3 - Part 3 - Heroku

titulo
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

Topics

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:

mysql01

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

mysql02

These are your connection data. Remember the password!

mysql03

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

mysql04

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

mysql05

Test the connection:

mysql06

Now, connect to the database:

mysql07

mysql08

mysql09

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:

mysql10

.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:

netcore01

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

netcore02

netcore03

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.

netcore04

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

netcore05

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

netcore06

MySqlConnector is another package that you need to install.

netcore07

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

netcore08

Implement the Dapper inside the GET and POST methods:

netcore09

netcore10

Run the API and see the result in the URL:

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

netcore11

Test the GET method:

netcore12

The result will be:

netcore13

Test the POST method:

netcore14

The result will be:

netcore15

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

netcore16

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.

.Net Core 3 (3 Part Series)

1) .Net Core 3 - Part 1 - Swagger 2) .Net Core 3 - Part 2 - MySQL 3) .Net Core 3 - Part 3 - Heroku

Posted on Oct 16 '19 by:

lucianopereira86 profile

LUCIANO DE SOUSA PEREIRA

@lucianopereira86

Luciano de Sousa Pereira. Brazilian. Bacharel degree in Computer Engineering. Full Stack Developer with 5 years of experience in multiple frameworks and programming languages.

Discussion

markdown guide