Connect MS SQL And MySQL Database To Your .NET Core Web API

Introduction

 
Today in this article will learn how to connect the database to our .NET Core WEB API.
 
As in our previous article, we learned how to create a simple .NET Core WEB API.
 
Now taking things  further today will learn about how to connect to our database (MS SQL Server or MySQL) using Package Manager Console.
 
Note
In this application, we are using .NET CORE 3.1.5 version and Repository Pattern. Please refer to the above-mentioned article if you want to start from the basics.
 
And also you should have an MS SQL Server or MySQLserver installed in your system. And in our reference example below I have used MS SQL 2017. (You can use any of the versions, but the commands below remain the same to import the database).
 
Now let's get started.
 
Step 1
 
Open MS SQL database and connect to the server and right-click on Database and click on New Database for creating a new database.
 
Connect MS SQL And MySQL Database To Your .NET Core Web API
 
Connect MS SQL And MySQL Database To Your .NET Core Web API
 
Step 2
 
Now create a new database, in my case I have given the database name as MyTestDb. Open the database and right-click on tables and click on new and then click on the table option. Now create a new table. In the example I have given my TestTable with 3 columns; testTableId as primary key with auto increment; testName & testDescription as nvarchar.
 
Insert some data in the table for testing purposes. 
 
Connect MS SQL And MySQL Database To Your .NET Core Web API
 
Connect MS SQL And MySQL Database To Your .NET Core Web API
  1. CREATE DATABASE [MyTestDb];  
  2.   
  3. USE [MyTestDb]  
  4. GO  
  5. /****** Object:  Table [dbo].[myTestTable]    Script Date: 09-08-2020 22:38:50 ******/  
  6. SET ANSI_NULLS ON  
  7. GO  
  8. SET QUOTED_IDENTIFIER ON  
  9. GO  
  10. CREATE TABLE [dbo].[myTestTable](  
  11.     [testTableId] [int] IDENTITY(1,1) NOT NULL,  
  12.     [testName] [nvarchar](100) NULL,  
  13.     [testDecription] [nvarchar](maxNULL,  
  14.  CONSTRAINT [PK_myTestTable] PRIMARY KEY CLUSTERED   
  15. (  
  16.     [testTableId] ASC  
  17. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  18. ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  19. GO   
Step 3
 
Now go to the application and add a new project of type "Class Library (.NET Standard)" and click on "Next". (Ref: Step 6 in here)
 
Select a project name as "ProjectName.DbModel" (in my case MyTestApp.DbModel) and click on "create"'.
  • Left-click on "MyTestApp" and go to Build Dependencies and go to Project Dependencies.
  • Select MyTestApp and select all projects in the "Depends on" section.
  • Select MyTestApp.DbModel projects in the "Depends on" section and press ok.
Connect MS SQL And MySQL Database To Your .NET Core Web API
 
Select MyTestApp.BAL and select MyTestApp.DbModel projects in the "Depends on" section and press ok.
Connect MS SQL And MySQL Database To Your .NET Core Web API
 
Step 4
 
In "MyTestApp" Left-click on "Dependencies" and go to Add Reference. (Ref: Step 10 in here)
 
Connect MS SQL And MySQL Database To Your .NET Core Web API
 
Select "MyTestApp.DbModel" and click ok.
Connect MS SQL And MySQL Database To Your .NET Core Web API
 
Similarly open MyTestApp.BAL and left-click on "Dependencies" and go to Add Reference. Select "MyTestApp.DbModel" and click ok.
 Connect MS SQL And MySQL Database To Your .NET Core Web API
 
Step 5
 
Now select Tools and in Tools select Nuget Package Manager and then select Package Manager Console.
 
Connect MS SQL And MySQL Database To Your .NET Core Web API
 
Step 6
 
Now run the below command in the console to install EntityFrameworkCore by selecting the default project to both MyTestApp & MyTestApp.DBModel.
  1. Install-Package Microsoft.EntityFrameworkCore -Version 3.1.2  
 Connect MS SQL And MySQL Database To Your .NET Core Web API
 
Now install the EntityFrameworkCore.Tools in both MyTestApp & MyTestApp.DBModel projects.
  1. Install-Package Microsoft.EntityFrameworkCore.Tools -Version 3.1.2  
Connect MS SQL And MySQL Database To Your .NET Core Web API 
 
Step 7
 
Now run the below command in the console to install EntityFrameworkCore.SqlServer by selecting the default project to MyTestApp.DBModel (below command is to MS SQL Server)
  1. Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 3.1.2   
For MySQL server run the below command and also create a database and table in MySQL.
  1. Install-Package MySql.Data.EntityFrameworkCore -Version 8.0.15   
You can remove the -Version X.X.X to install the latest version or any appropriate versions.
Connect MS SQL And MySQL Database To Your .NET Core Web API
 
Step 8
 
Now the most important step to import the database object is here. Run the below command for MS SQL Server, for importing the DB objects.
  1. Scaffold-DbContext "Server=ServerNameGoesHere;Database=MyTestDb;user id=sa;password=password;" Microsoft.EntityFrameworkCore.SqlServer -f    
 For MySQL please run the below command.
  1. Scaffold-DbContext "server=localhost;port=3306;user=root;password=yourPassword;database=MyTestDb" MySql.Data.EntityFrameworkCore -f  
Connect MS SQL And MySQL Database To Your .NET Core Web API
 
Now modify the code in the "MyNewApiBAL.cs" or in your business logic layer. Write a LINQ query to get the data from the database for testing purposes.
  1. using MyTestApp.DBModel;  
  2. using MyTestApp.IAL;  
  3. using System;  
  4. using System.Linq;  
  5. using static MyTestApp.DAL.MyCustomEntity;  
  6.   
  7. namespace MyTestApp.BAL  
  8. {  
  9.     public class MyNewApiBAL : IMyNewAPI  
  10.     {  
  11.         MyTestDbContext _db = new MyTestDbContext();  
  12.   
  13.         public MyNewApiBAL(MyTestDbContext db)  
  14.         {  
  15.             _db = db;  
  16.         }  
  17.   
  18.         public MyCustomResponse GetAllData()  
  19.         {  
  20.             MyCustomResponse response = new MyCustomResponse();  
  21.             try  
  22.             {  
  23.                 response.status = true;  
  24.                 //get all data rows from myTestTable
  25.                 var item = _db.MyTestTable.ToList();  
  26.   
  27.                 response.response = "API called successfully";  
  28.             }  
  29.             catch (Exception ex)  
  30.             {  
  31.                 response.status = false;  
  32.                 response.response = "Exception occoured";  
  33.             }  
  34.             return response;  
  35.         }  
  36.     }  
  37. }  
Now add the below code in StartUp.cs in ConfigureServices method.
  1. var connection = "Server=YourServerNameGoesHere;Database=MyTestDb;user id=sa;password=password;";  
  2. services.AddDbContext<MyTestDbContext>(options => options.UseSqlServer(connection));  
Now run the application and call the API from the swagger. (For understanding purposes I have put a breakpoint in code and to verify).
Connect MS SQL And MySQL Database To Your .NET Core Web API
 

Conclusion

 
In this article, we discussed how to easily connect the MS SQL and MySQL Database to our .NET Core WEB API by using the package manager console & commands. I hope you all enjoyed reading this and learned from it.