Techie BabajiLearn to Code. Build Real Skills.
ADO.NET · Practice

Complete CRUD Project

Put the whole track together: a small but production-shaped Products API using only ADO.NET.

Advanced 5 min Lesson 31 of 32Examples use Microsoft.Data.SqlClient on .NET 9 with SQL Server 2022.
Before you start:Repository PatternError Handling
0/32 in ADO.NET
On this page · 5 min · Advanced

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Code example
  5. Step by step
  6. Real-world use
  7. Common mistakes
  8. Best practices
  9. Remember this
  10. Practice exercise
  11. Mini quiz
  12. Related topics
  13. My notes
Reading time
5 min
Difficulty
Advanced
Updated
Oct 29, 2026

Related tutorials

Interview questions

In simple words#

The final project assembles every part you learned into one working machine: a Products API that creates, reads, updates and deletes data safely.

CRUD
Create, Read, Update, Delete.
Status codes
201 created, 204 no content, 404, 409.
Layers
Endpoint → repository → SQL Server.

Learning objectives#

  • Structure a project with a repository and DI
  • Expose CRUD endpoints with correct status codes
  • Validate input and handle SQL errors
  • Test it end to end

Explanation#

Project layout: Program.cs (endpoints), Data/SqlConnectionFactory.cs, Data/ProductRepository.cs, Models/Product.cs and database/schema.sql.

Endpoints: GET /api/products?page=1&size=20, GET /api/products/{id}, POST, PUT /api/products/{id} and DELETE /api/products/{id}.

Validation happens before SQL; duplicate names (unique index) become 409 Conflict; missing rows become 404.

Extensions: an order endpoint using the dbo.PlaceOrder procedure inside a transaction, and a CSV import with SqlBulkCopy.

Code example: Program.cs — the endpoints#

C#Program.cs Illustrative
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddSingleton<IDbConnectionFactory, SqlConnectionFactory>();
builder.Services.AddScoped<IProductRepository, ProductRepository>();
var app = builder.Build();


var products = app.MapGroup("/api/products");


products.MapGet("/", (IProductRepository repo, CancellationToken ct) => repo.GetAllAsync(ct));


products.MapGet("/{id:int}", async (int id, IProductRepository repo, CancellationToken ct) =>
    await repo.GetByIdAsync(id, ct) is { } p ? Results.Ok(p) : Results.NotFound());


products.MapPost("/", async (Product p, IProductRepository repo, CancellationToken ct) =>
{
    if (string.IsNullOrWhiteSpace(p.Name) || p.Price <= 0 || p.Stock < 0)
        return Results.ValidationProblem(new Dictionary<string, string[]> { ["product"] = ["Name, a positive price and non-negative stock are required."] });
    try
    {
        int id = await repo.CreateAsync(p, ct);
        return Results.Created(
quot;/api/products/{id}", p with { Id = id });
    }
    catch (SqlException ex) when (ex.Number is 2627 or 2601)
    {
        return Results.Conflict(new { message = 
quot;A product named '{p.Name}' already exists." });
    }
});


products.MapPut("/{id:int}", async (int id, Product p, IProductRepository repo, CancellationToken ct) =>
    await repo.UpdateAsync(p with { Id = id }, ct) ? Results.NoContent() : Results.NotFound());


products.MapDelete("/{id:int}", async (int id, IProductRepository repo, CancellationToken ct) =>
    await repo.DeleteAsync(id, ct) ? Results.NoContent() : Results.NotFound());


app.Run();
Expected output
POST /api/products {"name":"Webcam","price":3200,"stock":15}   → 201 Created, Location: /api/products/4
POST /api/products {"name":"Webcam","price":3200,"stock":15}   → 409 Conflict
GET  /api/products/999                                         → 404 Not Found
DELETE /api/products/4                                         → 204 No Content

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. DI wires the factory and repository from the previous lessons.
  2. GET by id returns 404 when the repository returns null.
  3. POST validates first, then maps a unique-index violation to 409.
  4. PUT and DELETE return 204 or 404 based on rows affected.

Real-world use case#

This project is a strong portfolio piece and the backend for the React and Angular integration lessons on this site.

Common mistakes#

Returning 500 for expected situations

Not found, conflicts and validation errors have their own status codes.

Skipping integration tests

Run the repository against a real SQL Server (for example in Docker) to catch SQL mistakes.

Best practices#

  • Unique index on Products(Name)
  • Repository behind an interface
  • Correct HTTP status codes
  • Integration tests with a disposable database

Remember this#

Factory + repository + endpoints = clean ADO.NET API.

404 not found, 409 conflict, 201 created, 204 no content.

Validate before SQL, map SqlException after.

Practice exercise#

Your task: Add a GET /api/products/search?q= endpoint using the SearchAsync method from the repository lesson.

Show solution
C# Illustrative
products.MapGet("/search", (string q, IProductRepository repo, CancellationToken ct) =>
    repo.SearchAsync(q, ct));

Mini quiz#

Complete CRUD Project quizMultiple choice Question 1 of 1

Which status code should PUT return when the product does not exist?

Score so far: 0/0

Want more? Practise all ADO.NET MCQs · ADO.NET interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.