A Small Query Helper in C#

Look at your routes. Almost every one runs cmd.ExecuteReader(), starts a list, reads each row with while (reader.Read()), and turns it into a dictionary with RowToDict(reader). Every route types the same moves again. A helper is a small function that makes those moves once, so each route gets its rows in one line.

A helper that takes a ready command: the route still makes its command, with its SQL and its $ values, and hands the whole command to the helper. For example, here is a helper for SQL that answers one number:

long Count(SqliteCommand cmd) {
    return (long)cmd.ExecuteScalar();
}

var cmd = db.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM orders WHERE status = $status";
cmd.Parameters.AddWithValue("$status", "new");
var newOrders = Count(cmd);

Your helpers: Query(cmd) runs the command and answers a List<Dictionary<string, object>>, one dictionary per row. QueryOne(cmd) answers only the first dictionary, or null when no row matched. In this lesson the check also counts the statements each request runs inside your Query (/check/calls), and it calls both helpers itself (/check/helper):

GET /products?category=kitchen  →  200  {"products": [{"id": 1, "name": "Mug", …}, {"id": 6, "name": "Teapot", …}], "total": 2, "next_page": null}
GET /check/calls                →  200  {"query_calls": 1}
GET /products?name=Sofa         →  200  {"products": [], "total": 0, "next_page": null}
GET /products/3                 →  200  {"id": 3, "name": "Lamp", "price": 29, "category": "home", "stock": 1, "discount": 0}
GET /check/calls                →  200  {"query_calls": 1}
GET /products/99                →  404  {"error": "Product not found"}
GET /check/helper               →  200  {"many": [{"id": 2, "name": "Notebook"}, {"id": 4, "name": "Pen"}, {"id": 7, "name": "Stapler"}], "empty": [], "one": {"name": "Eva Rossi", "city": "Rome"}, "missing": null, "no_params": {"customers": 5}}

Your task: below Migrate();, write the helpers Query and QueryOne. Query runs the command and answers a list of dictionaries. QueryOne answers the first dictionary, or null. Then make GET /products get its page from Query and GET /products/{id} get its row from QueryOne, in one line each.

Assignment
Follow the Coding Tutorial to write the Query and QueryOne helpers and use them in the two product routes.

Hint
Always build the full list of dictionaries first, in Query. QueryOne calls Query and takes out the first one. Remember that result[0] on an empty list throws an exception, so return null in that case.