One Product from the Database in C#

To read one product, a client puts its id in the path: /products/3. A route written as /products/{id} catches that piece of the path, and the parameter int id of your function turns it into a whole number.

This time the database does the searching, with a WHERE condition. You never paste the value into the SQL text: you write a placeholder, like $year, and give it its value separately with cmd.Parameters.AddWithValue. An id matches at most one row, so you call reader.Read() once: it gives true and moves onto the row, or false when the query found nothing. For example, with a table books:

var cmd = library.CreateCommand();
cmd.CommandText = "SELECT * FROM books WHERE year = $year";
cmd.Parameters.AddWithValue("$year", 1965);
using var reader = cmd.ExecuteReader();
if (!reader.Read()) {
    Console.WriteLine("No book from 1965");
}

When no row matches, the client asked for something that doesn't exist, so the answer is 404 with an error message. Results.Json takes the status as statusCode: after the body.

GET /products/3   →  200  {"id": 3, "name": "Lamp", "price": 29, "category": "home", "stock": 1}
GET /products/8   →  200  {"id": 8, "name": "Gift card", "price": 50, "category": null, "stock": 999}
GET /products/99  →  404  {"error": "Product not found"}
GET /products/0   →  404  {"error": "Product not found"}

Your task: add GET /products/{id}, which asks the database for that product with a placeholder and answers it as JSON, or {"error": "Product not found"} with the status 404.

Assignment
Follow the Coding Tutorial to add the route and its function.

Hint
reader.Read() gives false when the query finds nothing. Check for it before you turn the row into a dictionary with RowToDict(reader), and make sure every path of the function returns a result.