Filters in SQL

Searching by name was one optional filter. A shop needs more: ?max_price=10, ?category=office, or several at once, joined with &. A request can send any mix of them, so no single fixed query fits: you build the query piece by piece.

Keep the pieces of the WHERE in a list conditions, each with a placeholder like $max_price, and give each placeholder its value in the command with cmd.Parameters.AddWithValue. The visitor's text only ever goes into the parameters, so the query stays safe. At the end, string.Join(" AND ", ...) glues the pieces with AND between them:

var pieces = new List<string> { "year >= $year", "author = $author" };
Console.WriteLine(string.Join(" AND ", pieces));   // year >= $year AND author = $author
Console.WriteLine(string.Join(" AND ", new List<string>()));   // an empty string

The route reads max_price as an int?, a number that is null when the URL doesn't send it. Test every filter with != null: a maximum price of 0 is a real filter, not a missing one.

GET /products?max_price=10                  →  200  [{"id": 2, "name": "Notebook", …}, {"id": 4, "name": "Pen", …}, {"id": 7, "name": "Stapler", …}]
GET /products?category=home                 →  200  [{"id": 3, "name": "Lamp", …}, {"id": 5, "name": "Plant", …}]
GET /products?category=office&max_price=8   →  200  [{"id": 2, "name": "Notebook", …}, {"id": 4, "name": "Pen", …}]
GET /products?name=Teapot&category=kitchen  →  200  [{"id": 6, "name": "Teapot", "price": 22, "category": "kitchen", "stock": 4}]
GET /products?name=Lamp&max_price=20        →  200  []
GET /products?max_price=0                   →  200  []
GET /products?category=x' OR '1'='1         →  200  []
GET /products                               →  200  [… all 8 products …]

Your task: next to name, let GET /products use the optional filters max_price (products costing at most that much) and category. Build the WHERE from the filters the request really sends, with a placeholder for each value.

Assignment
Follow the Coding Tutorial to check each filter, collect the conditions and their values, and run the query you built.

Hint
Add a condition and its value in the same if, so every placeholder in the SQL gets its value. Add " WHERE " only when conditions.Count is more than 0.