Abayomi Ogunnusi

Posted on Aug 31, 2023

Rest Api Using C#, Dotnet, Docker, Entity Framework and Azure Data Studio

#microsoft #dotnet #csharp #azure

Hello Everyone, today we will be using C#, .NET, MSSQL, Azure Data Studio, and Entity Framework technologies to develop data-centric applications.

Ingredients

Visual Studio Code
Dotnet Core SDK
C# Extension for Visual Studio Code

Prerequisite

🏝️ Basic Knowledge of C#
🏝️ How to use the terminal

Agenda

🪢 Create a new C# project in Visual Studio.
🪢 Add the necessary references to the .NET Framework and Entity Framework libraries.
🪢 Create a database in Azure SQL Database.
🪢 Create a connection string to the database.
🪢 Map the entities to the tables in the database.
🪢 Write code to access the data from the database.
🪢 Run the application and test it.

Create a new web api project using dotnet cli



dotnet new webapi -n MusicApp

Clean the code base by removing the WeatherForecast.cs file and the WeatherForecastController.cs file



rm -rf WeatherForecast.cs
rm -rf Controllers/WeatherForecastController.cs

You should have ✅:

⚙️ Build the project

Build helps to compile the project and check for errors



dotnet build

🪝Let's install the packages that we will need

Run the below commands in the terminal



dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Tools

Confirm the packages are installed in the csproj file

Open the csproj file and confirm the packages are installed

Folder Structure

Create the

Data folder
- This will contain the database context
Models folder
- This will contain the models classes (This is the representation of the data in the database)
Controllers folder
- This will contain the controllers (This handles the requests and responses from the client)
DTOs folder
- This will contain the Data Transfer Objects (This handles the data that is sent to the client)
Services folder
- This will contain the services (This handles the business logic)
Repositories folder
- This will contain the repositories (This handles the data access)

Run the command below to create the folders



mkdir Data Models DTOs Services Repositories

Create the models

First we will create the models. These are the classes that will represent the data in the database.




using System.ComponentModel.DataAnnotations;

namespace MusicApp.Models
{
    public class Artist
    {

        public int Id { get; set; }

        [Required (ErrorMessage = "Name is required")]
        public string Name { get; set; }  = string.Empty;
        public string Genre { get; set; } = string.Empty;
    }
    }

🌿 Create the database context

Now we will create the database context. This will be the class that will connect to the database and handle the data access.
We will import the Artist model class that we created above.
In Data/AppDbContext.cs



using Microsoft.EntityFrameworkCore;
using MusicApp.Models;

namespace MusicApp.Data
{
    public class AppDbContext: DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options): base(options)
        {

        }

        // Create a DbSet for each model class
        public DbSet<Artist> Artists { get; set; } = null!;
    }
}

Connect the database context to the database

Steps

Install msql
Install docker on mac/windows
Install azure data studio
create docker id by signning up
sign in to docker
download the official image of sql container Image - azure sql edge (by microsoft)

copy the docker pull image and paste in terminal: ```bash

docker pull mcr.microsoft.com/azure-sql-edge

Once completed you will see the azure image in the docker app

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3wpbskxtckp8ba5v5yvn.jpg)



- Run the docker image container by pasting the below command in the terminal. Ensure you replace your password with the {password} placeholder
```bash


docker run -e "ACCEPT_EULA=1" -e "MSSQL_SA_PASSWORD={password}" -e "MSSQL_PID=Developer" -e "MSSQL_USER=SA" -p 1433:1433 -d --name=sql mcr.microsoft.com/azure-sql-edge

Confirm the container is running by running the command below ```bash

docker ps

You get

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/16lbnq3v07j7tnsss5cd.png)



- Connect to the database using azure data studio
Open your azure data studio and connect to the database

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i1ydfl8lqx549qa5mgtw.jpg)



Connect to the database using the credentials below

server - localhost
Username - SA
Password - ****
Remember Password Yes
Choose Database as Default



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rok46ami6zi9o4d16xmy.jpg)



##### Connect the app to the database
In the Program.cs file, we will add the connection string to the database
```csharp


using Microsoft.EntityFrameworkCore;
using MusicApp.Data;

var builder = WebApplication.CreateBuilder(args);


// connect to database
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"))); // DefaultConnection is the name of the connection string


builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

Set the connection string in the appsetting.json

You can eithert set the connection string in the appsetting.json file or use the DotNetEnv to Load the secret. Today we will go with the conventional way of setting the connection string in the appsetting.json file.
Then we Set the connection string



{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection":"Server=localhost,1433;Database=musicDb;User Id=SA;Password={***************};Encrypt=false;TrustServerCertificate=True;"
  }
}

Let's migrate the database using the dotnet cli



dotnet ef migrations add InitialCreate

The above command will create a migration folder with the migration file

Let's update the database using the dotnet cli

This will sync the database with the migration file



dotnet ef database update

On success, you will see the below message

And if we check our database we will see the tables have been created. Go to Azure Data Studio and refresh the database

Let's Assume we need to add a new column to the Artist table. We will run the below command to create a new migration file. E.g the CreatedAt Column and the Country Column




using System.ComponentModel.DataAnnotations;

namespace MusicApp.Models
{
    public class Artist
    {

        public int Id { get; set; }

        [Required(ErrorMessage = "Name is required")]
        public string Name { get; set; } = string.Empty;
        public string Genre { get; set; } = string.Empty;
        public string Country { get; set; } = string.Empty;

        public DateTime CreatedAt { get; set; } = DateTime.Now;
    }
}

And then we will run the below command to update the database



dotnet ef migrations add AddCreatedAtAndCountry
dotnet ef database update

On Success, go to Azure Data Studio and refresh the database

Create the DTOs

Next we will create the DTOs. These are the classes that will handle the data that is sent to the client.

In DTOs/Request/ArtistRequest.cs



using System.ComponentModel.DataAnnotations;

namespace MusicApp.DTOs
{
    public class ArtistRequest
    {
        [Required(ErrorMessage = "Name is required")]
        public string Name { get; set; } = string.Empty;

        [Required(ErrorMessage = "Genre is required")]
        public string Genre { get; set; } = string.Empty;

        [Required(ErrorMessage = "Country is required")]
        public string Country { get; set; } = string.Empty;
    }
}

In DTOs lets create a generic response class that will be used by all the responses



using System;

namespace MusicApp.DTOs
{
    public class Response<T>
    {
        public T? Data { get; set; }
        public bool Success { get; set; } = true;
        public string Message { get; set; } = string.Empty;
    }
}

Create the repositories Interface and Implementation classes

Next we will create the repositories. These are the classes that will handle the data access.
This will be an interface that will be implemented by the repository implementation class
In Repositories/IArtistRepository.cs. Ensure it an interface



using System.Collections.Generic;


namespace MusicApp.Repositories.Interfaces
{
    public interface IArtistRepository
    {
        Task<List<Artist>> GetAllArtistsAsync();
        Task<Artist> GetArtistByIdAsync(int id); // import the Artist model
        Task<Artist> CreateArtistAsync(Artist artist);
        Task<Artist> UpdateArtistAsync(Artist artist);
        Task<bool> DeleteArtistAsync(int id);
    }
}

Now we will create the repository implementation class
In Repositories/ArtistRepository.cs



using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using MusicApp.Data;
using MusicApp.Models;
using MusicApp.Repositories.Interfaces;

namespace MusicApp.Repositories
{
    public class ArtistRepository : IArtistRepository
    {
        private readonly AppDbContext _context;

        public ArtistRepository(AppDbContext context)
        {
            _context = context;
        }

        public async Task<List<Artist>> GetAllArtistsAsync()
        {
            return await _context.Artists.ToListAsync();
        }

        public async Task<Artist> GetArtistByIdAsync(int id)
        {
            return await _context.Artists.FindAsync(id);
        }

        public async Task<Artist> CreateArtistAsync(Artist artist)
        {
            _context.Artists.Add(artist);
            await _context.SaveChangesAsync();
            return artist;
        }

        public async Task<Artist> UpdateArtistAsync(Artist artist)
        {
            _context.Artists.Update(artist);
            await _context.SaveChangesAsync();
            return artist;
        }

        public async Task<bool> DeleteArtistAsync(int id)
        {
            var artist = await GetArtistByIdAsync(id);
            _context.Artists.Remove(artist);
            await _context.SaveChangesAsync();
            return true;
        }
    }
}

Create the services Interface and Implementation classes

Next we will create the services. These are the classes that will handle the business logic.
This will be an interface that will be implemented by the service implementation class
In Services/IArtistService.cs. Ensure it an interface



using MusicApp.DTOs;
using MusicApp.Models;

namespace MusicApp.Services.Interfaces
{
    public interface IArtistService
    {
        Task<Response<List<Artist>>> GetAllArtistsAsync();
        Task<Response<Artist>> GetArtistByIdAsync(int id);
        Task<Response<Artist>> CreateArtistAsync(ArtistRequest artistRequest);
        Task<Response<Artist>> UpdateArtistAsync(int id, ArtistRequest artistRequest);
        Task<Response<bool>> DeleteArtistAsync(int id);
    }
}

Now we will create the service implementation class
In Services/ArtistService.cs




using MusicApp.DTOs;
using MusicApp.Models;
using MusicApp.Repositories;
using MusicApp.Services.Interfaces;

namespace MusicApp.Services
{
    public class ArtistService : IArtistService
    {
        private readonly IArtistRepository _artistRepository;

        public ArtistService(IArtistRepository artistRepository)
        {
            _artistRepository = artistRepository;
        }

        public async Task<Response<List<Artist>>> GetAllArtistsAsync()
        {
            var response = new Response<List<Artist>>();
            var artists = await _artistRepository.GetAllArtistsAsync();
            response.Data = artists;
            return response;
        }

        public async Task<Response<Artist>> GetArtistByIdAsync(int id)
        {
            var response = new Response<Artist>();
            var artist = await _artistRepository.GetArtistByIdAsync(id);
            if (artist == null)
            {
                response.Success = false;
                response.Message = "Artist not found";
                return response;
            }
            response.Data = artist;
            return response;
        }

        public async Task<Response<Artist>> CreateArtistAsync(ArtistRequest artistRequest)
        {
            var response = new Response<Artist>();
            var artist = new Artist
            {
                Name = artistRequest.Name,
                Genre = artistRequest.Genre,
                Country = artistRequest.Country
            };
            var createdArtist = await _artistRepository.CreateArtistAsync(artist);
            response.Data = createdArtist;
            return response;
        }

        public async Task<Response<Artist>> UpdateArtistAsync(int id, ArtistRequest artistRequest)
        {
            var response = new Response<Artist>();
            var artist = await _artistRepository.GetArtistByIdAsync(id);
            if (artist == null)
            {
                response.Success = false;
                response.Message = "Artist not found";
                return response;
            }
            artist.Name = artistRequest.Name;
            artist.Genre = artistRequest.Genre;
            artist.Country = artistRequest.Country;
            var updatedArtist = await _artistRepository.UpdateArtistAsync(artist);
            response.Data = updatedArtist;
            return response;
        }

        public async Task<Response<bool>> DeleteArtistAsync(int id)
        {
            var response = new Response<bool>();
            var deleted = await _artistRepository.DeleteArtistAsync(id);
            response.Data = deleted;
            return response;
        }
    }
}

Note:

The Repository class handles the data access i.e communicates with the database
The Service class handles the business logic i.e communicates with the repository class and handles the business logic

So the flow is as follows:

The Controller class handles the requests and responses from the client
The Controller class communicates with the Service class
The Service class communicates with the Repository class
The Repository class communicates with the Database

Create the controllers

Next we will create the controllers. These are the classes that will handle the requests and responses from the client.
In Controllers/ArtistsController.cs



using Microsoft.AspNetCore.Mvc;
using MusicApp.DTOs;
using MusicApp.Services.Interfaces;

namespace MusicApp.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class ArtistsController : ControllerBase
    {
        private readonly IArtistService _artistService;

        public ArtistsController(IArtistService artistService)
        {
            _artistService = artistService;
        }

        [HttpGet]
        public async Task<IActionResult> GetAllArtistsAsync()
        {
            return Ok(await _artistService.GetAllArtistsAsync());
        }

        [HttpGet("{id}")]
        public async Task<IActionResult> GetArtistByIdAsync(int id)
        {
            return Ok(await _artistService.GetArtistByIdAsync(id));
        }

        [HttpPost]
        public async Task<IActionResult> CreateArtistAsync([FromBody] ArtistRequest artistRequest)
        {
            return Ok(await _artistService.CreateArtistAsync(artistRequest));
        }

        [HttpPut("{id}")]
        public async Task<IActionResult> UpdateArtistAsync(int id, [FromBody] ArtistRequest artistRequest)
        {
            return Ok(await _artistService.UpdateArtistAsync(id, artistRequest));
        }

        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteArtistAsync(int id)
        {
            return Ok(await _artistService.DeleteArtistAsync(id));
        }
    }
}

Lastly lets inject the dependencies in the Program.cs file



using Microsoft.EntityFrameworkCore;
using MusicApp.Data;
using MusicApp.Repositories;
using MusicApp.Repositories.Interfaces;
using MusicApp.Services;
using MusicApp.Services.Interfaces;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.AddScoped<IArtistRepository, ArtistRepository>();
builder.Services.AddScoped<IArtistService, ArtistService>();

builder.Services.AddControllers();

//... other code