Transactions in C#

Some changes only make sense together: take stock from one product and add it to another, and you need both UPDATEs or neither. A transaction groups statements so they happen all-or-nothing. It starts with BEGIN. COMMIT ends it and saves every change at once; ROLLBACK ends it and undoes every change since BEGIN. Until then, the changes are only seen by your own connection.

For example, a new order and its order line, saved together:

var query = "BEGIN; " +
    "INSERT INTO orders (customer_id, ordered, status) VALUES (2, '2026-06-01', 'paid'); " +
    "INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (8, 1, 2, 12); " +
    "COMMIT; ";

In this lesson the products table has the rule CHECK (stock >= 0): stock can't go below zero. The check runs the statements in query one at a time and prints the lamp's and the plant's stock after each one; at the end it shows what a new connection sees. It must print:

BEGIN: Lamp 1, Plant 6
UPDATE: Lamp 1, Plant 11
UPDATE: Error: CHECK constraint failed: stock >= 0
ROLLBACK: Lamp 1, Plant 6
BEGIN: Lamp 1, Plant 6
UPDATE: Lamp 1, Plant 7
UPDATE: Lamp 0, Plant 7
COMMIT: Lamp 0, Plant 7
What a new connection sees:
name | stock
Lamp | 0
Plant | 7
(2 rows)

Your task: in a transaction, try to move 5 items of stock from the lamp to the plant; when the lamp's step fails, roll it back. Then move 1 item in a new transaction and commit it.

Assignment
Follow the Coding Tutorial to write the statements of query, one per step, each ending with a semicolon.

Hint
Each statement is one string piece ending with a semicolon and a space, followed by +; after "COMMIT; " the C# statement ends with ;. The plant gets stock + 5 and the lamp stock - 5, each picked with a WHERE on its name; the second try uses 1 instead of 5.