n-Layer - SchoolManager - Herencia y navegación de entidades relacionadas (1/2)

 

Introducción

---

El diseño de una estructura en capas mucha veces requiere que se trabajen con objetos complejos, por lo general una entidad no tienes solo propiedades simples, algunas puedes representar la relación con otras entidades, es mas puede que la entidad en si sea solo una parte de una mayor

En esta oportunidad nos centraremos justamente en dos aspectos:

• representar una herencia, aquí abordaremos no solo como recuperar una entidad definida con un padre, sino también como persistirla
• cargar entidades relacionadas,

Para esto contaremos con la entidad Instructor en el modelo de administración de la base de datos de una escuela.

El modelo de datos que usaremos define la tabla Persona, pero en la misma tabla se pueden abstraer otras dos entidades Instructor y Alumno.

La implementación de la herencia en este caso lleva el nombre de “tabla por subclase”, en donde la relación uno a uno con la tabla OfficeAssigment determina si es un instructor o un Alumno, en este caso no se usa ningún campo discriminador para el tipo, la relación actúa como medio para determinarlo. Si hay una relación con la tabla OfficeAssigment  será un instructor, sino lo hay será un alumno.

 

Herencia de entidades (Recuperar entidad) 

---

Las entidades intervinientes en este modelo se representan en la siguiente imagen:

 

A simple vista se puede observar que la entidad Instructor hereda de persona, la pregunta que trataremos de responder es como definir un modelo de persistencia para esta entidad.

Empezaremos analizando la clase InstructorRepository la cual cuenta con el método:

public static InstructorEntity GetByKey(int id)
{
    InstructorEntity item = null;

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {
        conn.Open();

        string query = @"SELECT P.PersonID, 
                                P.LastName, 
                                P.FirstName, 
                                P.HireDate, 
                                P.EnrollmentDate, 
                                OA.Location
                        FROM Person P 
                            INNER JOIN OfficeAssignment OA 
                            ON P.PersonID = OA.InstructorID
                        WHERE P.PersonID = @id
                        ORDER BY P.PersonID";

        SqlCommand cmd = new SqlCommand(query, conn);
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;

        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        if (reader.Read())
        {
            item = ConvertInstructor(reader);
        }

    }

    return item;
}

private static InstructorEntity ConvertInstructor(IDataReader reader)
{
    InstructorEntity item = new InstructorEntity();

    item.PersonID = Convert.ToInt32(reader["PersonID"]);
    item.LastName = Convert.ToString(reader["LastName"]);
    item.FirstName = Convert.ToString(reader["FirstName"]);

    item.HireDate = reader["HireDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader["HireDate"]);
    
    item.Location = Convert.ToString(reader["Location"]);

    return item;


}

Recuperar una entidad, o una lista de Instructores no parece diferir mucho a como se haría con una entidad simple, en este caso la query involucra tanto la tabla del Instructores como la de Personas, lo que implica usar el INNER JOIN para unir los registros.

Herencia de entidades (Crear entidad)

---

Donde si veremos mayor cambios es al momento de actualizar la entidad, pues requiere impactar las actualizaciones en dos tablas diferentes

Empecemos por crear un nuevo instructor, la clase InstructorRepository contiene el método Save()

Definir un instructor implica varios pasos:

1. crear el registro en la tabla base, en este caso insertar el registro en Persona
2. crear el registro en la tabla OfficeAssignment
3. si la entidad tenia cursos asignados se crea la relación con esto

 

1- Grabar la entidad Persona, esta operación es bien simple, solo implica un INSERT en la tabla y recuperar el id generado.

public static void Save(PersonEntity person)
{
    string sql = @"INSERT INTO Person (
                    LastName,
                    FirstName)
              VALUES (@LastName, 
                    @FirstName);
              SELECT SCOPE_IDENTITY";


    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {
        conn.Open();

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@LastName", person.LastName);
        cmd.Parameters.AddWithValue("@FirstName", person.FirstName);

        person.PersonID = Convert.ToInt32(cmd.ExecuteScalar());

    }
}

2- Aquí no solo se actualiza los datos concretos del instructor en la tabla padre, sino que además se inserta en la tabla concreta que define el tipo, en esta operación se hace uso del mismo id que se recupero al crear la entidad padre.

public static void Save(InstructorEntity instructor)
{

    PersonRepository.Save((PersonEntity)instructor);

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {
        conn.Open();

        //
        // Actualiza los campos de la tabla Persona 
        // con el campo que define solo el instructor
        //
        string sqlUpdateP = @"UPDATE Person 
                            SET HireDate = @HireDate 
                            WHERE PersonID = @PersonID";

        using (SqlCommand cmd = new SqlCommand(sqlUpdateP, conn))
        {
            cmd.Parameters.AddWithValue("@HireDate", instructor.HireDate.HasValue ? instructor.HireDate.Value : (object)DBNull.Value);
            cmd.Parameters.AddWithValue("@PersonID", instructor.PersonID);

            cmd.ExecuteNonQuery();
        }

        //
        // Inserta el registro que define al instructor concretamente
        //
        string spInsertOA = @"INSERT OfficeAssignment (InstructorID, Location) 
                                   VALUES (@InstructorID, @Location)";

        using (SqlCommand cmd = new SqlCommand(spInsertOA, conn))
        {
            cmd.Parameters.AddWithValue("@InstructorID", instructor.PersonID);
            cmd.Parameters.AddWithValue("@Location", instructor.Location);

            cmd.ExecuteNonQuery();
        }

    }

    //
    // Se procesa los cursos asignados 
    //
    CourseRepository.RelateWithPerson((PersonEntity)instructor, instructor.Courses);

}

3- En caso de existir entidades relacionadas se realiza la operación de merge entre los datos provenientes de la selección del usuario y los datos existentes en la tabla

Para realizar la tarea de forma simple se elimina toda relación y se procede a crearlas nuevamente, pero si se anima se podría haber utilizado la instrucción MERGE de T-SQL.

En la línea:

//
// Se procesa los cursos asignados 
//
CourseRepository.RelateWithPerson((PersonEntity)instructor, instructor.Courses);

Se invoca la clase responsable de crear la relación entre las entidad persona y cursos.

 

/// <summary>
/// Dada una persona y una lista de cursos se crea la relacion entre las entidades.
/// 
/// Para implementar un merge simple que permita registrar los cursos agregados o eliminados por el usuario, 
/// se realiza se elimina toda la relacion y volverla a insertar 
/// </summary>
/// <param name="person"></param>
/// <param name="courses"></param>
public static void RelateWithPerson(PersonEntity person, List<CourseEntity> courses)
{

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {
        conn.Open();

        //se elimina la relacion existentes
        string sqlDelete = @"DELETE CourseInstructor WHERE PersonID = @PersonID";

        using (SqlCommand cmd = new SqlCommand(sqlDelete, conn))
        {
            cmd.Parameters.AddWithValue("@PersonID", person.PersonID);

            cmd.ExecuteNonQuery();
        }


        //se relaciona los cursos asociados a la entidad 
        string sqlCourseInstructor = @"INSERT CourseInstructor (CourseID, PersonID) 
                                            VALUES (@CourseID, @PersonID)";

        using (SqlCommand cmd = new SqlCommand(sqlCourseInstructor, conn))
        {

            foreach (CourseEntity course in courses)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@CourseID", course.CourseID);
                cmd.Parameters.AddWithValue("@PersonID", person.PersonID);

                cmd.ExecuteNonQuery();
            }
        }

    }


}

Herencia de entidades (Actualizar entidad)

---

La actualización de une entidad que implementar una herencia es muy similar a la creación.

1. actualizar el registro en la tabla base
2. actualizar el registro en la tabla OfficeAssignment
3. si la entidad tenia cursos asignados se crea la relación con esto

 

1- Se invoca al metodo Update() de PersonRepository

public static void Update(PersonEntity person)
{
    string sql = @"UPDATE Person SET
                        LastName = @LastName,
                        FirstName = @FirstName
                    WHERE PersonID = @PersonID";

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {
        conn.Open();

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@LastName", person.LastName);
        cmd.Parameters.AddWithValue("@FirstName", person.FirstName);
        cmd.Parameters.AddWithValue("@PersonID", person.PersonID);

        cmd.ExecuteNonQuery();

    }
}

 

2 – Se actualiza la tabla que define al Instructor, este se define en el método Update() de la clase InstructorRepository.

public static void Update(InstructorEntity instructor)
{
    //
    //Se actualiza el registro de la Persona
    //
    PersonRepository.Update((PersonEntity)instructor);


    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {
        conn.Open();

        //
        // Actualiza los campos de la tabla Persona 
        // con el campo que define solo el instructor
        //
        string sqlUpdateP = @"UPDATE Person 
                                SET HireDate = @HireDate 
                            WHERE PersonID = @PersonID";

        using (SqlCommand cmd = new SqlCommand(sqlUpdateP, conn))
        {
            cmd.Parameters.AddWithValue("@HireDate", instructor.HireDate.HasValue ? instructor.HireDate.Value : (object)DBNull.Value);
            cmd.Parameters.AddWithValue("@PersonID", instructor.PersonID);

            cmd.ExecuteNonQuery();
        }

        //
        // Actualiza el registro que define al instructor concretamente
        //
        string sqlUpdateOA = @"UPDATE OfficeAssignment 
                                        SET Location = @Location 
                                   WHERE InstructorID = @InstructorID";

        using (SqlCommand cmd = new SqlCommand(sqlUpdateOA, conn))
        {
            cmd.Parameters.AddWithValue("@InstructorID", instructor.PersonID);
            cmd.Parameters.AddWithValue("@Location", instructor.Location);

            cmd.ExecuteNonQuery();
        }

    }

    //
    // Se procesa los cursos asignados 
    //
    CourseRepository.RelateWithPerson((PersonEntity)instructor, instructor.Courses);

}

3 – Al igual que al crear la entidad se actualizan las relaciones con las demás entidades, en este caso se aplica el mismo código para reflejar la relación con los cursos.

 

Código

---

 

[c#]