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

​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

Here, I have used the MSSQL server running on the container. Make sure Docker Deamon is running. otherwise, it'll throw an error.

Now, build & run the application. You can able to see the swagger is opened on the browser with the mentioned endpoints.

Finally, you can make the basic curd operations from the Swagger and then data will be stored in the database on the Docker Container.

You can see the data from the table and the API call returns the same. Kindly try it from your end and let me know if you are facing any issues.

Also, In my next article, I'll show how to deploy this API application in Docker Container.