CRUD Operations in .NET 9 with SQL Server: A Practical Guide

Introduction

CRUD operations (Create, Read, Update, Delete) are fundamental in web development. In this tutorial, we will build a simple blog post application using .NET 9, Entity Framework Core, and SQL Server. By the end, you’ll understand how to implement CRUD operations in a modern .NET web API.

---

Prerequisites

• .NET 9 SDK installed
• SQL Server instance running
• Visual Studio 2022 or VS Code
• Basic knowledge of C# and SQL

---

Step 1: Create a .NET 9 Web API Project

dotnet new webapi -n BlogApi
cd BlogApi

---

Step 2: Install Entity Framework Core

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

---

Step 3: Create the BlogPost Model

namespace BlogApi.Models
{
    public class BlogPost
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public DateTime CreatedAt { get; set; } = DateTime.Now;
    }
}

---

Step 4: Setup DbContext

using Microsoft.EntityFrameworkCore;
using BlogApi.Models;

namespace BlogApi.Data
{
    public class BlogDbContext : DbContext
    {
        public BlogDbContext(DbContextOptions<BlogDbContext> options) : base(options) { }
        public DbSet<BlogPost> BlogPosts { get; set; }
    }
}

Add DbContext to Program.cs:

builder.Services.AddDbContext<BlogDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

And in appsettings.json:

"ConnectionStrings": {
  "DefaultConnection": "Server=YOUR_SERVER_NAME;Database=BlogDb;Trusted_Connection=True;"
}

---

Step 5: Create BlogPosts Controller

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using BlogApi.Data;
using BlogApi.Models;

namespace BlogApi.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class BlogPostsController : ControllerBase
    {
        private readonly BlogDbContext _context;
        public BlogPostsController(BlogDbContext context)
        {
            _context = context;
        }

        [HttpGet]
        public async Task<IActionResult> GetAll() => Ok(await _context.BlogPosts.ToListAsync());

        [HttpGet("{id}")]
        public async Task<IActionResult> Get(int id)
        {
            var post = await _context.BlogPosts.FindAsync(id);
            return post == null ? NotFound() : Ok(post);
        }

        [HttpPost]
        public async Task<IActionResult> Create(BlogPost post)
        {
            _context.BlogPosts.Add(post);
            await _context.SaveChangesAsync();
            return CreatedAtAction(nameof(Get), new { id = post.Id }, post);
        }

        [HttpPut("{id}")]
        public async Task<IActionResult> Update(int id, BlogPost post)
        {
            if (id != post.Id) return BadRequest();
            _context.Entry(post).State = EntityState.Modified;
            await _context.SaveChangesAsync();
            return NoContent();
        }

        [HttpDelete("{id}")]
        public async Task<IActionResult> Delete(int id)
        {
            var post = await _context.BlogPosts.FindAsync(id);
            if (post == null) return NotFound();
            _context.BlogPosts.Remove(post);
            await _context.SaveChangesAsync();
            return NoContent();
        }
    }
}

---

Step 6: Apply Migrations and Run

dotnet ef migrations add InitialCreate
dotnet ef database update
dotnet run

Test API endpoints using Postman or Swagger.

---

Conclusion

You now have a fully functional CRUD API using .NET 9 and SQL Server. This structure can be extended to more complex applications, including authentication, pagination, and advanced filtering.

---

Project Structure

BlogApi/
├── Controllers/
│   └── BlogPostsController.cs
├── Data/
│   └── BlogDbContext.cs
├── Models/
│   └── BlogPost.cs
├── Program.cs
├── appsettings.json
├── BlogApi.csproj

---

#DotNet9 #SQLServer #CRUD #EntityFrameworkCore #WebAPI #CSharp #FullStack #DotNetDevelopment

get source code from github