How To Connect MySQL With ASP.NET Core

Objective

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.

Prerequisites

1. MySQL (You can get MySQL IDE – MySQL Work Bench here).
2. NET CORE Environment Setup. (you can get it here).
3. Visual studio 2015 or Visual Studio 2017(download VS 2017 here).
4. MySQL database.

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.

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

Clean build and run the Application for testing.

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.

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

3. To check if DB is created or not, run the command given below.
   
   
   
   
4. Similarly, we run SQL queries on CLI, create tables and insert data into it.
5. Now, let's check with MySQL workbench IDE
6. Select Query template. Write SQL query on query editor. Click Lighting icon. 
   
   
   
   on top menu.
   
   
   

7. After creating table, insert records in created DB, run Select query on the table.
   
   
   
   
8. 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
   
   
   
   
   

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

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

4. Add Album properties in an Album class.
   

   namespace NetCoreWebApp.Models
   {
       public class Album
       {
           private MusicStoreContext context;
   
           public int Id { get; set; }
   
           public string Name { get; set; }
   
           public string ArtistName { get; set; }
   
           public int Price { get; set; }
   
           public string Genre { get; set; }
       }
   }

1. Create a new MusicStoreContext class, which will contain the connections and MusicStore data entities, as shown below.
   

   using MySql.Data.MySqlClient;
   using System;
   using System.Collections.Generic;
   
   namespace NetCoreWebApp.Models
   {
       public class MusicStoreContext
       {
           public string ConnectionString { get; set; }
   
           public MusicStoreContext(string connectionString)
           {
               this.ConnectionString = connectionString;
           }
   
           private MySqlConnection GetConnection()
           {
               return new MySqlConnection(ConnectionString);
           }
   
   
       }
   }

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

   using Microsoft.AspNetCore.Builder;
   using Microsoft.AspNetCore.Hosting;
   using Microsoft.Extensions.Configuration;
   using Microsoft.Extensions.DependencyInjection;
   using Microsoft.Extensions.Logging;
   using NetCoreWebApp.Models;
   namespace NetCoreWebApp
   {
       public class Startup
       {
           public Startup(IHostingEnvironment env)
           {
               var builder = new ConfigurationBuilder()
                   .SetBasePath(env.ContentRootPath)
                   .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
                   .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
                   .AddEnvironmentVariables();
               Configuration = builder.Build();
           }
   
           public IConfigurationRoot Configuration { get; }
   
           // This method gets called by the runtime. Use this method to add services to the container.
           public void ConfigureServices(IServiceCollection services)
           {
               // Add framework services.
               services.AddMvc();
               services.Add(new ServiceDescriptor(typeof(MusicStoreContext), new MusicStoreContext(Configuration.GetConnectionString("DefaultConnection"))));
           }
   
           // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
           public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
           {
               loggerFactory.AddConsole(Configuration.GetSection("Logging"));
               loggerFactory.AddDebug();
   
               if (env.IsDevelopment())
               {
                   app.UseDeveloperExceptionPage();
                   app.UseBrowserLink();
               }
               else
               {
                   app.UseExceptionHandler("/Home/Error");
               }
   
               app.UseStaticFiles();
   
               app.UseMvc(routes =>
               {
                   routes.MapRoute(
                       name: "default",
                       template: "{controller=Home}/{action=Index}/{id?}");
               });
           }
       }
   }

Fetch data from My SQL database

5. To get the data from the database, we need ‘GetAllAlbums()’ method, our DB context, add ‘GetAll Albums()’ method in “MusicStoreCotext” class.
   

   public List<Album> GetAllAlbums()
           {
               List<Album> list = new List<Album>();
   
               using (MySqlConnection conn = GetConnection())
               {
                   conn.Open();
         MySqlCommand cmd = new MySqlCommand("select * from Album where id < 10", conn);
   
                   using (var reader = cmd.ExecuteReader())
                   {
                       while (reader.Read())
                       {
                           list.Add(new Album()
                           {
                               Id = Convert.ToInt32(reader["Id"]),
                               Name = reader["Name"].ToString(),
                               ArtistName = reader["ArtistName"].ToString(),
                               Price = Convert.ToInt32(reader["Price"]),
                               Genre = reader["genre"].ToString()
                           });
                       }
                   }
                   }
                   return list;
               }

6. Now, we need controller to manage our code. Add controller with name AlbumsController.
   
   
   
   Add the code given below in Album Controller to get the data from DB.
   

   namespace NetCoreWebApp.Controllers
   {
       public class AlbumController : Controller
       {
           public IActionResult Index()
           {
               MusicStoreContext context = HttpContext.RequestServices.GetService(typeof(NetCoreWebApp.Models.MusicStoreContext)) as MusicStoreContext;
   
               return View(context.GetAllAlbums());
           }
   
   
       }
   }

6. 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.
   
   
7. Select Layout page by clicking button. Now, click add.
   
   
   

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

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

   app.UseMvc(routes =>
              {
                  routes.MapRoute(
                      name: "default",
                      template: "{controller=Album}/{action=Index}/{id?}");
              });

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

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.

Feel free to share suggestions and feedback.