Connect the Shop to a Database

A list inside your code forgets everything when the app stops. A database keeps data in a file instead. From now on the shop's products live in the file shop.db, in the products table, and the Microsoft.Data.Sqlite library lets your app talk to it.

You open a connection to the file once, when the app starts: a SqliteConnection, then Open(). To run a query, you make a command with CreateCommand(), put the SQL in its CommandText and run it with ExecuteReader(). The reader walks the rows one at a time: reader.Read() moves to the next row, and reader.GetValue(0) gives the value of its first column. For example, with a table books that has a title and a year:

var cmd = library.CreateCommand();
cmd.CommandText = "SELECT title, year FROM books";
using var reader = cmd.ExecuteReader();
while (reader.Read()) {
    Console.WriteLine($"{reader.GetValue(0)} {reader.GetValue(1)}");
}
// Dune 1965
// Emma 1815

An API answers with objects, not bare values, so each row becomes a Dictionary<string, object> whose keys say what each value is. A cell that is empty in the database (NULL) must become C#'s null: reader.IsDBNull(3) tells you when column 3 is empty. JSON writes it as null.

GET /           →  200  Welcome to the shop!
GET /products   →  200  [{"id": 1, "name": "Mug", "price": 12, "category": "kitchen"},
                         {"id": 2, "name": "Notebook", "price": 7, "category": "office"},
                         … 8 products in all …
                         {"id": 8, "name": "Gift card", "price": 50, "category": null}]

Your task: connect the app to shop.db and make GET /products read the products from the products table, turning each row into a dictionary with the keys id, name, price and category.

Assignment
Follow the Coding Tutorial to open the connection, run the query and turn its rows into the list of products.

Hint
The values of a row come in the same order as the columns of your SELECT, and indexes start at 0: reader.GetValue(0) is the first column you named. For the category, use condition ? a : b with reader.IsDBNull(3).