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

create database FixItDb;

we are going to create a table on this database.

use FixItDb;

next to this let us create the table.

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.

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.

select * from Service;

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.

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

then install it.

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

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.

  <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

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

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.

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.

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.

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.


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.


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.

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.

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


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

the whole appSettings.json code will be.

 {
  "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.

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.

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

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

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

the whole startUp.cs file looks like the following

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.