What an Order Contains in C#

An order can hold several products. They are in a third table, order_items: one row per product in an order, with the order_id it belongs to, the product_id, the quantity and the unit_price, the price of one item when the order was placed. A JOIN with products gives each item its name.

Such a query can find many rows. A while (reader.Read()) loop goes through them one at a time: each Read() moves to the next row, and gives false when none is left. To add up a value over the rows, start a variable at 0 before the loop and add to it inside the loop. Here is the idea on another value, the prices of the kitchen products:

var cmd = db.CreateCommand();
cmd.CommandText = "SELECT price FROM products WHERE category = $category";
cmd.Parameters.AddWithValue("$category", "kitchen");
using var reader = cmd.ExecuteReader();
long total = 0;
while (reader.Read()) {
    total = total + (long)reader["price"];
}

An order's total uses unit_price, the price saved with the order, not the product's price today: when the shop changes a price, the orders already placed keep their totals. Every order has at least one item, so a reader with no rows means there is no such order: then reader.HasRows is false.

Your route must give these answers (the PUT raises the Mug's price to 15 before order 7 is read):

GET /orders/1/items   →  200  {"order_id": 1, "items": [{"name": "Mug", "quantity": 2, "unit_price": 12}, {"name": "Pen", "quantity": 5, "unit_price": 2}], "total": 34}
GET /orders/5/items   →  200  {"order_id": 5, "items": [{"name": "Teapot", "quantity": 2, "unit_price": 22}, {"name": "Lamp", "quantity": 1, "unit_price": 29}], "total": 73}
GET /orders/6/items   →  200  {"order_id": 6, "items": [{"name": "Stapler", "quantity": 1, "unit_price": 9}], "total": 9}
PUT /products/1  {"name": "Mug", "price": 15, "category": "kitchen"}  →  200  {"id": 1, "name": "Mug", "price": 15, "category": "kitchen", "stock": 10}
GET /orders/7/items   →  200  {"order_id": 7, "items": [{"name": "Mug", "quantity": 4, "unit_price": 12}, {"name": "Notebook", "quantity": 2, "unit_price": 7}], "total": 62}
GET /orders/99/items  →  404  {"error": "Order not found"}

Your task: the route GET /orders/{id}/items is started for you, with its cmd and its $id parameter. Make it read the order's items with their product names, add up the total from the saved prices, and answer 404 when no item belongs to that order.

Assignment
Follow the Coding Tutorial to fill in the route /orders/{id}/items: its JOIN query, the 404, and the loop that builds the items and the total.

Hint
Check for no rows with !reader.HasRows, not !reader.Read(): Read() would move past the first row, and the loop would miss it. Start total at 0 before the loop, not inside it.