.Net Core 3 - Part 2 - MySQL
Tags: netcore, mysql, dapper
Published: 2019-10-16 00:41:08

![titulo](https://thepracticaldev.s3.amazonaws.com/i/03r31hgj1fj7xq9ob91t.JPG)
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](https://github.com/lucianopereira86/NetCore3-MySQL).

## Technologies

- [.Net Core 3](https://docs.microsoft.com/pt-br/dotnet/core/whats-new/dotnet-core-3-0)
- [MySQL](https://www.mysql.com/)
- [MySQL Workbench](https://www.mysql.com/products/workbench/)
- [Dapper](https://dapper-tutorial.net/)

## Topics

- [MySQL](#mysql)
- [.Net Core 3](#net-core-3)

### MySQL

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

First, access this website:

```bash
https://remotemysql.com/
```

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

![mysql01](https://thepracticaldev.s3.amazonaws.com/i/krwh49dqrcqihhbrwedm.JPG)

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

![mysql02](https://thepracticaldev.s3.amazonaws.com/i/3hxabamqbb9dr6qx31pw.JPG)

These are your connection data. Remember the password!

![mysql03](https://thepracticaldev.s3.amazonaws.com/i/9un2479pnpwwiw020puz.JPG)

Now download the MySQL Workbench through this [link](https://dev.mysql.com/downloads/workbench/). 
After the installation process, open the program and click on "Manage Connections...":

![mysql04](https://thepracticaldev.s3.amazonaws.com/i/4vzvjv44vdbl16ij8hyl.JPG)

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

![mysql05](https://thepracticaldev.s3.amazonaws.com/i/p4oce696r65kkwv6c40b.JPG)

Test the connection:

![mysql06](https://thepracticaldev.s3.amazonaws.com/i/5gd2vc93frmoaclmp0a1.JPG)

Now, connect to the database:

![mysql07](https://thepracticaldev.s3.amazonaws.com/i/tr801ubfsdanzisd1tw1.JPG)

![mysql08](https://thepracticaldev.s3.amazonaws.com/i/42mlxdq2ix4ti2pab9r3.JPG)

![mysql09](https://thepracticaldev.s3.amazonaws.com/i/kq5tt7xetznrzbj501dy.JPG)

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

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

Add some lines:

```sql
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](https://thepracticaldev.s3.amazonaws.com/i/te9d94kzjp1t3jyalh1e.JPG)

### .Net Core 3

Download the [project sample](https://github.com/lucianopereira86/NetCore3-Swagger) from the [part 1](https://dev.to/lucianopereira86/net-core-3-part-1-swagger-2bkf).

Inside the _appsettings.json_, add a new object called _ConnectionStrings_ containing the MySQL connection string:

![netcore01](https://thepracticaldev.s3.amazonaws.com/i/ch5d00zbg9r309112k9b.JPG)

Create a folder named _Models_ in the root with two classes: _ConnectionStrings_ and _User_.

![netcore02](https://thepracticaldev.s3.amazonaws.com/i/12fowcoinbsriae5pqdw.JPG)

![netcore03](https://thepracticaldev.s3.amazonaws.com/i/iv5ve7er6lvlo3gzdwvb.JPG)

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](https://thepracticaldev.s3.amazonaws.com/i/tz59hnxvulgs2nlxsp65.JPG)

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

![netcore05](https://thepracticaldev.s3.amazonaws.com/i/y83s5p09lmtupz8xd7kw.JPG)

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

![netcore06](https://thepracticaldev.s3.amazonaws.com/i/17pl2nr7gyq2dxu83uib.JPG)

MySqlConnector is another package that you need to install.

![netcore07](https://thepracticaldev.s3.amazonaws.com/i/jmgrbki7mu3y2lpebnwy.JPG)

Create a constructor inside the _UserController_ injecting the _ConnectionStrings_ singleton in a local variable.

![netcore08](https://thepracticaldev.s3.amazonaws.com/i/qi7hck84cec753gsxnjp.JPG)

Implement the Dapper inside the GET and POST methods:

![netcore09](https://thepracticaldev.s3.amazonaws.com/i/hx8yvfcmuqbe8b5h9915.JPG)

![netcore10](https://thepracticaldev.s3.amazonaws.com/i/eiljgkzh9kwerrxvhqre.JPG)

Run the API and see the result in the URL:

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

![netcore11](https://thepracticaldev.s3.amazonaws.com/i/kn2v8dced4upf4413okt.JPG)

Test the GET method:

![netcore12](https://thepracticaldev.s3.amazonaws.com/i/owv3dvcko0rrt22oqzw2.JPG)

The result will be:

![netcore13](https://thepracticaldev.s3.amazonaws.com/i/karabd5q4pz85m36an5s.JPG)

Test the POST method:

![netcore14](https://thepracticaldev.s3.amazonaws.com/i/xa0ypq1ybgo7zxdyo45x.JPG)

The result will be:

![netcore15](https://thepracticaldev.s3.amazonaws.com/i/9no0v7kiyq2nzbchmtl2.JPG)

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

![netcore16](https://thepracticaldev.s3.amazonaws.com/i/hi0wftqbpsem3dvojrrk.JPG)

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