Бази данни

Накратко: Базата данни пази данните в таблици и ги търси бързо по заявка на SQL; в C# с нея се говори през ADO.NET, а параметрите пазят от SQL injection.

Проблем

Каталог във файл

Каталогът на магазина е CSV файл, както в модула за файловете. Докато продуктите са сто, става. После стават сто хиляди и се появяват проблеми:

• за да намериш един продукт, четеш целия файл;
• „всички под 5 €, подредени по цена“ е цикъл, if и сортиране, писани на ръка;
• две каси пишат във файла едновременно и единият запис се губи или файлът се поврежда;
• токът спира по средата на записа и половината файл го няма.

Затова данните на почти всяка истинска програма са в база данни: програма, чиято работа е да пази данни, да ги търси бързо и да не ги губи.

Идеята

Таблици и SQL

Релационната база пази данните в таблици. Таблицата има колони (име, тип) и редове (по един за всеки продукт). Колоната id е първичен ключ: стойността ѝ е различна за всеки ред, затова по нея се намира точно един.

С базата се говори на езика SQL. Петте най-чести команди:

SQL

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

INSERT INTO products VALUES (1, 'Кафе', 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 прави таблица, INSERT добавя ред, UPDATE променя, DELETE трие.
• SELECT чете: кои колони, от коя таблица, при какво условие (WHERE) и в какъв ред (ORDER BY).

Ти казваш какво искаш, а базата решава как да го намери бързо.

По-точно, за любопитните

• Известни релационни бази са PostgreSQL, MySQL, Oracle и SQL Server. Те работят като отделни сървъри. Тук ползваме SQLite: малка база, която живее във файл или изцяло в паметта на програмата. В .NET тя идва с пакета Microsoft.Data.Sqlite от Microsoft (dotnet add package Microsoft.Data.Sqlite). Адресът Data Source=:memory: е база в паметта: живее, докато връзката е отворена, и всяко пускане започва с празна.
• Текстът в SQL е в единични кавички: 'Кафе'. В PostgreSQL, MySQL и SQL Server DECIMAL(8, 2) е точно число с 2 знака след запетаята, подходящо за пари. SQLite не пази точни десетични числа: там 7.50 е обикновено число с плаваща запетая.

Виж го

ADO.NET: връзка, команда, резултат

ADO.NET е начинът, по който .NET говори с релационни бази. Всяка база има свой пакет с едни и същи части: връзката е SqliteConnection, заявката е SqliteCommand, а резултатът се чете със SqliteDataReader (за SQL Server са SqlConnection, SqlCommand, SqlDataReader). Методът Setup прави таблицата и добавя три продукта:

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, 'Кафе', 7.50), (2, 'Чай', 3.20), (3, 'Шоколад', 2.40)";
    cmd.ExecuteNonQuery();
}

Четем редовете и правим от всеки обект от 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);
}

Изход

Product { Id = 3, Name = Шоколад, Price = 2.4 }
Product { Id = 2, Name = Чай, Price = 3.2 }
Product { Id = 1, Name = Кафе, Price = 7.5 }

• new SqliteConnection(адрес) прави връзката, а Open() я отваря. Адресът (connection string) казва коя база: тук SQLite в паметта, или файл като Data Source=shop.db.
• conn.CreateCommand() прави команда, а CommandText е SQL-ът ѝ. ExecuteNonQuery() е за команди, които променят (CREATE, INSERT, UPDATE, DELETE), и връща колко реда са променени. ExecuteReader() е за SELECT.
• reader.Read() минава на следващия ред и връща false, когато редовете свършат. GetInt32, GetString и GetDouble взимат колона по номер: от 0, в реда на SELECT. По име е reader.GetOrdinal("name").
• Връзката, командата и читателят се затварят: using var ги затваря (вика Dispose()), когато блокът свърши.
• Грешките са изключения SqliteException. Проверени изключения в C# няма, затова методите не пишат 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);
}

Стъпка по стъпка, с думи

1. Връзка с базата в паметта. Setup прави таблицата products и добавя 3 реда.
2. Празен списък, в който ще влязат обектите.
3. SELECT с ORDER BY price: ExecuteReader връща читател към редовете, от най-евтиния.
4. reader.Read() минава на ред 1. GetInt32, GetString и GetDouble взимат колоните по номер, а new Product прави от тях обект: Шоколад.
5. reader.Read() минава на ред 2. GetInt32, GetString и GetDouble взимат колоните по номер, а new Product прави от тях обект: Чай.
6. reader.Read() минава на ред 3. GetInt32, GetString и GetDouble взимат колоните по номер, а new Product прави от тях обект: Кафе.
7. reader.Read() връща false: редовете свършиха и цикълът спира.
8. Списъкът е от обикновени C# обекти. Печатът показва record-а с полетата му.

По-точно, за любопитните

• С истинска база на сървър адресът е друг и има потребител и парола. Също трябва и драйверът на базата като зависимост в проекта (за MySQL: пакетът MySqlConnector, с MySqlConnection вместо SqliteConnection):

var address = "Server=localhost;Port=3306;Database=shop;User ID=shop_app;Password=парола";
using var conn = new MySqlConnection(address);
conn.Open();
Console.WriteLine("Връзката с базата е успешна!");

• Паролата не се пише в кода, а се чете от настройките или от променлива на средата (appsettings.json или Environment.GetEnvironmentVariable).

• Цените тук са double, за да е кратко. В истински магазин парите са decimal: колоната DECIMAL в PostgreSQL или SQL Server се чете с reader.GetDecimal(2). SQLite не пази точни десетични числа, затова там парите се пазят като цели стотинки.

Пробвай

Кое ще излезе?

Продуктите са Кафе 7,50 €, Чай 3,20 € и Шоколад 2,40 €. Какво ще отпечата програмата?

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));
}

Отговор

Шоколад
Чай

Чай
Шоколад

Кафе
Чай
Шоколад

Провери

Проблем

Търсачката, която показва всичко

Сайтът търси продукт по име, което потребителят пише. Заявката се сглобява с $"…{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("Търся: Чай");
Search(conn, "Чай");
Console.WriteLine("Търся: x' OR '1'='1");
Search(conn, "x' OR '1'='1");

Изход

Търся: Чай
  Чай: 3.2 €
Търся: x' OR '1'='1
  Кафе: 7.5 €
  Чай: 3.2 €
  Шоколад: 2.4 €

С „Чай“ работи. С x' OR '1'='1 заявката става ... WHERE name = 'x' OR '1'='1'. Условието е винаги вярно и излиза цялата таблица. Входът на потребителя е станал част от SQL. Това е SQL injection, една от най-честите атаки срещу сайтове. Със същия трик може да се влезе без парола или да се изтрие таблица.

Идеята

Параметри: $name

Решението е заявката и стойностите да вървят отделно. Заявката има параметър с име, $name, на мястото на стойността, а стойността се подава с 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)} €");
    }
}

Изход

Търся: Чай
  Чай: 3.2 €
Търся: x' OR '1'='1

Сега x' OR '1'='1 е просто текст, който се търси като име. Такъв продукт няма и не излиза нищо. Каквото и да напише потребителят, то не може да стане SQL.

Правилото е просто: стойност от потребителя, от файл или от мрежата никога не се долепя в SQL. Винаги параметър. Microsoft.Data.Sqlite приема имената $name, @name и :name. В SQL Server и в повечето примери за .NET ще видиш @name.

Пробвай

Кафе с апостроф

Методът преименува продукт. С името Кафе 'Арабика' програмата спира с SqliteException: SQLite Error 1: 'near "Арабика": syntax error'. На кой ред е грешката?

Избери реда с грешката.

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}

Провери

Идеята

Транзакция: всичко или нищо

Ана превежда 150 € на Борис. Това са две заявки: добави на Борис, извади от Ана. В таблицата има условие CHECK (balance >= 0), затова втората заявка се проваля: Ана има само 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, 'Ана', 100), (2, 'Борис', 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("Преводът на 150 € е отказан.");
}
PrintBalances(conn);

Изход

Преводът на 150 € е отказан.
  Ана: 100 €
  Борис: 200 €

Преводът е отказан, но Борис вече е получил 150 € от нищото. По подразбиране всяка заявка се записва веднага. Двете заявки трябва да са транзакция: или минават и двете, или нито една.

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;
    }
}

Изход

Преводът на 150 € е отказан.
  Ана: 100 €
  Борис: 50 €

• conn.BeginTransaction() започва транзакция: промените още не са окончателни. Командите, които правиш след това с CreateCommand(), са в нея.
• tx.Commit() ги записва всички наведнъж.
• tx.Rollback() ги отменя всички, ако нещо се провали. throw; пуска същото изключение нагоре, за да разбере и викащият.
• using var tx е предпазител: ако транзакцията стигне края на блока без Commit(), Dispose() я отменя.

По-точно, за любопитните

• Изключението при провалената заявка е SqliteException със съобщение SQLite Error 19: 'CHECK constraint failed: no_debt'. 19 е кодът на SQLite за нарушено правило (e.SqliteErrorCode). no_debt е името на условието: в таблицата то е balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Базите обещават четири неща за транзакциите, известни като ACID: всичко или нищо (atomicity), данните остават верни според правилата на таблиците (consistency), едновременните транзакции не си пречат (isolation) и записаното не се губи дори при спрял ток (durability).

Идеята

ORM: обекти вместо редове

С ADO.NET за всеки клас пишеш SQL и превръщаш редовете в обекти на ръка. ORM (object-relational mapping) прави това вместо теб: описваш класа, а библиотеката сама прави таблицата, заявките и обектите. В .NET това е Entity Framework Core (EF Core) от Microsoft, за SQLite с пакета Microsoft.EntityFrameworkCore.Sqlite. Друг известен е Dapper: по-лек, там SQL-а го пишеш ти, а той прави обектите.

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")] дава името на таблицата. Нещата в [...] са атрибути: бележки към класа и свойствата, които EF Core чете. Ще ги срещнеш пак в уеб модула.
• Свойството Id е първичният ключ по договореност (името е Id или ProductId), а базата му дава стойност.
• [Required] е NOT NULL, а [MaxLength(50)] дава дължината. Свойствата без атрибут също стават колони.
• DbContext е връзката с базата. Всеки DbSet<Product> е таблица, а OnConfiguring казва коя е базата: тук SQLite в паметта.

Записът и четенето минават през контекста:

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

db.Products.Add(new Product { Name = "Кафе", Price = 7.50 });
db.Products.Add(new Product { Name = "Чай", 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} €");
}

Изход

Чай: 3.2 €

EnsureCreated() прави таблиците. Add добавя обект, а SaveChanges() записва всички добавени и променени обекти наведнъж, в една транзакция. Заявката е LINQ, като при списъците, но EF Core не минава през обектите в паметта: превежда Where(p => p.Price < 5) в SQL, който говори за класа и свойството Price, и го праща на базата. ToList() пуска заявката и връща готови обекти.

Базата в паметта живее, докато връзката е отворена, а EF Core иначе я отваря и затваря при всяка заявка. Затова OpenConnection() я държи отворена. С файл (Data Source=shop.db) този ред не трябва.

Тук на сайта този код се пуска: EF Core 10 със SQLite е в проверката. Изходът е от нея.

По-точно, за любопитните

• Заявката с LINQ е код, затова условията лесно се сглобяват според избора на потребителя. Метод, който търси по име:

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

• Когато ти трябва точно твой SQL, FromSql го пуска и връща обекти. Стойността в {max} не се долепя: FromSql я праща като параметър, затова тук injection няма. FromSqlRaw е друго: взима готов текст, и ако го сглобиш с + или $"…", стойността пак се долепя.

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

• Транзакция с отмяна при грешка, когато една промяна е няколко SaveChanges:

using var tx = db.Database.BeginTransaction();
try
{
    db.Products.Add(new Product { Name = "Шоколад", Price = 2.40 });
    db.SaveChanges();
    // още промени по обектите и SaveChanges
    tx.Commit();
}
catch
{
    tx.Rollback();
    throw;
}

• ToQueryString() показва SQL-а на заявката, без да я пуска. Така се вижда, че отдолу пак е SQL, а max е параметър:

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

Изход

.param set @max 5.0

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

• В стари проекти на .NET Framework ще срещнеш Entity Framework 6 (System.Data.Entity). EF Core е новият, за .NET 5 и нагоре, а идеите са същите.

Упражнение

Магазин с база

Реши задачите в редактора си, с пакета Microsoft.Data.Sqlite в проекта и база Data Source=:memory:.1. Каталог. Направи таблица products и методи Add, SetPrice, Remove и List, всички с параметри ($id, $name, $price) освен List. Добави Кафе (7,50 €), Чай (3,20 €) и Шоколад (2,40 €), отпечатай, смени цената на кафето на 6,90 €, изтрий шоколада и отпечатай пак:

Изход

1. Кафе: 7.5 €
2. Чай: 3.2 €
3. Шоколад: 2.4 €
---
1. Кафе: 6.9 €
2. Чай: 3.2 €

2. Търсене по част от името. Продуктите са Кафе, Какао, Чай и Шоколад. Метод Find(conn, text) намира всички, чието име съдържа текста, без значение от малки и главни букви. LOWER в SQLite сменя само латинските букви, затова дай на базата своя функция: conn.CreateFunction("lower_text", (string text) => text.ToLowerInvariant()), после WHERE lower_text(name) LIKE $text със стойност "%" + text.ToLowerInvariant() + "%". Пробвай „КА“, „чай“ и опит за injection:

Изход

Търся „КА“:
  Какао: 4.8 €
  Кафе: 7.5 €
Търся „чай“:
  Чай: 3.2 €
Търся „' OR '1'='1“:
  няма такива продукти

3. Превод, който се проваля наполовина. Сметките са Ана (100 €) и Борис (50 €). Transfer(conn, from, to, amount) първо вади от from, после добавя на to. Ако ExecuteNonQuery() върне 0 (няма такава сметка), хвърли InvalidOperationException("Няма сметка " + id). Всичко е в транзакция от BeginTransaction(). Преведи 30 € от 1 към 2, после 40 € от 1 към несъществуващата 99:

Изход

Преведени 30 € от 1 към 2
Отказан: Няма сметка 99
  Ана: 70 €
  Борис: 80 €

Вторият превод е извадил 40 € от Ана, преди да се провали, но Rollback() ги е върнал.

Тест

Провери се

1. Какво връща ExecuteReader?

SqliteDataReader: редовете от резултата, които обхождаш с Read()Списък от обекти ProductБроя на променените редове

2. Защо $"... WHERE name = '{name}'" е опасно?

Защото е бавноНе е опасно, ако name е stringЗащото текстът от потребителя става част от SQL и може да промени заявката

3. Преводът е две заявки. Първата минава, втората се проваля. Какво прави tx.Rollback()?

Отменя всички промени от BeginTransaction насам, и първатаОтменя само вторатаПовтаря втората заявка

4. Какво прави ORM като Entity Framework Core?

Само ускорява SQL заявкитеПревръща обекти в редове и обратно, без да пишеш SQL за всяко нещоЗаменя базата данни

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: Списъци · Ламбди и делегати · LINQ · record, switch и шаблони
• Продължава в: Инжекции: когато данните станат команда · Монолит, модулен монолит, услуги · Transactional outbox · Транзакция през няколко услуги (saga)
• Задачи по темата: Програми в истинския свят