Introduction to PingCap/TiDB/ Part-3: Building a .Net core web API and connect to TiDB Mysql Server
Published: 2021-04-20 20:28:41

In the previous part of this article, we tried to connect the TiDB MySQL with Grafana and tried to visualize and monitor the data that comes from MySQL. In this article, we are going to implement a .Net core web API on top of the TiDB MySQL server.


#**Pre requests**
**1. See  [part 1](https://dev.to/yared123yared/introduction-to-pingcap-tidb-part-1-installation-of-tidb-on-windows-machine-1320) of this article in order to set up TiDB successfully in your local machine.**

**2. Make sure you installed *.Net* if you haven't installed go to this [article](https://docs.microsoft.com/en-us/dotnet/framework/install/on-windows-10) and install it.**



**Step 1: Creating the database**

To create the database we should start the TiDB server first using the following command.

```
tiup playground
```

Then, we need to start the MySQL server on the IP address that the TiDB is running and any port number.

```
mysql --host 127.0.0.1 --port 4000 -u root
```

next to this let us create the database

```SQL
create database FixItDb;
```
we are going to create a table on this database. 

```SQL
use FixItDb;
```

next to this let us create the table.

```SQL
CREATE TABLE  Service(
    	ServiceId  int NOT NULL  AUTO_INCREMENT,
    	ServiceName  varchar(255) NOT NULL,
   	Description  varchar(255) NOT NULL,
  	Category  varchar(255) NOT NULL,
	InitialPrice  int,
	IntermediatePrice int ,
	AdvancedPrice  int ,
	AddedTime DATETIME,
	PRIMARY KEY (ServiceId)
);
```
let us add some data to it.

```SQL
INSERT INTO Service(ServiceName,Description,Category,InitialPrice,IntermediatePrice,AdvancedPrice,AddedTime) VALUES("Fixing Electronics","The wire have to be fixed","Electrical",34,223,10,"2020-06-03 09:37:13");
INSERT INTO Service(ServiceName,Description,Category,InitialPrice,IntermediatePrice,AdvancedPrice,AddedTime) VALUES("Fixing Electronics","The wire have to be fixed","Electrical",34,223,10,"2020-06-03 18:10:13");
INSERT INTO Service(ServiceName,Description,Category,InitialPrice,IntermediatePrice,AdvancedPrice,AddedTime) VALUES("Fixing Electronics","The wire have to be fixed","Electrical",34,223,10,"2020-06-03 01:11:13");
INSERT INTO Service(ServiceName,Description,Category,InitialPrice,IntermediatePrice,AdvancedPrice,AddedTime) VALUES("Fixing Electronics","The wire have to be fixed","Electrical",34,223,10,"2020-06-03 06:13:13");
INSERT INTO Service(ServiceName,Description,Category,InitialPrice,IntermediatePrice,AdvancedPrice,AddedTime) VALUES("Fixing Electronics","The wire have to be fixed","Electrical",34,223,10,"2020-06-03 12:09:13");
INSERT INTO Service(ServiceName,Description,Category,InitialPrice,IntermediatePrice,AdvancedPrice,AddedTime) VALUES("Fixing Electronics","The wire have to be fixed","Electrical",34,223,10,"2021-06-03 12:09:13");
INSERT INTO Service(ServiceName,Description,Category,InitialPrice,IntermediatePrice,AdvancedPrice,AddedTime) VALUES("Fixing Electronics","The wire have to be fixed","Electrical",34,223,10,"2021-06-03 01:01:13");
INSERT INTO Service(ServiceName,Description,Category,InitialPrice,IntermediatePrice,AdvancedPrice,AddedTime) VALUES("Fixing Electronics","The wire have to be fixed","Electrical",34,223,10,"2021-06-03 06:09:13");

```
now if we select everything from the **Service** table we should see all the data. 

```SQL
select * from Service;
```
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zhdw53iwav3yl6x6e3vp.png)
 
**Step 2: configuring the .Net web API project**

After we set up our MySQL server well the next step will be configuring the .Net core web API for connecting with TiDB MySQL.


Create a new .Net web API project by using this command. 

```cmd
dotnet new  webapi -n <name>
```
then we will get the template for a web API. remove the default controller from the controller directory. 


The next thing we need to do is to install the necessary packages. 

to install package we have especial extension called **NuGet Package Manager** from the visual studio code go to the extension tab and search for **NuGet Package Manager** 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kcg85ugfyy9uwrlc7u1e.png)

then install it. 

then after that hit on **ctrl + shift + p** and you will see a drop-down option from the above. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kfjf41e2tu0tmnng4rb9.png)

Click on the above choice which says that **Nuget Package Manager: Add Package** it will prompt you to enter the package name so enter the package name in this case the package we want to install is **MySql.Data.EntityFrameworkCore** which used to connect to MySQL database.

then choose the version number that can work fine with the  .Net version of your computer. after some seconds it will automatically install it and put it on the **.csproj** file.

do the same for the rest of the packages too. 

```csproj
  <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.12" />
  <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="3.2.4" />
  <PackageReference Include="MySql.Data.EntityFrameworkCore" Version="8.0.19" />
```
the whole code of our **.csproj** file will look like the following

```c#
<Project Sdk="Microsoft.NET.Sdk.Web" ToolsVersion="Current">
  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <OldToolsVersion>2.0</OldToolsVersion>
    <GenerateAssemblyInfo>false</GenerateAssemblyInfo>
  </PropertyGroup>
  <ItemGroup>    
  <PackageReference Include="AutoMapper.Extensions.Microsoft.DependencyInjection" Version="8.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.12" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="3.2.4" />
    <PackageReference Include="MySql.Data.EntityFrameworkCore" Version="8.0.19" />
 </ItemGroup>
</Project>
```

**Step 3: add a model called Service.cs**

First, create a folder called **Model** and in that folder create *Service.cs* model.

This model should be the same as the database column we have created before. so our **Service.cs** code will look like this. 

```c#
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System;

namespace fixit.DTO
{
    public class ServiceDto
    {

        [Required]
        public int ServiceId { get; set; }
        [Required]
        public string ServiceName { get; set; }
        [Required]
        public string Description { get; set; }
        [Required]
        public string Category { get; set; }
        [Required]
        public int InitialPrice { get; set; }
        [Required]
        public int IntermediatePrice { get; set; }
        [Required]
        public int AdvancedPrice { get; set; }
        [Required]
        public DateTime AddedTime { get; set; }




    }
}
```

**Step 4: add a DTO called *ServiceDto.cs*.**
First, create a folder called **DTO** and in this folder create a file called *ServiceDto.cs* and add the following code. 

```c#
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System;

namespace fixit.DTO
{
    public class ServiceDto
    {

        [Required]
        public int ServiceId { get; set; }
        [Required]
        public string ServiceName { get; set; }
        [Required]
        public string Description { get; set; }
        [Required]
        public string Category { get; set; }
        [Required]
        public int InitialPrice { get; set; }
        [Required]
        public int IntermediatePrice { get; set; }
        [Required]
        public int AdvancedPrice { get; set; }
        [Required]
        public DateTime AddedTime { get; set; }





    }
}
```
The purpose of adding this file to the project is in order to map the data that come from the database to some format we want. which will be stored on the Dto file called *ServiceDto.cs*

**Step 5: Add a Profile called *ServiceProfile.cs*.**
create a folder called *Profile* and in this folder create a file called **ServiceProfile.cs** and in this file add the following code.
```c#
using AutoMapper;
using fixit.DTO;
using fixit.Models;

namespace fixit.Profiles
{
    public class ServiceProfile : Profile
    {
        public ServiceProfile()
        {
            CreateMap<fixit.Models.Service, ServiceDto>()
            .ForMember(dest => dest.ServiceId, opt => opt.MapFrom(src => src.ServiceId))
            .ForMember(dest => dest.ServiceName, opt => opt.MapFrom(src => src.ServiceName))
            .ForMember(dest => dest.Description, opt => opt.MapFrom(src => src.Description))
            .ForMember(dest => dest.Category, opt => opt.MapFrom(src => src.Category))
            .ForMember(dest => dest.InitialPrice, opt => opt.MapFrom(src => src.InitialPrice))
            .ForMember(dest => dest.IntermediatePrice, opt => opt.MapFrom(src => src.IntermediatePrice))
            .ForMember(dest => dest.AdvancedPrice, opt => opt.MapFrom(src => src.AdvancedPrice))
            .ForMember(dest => dest.AddedTime, opt => opt.MapFrom(src => src.AddedTime));
           CreateMap<ServiceDto, fixit.Models.Service>();

    
    
    
 }

    }
}
```
What this code will do is mapping from the model to Dto using a special package called **AutoMapper**.

**Step 6: Create an interface called  **IRepository.cs**
create a folder called **Data** and in that folder create a file called **IRepository.cs** and add the following code.

```c#
using System.Collections.Generic;
using System.Threading.Tasks;
using fixit.DTO;

namespace fixit.Data
{
    public interface IRepository<T>
    {
        Task<List<T>> GetData();
        Task<T> GetDataById(int id);

        Task<T> InsertData(T service);
        Task<T> UpdateData(T service);
        Task<bool> DeleteData(T service);

    }
}
```
This interface act as a mediator between the controller and repository.

**Step 7:  Add a DataContext for the Service model**

from the **Data** folder, we have created before, create a new file called **DataContext.cs** and add the following code to it.

```c#

using fixit.Models;
using Microsoft.EntityFrameworkCore;
using MySql.Data.EntityFrameworkCore;
namespace fixit.Data

{
    
    public class DataContext : DbContext
    {
        public DataContext() { }
        public DataContext(DbContextOptions<DataContext> options) : base(options) { }

            public DbSet<Service> Service { get; set; }
        
        }
}


```

**Step 8: Add ServiceRepsitory.cs**
From the **Data** folder again create a file called **ServiceRepositiry.cs** this file will contain all the CRUD operations that will be performed on the database. the code of all the CRUD operations is like this just copy and paste it. 

```c#

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using fixit.Models;


namespace fixit.Data
{
    public class ServiceRepository: IRepository<Service>
    {
        private readonly DataContext _context;
        public ServiceRepository(DataContext context)
        {
            _context = context;
        }
        // Delete Service objects
        public async Task<bool> DeleteData(Service service)
        {
            Console.WriteLine("Delete method invoked");
            _context.Service.Remove(service);
            await _context.SaveChangesAsync();
            return true;
        }


        // Get all service objects
        public async Task<List<Service>> GetData()
        {
            //    Getting database data here
            var model = await _context.Service.ToListAsync();
            return model;

        }
        // Get Service by  id
        public async Task<Service> GetDataById(int id)
        {
            return await _context.Service.FirstOrDefaultAsync(x => x.ServiceId == id);
        }
       
        // Update and create new service objects
        public async Task<Service> InsertData(Service service)
        {

            Console.WriteLine("Create data  method invoked");
            _context.Service.Add(service);

            await _context.SaveChangesAsync();
            return service;
        }

        public async Task<Service> UpdateData(Service service)
        {




            Console.WriteLine("Update method  invoked");



            _context.Update(service).Property(x => x.ServiceId).IsModified = false;
            _context.SaveChanges();

            return service;
        }
    }
}
```

**Step 9: Add ServiceController.cs**

By default when the project created it will contain a folder called *Controller*. if it does not exist create it. then in that folder create a file called **ServiceController.cs** and add the following code to it.

```c#
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using AutoMapper;
using fixit.Data;
using fixit.DTO;
using Microsoft.AspNetCore.Mvc;
using fixit.Models;

using Microsoft.AspNetCore.Authorization;

namespace Controllers
{

    
    [ApiController]
    [Route("api/services")]
    public class ServiceController : ControllerBase
    {
        private readonly IRepository<Service> _repo;
        private readonly IMapper _mapper;
        public ServiceController(IRepository<Service> repo, IMapper mapper)
        {
            _repo = repo;
            _mapper = mapper;
        }



        [HttpGet]
        public async Task<IActionResult> GetServices()
        {
            Console.WriteLine("This is the get All service method");

            var model = await _repo.GetData();
           
            return Ok(_mapper.Map<IEnumerable<ServiceDto>>(model));
        
        }

        [HttpGet("{id}")]
        public async Task<IActionResult> GetServiceById(int id)
        {
             Console.WriteLine("This is the comming id ");
            Console.WriteLine(id);
             

            var model = await _repo.GetDataById(id);
            return Ok(_mapper.Map<ServiceDto>(model));
        }
     
     
        //  Service Post method

         [HttpPost]
        public async Task<IActionResult> CreateService(ServiceDto serviceDto)
        {
            Console.WriteLine("Crate Service Method Invoked");
            var service = _mapper.Map<Service>(serviceDto);
            await _repo.UpdateData(service);
            return Ok(serviceDto);
        }
        
        // Service Delete method
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteServices(int id)
        {
            var service = await _repo.GetDataById(id);
            // var service = _mapper.Map<Service>(serviceDto);
            await _repo.DeleteData(service);
            return Ok(_mapper.Map<ServiceDto>(service));

            
        }
    }
}
```
The purpose of this controller is to receive any requests from any client and get information from the database through the mediator interface then after that, it will return the data back to the client.

**Step 10: update the connection string on appSettings.json**

the database information will be store on the connection string, later on, to connect it to the data context on the startup method.

in this case, we need to give information about the database that is running on the TiDB server. you can get this information from the command you used to start MySQL.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6wi7blunzvlovu3hd17u.png)

**mysql --host 127.0.0.1 --port 4000 -u root** from this we can get all the necessary information about the database.

The server is running on the IP address of **127.0.0.1** which means it is **localhost** and on the port number of **4000** with a user name of **root** and an **empty** password. using this information we can write the connection string as

**"Server=localhost;port=4000;database=FixItDb;user=root;password=;"**

and put this to the **ConnectionStrings** object by assigning to a variable that used to access this information from some other file. for my case it is **fixItConnection**

```JSON
 
 "ConnectionStrings": {
   "fixItConnection": "Server=localhost;port=4000;database=FixItDb;user=root;password=;"
 }
```

the whole **appSettings.json** code will be. 

```JSON
 {
  "AppSettings": {
    
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  
  "ConnectionStrings": {
    "fixItConnection": "Server=localhost;port=4000;database=FixItDb;user=root;password=;"
  }
}
```

**Step 11: Updating the startUp.cs**

update the *startUp.cs* file of the project in order to connect to MySQL. 

from the **ConfigureService** method add the following line of code. 

```c#
services.AddDbContext<DataContext>(opt => opt.UseMySql(Configuration.GetConnectionString("fixItConnection")));
```

here the **UseMySql** method will be imported from the package we have installed before.**MySql.Data.EntityFrameworkCore** and used to interconnect the database information stored on the **fixItConnection** to the **DataContext**.

down to this, we should insert a code for **CORS** this gives permission to our back-end server in order to be accessible by any front-end running on the same machine. unless otherwise, we will get a cors error whenever we try to access this server from the same machine.

```c#
services.AddCors(option =>
            {
                option.AddPolicy("allowedOrigin",
                    builder => builder.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader()
                    );
            });
```

Then we need to register **Controllers** and **Repository** we have created so far.

```c#
  services.AddControllers();
  services.AddScoped<IRepository<Service>, ServiceRepository>();
```


the whole startUp.cs file looks like the following 

```c#
using System;
using AutoMapper;
using fixit.Data;
using fixit.Models;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

using System.Text;

using MySql.Data.EntityFrameworkCore;
// using Pomelo.EntityFrameworkCore.MySql;
// using fixit.Service;

namespace fixit
{
    public class Startup
    {   
             readonly string AllowedOrigin = "allowedOrigin";
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<DataContext>(opt => opt.UseMySql(Configuration.GetConnectionString("fixItConnection")));
            services.AddAutoMapper(AppDomain.CurrentDomain.GetAssemblies());

           services.AddCors(option =>
            {
                option.AddPolicy("allowedOrigin",
                    builder => builder.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader()
                    );
            });
          
            services.AddControllers();
         
            services.AddScoped<IRepository<Service>, 
            ServiceRepository>();
        
         
         
         

        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            app.UseCors(AllowedOrigin);
            app.UseHttpsRedirection();

            app.UseRouting();

            app.UseAuthentication();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

```

#**Step 12: Running the Code and Check the result**
**Almost Done**. now we have done with the implementation staffs. the next thing will be running the code and check its functionality. in order to run any dotnet project what we need to do is to execute the following command from the terminal.

```cmd
dotnet run
```

you will get a page that looks like this. 
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1tp0ebnpqrmb9erk1eso.png)
copy the IP address from the page running and test it using postman. 

**http://localhost:5000/api/services/** this is the URL for the controller Service let us try the Get method from the postman. 
![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p5so7mxmtvzr4g4aante.png)

we got the data that has been store in the TiDB Mysql server.

This will be the end of this article.

Thank you!