Exercise_1-Tier_SQLite

Lab Exercise: Building a 1-Tier Architecture with SQLite

Goal

You build a small tool-lending system for the school workshop as a pure 1-Tier application: user interface, business logic and database engine all run in one process on one machine, working on one local file.

Along the way you will measure what the theory claims: no network latency, real ACID transactions — but no multi-user capability.

   +--------------------------------------------------+
   |  ToolLending.exe                                  |
   |  Console UI  ->  Logic  ->  Microsoft.Data.Sqlite |
   +---------------------------|----------------------+
                               v
                        lending.db  (file)

---

Task 0 — Project setup (5 min)

dotnet new console -o ToolLending
cd ToolLending
dotnet add package Microsoft.Data.Sqlite
dotnet run

There is no server to install and no service to start. The DBMS is a NuGet package that is linked into your executable. Note this observation — you will need it in Task 6.

Create a file Database.cs and open a connection:

using Microsoft.Data.Sqlite;
 
public static class Database
{
    public const string ConnectionString = "Data Source=lending.db";
 
    public static SqliteConnection OpenConnection()
    {
        var connection = new SqliteConnection(ConnectionString);
        connection.Open();
 
        // SQLite has foreign key checking DISABLED by default -
        // it must be switched on for every single connection!
        using var pragma = connection.CreateCommand();
        pragma.CommandText = "PRAGMA foreign_keys = ON;";
        pragma.ExecuteNonQuery();
 
        return connection;
    }
}

---

Task 1 — Schema and seed data (10 min)

Write a method Database.Initialize() that creates the schema if it does not exist:

CREATE TABLE IF NOT EXISTS Member (
    MemberId  INTEGER PRIMARY KEY AUTOINCREMENT,
    FirstName TEXT NOT NULL,
    LastName  TEXT NOT NULL,
    Email     TEXT NOT NULL UNIQUE
);
 
CREATE TABLE IF NOT EXISTS Tool (
    ToolId    INTEGER PRIMARY KEY AUTOINCREMENT,
    Name      TEXT    NOT NULL,
    Available INTEGER NOT NULL CHECK (Available >= 0)
);
 
CREATE TABLE IF NOT EXISTS Loan (
    LoanId     INTEGER PRIMARY KEY AUTOINCREMENT,
    MemberId   INTEGER NOT NULL REFERENCES Member(MemberId),
    ToolId     INTEGER NOT NULL REFERENCES Tool(ToolId),
    LoanDate   TEXT    NOT NULL DEFAULT (datetime('now')),
    ReturnDate TEXT    NULL
);

a) Execute this script from C# (ExecuteNonQuery). b) Insert at least 3 members and 4 tools (e.g. Cordless drill / 2, Multimeter / 5, Oscilloscope / 1, Soldering station / 3). Use INSERT OR IGNORE so that a second program start does not create duplicates. c) Open lending.db with DB Browser for SQLite (or the VS Code SQLite extension) and verify the content.

Compare with MySQL — write down 3 differences you notice: SQLite has no VARCHAR(n) length enforcement, no real DATETIME or BOOLEAN type (type affinity: everything is TEXT / INTEGER / REAL / BLOB / NULL), AUTOINCREMENT instead of AUTO_INCREMENT, no users/passwords/GRANT at all, and the whole database is one file.

---

Task 2 — CRUD with parameters (15 min)

Implement these methods. Always use parameters — never string concatenation (SQL injection!).

public static int AddMember(string firstName, string lastName, string email)
{
    using var connection = Database.OpenConnection();
    using var cmd = connection.CreateCommand();
    cmd.CommandText = @"INSERT INTO Member (FirstName, LastName, Email)
                        VALUES ($first, $last, $mail);
                        SELECT last_insert_rowid();";
    cmd.Parameters.AddWithValue("$first", firstName);
    cmd.Parameters.AddWithValue("$last",  lastName);
    cmd.Parameters.AddWithValue("$mail",  email);
    return Convert.ToInt32(cmd.ExecuteScalar());
}

a) ListTools() – print ToolId, Name, Available using a SqliteDataReader. b) ListOpenLoans() – all loans with ReturnDate IS NULL, joined with Member and Tool, so the output shows real names, not IDs. c) ReturnTool(int loanId) – set ReturnDate = datetime('now') and increase Tool.Available by 1. d) Build a small console menu (1 = list tools, 2 = borrow, 3 = return, 4 = open loans, 0 = exit).

Reader pattern:

using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine($"{reader.GetInt32(0),4}  {reader.GetString(1),-20} {reader.GetInt32(2)}");
}

---

Task 3 — Transactions: ACID in a 1-Tier system (10 min)

“Borrowing a tool” consists of two statements that must both succeed or both fail:

1. INSERT INTO Loan ...
2. UPDATE Tool SET Available = Available - 1 WHERE ToolId = ...

a) Implement BorrowTool(int memberId, int toolId) using an explicit transaction:

using var connection = Database.OpenConnection();
using var transaction = connection.BeginTransaction();
try
{
    // ... two commands, each with cmd.Transaction = transaction;
    transaction.Commit();
}
catch (SqliteException ex)
{
    transaction.Rollback();
    Console.WriteLine($"Borrowing failed: {ex.Message}");
}

b) Provoke the error: borrow the Oscilloscope (Available = 1) twice. The second attempt violates CHECK (Available >= 0). c) Verify in DB Browser that no orphan Loan row was written. Which letter of ACID did you just demonstrate? d) Now comment out the transaction.Commit() line, run it again and check the database. Which letter of ACID does that demonstrate?

---

Task 4 — Measuring latency (5 min)

Insert 1000 dummy loans, once without and once with a surrounding transaction:

var sw = System.Diagnostics.Stopwatch.StartNew();
for (int i = 0; i < 1000; i++) { /* single INSERT, auto-commit */ }
Console.WriteLine($"without transaction: {sw.ElapsedMilliseconds} ms");

a) Note both times. The difference is typically a factor of 50–1000. b) Explain it: without an explicit transaction every single INSERT is its own transaction and must be flushed to disk (fsync) to keep the D in ACID. c) Compare the per-statement time with the numbers from the scriptum (§4.3): LAN ≈ 0.2–1 ms, internet ≈ 20–200 ms per round trip. What does this tell you about the response latency of a 1-Tier architecture? Delete the dummy rows afterwards.

---

Task 5 — Hitting the wall: concurrent access (5 min)

a) Add a command to your menu that opens a transaction, writes one UPDATE, prints "Transaction open - press ENTER to commit" and waits for Console.ReadLine(). b) Open two terminals, run the program in both. In terminal A start that command and do not press ENTER. In terminal B try to borrow a tool. c) Record the exact error message (expect: SQLite Error 5: 'database is locked'). d) Now try PRAGMA journal_mode = WAL; once on the database and repeat the test with a reading operation in terminal B. What changed, what did not?

Conclusion: SQLite serializes writers with a file lock. It is excellent for one application, but it is not a multi-user server. This is the hard limit of 1-Tier — and the reason 2-Tier exists.

---

Task 6 — Reflection (write down, ~5 min or homework)

1. Draw your finished system as a tier diagram. Label UI, logic, data access and storage. How many layers does your code have? How many tiers does it have?
2. The workshop now has 5 people who all want to lend tools at the same time. List three concrete problems with your current solution.
3. Describe what exactly has to change to turn your program into a 2-Tier system — and which parts of your code you could keep unchanged. (Hint: think about the data access layer and the connection string.)
4. Now describe the step to 3-Tier with Blazor + Web API. Which two advantages do you gain that 2-Tier cannot give you?
5. Fill in the table for your application, with a one-sentence justification each:

Metric	Rating of your 1-Tier app	Why
Data consistency
Scalability
Data latency

---

Extension tasks (for the fast ones)

• E1: Replace the raw ADO.NET code with EF Core (Microsoft.EntityFrameworkCore.Sqlite) and a DbContext. Keep both implementations in your solution and use an Interface to be able to switch your implementation. Which layer did you just change — and does the architecture change?
• E2: Add an index on Loan(ReturnDate), fill the table with 100,000 rows and compare ListOpenLoans() with and without the index (EXPLAIN QUERY PLAN).
• E3: Implement Backup() — in a 1-Tier system, what does “backup” actually mean?
• E4: Write a second program that opens the same lending.db on a network share. Document what happens (and why you should never do this in production).