DEV Community

Cover image for Rest Api Using C#, Dotnet, Docker, Entity Framework and Azure Data Studio
Abayomi Ogunnusi
Abayomi Ogunnusi

Posted on

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

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


Enter fullscreen mode Exit fullscreen mode

Image description

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


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


Enter fullscreen mode Exit fullscreen mode

You should have ✅:

Image description

⚙️ Build the project

Build helps to compile the project and check for errors



dotnet build


Enter fullscreen mode Exit fullscreen mode

Image description

🪝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


Enter fullscreen mode Exit fullscreen mode
Confirm the packages are installed in the csproj file

Open the csproj file and confirm the packages are installed

Image description


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)

Image description

Run the command below to create the folders



mkdir Data Models DTOs Services Repositories


Enter fullscreen mode Exit fullscreen mode
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;
    }
    }


Enter fullscreen mode Exit fullscreen mode
🌿 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!;
    }
}


Enter fullscreen mode Exit fullscreen mode
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)

Image description

  • 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


Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

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();


Enter fullscreen mode Exit fullscreen mode
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;"
  }
}



Enter fullscreen mode Exit fullscreen mode
Let's migrate the database using the dotnet cli


dotnet ef migrations add InitialCreate


Enter fullscreen mode Exit fullscreen mode

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

Image description

Let's update the database using the dotnet cli

This will sync the database with the migration file



dotnet ef database update


Enter fullscreen mode Exit fullscreen mode

On success, you will see the below message

Image description

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

Image description

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


Enter fullscreen mode Exit fullscreen mode

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



dotnet ef migrations add AddCreatedAtAndCountry
dotnet ef database update


Enter fullscreen mode Exit fullscreen mode

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

Image description

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


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode
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);
    }
}


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode
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);
    }
}


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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));
        }
    }
}


Enter fullscreen mode Exit fullscreen mode
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


Enter fullscreen mode Exit fullscreen mode
Run the project


dotnet run


Enter fullscreen mode Exit fullscreen mode

Image description

Test the endpoints using swagger

Go to https://localhost:5107/swagger/index.html
Image description

Rest Client

Create a new file in the root directory called rest.http

Now let's test the endpoints

Create a new artist

Image description

Get all artists

Image description

I will create more in the background just to have more data to play with

Image description

Get artist by id

Image description

Delete Artist by id

Image description

Update

Image description

Now Let's check our database

Image description

Conclusion

We have successfully created a REST API using .NET and C#. We also tested the endpoints using Swagger and the REST Client. Congratulations! 🕺🏻

I am glad that I was able to help you with this. Let me know if you have other requests or questions.

Top comments (2)

Collapse
 
fave7903 profile image
Favour Solomon

Just what I needed, great article 👍

Collapse
 
drsimplegraffiti profile image
Abayomi Ogunnusi

Thanks @fave7903