Rest Api Using C#, Dotnet, Docker, Entity Framework and Azure Data Studio
Tags: microsoft, dotnet, csharp, azure
Published: 2023-08-31 15:09:56

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
```bash
dotnet new webapi -n MusicApp
```

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



##### Clean the code base by removing the WeatherForecast.cs file and the WeatherForecastController.cs file
```bash
rm -rf WeatherForecast.cs
rm -rf Controllers/WeatherForecastController.cs
```

You should have ✅:

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




##### ⚙️ Build the project
Build helps to compile the project and check for errors
```bash
dotnet build
```

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



#### 🪝Let's install the packages that we will need
Run the below commands in the terminal
```bash
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

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

---


##### 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ahbvagk0wly3pq4aqccy.png)


Run the command below to create the folders
```bash
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.
```csharp

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
```csharp
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)

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


- 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
```json
{
  "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
```bash
dotnet ef migrations add InitialCreate
```
The above command will create a migration folder with the migration file

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



##### Let's update the database using the dotnet cli
This will sync the database with the migration file
```bash
dotnet ef database update
```

On success, you will see the below message

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



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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n69lia8sq4yaaq4qxjm1.jpg)



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

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
```bash
dotnet ef migrations add AddCreatedAtAndCountry
dotnet ef database update
```

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

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




##### 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
```csharp
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
```csharp
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
```csharp
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
```csharp
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
```csharp
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
```csharp

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

##### Run the project
```bash
dotnet run
```

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



##### Test the endpoints using swagger
Go to https://localhost:5107/swagger/index.html
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2z47k4mgfyci25nrtpzc.jpg)



##### 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t6beze8ofj9yzz3nv9u5.jpg)



##### Get all artists

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



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

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




##### Get artist by id

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



##### Delete Artist by id

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



##### Update

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



Now Let's check our database

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



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