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.

 

 

 

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. 

 

 

CREATE DATABASE [MyTestDb];

USE [MyTestDb]
GO
/****** Object:  Table [dbo].[myTestTable]    Script Date: 09-08-2020 22:38:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[myTestTable](
    [testTableId] [int] IDENTITY(1,1) NOT NULL,
    [testName] [nvarchar](100) NULL,
    [testDecription] [nvarchar](max) NULL,
 CONSTRAINT [PK_myTestTable] PRIMARY KEY CLUSTERED
(
    [testTableId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
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.

 

Select MyTestApp.BAL and select MyTestApp.DbModel projects in the "Depends on" section and press ok.

 

Step 4

 

In "MyTestApp" Left-click on "Dependencies" and go to Add Reference. (Ref: Step 10 in here)

 

 

Select "MyTestApp.DbModel" and click ok.

 

Similarly open MyTestApp.BAL and left-click on "Dependencies" and go to Add Reference. Select "MyTestApp.DbModel" and click ok.

 

 

Step 5

 

Now select Tools and in Tools select Nuget Package Manager and then select Package Manager Console.

 

 

Step 6

 

Now run the below command in the console to install EntityFrameworkCore by selecting the default project to both MyTestApp & MyTestApp.DBModel.

Install-Package Microsoft.EntityFrameworkCore -Version 3.1.2

 

 

Now install the EntityFrameworkCore.Tools in both MyTestApp & MyTestApp.DBModel projects.

Install-Package Microsoft.EntityFrameworkCore.Tools -Version 3.1.2

 

 

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)

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.

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.

 

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.

Scaffold-DbContext "Server=ServerNameGoesHere;Database=MyTestDb;user id=sa;password=password;" Microsoft.EntityFrameworkCore.SqlServer -f

 For MySQL please run the below command.

Scaffold-DbContext "server=localhost;port=3306;user=root;password=yourPassword;database=MyTestDb" MySql.Data.EntityFrameworkCore -f

 

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.

using MyTestApp.DBModel;
using MyTestApp.IAL;
using System;
using System.Linq;
using static MyTestApp.DAL.MyCustomEntity;

namespace MyTestApp.BAL
{
    public class MyNewApiBAL : IMyNewAPI
    {
        MyTestDbContext _db = new MyTestDbContext();

        public MyNewApiBAL(MyTestDbContext db)
        {
            _db = db;
        }

        public MyCustomResponse GetAllData()
        {
            MyCustomResponse response = new MyCustomResponse();
            try
            {
                response.status = true;
                //get all data rows from myTestTable
                var item = _db.MyTestTable.ToList();

                response.response = "API called successfully";
            }
            catch (Exception ex)
            {
                response.status = false;
                response.response = "Exception occoured";
            }
            return response;
        }
    }
}

Now add the below code in StartUp.cs in ConfigureServices method.

var connection = "Server=YourServerNameGoesHere;Database=MyTestDb;user id=sa;password=password;";
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).

 

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.