Saving Library's state to a JSON file, as in the previous lesson, works well while the data is small and only one process reads and writes it at a time. But a relational database solves problems a file doesn't handle well: efficient queries over millions of rows, concurrent access from several processes without corrupting the data, and integrity guaranteed by the engine itself. This lesson introduces ADO.NET, .NET's classic set of classes for talking directly to a database with SQL, using SQLite as the example engine. You'll see how to open a connection, create a table, insert data safely against SQL injection, and read it back — and why, after seeing how much work it takes to do it all by hand, the next lesson introduces Entity Framework to automate it.

1. What ADO.NET is and when it's used directly
2. SQLite as an example database and the connection string
3. SqliteConnection: opening and closing the connection with using
4. Creating the Books table with ExecuteNonQuery
5. Inserting data safely with SqliteParameter
6. Reading data with ExecuteReader
7. BiblioTech's catalog in SQLite, from start to finish

ADO.NET is the set of .NET classes, available since its earliest versions, for communicating with a relational database by writing SQL directly. Its main pieces are common across any engine (SQL Server, PostgreSQL, SQLite, MySQL...), though each one supplies its own implementation through a provider:

Writing SQL by hand with ADO.NET gives you total control over the exact query being run, and it's the foundation on which higher-level tools, like Entity Framework (next lesson), are built. Day to day, most applications use an ORM and rarely touch ADO.NET directly; even so, understanding this layer helps you understand what an ORM does "underneath," and it remains the right choice when you need maximum control over the exact SQL that gets executed (for example, a query heavily optimized for a specific case).

This lesson uses SQLite, a relational database engine that stores the entire database in a single file, with no need to install or manage a separate server — ideal for learning and for small applications. The required NuGet package is Microsoft.Data.Sqlite:

Every connection starts with a connection string, a text describing where and how to connect. For SQLite, it's enough to indicate the file path:

If bibliotech.db doesn't exist yet, SQLite creates it automatically on the first connection attempt; no extra installation step is needed beyond the NuGet package.

SqliteConnection implements IDisposable (recall the File I/O lesson): opening a connection reserves an external resource (a channel to the database file) that must always be closed, even if something fails halfway through. That's why, just like with StreamReader/StreamWriter, it's always declared with using:

connection.Open() is an explicit call: creating the SqliteConnection object doesn't open the connection by itself, it only prepares it; until Open() is called, there's no channel actually established to the database.

Any SQL statement that doesn't return rows (CREATE TABLE, INSERT, UPDATE, DELETE) is executed with SqliteCommand.ExecuteNonQuery():

connection.CreateCommand() creates a SqliteCommand already associated with that connection; CommandText is the SQL to execute (here, with a C# 11 multi-line literal string, """...""", convenient for long statements). IF NOT EXISTS avoids an error if the program runs several times against the same table from an earlier run. SQLite has no native BOOLEAN type; by convention, Available is stored as INTEGER (0/1), and the Microsoft.Data.Sqlite provider itself takes care of converting between C#'s bool and SQLite's 0/1 automatically.

The most dangerous mistake when building SQL by hand is concatenating program values directly into the SQL text: it opens the door to SQL injection, a serious vulnerability.

AddWithValue associates each placeholder ($isbn, $title...) with a concrete C# value; the provider takes care of escaping it and sending it separately from the SQL text, so the value's content can never alter the statement's structure. INSERT OR REPLACE is a SQLite extension that inserts the row if the Isbn (primary key) doesn't exist, or overwrites it if it already did — useful so you don't have to distinguish "insert" from "update" in this example. Rule with no exceptions: any value coming from the program (user input, data from another system...) must always travel as a parameter, never concatenated directly into the SQL text.

Statements that do return rows (SELECT) are executed with ExecuteReader(), which returns a SqliteDataReader: a read-only, forward-only cursor over the result, walked with Read():

reader.Read() advances to the next record and returns false when there are no more left, exactly the same while loop pattern as StreamReader.ReadLine() in the previous lesson. GetString(index), GetBoolean(index), GetInt32(index)... access each column by its position (starting at 0) in the order given in the SELECT; alternatives like GetOrdinal("Title") let you get that position from the column name instead, more resilient if the SELECT's order changes over time.

Putting everything above together, here's how BiblioTech's complete book catalog gets saved and recovered (for simplicity, this lesson focuses on Book; a separate Magazines table would follow exactly the same pattern):

Compare the size of this code with the Serialization lesson's: here you had to write, by hand, the table-creation SQL, the insert SQL with its parameters one by one, the select SQL, and the manual column-by-column mapping to Book. It works, and it gives total control over the exact SQL, but it's repetitive work that would grow substantially once you add Members and Loans (with their relationships between tables). That repetitive work is exactly what an ORM (Object-Relational Mapper) solves — the topic of the next lesson: Entity Framework.

• Concatenating values directly into SQL ($"... WHERE Isbn = '{isbn}'"): opens the door to SQL injection. Always use SqliteParameter (via AddWithValue or Add) for any value that isn't a fixed part of the statement.
• Not closing the connection: without using, a connection left open and never closed will, over time, exhaust the number of connections available to the database. Always declare SqliteConnection, SqliteCommand, and SqliteDataReader with using.
• Accessing a column at the wrong position in GetString/GetInt32/...: if the SELECT's column order doesn't match the indexes used when reading the SqliteDataReader, you get data from the wrong column without the compiler catching it. GetOrdinal("ColumnName") is more robust than relying on fixed indexes if the SELECT might change.
• Forgetting IF NOT EXISTS in CREATE TABLE: without it, running the program a second time against the same database file would throw an error because the table would already exist.
• Tip: for any real application beyond a learning example, an ORM like Entity Framework (next lesson) drastically reduces this repetitive code and avoids manual mapping errors; reserve direct ADO.NET for very specific queries that need maximum control over the exact SQL executed.

1. Write the code to create a Members table in SQLite with columns Id (INTEGER PRIMARY KEY) and Name (TEXT NOT NULL), using CREATE TABLE IF NOT EXISTS and ExecuteNonQuery.

2. Write the code to insert a Member into the Members table from the previous exercise, using SqliteParameter (with AddWithValue) for both values, never direct concatenation.

3. Write a function that reads every record from the Members table with ExecuteReader, rebuilds a Member from each row, and returns a List<Member> with all of them.

using SqliteCommand command = connection.CreateCommand();
command.CommandText =
    """
    CREATE TABLE IF NOT EXISTS Members (
        Id INTEGER PRIMARY KEY,
        Name TEXT NOT NULL
    )
    """;
command.ExecuteNonQuery();

Member member = new Member(1, "Ana Martinez");

using SqliteCommand command = connection.CreateCommand();
command.CommandText = "INSERT OR REPLACE INTO Members (Id, Name) VALUES ($id, $name)";
command.Parameters.AddWithValue("$id", member.Id);
command.Parameters.AddWithValue("$name", member.Name);
command.ExecuteNonQuery();

List<Member> LoadMembersFromSqlite(SqliteConnection connection)
{
    List<Member> members = new List<Member>();

    using SqliteCommand command = connection.CreateCommand();
    command.CommandText = "SELECT Id, Name FROM Members";

    using SqliteDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        int id = reader.GetInt32(0);
        string name = reader.GetString(1);
        members.Add(new Member(id, name));
    }

    return members;
}

In this lesson you've used classic ADO.NET to talk directly to a SQLite database: opening a connection with SqliteConnection, executing statements with no result via ExecuteNonQuery, inserting data safely against SQL injection with SqliteParameter, and reading results with ExecuteReader. BiblioTech's book catalog can now live in a real relational database, not just a text or JSON file.

You've also seen firsthand how repetitive this approach is: SQL written by hand for every operation, parameters added one at a time, and manual column-by-column mapping to each object property. The next lesson, Entity Framework, introduces an ORM that automates almost all of this work: it maps BiblioTech's domain classes to tables, generates the SQL for you, and lets you query with LINQ — the same tool you already know from Module 4 — instead of hand-written SQL.