Bases de datos

En resumen: Una base de datos guarda los datos en tablas y los encuentra rápido con una consulta SQL; en C# hablas con ella a través de ADO.NET, y los parámetros protegen contra la inyección SQL.

Problema

Un catálogo en un archivo

El catálogo de la tienda es un archivo CSV, como en el módulo de archivos. Mientras hay cien productos, funciona. Luego hay cien mil, y aparecen problemas:

• para encontrar un producto, lees el archivo entero;
• «todo lo que cuesta menos de 5 €, ordenado por precio» es un bucle, un if y una ordenación, escritos a mano;
• dos cajas escriben en el archivo a la vez, y un registro se pierde o el archivo se estropea;
• se va la luz en mitad de una escritura, y desaparece la mitad del archivo.

Por eso los datos de casi cualquier programa real viven en una base de datos: un programa cuyo trabajo es guardar datos, encontrarlos rápido y no perderlos nunca.

La idea

Tablas y SQL

Una base de datos relacional guarda los datos en tablas. Una tabla tiene columnas (nombre, tipo) y filas (una por cada producto). La columna id es la clave primaria: su valor es distinto en cada fila, así que con ella se encuentra exactamente una.

Con la base de datos se habla en SQL. Los cinco comandos más comunes:

SQL

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

INSERT INTO products VALUES (1, 'Café', 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 crea una tabla, INSERT añade una fila, UPDATE cambia filas y DELETE las borra.
• SELECT lee: qué columnas, de qué tabla, con qué condición (WHERE) y en qué orden (ORDER BY).

Tú dices qué quieres, y la base de datos decide cómo encontrarlo rápido.

Más precisamente, para curiosos

• Bases de datos relacionales conocidas son PostgreSQL, MySQL, Oracle y SQL Server. Funcionan como servidores aparte. Aquí usamos SQLite: una base de datos pequeña que vive en un archivo o entera en la memoria del programa. En .NET llega con el paquete Microsoft.Data.Sqlite de Microsoft (dotnet add package Microsoft.Data.Sqlite). La dirección Data Source=:memory: es una base de datos en memoria: vive mientras la conexión está abierta, y cada ejecución empieza con una vacía.
• El texto en SQL va entre comillas simples: 'Café'. En PostgreSQL, MySQL y SQL Server, DECIMAL(8, 2) es un número exacto con 2 decimales, adecuado para dinero. SQLite no guarda números decimales exactos: ahí 7.50 es un número de coma flotante normal.

Míralo

ADO.NET: conexión, comando, resultado

ADO.NET es la forma en que .NET habla con las bases de datos relacionales. Cada base de datos tiene su propio paquete con las mismas piezas: la conexión es una SqliteConnection, la consulta es un SqliteCommand, y el resultado se lee con un SqliteDataReader (para SQL Server son SqlConnection, SqlCommand y SqlDataReader). El método Setup crea la tabla y añade tres productos:

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, 'Café', 7.50), (2, 'Té', 3.20), (3, 'Chocolate', 2.40)";
    cmd.ExecuteNonQuery();
}

Leemos las filas y convertimos cada una en un objeto de 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);
}

Salida

Product { Id = 3, Name = Chocolate, Price = 2.4 }
Product { Id = 2, Name = Té, Price = 3.2 }
Product { Id = 1, Name = Café, Price = 7.5 }

• new SqliteConnection(dirección) crea la conexión, y Open() la abre. La dirección (la connection string) dice qué base de datos: aquí, SQLite en memoria, o un archivo como Data Source=shop.db.
• conn.CreateCommand() crea un comando, y CommandText es su SQL. ExecuteNonQuery() es para los comandos que cambian algo (CREATE, INSERT, UPDATE, DELETE), y devuelve cuántas filas cambiaron. ExecuteReader() es para SELECT.
• reader.Read() pasa a la fila siguiente y devuelve false cuando se acaban las filas. GetInt32, GetString y GetDouble toman una columna por su número: desde 0, en el orden del SELECT. Por nombre es reader.GetOrdinal("name").
• La conexión, el comando y el lector hay que cerrarlos: using var los cierra (llama a Dispose()) cuando termina el bloque.
• Los errores son excepciones SqliteException. C# no tiene excepciones comprobadas, así que los métodos no escriben 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);
}

Paso a paso, en palabras

1. Una conexión con la base de datos en memoria. Setup crea la tabla products y añade 3 filas.
2. Una lista vacía, donde irán los objetos.
3. SELECT con ORDER BY price: ExecuteReader devuelve un lector sobre las filas, de la más barata a la más cara.
4. reader.Read() pasa a la fila 1. GetInt32, GetString y GetDouble toman las columnas por número, y new Product crea con ellas un objeto: Chocolate.
5. reader.Read() pasa a la fila 2. GetInt32, GetString y GetDouble toman las columnas por número, y new Product crea con ellas un objeto: Té.
6. reader.Read() pasa a la fila 3. GetInt32, GetString y GetDouble toman las columnas por número, y new Product crea con ellas un objeto: Café.
7. reader.Read() devuelve false: se acabaron las filas y el bucle se detiene.
8. La lista tiene objetos C# normales. Al imprimirlos se ve el record con sus campos.

Más precisamente, para curiosos

• Con una base de datos real en un servidor, la dirección es otra y hay un usuario y una contraseña. Además necesitas el driver (controlador) de la base de datos como dependencia en el proyecto (para MySQL: el paquete MySqlConnector, con MySqlConnection en lugar de SqliteConnection):

var address = "Server=localhost;Port=3306;Database=shop;User ID=shop_app;Password=secreto";
using var conn = new MySqlConnection(address);
conn.Open();
Console.WriteLine("¡Conectado a la base de datos!");

• La contraseña no se escribe en el código. Se lee de la configuración o de una variable de entorno (appsettings.json o Environment.GetEnvironmentVariable).

• Aquí los precios son double para que sea breve. En una tienda real, el dinero es un decimal: una columna DECIMAL en PostgreSQL o SQL Server se lee con reader.GetDecimal(2). SQLite no guarda números decimales exactos, así que ahí el dinero se guarda en céntimos enteros.

Pruébalo

¿Qué se imprimirá?

Los productos son Café 7,50 €, Té 3,20 € y Chocolate 2,40 €. ¿Qué imprimirá el programa?

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

Respuesta

Chocolate
Té

Té
Chocolate

Café
Té
Chocolate

Comprobar

Problema

El buscador que lo muestra todo

La web busca un producto por el nombre que escribe el usuario. La consulta se arma con $"…{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("Buscando: Té");
Search(conn, "Té");
Console.WriteLine("Buscando: x' OR '1'='1");
Search(conn, "x' OR '1'='1");

Salida

Buscando: Té
  Té: 3.2 €
Buscando: x' OR '1'='1
  Café: 7.5 €
  Té: 3.2 €
  Chocolate: 2.4 €

Con «Té» funciona. Con x' OR '1'='1 la consulta se convierte en ... WHERE name = 'x' OR '1'='1'. La condición siempre es verdadera, y sale la tabla entera. La entrada del usuario ha pasado a formar parte del SQL. Esto es la inyección SQL (SQL injection), uno de los ataques más comunes contra sitios web. Con el mismo truco, alguien puede entrar sin contraseña o borrar una tabla.

La idea

Parámetros: $name

La solución es enviar la consulta y los valores por separado. La consulta tiene un parámetro con nombre, $name, en el lugar del valor, y el valor se pasa con 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)} €");
    }
}

Salida

Buscando: Té
  Té: 3.2 €
Buscando: x' OR '1'='1

Ahora x' OR '1'='1 es solo un texto que se busca como nombre. No existe ese producto, y no sale nada. Escriba lo que escriba el usuario, no puede convertirse en SQL.

La regla es sencilla: un valor del usuario, de un archivo o de la red nunca se pega dentro del SQL. Usa siempre un parámetro. Microsoft.Data.Sqlite acepta los nombres $name, @name y :name. En SQL Server y en la mayoría de los ejemplos de .NET verás @name.

Pruébalo

Café con apóstrofo

El método cambia el nombre de un producto. Con el nombre Café 'Arábica' el programa se detiene con SqliteException: SQLite Error 1: 'near "Arábica": syntax error'. ¿En qué línea está el error?

Elige la línea con el 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}

Comprobar

La idea

Transacción: todo o nada

Ana le transfiere 150 € a Carlos. Son dos consultas: sumarle a Carlos y restarle a Ana. La tabla tiene la condición CHECK (balance >= 0), así que la segunda consulta falla: Ana solo tiene 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, 'Carlos', 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("La transferencia de 150 € fue rechazada.");
}
PrintBalances(conn);

Salida

La transferencia de 150 € fue rechazada.
  Ana: 100 €
  Carlos: 200 €

La transferencia se rechazó, pero Carlos ya ha recibido 150 € de la nada. Por defecto, cada consulta se guarda enseguida. Las dos consultas tienen que ser una transacción: o pasan las dos, o ninguna.

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

Salida

La transferencia de 150 € fue rechazada.
  Ana: 100 €
  Carlos: 50 €

• conn.BeginTransaction() empieza una transacción: los cambios todavía no son definitivos. Los comandos que crees después con CreateCommand() van dentro de ella.
• tx.Commit() los guarda todos de una vez.
• tx.Rollback() los deshace todos si algo falla. throw; lanza la misma excepción hacia arriba, para que también se entere quien llamó.
• using var tx es una red de seguridad: si la transacción llega al final del bloque sin Commit(), Dispose() la deshace.

Más precisamente, para curiosos

• La excepción de la consulta que falla es una SqliteException con el mensaje SQLite Error 19: 'CHECK constraint failed: no_debt'. 19 es el código de SQLite para una regla que no se cumple (e.SqliteErrorCode). no_debt es el nombre de la condición: en la tabla es balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Las bases de datos prometen cuatro cosas sobre las transacciones, conocidas como ACID: todo o nada (atomicidad, atomicity), los datos siguen siendo correctos según las reglas de las tablas (consistencia, consistency), las transacciones que se ejecutan a la vez no se estorban (aislamiento, isolation), y lo que se ha guardado no se pierde aunque se vaya la luz (durabilidad, durability).

La idea

ORM: objetos en lugar de filas

Con ADO.NET, para cada clase escribes SQL y conviertes las filas en objetos a mano. Un ORM (object-relational mapping, mapeo objeto-relacional) lo hace por ti: describes la clase, y la biblioteca crea sola la tabla, las consultas y los objetos. En .NET es Entity Framework Core (EF Core) de Microsoft, para SQLite con el paquete Microsoft.EntityFrameworkCore.Sqlite. Otro conocido es Dapper: es más ligero, el SQL lo escribes tú, y él crea los objetos.

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")] da el nombre de la tabla. Lo que va entre [...] son atributos: notas sobre la clase y sus propiedades que EF Core lee. Los verás otra vez en el módulo web.
• La propiedad Id es la clave primaria por convención (el nombre es Id o ProductId), y la base de datos le da un valor.
• [Required] es NOT NULL, y [MaxLength(50)] da la longitud. Las propiedades sin atributo también se convierten en columnas.
• El DbContext es la conexión con la base de datos. Cada DbSet<Product> es una tabla, y OnConfiguring dice qué base de datos es: aquí, SQLite en memoria.

Guardar y leer pasa por el contexto:

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

db.Products.Add(new Product { Name = "Café", Price = 7.50 });
db.Products.Add(new Product { Name = "Té", 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} €");
}

Salida

Té: 3.2 €

EnsureCreated() crea las tablas. Add añade un objeto, y SaveChanges() guarda todos los objetos añadidos y cambiados de una vez, en una transacción. La consulta es LINQ, como con las listas, pero EF Core no recorre objetos en memoria: convierte Where(p => p.Price < 5) en SQL, que habla de la clase y de su propiedad Price, y lo envía a la base de datos. ToList() ejecuta la consulta y devuelve objetos ya hechos.

La base de datos en memoria vive mientras la conexión está abierta, y EF Core, si no, la abre y la cierra en cada consulta. Por eso OpenConnection() la mantiene abierta. Con un archivo (Data Source=shop.db) no necesitas esta línea.

Aquí en el sitio, este código se ejecuta: EF Core 10 con SQLite está en el comprobador. La salida viene de él.

Más precisamente, para curiosos

• Una consulta LINQ es código, así que es fácil armar las condiciones según lo que elige el usuario. Un método que busca por nombre:

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

• Cuando necesitas exactamente tu propio SQL, FromSql lo ejecuta y devuelve objetos. El valor de {max} no se pega: FromSql lo envía como parámetro, así que aquí no hay inyección. FromSqlRaw es distinto: recibe un texto ya hecho, y si lo armas con + o $"…", el valor vuelve a quedar pegado.

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

• Una transacción que se deshace si hay un error, cuando un cambio son varias llamadas a SaveChanges:

using var tx = db.Database.BeginTransaction();
try
{
    db.Products.Add(new Product { Name = "Chocolate", Price = 2.40 });
    db.SaveChanges();
    // más cambios en los objetos y SaveChanges
    tx.Commit();
}
catch
{
    tx.Rollback();
    throw;
}

• ToQueryString() muestra el SQL de la consulta sin ejecutarla. Así ves que por debajo sigue siendo SQL, y que max es un parámetro:

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

Salida

.param set @max 5.0

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

• En proyectos antiguos de .NET Framework verás Entity Framework 6 (System.Data.Entity). EF Core es el nuevo, para .NET 5 y posteriores, y las ideas son las mismas.

Ejercicio

Una tienda con base de datos

Resuelve las tareas en tu editor, con el paquete Microsoft.Data.Sqlite en el proyecto y la base de datos Data Source=:memory:.1. Catálogo. Crea una tabla products y los métodos Add, SetPrice, Remove y List, todos con parámetros ($id, $name, $price) menos List. Añade Café (7,50 €), Té (3,20 €) y Chocolate (2,40 €), imprímelos, cambia el precio del café a 6,90 €, borra el chocolate e imprime otra vez:

Salida

1. Café: 7.5 €
2. Té: 3.2 €
3. Chocolate: 2.4 €
---
1. Café: 6.9 €
2. Té: 3.2 €

2. Búsqueda por parte del nombre. Los productos son Café, Cacao, Té y Éclair. Un método Find(conn, text) encuentra todos los productos cuyo nombre contiene el texto, sin importar mayúsculas y minúsculas. LOWER en SQLite solo cambia las letras de la A a la Z, así que É se queda como está. Dale a la base de datos una función propia: conn.CreateFunction("lower_text", (string text) => text.ToLowerInvariant()), luego WHERE lower_text(name) LIKE $text con el valor "%" + text.ToLowerInvariant() + "%". Prueba «CA», «éclair» y un intento de inyección:

Salida

Buscando «CA»:
  Cacao: 4.8 €
  Café: 7.5 €
Buscando «éclair»:
  Éclair: 2.4 €
Buscando «' OR '1'='1»:
  no se encontraron productos

3. Una transferencia que falla a medias. Las cuentas son Ana (100 €) y Carlos (50 €). Transfer(conn, from, to, amount) primero resta de from y luego suma a to. Si ExecuteNonQuery() devuelve 0 (no existe esa cuenta), lanza InvalidOperationException("No existe la cuenta " + id). Todo va en una transacción de BeginTransaction(). Transfiere 30 € de la 1 a la 2, y luego 40 € de la 1 a la cuenta 99, que no existe:

Salida

Transferidos 30 € de 1 a 2
Rechazada: No existe la cuenta 99
  Ana: 70 €
  Carlos: 80 €

La segunda transferencia le quitó 40 € a Ana antes de fallar, pero Rollback() se los devolvió.

Test

Ponte a prueba

1. ¿Qué devuelve ExecuteReader?

Una lista de objetos ProductUn SqliteDataReader: las filas del resultado, que recorres con Read()El número de filas cambiadas

2. ¿Por qué es peligroso $"... WHERE name = '{name}'"?

No es peligroso si name es un stringPorque el texto del usuario pasa a formar parte del SQL y puede cambiar la consultaPorque es lento

3. Una transferencia son dos consultas. La primera sale bien, la segunda falla. ¿Qué hace tx.Rollback()?

Deshace todos los cambios desde BeginTransaction, también el primeroDeshace solo la segundaRepite la segunda consulta

4. ¿Qué hace un ORM como Entity Framework Core?

Convierte objetos en filas y al revés, sin que escribas SQL para cada cosaSolo hace más rápidas las consultas SQLSustituye a la base de datos

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: Listas · Lambdas y delegados · LINQ · record, switch y patrones
• Continúa en: Inyecciones: cuando los datos se vuelven una orden · Monolito, monolito modular, servicios · Transactional Outbox · Una transacción entre servicios (Saga)
• Ejercicios sobre el tema: Programas en el mundo real