Relación uno a muchos con el patrón repositorio en ADO.NET

Por lo general, en tus aplicaciones vas a contar con múltiples clases que no van a actuar independientes unas de las otras, sino que se entrelazan mediante relaciones. Estas relaciones se ven claras si las vemos separadas, por un lado las clases se relacionan con otras mediante composición y por otro, en nuestra base de datos, las relaciones ocurren mediante claves foráneas. Para conectar estas dos lógicas el patrón repositorio debe actuar como «puente», y en este caso vamos a centrarnos en cómo el patrón repositorio se puede utilizar para la relación uno a muchos.

⚠ ¡Aviso navegantes! ⚠

Esta implementación del patrón repositorio asume ciertas nociones previas explicadas en los
siguientes artículos:

👉 Qué es el patrón repositorio y sus ventajas donde explicamos cómo funciona el patrón y por qué querrías usarlo.
👉 Implementación básica del patrón repositorio con una clase sin relaciones, donde se explica cómo funciona SqlConnection, SqlCommand y SqlReader.

Sugiero revisarlos en caso de que tengas alguna duda sobre cualquiera de estos dos puntos.

Relación uno a muchos

Voy a partir de la misma premisa de la última vez. Estamos construyendo un blog y tenemos nuestra clase Artículo que habíamos definido previamente, aunque ahora también queremos poder añadirle un autor. Este autor puede tener asignados a sí mismo múltiples artículos que haya escrito, por lo que podemos definir una la clase Autor de la siguiente manera.

public class Autor
{
    public int Id { get; set; }
    public string Nombre { get; set; } = "";
    public string Apellido { get; set; } = "";
    public List<Articulo> ArticulosEscritos { get; set; } = new List<Articulo>();
}

Por lo que podríamos actualizar a nuestra clase Artículo para que pudiéramos también obtener al autor de dicho artículo desde ahí.

public class Articulo
{
    public int Id { get; set; }
    public string Titulo { get; set; } = "";
    public string Contenido { get; set; } = "";
    public DateTime FechaCreacion { get; set; }
    public Autor Autor { get; set; } // No mapeado en la base de datos
}

Creada esta nueva clase y actualizada de la artículo, no nos queda otra que actualizar las tablas de nuestra base de datos para que esté acorde con esta nueva entidad y relación uno a muchos. Por tanto, el diagrama UML quedaria tal que:

Es decir, hemos creado una tabla intermedia llamada AutorArticulo, y desde esta vamos a extraer información de los artículos asignados al autor. En esta relación también establecemos la forma en la que sucederán las actualizaciones y eliminaciones, en mi caso quiero que AutorArticulo se actualice y elimine en cascada en base a las tablas Autor y Artículo. Este punto es interesante porque luego en la implementación nos permitirá no tener que hacer ninguna modificación en el método de eliminación del repositorio.

Por verificar que todo ha ido bien, podemos asignar datos demo y simular una consulta SQL de las que usaremos.

SELECT ArticuloId, AutorId, Nombre, Apellidos, Titulo, Contenido, FechaCreacion
FROM AutorArticulo 
JOIN Autor ON AutorArticulo.AutorId = Autor.Id
JOIN Articulo ON AutorArticulo.ArticuloId = Articulo.Id

En este caso en vez de poner un SELECT con Wildcard ( * ), he puesto directamente los campos por legibilidad y para eliminar campos innecesario por estar repetidos. Con los datos que he introducido para el testeo, devuelve la siguiente tabla.

Los datos son los esperados, así que vamos a actualizar nuestro patrón repositorio para que considere esta nueva relación uno a muchos. Pero antes de empezar a hacer cambios, voy a presentaros a una nueva clase que vamos a usar: SqlTransaction.

SqlTransaction y las acciones dependientes

La clase SqlTransaction es una clase que sirve para indicar que una serie de transacciones SQL van a suceder. Esta clase empieza a cobrar relevancia en situaciones donde necesitamos que se produzcan dos acciones en nuestra base de datos y si una falla la otra no debe ejecutarse o debe deshacerse, pues de otra manera generaría errores en la persistencia de los datos.

De esta manera, los comandos SQL se ejecutan en el seno del objeto SqlTransaction en una primera instancia, una especie de Sandbox para comandos. En otras palabras, hasta que no se llega hasta el método Commit, los métodos ExecuteNonQuery realizados en los comandos no se reflejan en la base de datos.

Pongamos un ejemplo de esta situación. Hemos creado un nuevo artículo y ahora queremos guardarlo en la base de datos. Sin embargo, nuestro actual método para guardar ya no solo lo debe guardar en la tabla Artículo, sino que también debe añadir el Id del nuevo Artículo a la tabla conjunta AutorArticulo para que sepamos quién ha creado dicho artículo.

using SqlConnection con = new SqlConnection(connectionString);
con.Open();

SqlCommand cmdArticulo = new SqlCommand("INSERT INTO Articulo OUTPUT INSERTED.Id VALUES(@Titulo, @Contenido, @Fecha)", con);
cmdArticulo.Parameters.AddWithValue("@Titulo", articulo.Titulo);
cmdArticulo.Parameters.AddWithValue("@Contenido", articulo.Contenido);
cmdArticulo.Parameters.AddWithValue("@Fecha", DateTime.Now);

SqlCommand cmdAutorArticulo = new SqlCommand("INSERT INTO AutorArticulo VALUES(@AutorId, @ArticuloId)", con);
cmdAutorArticulo.Parameters.AddWithValue("@AutorId", articulo.Autor.Id);

try
{
    int idArticulo = (int)cmdArticulo.ExecuteScalar();
    cmdAutorArticulo.Parameters.AddWithValue("@ArticuloId", idArticulo);

    
    // ¿Qué ocurre si este método falla?
    cmdAutorArticulo.ExecuteNonQuery();
}
catch (Exception ex)
{
    /// Gestionar la excepción
}

con.Close();

Por si te lo estás preguntando, el INSERTED.Id en el primer INSERT sumado al cmdArticulo.ExecuteScalar() nos permite obtener el Id del artículo recién insertado para luego poder utilizarlo en la tabla conjunta.

Ahora digamos que la inserción en la tabla de Artículo ocurre adecuadamente pero luego falla el cmdAutorArticulo.ExecuteNonQuery(), por ejemplo, porque el Id de autor indicado no existe. Cuando hagamos la consulta para extraer los artículos, la sentencia «JOIN Autor ON AutorArticulo.AutorId = Autor.Id» no va a encontrar el AutorArticulo.Id asociado a ese nuevo artículo, por lo que no devolverá ese Artículo.

Es decir, tendremos un artículo en la base de datos que estaría huérfano de autor y que nunca saldría en la aplicación 🤯

Y aquí es donde el método Commit() y Rollback() brillan. Como el método Commit es el último que se ejecuta, posterior a los ExecuteNonQuery, si estos han fallado, el Commit no se ha realizado y por tanto el método Rollback se encargará de deshacer todo lo que hubiera ocurrido. Estos dos métodos los veremos en la implementación final de la inserción en el siguiente apartado.

Métodos CRUD repositorio autores

public void AddAutor(Autor autor)
{
    using SqlConnection con = new SqlConnection(connectionString);
    con.Open();
    SqlCommand cmdAutor = new SqlCommand("INSERT INTO Autor VALUES(@Nombre, @Apellidos)", con);
    cmdAutor.Parameters.AddWithValue("@Nombre", autor.Nombre);
    cmdAutor.Parameters.AddWithValue("@Apellidos", autor.Apellido);

    try
    {
        cmdAutor.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        /// Gestionar la excepción
    }

    con.Close();
}

public Autor GetAutorById(int id)
{
    Autor autor = new Autor();

    try
    {
        using SqlConnection con = new SqlConnection(connectionString);
        con.Open();

        
        // Obtenemos lista del autor, tenga o no artículos
        SqlCommand cmd = new SqlCommand("SELECT DISTINCT ArticuloId, Autor.Id As AutorId, Nombre, Apellidos, Titulo, Contenido, FechaCreacion FROM AutorArticulo RIGHT JOIN Autor ON AutorArticulo.AutorId = Autor.Id LEFT JOIN Articulo ON AutorArticulo.ArticuloId = Articulo.Id WHERE Autor.Id = @AutorId", con);
        cmd.Parameters.AddWithValue("@AutorId", id);

        SqlDataReader reader = cmd.ExecuteReader();
        List<Articulo> articulos = new List<Articulo>();

        using (reader)
        {
            while (reader.Read())
            {
                autor.Id = (int)reader["AutorId"];
                autor.Nombre = (string)reader["Nombre"];
                autor.Apellido = (string)reader["Apellidos"];

                // Si no tiene artículos, pasar a la siguiente fila
                if (reader["ArticuloId"] == DBNull.Value)
                    continue;

                Articulo articulo = new Articulo();
                articulo.Id = (int)reader["ArticuloId"];
                articulo.Titulo = (string)reader["Titulo"];
                articulo.Contenido = (string)reader["Contenido"];
                articulo.FechaCreacion = (DateTime)reader["FechaCreacion"];
                articulos.Add(articulo);
            }

            autor.ArticulosEscritos = articulos;
        }

        con.Close();
    }
    catch (Exception ex)
    {
        /// Gestionar la excepción
    }

    return autor;
}

public IList<Autor> GetAllAutores()
{
    List<Autor> autores = new List<Autor>();
    Dictionary<Autor, List<Articulo>> articulosPorAutor = new Dictionary<Autor, List<Articulo>>(); 
    try
    {
        using SqlConnection con = new SqlConnection(connectionString);                
        con.Open();


        // Obtenemos lista de todos los autores, tengan o no artículos
        SqlCommand cmd = new SqlCommand("SELECT DISTINCT ArticuloId, Autor.Id As AutorId, Nombre, Apellidos, Titulo, Contenido, FechaCreacion FROM AutorArticulo RIGHT JOIN Autor ON AutorArticulo.AutorId = Autor.Id LEFT JOIN Articulo ON AutorArticulo.ArticuloId = Articulo.Id", con);
        SqlDataReader reader = cmd.ExecuteReader();

        using (reader)
        {
            while (reader.Read())
            {
                Autor autor = new Autor();
                autor.Id = (int)reader["AutorId"];
                autor.Nombre = (string)reader["Nombre"];
                autor.Apellido = (string)reader["Apellidos"];

                // Si no tiene artículos, añadir a la lista y pasar a la siguiente fila
                if (reader["ArticuloId"] == DBNull.Value)
                {
                    autores.Add(autor);
                    continue;
                }
 
                Articulo articulo = new Articulo();
                articulo.Id = (int)reader["ArticuloId"];
                articulo.Titulo = (string)reader["Titulo"];
                articulo.Contenido = (string)reader["Contenido"];
                articulo.FechaCreacion = (DateTime)reader["FechaCreacion"];

                // Verificamos si el autor está ya en la lista
                if (autores.Any(a => a.Id == autor.Id))
                {
                    // Añadimos artículo a sus artículos escritos
                    autores.Where(a => a.Id == autor.Id)
                        .FirstOrDefault().ArticulosEscritos
                        .Add(articulo);
                }
                else
                {
                    autor.ArticulosEscritos.Add(articulo);
                    autores.Add(autor);
                }
                          
            }
        }

        con.Close();
    }
    catch (Exception ex)
    {
        /// Gestionar la excepción
    }

    return autores;
}

public void UpdateAutor(Autor autor)
{
    using SqlConnection con = new SqlConnection(connectionString);
    con.Open();
    SqlCommand cmdAutor = new SqlCommand("UPDATE Autor SET Nombre = @Nombre, Apellidos = @Apellidos WHERE Id = @AutorId", con);
    cmdAutor.Parameters.AddWithValue("@AutorId", autor.Id);
    cmdAutor.Parameters.AddWithValue("@Nombre", autor.Nombre);
    cmdAutor.Parameters.AddWithValue("@Apellidos", autor.Apellido);

    try
    {
        cmdAutor.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        /// Gestionar la excepción
    }

    con.Close();
}

public void DeleteAutorById(int id)
{
    try
    {
        using SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand("DELETE FROM Autor WHERE Id = @AutorId", con);
        cmd.Parameters.AddWithValue("@AutorId", id);
        cmd.Connection = con;
        cmd.ExecuteNonQuery();
        con.Close();
    }
    catch (Exception ex)
    {
        /// Gestionar la excepción
    }
}

Métodos CRUD repositorio artículos

public void AddArticulo(Articulo articulo)
{
    using SqlConnection con = new SqlConnection(connectionString);
    con.Open();
    SqlTransaction transaction = con.BeginTransaction();

    SqlCommand cmdArticulo = new SqlCommand("INSERT INTO Articulo OUTPUT INSERTED.Id VALUES(@Titulo, @Contenido, @Fecha) SELECT SCOPE_IDENTITY()", con);
    cmdArticulo.Transaction = transaction;
    cmdArticulo.Parameters.AddWithValue("@Titulo", articulo.Titulo);
    cmdArticulo.Parameters.AddWithValue("@Contenido", articulo.Contenido);
    cmdArticulo.Parameters.AddWithValue("@Fecha", DateTime.Now);

    SqlCommand cmdAutorArticulo = new SqlCommand("INSERT INTO AutorArticulo VALUES(@AutorId, @ArticuloId)", con);
    cmdAutorArticulo.Transaction = transaction;
    cmdAutorArticulo.Parameters.AddWithValue("@AutorId", articulo.Autor.Id);

    try
    {
        int idArticulo = (int)cmdArticulo.ExecuteScalar();
        cmdAutorArticulo.Parameters.AddWithValue("@ArticuloId", idArticulo);
        cmdAutorArticulo.ExecuteNonQuery();
        transaction.Commit();
    }
    catch (Exception ex)
    {
        /// Gestionar la excepción y deshacer inserciones para evitar fallos en la persistencia de los datos
        transaction.Rollback();
    }

    con.Close();
}

public Articulo GetArticuloById(int id)
{
    Articulo articulo = new Articulo();

    try
    {
        using SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT ArticuloId, AutorId, Nombre, Apellidos, Titulo, Contenido, FechaCreacion FROM AutorArticulo JOIN Autor ON AutorArticulo.AutorId = Autor.Id JOIN Articulo ON AutorArticulo.ArticuloId = Articulo.Id WHERE ArticuloId = @IdArticulo", con);
        cmd.Parameters.AddWithValue("@IdArticulo", id);
        SqlDataReader reader = cmd.ExecuteReader();
        using (reader)
        {
            while (reader.Read())
            {
                Autor autor = new Autor();
                articulo.Id = (int)reader["ArticuloId"];
                articulo.Titulo = (string)reader["Titulo"];
                articulo.Contenido = (string)reader["Contenido"];
                articulo.FechaCreacion = (DateTime)reader["FechaCreacion"];
                autor.Id = (int)reader["AutorId"];
                autor.Nombre = (string)reader["Nombre"];
                autor.Apellido = (string)reader["Apellidos"];
                articulo.Autor = autor;
            }
        }

        con.Close();            
    }
    catch (Exception ex)
    {
        /// Gestionar la excepción
    }

    return articulo;
}

public IList<Articulo> GetAllArticulos()
{
    List<Articulo> articulos = new List<Articulo>();

    try
    {
        using SqlConnection con = new SqlConnection(connectionString);                
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT ArticuloId, AutorId, Nombre, Apellidos, Titulo, Contenido, FechaCreacion FROM AutorArticulo JOIN Autor ON AutorArticulo.AutorId = Autor.Id JOIN Articulo ON AutorArticulo.ArticuloId = Articulo.Id", con);
        SqlDataReader reader = cmd.ExecuteReader();

        using (reader)
        {
            while (reader.Read())
            {
                Articulo articulo = new Articulo();
                Autor autor = new Autor();
                articulo.Id = (int)reader["ArticuloId"];
                articulo.Titulo = (string)reader["Titulo"];
                articulo.Contenido = (string)reader["Contenido"];
                articulo.FechaCreacion = (DateTime)reader["FechaCreacion"];
                autor.Id = (int)reader["AutorId"];
                autor.Nombre = (string)reader["Nombre"];
                autor.Apellido = (string)reader["Apellidos"];
                articulo.Autor = autor;
                articulos.Add(articulo);
            }
        }

        con.Close();
    }
    catch (Exception ex)
    {
        /// Gestionar la excepción
    }

    return articulos;
}

public void UpdateArticulo(Articulo articulo)
{
    using SqlConnection con = new SqlConnection(connectionString);
    con.Open();
    SqlTransaction transaction = con.BeginTransaction();

    SqlCommand cmdArticulo = new SqlCommand("UPDATE Articulo SET Titulo = @Titulo, Contenido = @Contenido WHERE Id = @IdArticulo", con);
    cmdArticulo.Transaction = transaction;
    cmdArticulo.Parameters.AddWithValue("@IdArticulo", articulo.Id);
    cmdArticulo.Parameters.AddWithValue("@Titulo", articulo.Titulo);
    cmdArticulo.Parameters.AddWithValue("@Contenido", articulo.Contenido);

    SqlCommand cmdAutorArticulo = new SqlCommand("UPDATE AutorArticulo SET AutorId = @AutorId WHERE ArticuloId = @IdArticulo", con);
    cmdAutorArticulo.Transaction = transaction;
    cmdAutorArticulo.Parameters.AddWithValue("@AutorId", articulo.Autor.Id);
    cmdAutorArticulo.Parameters.AddWithValue("@IdArticulo", articulo.Id);

    try
    {
        cmdArticulo.ExecuteNonQuery();
        cmdAutorArticulo.ExecuteNonQuery();
        transaction.Commit();
    }
    catch (Exception ex)
    {
        /// Gestionar la excepción y deshacer inserciones para evitar fallos en la persistencia de los datos
        transaction.Rollback();
    }

    con.Close();

}

public void DeleteArticuloById(int id)
{
    try
    {
        using SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand("DELETE FROM Articulo WHERE Id = @IdArticulo", con);
        cmd.Parameters.AddWithValue("@IdArticulo", id);
        cmd.ExecuteNonQuery();
        con.Close();
    }
    catch (Exception ex)
    {
        /// Gestionar la excepción
    }
}