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
Run the project
dotnet run
Test the endpoints using swagger
Go to https://localhost:5107/swagger/index.html
Rest Client
Create a new file in the root directory called rest.http
Now let's test the endpoints
Create a new artist
Get all artists
I will create more in the background just to have more data to play with
Get artist by id
Delete Artist by id
Update
Now Let's check our database
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)
Just what I needed, great article 👍
Thanks @fave7903