Databases

In short: A database keeps data in tables and finds it fast with an SQL query; in C# you talk to it through ADO.NET, and parameters protect against SQL injection.

Problem

A catalog in a file

The store's catalog is a CSV file, as in the files module. While there are a hundred products, it works. Then there are a hundred thousand, and problems show up:

• to find one product, you read the whole file;
• "everything under €5, sorted by price" is a loop, an if and a sort, written by hand;
• two cash registers write to the file at the same time, and one record gets lost or the file gets damaged;
• the power goes out in the middle of a write, and half the file is gone.

That's why the data of almost every real program lives in a database: a program whose job is to keep data, find it fast and never lose it.

The idea

Tables and SQL

A relational database keeps data in tables. A table has columns (name, type) and rows (one for each product). The id column is the primary key: its value is different for each row, so it finds exactly one row.

You talk to the database in SQL. The five most common commands:

SQL

CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    price DECIMAL(8, 2)
);

INSERT INTO products VALUES (1, 'Coffee', 7.50);
SELECT name, price FROM products WHERE price < 5 ORDER BY price;
UPDATE products SET price = 6.90 WHERE id = 1;
DELETE FROM products WHERE id = 3;

• CREATE TABLE makes a table, INSERT adds a row, UPDATE changes rows and DELETE removes them.
• SELECT reads: which columns, from which table, under what condition (WHERE) and in what order (ORDER BY).

You say what you want, and the database decides how to find it fast.

More precisely, if you are curious

• Well-known relational databases are PostgreSQL, MySQL, Oracle and SQL Server. They run as separate servers. Here we use SQLite: a small database that lives in a file or entirely in the program's memory. In .NET it comes with Microsoft's Microsoft.Data.Sqlite package (dotnet add package Microsoft.Data.Sqlite). The address Data Source=:memory: is a database in memory: it lives while the connection is open, and every run starts with an empty one.
• Text in SQL goes in single quotes: 'Coffee'. In PostgreSQL, MySQL and SQL Server, DECIMAL(8, 2) is an exact number with 2 digits after the decimal point, which suits money. SQLite doesn't keep exact decimal numbers: there, 7.50 is an ordinary floating-point number.

See it

ADO.NET: connection, command, result

ADO.NET is how .NET talks to relational databases. Each database has its own package with the same parts: the connection is a SqliteConnection, the query is a SqliteCommand, and you read the result with a SqliteDataReader (for SQL Server they're SqlConnection, SqlCommand and SqlDataReader). The Setup method makes the table and adds three products:

static void Setup(SqliteConnection conn)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(8, 2))";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "INSERT INTO products VALUES (1, 'Coffee', 7.50), (2, 'Tea', 3.20), (3, 'Chocolate', 2.40)";
    cmd.ExecuteNonQuery();
}

We read the rows and turn each one into an object of record Product(int Id, string Name, double Price):

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
Setup(conn);
var products = new List<Product>();
using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT id, name, price FROM products ORDER BY price";
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    products.Add(new Product(reader.GetInt32(0), reader.GetString(1), reader.GetDouble(2)));
}
foreach (var p in products)
{
    Console.WriteLine(p);
}

Output

Product { Id = 3, Name = Chocolate, Price = 2.4 }
Product { Id = 2, Name = Tea, Price = 3.2 }
Product { Id = 1, Name = Coffee, Price = 7.5 }

• new SqliteConnection(address) makes the connection, and Open() opens it. The address (the connection string) says which database: here, SQLite in memory, or a file like Data Source=shop.db.
• conn.CreateCommand() makes a command, and CommandText is its SQL. ExecuteNonQuery() is for commands that change things (CREATE, INSERT, UPDATE, DELETE), and it returns how many rows changed. ExecuteReader() is for SELECT.
• reader.Read() moves to the next row and returns false when the rows run out. GetInt32, GetString and GetDouble get a column by number: from 0, in the order of the SELECT. By name, it's reader.GetOrdinal("name").
• The connection, the command and the reader have to be closed: using var closes them (it calls Dispose()) when the block ends.
• Errors are SqliteException exceptions. C# has no checked exceptions, so the methods don't write throws.

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
Setup(conn);
var products = new List<Product>();
using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT id, name, price FROM products ORDER BY price";
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    products.Add(new Product(reader.GetInt32(0), reader.GetString(1), reader.GetDouble(2)));
}
foreach (var p in products)
{
    Console.WriteLine(p);
}

Step by step, in words

1. A connection to the database in memory. Setup makes the products table and adds 3 rows.
2. An empty list that the objects will go into.
3. SELECT with ORDER BY price: ExecuteReader returns a reader over the rows, cheapest first.
4. reader.Read() moves to row 1. GetInt32, GetString and GetDouble get the columns by number, and new Product makes an object from them: Chocolate.
5. reader.Read() moves to row 2. GetInt32, GetString and GetDouble get the columns by number, and new Product makes an object from them: Tea.
6. reader.Read() moves to row 3. GetInt32, GetString and GetDouble get the columns by number, and new Product makes an object from them: Coffee.
7. reader.Read() returns false: the rows ran out and the loop stops.
8. The list holds ordinary C# objects. Printing shows the record with its fields.

More precisely, if you are curious

• With a real database on a server, the address is different and there's a user and a password. You also need the database's driver as a dependency in the project (for MySQL: the MySqlConnector package, with MySqlConnection instead of SqliteConnection):

var address = "Server=localhost;Port=3306;Database=shop;User ID=shop_app;Password=secret";
using var conn = new MySqlConnection(address);
conn.Open();
Console.WriteLine("Connected to the database!");

• The password isn't written in the code. It's read from the settings or from an environment variable (appsettings.json or Environment.GetEnvironmentVariable).

• The prices here are double to keep it short. In a real store, money is a decimal: a DECIMAL column in PostgreSQL or SQL Server is read with reader.GetDecimal(2). SQLite doesn't keep exact decimal numbers, so there money is kept as whole cents.

Try it

What will it print?

The products are Coffee €7.50, Tea €3.20 and Chocolate €2.40. What will the program print?

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
Setup(conn);
using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT name FROM products WHERE price < 5 ORDER BY price DESC";
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader.GetString(0));
}

Answer

Chocolate
Tea

Tea
Chocolate

Coffee
Tea
Chocolate

Check

Problem

The search that shows everything

The website searches for a product by the name the user types. The query is put together with $"…{name}…":

static void Search(SqliteConnection conn, string name)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = $"SELECT name, price FROM products WHERE name = '{name}'";
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine($"  {reader.GetString(0)}: €{reader.GetDouble(1)}");
    }
}

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
Setup(conn);
Console.WriteLine("Searching: Tea");
Search(conn, "Tea");
Console.WriteLine("Searching: x' OR '1'='1");
Search(conn, "x' OR '1'='1");

Output

Searching: Tea
  Tea: €3.2
Searching: x' OR '1'='1
  Coffee: €7.5
  Tea: €3.2
  Chocolate: €2.4

With "Tea" it works. With x' OR '1'='1 the query becomes ... WHERE name = 'x' OR '1'='1'. The condition is always true, and the whole table comes out. The user's input has become part of the SQL. This is SQL injection, one of the most common attacks on websites. With the same trick, someone can log in without a password or delete a table.

The idea

Parameters: $name

The solution is to send the query and the values separately. The query has a named parameter, $name, in place of the value, and you pass the value with cmd.Parameters.AddWithValue("$name", name):

static void Search(SqliteConnection conn, string name)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT name, price FROM products WHERE name = $name";
    cmd.Parameters.AddWithValue("$name", name);
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine($"  {reader.GetString(0)}: €{reader.GetDouble(1)}");
    }
}

Output

Searching: Tea
  Tea: €3.2
Searching: x' OR '1'='1

Now x' OR '1'='1 is just text that is searched for as a name. There's no such product, and nothing comes out. Whatever the user types, it can't become SQL.

The rule is simple: a value from the user, from a file or from the network is never glued into SQL. Always use a parameter. Microsoft.Data.Sqlite accepts the names $name, @name and :name. In SQL Server and in most .NET examples, you'll see @name.

Try it

Coffee with an apostrophe

The method renames a product. With the name Coffee 'Arabica' the program stops with SqliteException: SQLite Error 1: 'near "Arabica": syntax error'. Which line has the bug?

Pick the line with the bug.

1static void Rename(SqliteConnection conn, int id, string newName)2{3 using var cmd = conn.CreateCommand();4 cmd.CommandText = $"UPDATE products SET name = '{newName}' WHERE id = {id}";5 cmd.ExecuteNonQuery();6}

Check

The idea

Transaction: all or nothing

Ana transfers €150 to Boris. That's two queries: add to Boris, subtract from Ana. The table has the condition CHECK (balance >= 0), so the second query fails: Ana has only €100.

static void OpenAccounts(SqliteConnection conn)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(50), balance INT CONSTRAINT no_debt CHECK (balance >= 0))";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "INSERT INTO accounts VALUES (1, 'Ana', 100), (2, 'Boris', 50)";
    cmd.ExecuteNonQuery();
}

static void Move(SqliteConnection conn, int id, int amount)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "UPDATE accounts SET balance = balance + $amount WHERE id = $id";
    cmd.Parameters.AddWithValue("$amount", amount);
    cmd.Parameters.AddWithValue("$id", id);
    cmd.ExecuteNonQuery();
}

static void PrintBalances(SqliteConnection conn)
{
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT owner, balance FROM accounts ORDER BY id";
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine($"  {reader.GetString(0)}: €{reader.GetInt32(1)}");
    }
}

static void Transfer(SqliteConnection conn, int from, int to, int amount)
{
    Move(conn, to, amount);
    Move(conn, from, -amount);
}

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
OpenAccounts(conn);
try
{
    Transfer(conn, 1, 2, 150);
}
catch (SqliteException)
{
    Console.WriteLine("The transfer of €150 was refused.");
}
PrintBalances(conn);

Output

The transfer of €150 was refused.
  Ana: €100
  Boris: €200

The transfer was refused, but Boris has already received €150 out of nothing. By default, every query is saved right away. The two queries must be a transaction: either both go through, or neither does.

static void Transfer(SqliteConnection conn, int from, int to, int amount)
{
    using var tx = conn.BeginTransaction();
    try
    {
        Move(conn, to, amount);
        Move(conn, from, -amount);
        tx.Commit();
    }
    catch (SqliteException)
    {
        tx.Rollback();
        throw;
    }
}

Output

The transfer of €150 was refused.
  Ana: €100
  Boris: €50

• conn.BeginTransaction() starts a transaction: the changes aren't final yet. The commands you make after that with CreateCommand() are in it.
• tx.Commit() saves them all at once.
• tx.Rollback() undoes them all if something fails. throw; sends the same exception on up, so the caller finds out too.
• using var tx is a safety net: if the transaction reaches the end of the block without Commit(), Dispose() undoes it.

More precisely, if you are curious

• The exception for the failed query is a SqliteException with the message SQLite Error 19: 'CHECK constraint failed: no_debt'. 19 is SQLite's code for a broken rule (e.SqliteErrorCode). no_debt is the name of the condition: in the table it's balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Databases promise four things about transactions, known as ACID: all or nothing (atomicity), the data stays correct according to the table rules (consistency), transactions running at the same time don't get in each other's way (isolation), and what's saved isn't lost even if the power goes out (durability).

The idea

ORM: objects instead of rows

With ADO.NET, for each class you write SQL and turn the rows into objects by hand. An ORM (object-relational mapping) does this for you: you describe the class, and the library makes the table, the queries and the objects by itself. In .NET this is Microsoft's Entity Framework Core (EF Core), for SQLite with the Microsoft.EntityFrameworkCore.Sqlite package. Another well-known one is Dapper: it's lighter, you write the SQL yourself, and it makes the objects.

Models.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

[Table("products")]
class Product
{
    public int Id { get; set; }

    [Required, MaxLength(50)]
    public string Name { get; set; }

    public double Price { get; set; }
}

class ShopContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlite("Data Source=:memory:");
    }
}

• [Table("products")] gives the name of the table. The things in [...] are attributes: notes on the class and its properties that EF Core reads. You'll see them again in the web module.
• The Id property is the primary key by convention (the name is Id or ProductId), and the database gives it a value.
• [Required] is NOT NULL, and [MaxLength(50)] gives the length. Properties without an attribute become columns too.
• The DbContext is the connection to the database. Each DbSet<Product> is a table, and OnConfiguring says which database it is: here, SQLite in memory.

Saving and reading go through the context:

using var db = new ShopContext();
db.Database.OpenConnection();
db.Database.EnsureCreated();

db.Products.Add(new Product { Name = "Coffee", Price = 7.50 });
db.Products.Add(new Product { Name = "Tea", Price = 3.20 });
db.SaveChanges();

var cheap = db.Products
    .Where(p => p.Price < 5)
    .OrderBy(p => p.Price)
    .ToList();
foreach (var p in cheap)
{
    Console.WriteLine($"{p.Name}: €{p.Price}");
}

Output

Tea: €3.2

EnsureCreated() makes the tables. Add adds an object, and SaveChanges() saves all added and changed objects at once, in one transaction. The query is LINQ, as with lists, but EF Core doesn't go through objects in memory: it turns Where(p => p.Price < 5) into SQL, which talks about the class and its Price property, and sends it to the database. ToList() runs the query and returns ready objects.

The database in memory lives while the connection is open, and EF Core otherwise opens and closes it for each query. That's why OpenConnection() keeps it open. With a file (Data Source=shop.db) you don't need this line.

Here on the site, this code runs: EF Core 10 with SQLite is in the checker. The output comes from it.

More precisely, if you are curious

• A LINQ query is code, so the conditions are easy to put together from the user's choices. A method that searches by name:

static List<Product> Named(ShopContext db, string name)
{
    return db.Products.Where(p => p.Name == name).ToList();
}

• When you need exactly your own SQL, FromSql runs it and returns objects. The value in {max} isn't glued in: FromSql sends it as a parameter, so there's no injection here. FromSqlRaw is different: it takes ready text, and if you build it with + or $"…", the value is glued in again.

static List<Product> Cheaper(ShopContext db, double max)
{
    return db.Products.FromSql($"SELECT * FROM products WHERE price < {max}").ToList();
}

• A transaction that is undone on an error, when one change is several SaveChanges calls:

using var tx = db.Database.BeginTransaction();
try
{
    db.Products.Add(new Product { Name = "Chocolate", Price = 2.40 });
    db.SaveChanges();
    // more changes to the objects and SaveChanges
    tx.Commit();
}
catch
{
    tx.Rollback();
    throw;
}

• ToQueryString() shows the SQL of the query without running it. That way you can see it's still SQL underneath, and max is a parameter:

var max = 5.0;
var query = db.Products.Where(p => p.Price < max).OrderBy(p => p.Price);
Console.WriteLine(query.ToQueryString());

Output

.param set @max 5.0

SELECT "p"."Id", "p"."Name", "p"."Price"
FROM "products" AS "p"
WHERE "p"."Price" < @max
ORDER BY "p"."Price"

• In older .NET Framework projects you'll see Entity Framework 6 (System.Data.Entity). EF Core is the new one, for .NET 5 and later, and the ideas are the same.

Exercise

A store with a database

Solve the tasks in your editor, with the Microsoft.Data.Sqlite package in the project and the database Data Source=:memory:.1. Catalog. Make a products table and the methods Add, SetPrice, Remove and List, all with parameters ($id, $name, $price) except List. Add Coffee (€7.50), Tea (€3.20) and Chocolate (€2.40), print them, change the price of the coffee to €6.90, delete the chocolate and print again:

Output

1. Coffee: €7.5
2. Tea: €3.2
3. Chocolate: €2.4
---
1. Coffee: €6.9
2. Tea: €3.2

2. Search by part of the name. The products are Coffee, Cocoa, Tea and Éclair. A method Find(conn, text) finds all products whose name contains the text, ignoring upper and lower case. LOWER in SQLite changes only the letters A to Z, so É stays as it is. Give the database a function of your own: conn.CreateFunction("lower_text", (string text) => text.ToLowerInvariant()), then WHERE lower_text(name) LIKE $text with the value "%" + text.ToLowerInvariant() + "%". Try "CO", "éclair" and an injection attempt:

Output

Searching for "CO":
  Cocoa: €4.8
  Coffee: €7.5
Searching for "éclair":
  Éclair: €2.4
Searching for "' OR '1'='1":
  no products found

3. A transfer that fails halfway. The accounts are Ana (€100) and Boris (€50). Transfer(conn, from, to, amount) first subtracts from from, then adds to to. If ExecuteNonQuery() returns 0 (no such account), throw InvalidOperationException("No account " + id). Everything is in a transaction from BeginTransaction(). Transfer €30 from 1 to 2, then €40 from 1 to the account 99, which doesn't exist:

Output

Transferred €30 from 1 to 2
Refused: No account 99
  Ana: €70
  Boris: €80

The second transfer took €40 from Ana before it failed, but Rollback() gave it back.

Test

Check yourself

1. What does ExecuteReader return?

A SqliteDataReader: the rows of the result, which you walk through with Read()A list of Product objectsThe number of changed rows

2. Why is $"... WHERE name = '{name}'" dangerous?

Because it is slowIt is not dangerous if name is a stringBecause the text from the user becomes part of the SQL and can change the query

3. A transfer is two queries. The first one succeeds, the second one fails. What does tx.Rollback() do?

It undoes only the second oneIt undoes all changes since BeginTransaction, the first one tooIt repeats the second query

4. What does an ORM like Entity Framework Core do?

It replaces the databaseIt only makes SQL queries fasterIt turns objects into rows and back, without you writing SQL for everything

Check

Start the lesson over

Related lessons

• Builds on: Lists · Lambdas and delegates · LINQ · record, switch and patterns
• Continues in: Injections: when data becomes a command · Monolith, modular monolith, services · Transactional outbox · A transaction across services (saga)
• Tasks on this topic: Real-world programs