DEV Community

Cover image for CRUD Operations in .NET 9 with SQL Server: A Practical Guide
Morteza Jangjoo
Morteza Jangjoo

Posted on

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

#dotnetcore #sqlserver #entityframeworkcore #webapi

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
Enter fullscreen mode Exit fullscreen mode

Step 2: Install Entity Framework Core 

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools
Enter fullscreen mode Exit fullscreen mode

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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; }
    }
}
Enter fullscreen mode Exit fullscreen mode

Add DbContext to Program.cs:

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

And in appsettings.json:

"ConnectionStrings": {
  "DefaultConnection": "Server=YOUR_SERVER_NAME;Database=BlogDb;Trusted_Connection=True;"
}
Enter fullscreen mode Exit fullscreen mode

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();
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Step 6: Apply Migrations and Run 

dotnet ef migrations add InitialCreate
dotnet ef database update
dotnet run
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

get source code from github

Top comments (0)