How to connect mysql database in asp.net mvc

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 a .NET Core Web app with VS 2017

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 the Web Application template and click OK. It will create a .NET Core Web app for you.

Clean build and run the Application for testing.

Set up MySQL database

To create the 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 a database, using CLI, we first need to log in with the password, which we have provided at the time of installation.
2. After login, type the command “create database MusicStoreDB;”.
   

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

2. Similarly, we run SQL queries on CLI, create tables, and insert data into it.
3. Now, let's check with MySQL workbench IDE
4. Select the Query template. Write SQL query on query editor. Click the Lighting icon. 
   
   on the top menu.
   

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

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

Connect with MySQL

To connect with the 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
   
   

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

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

1. Add Album properties in an Album class.
   1. namespace NetCoreWebApp.Models  
   2. {  
   3.     public class Album  
   4.     {  
   5.         private MusicStoreContext context;  
   6.   
   7.         public int Id { get; set; }  
   8.   
   9.         public string Name { get; set; }  
   10.   
   11.         public string ArtistName { get; set; }  
   12.   
   13.         public int Price { get; set; }  
   14.   
   15.         public string Genre { get; set; }  
   16.     }  
   17. }  

1. Create a new MusicStoreContext class, which will contain the connections and MusicStore data entities, as shown below.
   1. using MySql.Data.MySqlClient;    
   2. using System;    
   3. using System.Collections.Generic;    
   4.     
   5. namespace NetCoreWebApp.Models    
   6. {    
   7.     public class MusicStoreContext    
   8.     {    
   9.         public string ConnectionString { get; set; }    
   10.     
   11.         public MusicStoreContext(string connectionString)    
   12.         {    
   13.             this.ConnectionString = connectionString;    
   14.         }    
   15.     
   16.         private MySqlConnection GetConnection()    
   17.         {    
   18.             return new MySqlConnection(ConnectionString);    
   19.         }  
   20.     }    
   21. }  

1. 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.
   1. using Microsoft.AspNetCore.Builder;  
   2. using Microsoft.AspNetCore.Hosting;  
   3. using Microsoft.Extensions.Configuration;  
   4. using Microsoft.Extensions.DependencyInjection;  
   5. using Microsoft.Extensions.Logging;  
   6. using NetCoreWebApp.Models;  
   7. namespace NetCoreWebApp  
   8. {  
   9.     public class Startup  
   10.     {  
   11.         public Startup(IHostingEnvironment env)  
   12.         {  
   13.             var builder = new ConfigurationBuilder()  
   14.                 .SetBasePath(env.ContentRootPath)  
   15.                 .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)  
   16.                 .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)  
   17.                 .AddEnvironmentVariables();  
   18.             Configuration = builder.Build();  
   19.         }  
   20.   
   21.         public IConfigurationRoot Configuration { get; }  
   22.   
   23.         // This method gets called by the runtime. Use this method to add services to the container.  
   24.         public void ConfigureServices(IServiceCollection services)  
   25.         {  
   26.             // Add framework services.  
   27.             services.AddMvc();  
   28.             services.Add(new ServiceDescriptor(typeof(MusicStoreContext), new MusicStoreContext(Configuration.GetConnectionString("DefaultConnection"))));  
   29.         }  
   30.   
   31.         // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.  
   32.         public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)  
   33.         {  
   34.             loggerFactory.AddConsole(Configuration.GetSection("Logging"));  
   35.             loggerFactory.AddDebug();  
   36.   
   37.             if (env.IsDevelopment())  
   38.             {  
   39.                 app.UseDeveloperExceptionPage();  
   40.                 app.UseBrowserLink();  
   41.             }  
   42.             else  
   43.             {  
   44.                 app.UseExceptionHandler("/Home/Error");  
   45.             }  
   46.   
   47.             app.UseStaticFiles();  
   48.   
   49.             app.UseMvc(routes =>  
   50.             {  
   51.                 routes.MapRoute(  
   52.                     name: "default",  
   53.                     template: "{controller=Home}/{action=Index}/{id?}");  
   54.             });  
   55.         }  
   56.     }  
   57. }  

Fetch data from My SQL database

1. To get the data from the database, we need ‘GetAllAlbums()’ method, our DB context, add ‘GetAll Albums()’ method in “MusicStoreCotext” class.
   1. public List<Album> GetAllAlbums()  
   2. {  
   3.     List<Album> list = new List<Album>();  
   4.   
   5.     using (MySqlConnection conn = GetConnection())  
   6.     {  
   7.         conn.Open();  
   8.         MySqlCommand cmd = new MySqlCommand("select * from Album where id < 10", conn);  
   9.   
   10.         using (var reader = cmd.ExecuteReader())  
   11.         {  
   12.             while (reader.Read())  
   13.             {  
   14.                 list.Add(new Album()  
   15.                 {  
   16.                     Id = Convert.ToInt32(reader["Id"]),  
   17.                     Name = reader["Name"].ToString(),  
   18.                     ArtistName = reader["ArtistName"].ToString(),  
   19.                     Price = Convert.ToInt32(reader["Price"]),  
   20.                     Genre = reader["genre"].ToString()  
   21.                 });  
   22.             }  
   23.         }  
   24.     }  
   25.     return list;  
   26. }

2. 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.
   1. namespace NetCoreWebApp.Controllers  
   2. {  
   3.     public class AlbumController : Controller  
   4.     {  
   5.         public IActionResult Index()  
   6.         {  
   7.             MusicStoreContext context = HttpContext.RequestServices.GetService(typeof(NetCoreWebApp.Models.MusicStoreContext)) as MusicStoreContext;  
   8.   
   9.             return View(context.GetAllAlbums());  
   10.         }  
   11.   
   12.   
   13.     }  
   14. }  

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

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

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.
   1. app.UseMvc(routes =>  
   2.            {  
   3.                routes.MapRoute(  
   4.                    name: "default",  
   5.                    template: "{controller=Album}/{action=Index}/{id?}");  
   6.            });  

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

Conclusion

In this article, we have seen how to make a connection with the 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.

How connect MySQL to MVC?

How does ASP MVC connect to database?

Can ASP Net connect to MySQL?

Can we connect MVC to database?