Insert Many at Once in C#

A supplier sends the shop a list of new products. One POST /products per product costs one request, one query and one commit each, and the commit is the slow part: it waits until the disk has really stored the data. A batch sends the whole list in one request, and the shop saves it with one commit.

RunMany and a transaction: RunMany(sql, rows) runs one SQL statement once for each row of a list, filling the $ marks from that row's dictionary, and counts it all as one query. A transaction around it makes all the inserts wait together, and one Commit() saves them. Here it adds two lines to order 4 at once, 2 mugs and 1 lamp:

var items = new List<Dictionary<string, object>> {
    new() { ["$order"] = 4, ["$product"] = 1, ["$quantity"] = 2 },
    new() { ["$order"] = 4, ["$product"] = 3, ["$quantity"] = 1 },
};
using var transaction = db.BeginTransaction();
RunMany("INSERT INTO order_items (order_id, product_id, quantity) VALUES ($order, $product, $quantity)", items);
transaction.Commit();

All or nothing: if one row is refused, like a product with no price in a NOT NULL column, SQLite throws a SqliteException when it reaches that row. The rows before it are inserted but not saved yet: they wait in the open transaction, and transaction.Rollback() throws them all away. The client can fix its list and send it again without making doubles.

GET /products  →  200  [{"id": 1, "name": "Mug", "price": 12}, …, {"id": 8, "name": "Gift card", "price": 50}]
POST /products/batch  [{"name": "Cup", "price": 8}, {"name": "Bowl", "price": 11}, {"name": "Vase", "price": 18}]
    →  201  {"added": 3}
GET /debug/queries  →  200  {"queries": 2}   (1 for the first GET /products, 1 for the whole batch)
GET /products  →  200  [… the 8 products …, {"id": 9, "name": "Cup", "price": 8}, {"id": 10, "name": "Bowl", …}, {"id": 11, "name": "Vase", …}]
POST /products/batch  [{"name": "Tray", "price": 6}, {"name": "Clock"}]
    →  400  {"error": "Each product needs a name and a price"}
GET /products/12  →  404  {"error": "Product not found"}

Your task: RunMany is written for you except the line that runs each row's command: add it. Then finish POST /products/batch, whose rows are already built from the JSON list: open a transaction, insert all the rows with one RunMany call inside a try, roll back and answer 400 on a SqliteException, and otherwise commit once and clear the cache. The route already answers {"added": …} with 201.

Assignment
Follow the Coding Tutorial to finish RunMany and the POST /products/batch route.

Hint
A missing price is already null in its row, and SQLite refuses it for you. Put transaction.Commit() after the whole try/catch, so it runs only when every row went in.