EasyHostingASP.NET | Best and cheap ASP.NET Core hosting. In this article, we are going to understand how to connect .NET CORE Application with MySQL and read data from MySQL, using .NET Core MySQL connector.

Steps to create.NET Core Web app with VS 2017 are given below. 

Go to File -> New Project -> Select Web from templates->choose ASP.NET Core Web Application (.NET Core).

Provide the name for the Web app and click OK.

image001

Select Web Application template and click OK. It will create .NET Core Web app for you.

image002

image003

Clean build and run the Application for testing.

image004

Set up database

To create MySQL database, we must have IDE for MySQL or we need to have MySQL CLI. We can create the database, either with IDE or using CLI commands.

We can create the database in both ways. Let’s check out CLI first.

  • To create database, using CLI, we first need to login with password, which we have provided at the time of installation.
  • After login, type the command “create database MusicStoreDB;”.

image005

  • To check if DB is created or not, run the command given below.

image006

  • Similarly, we run SQL queries on CLI, create tables and insert data into it.
  • Now, let’s check with MySQL workbench IDE
  • Select Query template. Write SQL query on query editor. Click Lighting icon.

image007

on top menu.

image008

  • After creating table, insert records in created DB, run Select query on the table.

image009

  • For now, we did for single table only. We are done with the database creation and data insertion.

Connect with MySQL

To connect with MySQL database, we must have some NuGet installed in our Application.

  1. Go to Application ->right click on project name-> select Manage Nuget Packages-> Type MySql .Data

image010

image011

  • Go to Project root-> appsettings.json-> enter the connection string, as shown below.

image012

  • Create new folder Models and add Class ‘MusicStoreContext’& ‘Album’ in it.

image013

image014

Add Album properties in an Album class.

  • Create a new MusicStoreContext class, which will contain the connections and MusicStore data entities, as shown below.
  • To use context in our Application, we need to register instance as a Service in our Application. To register context, we need to add on line of code in ‘startup.cs’ file under ‘ConfigureServices’ method.

Fetch data from My SQL database

  • To get the data from the database, we need ‘GetAllAlbums()’ method, our DB context, add ‘GetAll Albums()’ method in “MusicStoreCotext” class.
  • Now, we need controller to manage our code. Add controller with name AlbumsController.

image015

Add the code given below in Album Controller to get the data from DB.

  • After adding controller and code, we need view to display the data to the end user. Create folder under Views with name Albums. Right click on Albums folder and add new view Albums.
  • Select Layout page by clicking button. Now, click add.

You can create view from by selecting data model in dropdown or you can create blank view with the default index name and add the code, as shown below.

image016

  1. To route to our Album action, we need to update Startup.cs and add Album controller name, so we after running an app directly. We get the “Album/index” page.

  • Now, just run an Application and we will get the output, as shown below.

image017

Conclusion

In this article, we have seen how to make the connection with MYSQL Server database. In case of remote, we need to update the connection string with an appropriate Server name and the port name

Alexia Pamelov