Implement CRUD Operations in .NET Core 8 with Dockerized MSSQL Server

Manikandan Murugesan
1y
11.4k
0
5

Article

In this article, I'm going to show how to implement the basic CRUD operations in ASP.NET Core Web API in .NET Core 8 and also save the data in the dockerized MS SQL Server.

This is a continuation of my previous article about configuring the MS SQL Server in the Docker Desktop. Kindly refer to the article to know more about the setup.

Requirements

Visual Studio 2019 or 2022
.NET 8
Docker Desktop & SQL Server Container
Basic understanding of C#, DOTNET Core Framework & Web API's

We need to create a Web API application in Visual Studio 2022 & Choose "Create a new Project".

Visual Studio

Then on the Project Template page, choose the "ASP.NET Core Web API"

Create new project

On the Configure page, Kindly enter the Project Name, and Solution Name and Choose "Next"

Configure new project

Chose the Additional information as it is and then press "Create"

Additional information

Also, create the Folders & Files in the screenshot of Solution Explorer except for the migration folder which was created at the time of migration.

Solution Explorer

Also, install the necessary packages via the Package Manager console.

Installed packages

Kindly copy and paste the code for the respective files.

Student.cs

namespace StudentAPI.Models
{
    public class Student
    {
        public int Id { get; set; }
        public required string Name { get; set; }
        public required string Email { get; set; }

    }
}

DataContext.cs

using Microsoft.EntityFrameworkCore;
using StudentAPI.Models;

namespace StudentAPI.Data
{
    public class DataContext: DbContext
    {
        public DataContext(DbContextOptions<DataContext> options): base(options)
        {

        }
        public DbSet<Student> Students { get; set; }
    }
}

StudentController.cs

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Infrastructure;
using Microsoft.EntityFrameworkCore;
using StudentAPI.Data;
using StudentAPI.Models;
using System.Runtime.CompilerServices;

namespace StudentAPI.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class StudentController : ControllerBase
    {
        private readonly DataContext _context;

        public StudentController(DataContext context)
        {
            _context = context;
        }

        [HttpGet("GetAllStudents")]
        public async Task<ActionResult<List<Student>>> GetAllStudents()
        {
            var students = await _context.Students.ToListAsync();
            return Ok(students);
        }

        [HttpGet("GetStudentById/{id}")]
        public async Task<ActionResult<Student>> GetStudentById(int id)
        {
            var student = await _context.Students.Where(Student => Student.Id == id).FirstOrDefaultAsync();
            if(student == null)
            {
                return NotFound();
            }
            return Ok(student);
        }

        [HttpPost("AddStudent")]
        public async Task<ActionResult> AddStudent(Student student)
        {
            await _context.Students.AddAsync(student);
            await _context.SaveChangesAsync();
            return Ok(student);
        }

        [HttpDelete("DeleteStudent/{id}")]
        public async Task<ActionResult> DeleteStudent(int id)
        {
            var student = await _context.Students.Where(Student => Student.Id == id).FirstOrDefaultAsync();
            if(student == null)
            {
                return NotFound();
            }
            _context.Students.Remove(student);
            await _context.SaveChangesAsync();
            return Ok();
        }

        [HttpPut("UpdateStudent")]

        public async Task<ActionResult<Student>> UpdateStudent(Student updateStudent)
        {
            var dbStudent = await _context.Students.FindAsync(updateStudent.Id);
            if(dbStudent == null)
            {
                return NotFound("Student not found...");
            }
            dbStudent.Name = updateStudent.Name;
            dbStudent.Email = updateStudent.Email;

            await _context.SaveChangesAsync();

            return Ok(await _context.Students.ToListAsync());

        }
    }
}

Add the MSSQL Server Configuration line on the Program.cs.

builder.Services.AddDbContext<DataContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("<ConnectionName>")));

Also, kindly add the Database connection to the appsettings.json file. Use your User ID & Password.

"ConnectionStrings": {
  "DockerDBConnection": "Data Source=localhost,1433;Database=StudentDB;User ID=sa;Password=Test@123;Connect Timeout=30;Encrypt=False;Trust Server Certificate=True;Application Intent=ReadWrite;Multi Subnet Failover=False"
}

Then you need to open the Package Manager console. Type the command to add the migration

Add-migration initial

After the successful migration, then update-database to update the scheme on the database

update-database

Now, you can able to see the Database created on the Docker SQL Server. Open the MS SQL Server Management Studio -> Login using the Docker Credentials -> Refresh -> Able to see the database on the Object Explorer

Object Explorer