viernes, 15 de marzo de 2013

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

 

Introducción


Se continua con la explicación que comenzó en:

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

En esta ocasión nos centraremos en como recuperar entidades relacionadas o asociadas, usando ado.net

Vamos a enfocarnos en el vinculo que tiene un un instructores y sus cursos.

image

En el articulo anterior se pudo comprender como se persisten las relaciones en conjunto con los concepto de herencia, pero en este caso vamos a recuperar la información de una entidad y sus relaciones todo en la misma operación.

Seguramente seria una buena idea crear dos funcionalidades, una que permita recuperar la entidad sin sus relaciones GetByKey(), o sea la entidad pura, y otro método en donde una única query recupere y arme la estructura jerárquica GetByKeyWithRelations().

 

Recuperar entidad y relaciones


Para poder llevar a cabo esta tarea se va a necesitar de la ayuda de linq, este permitirá trabajar una entidad simple para darle estructura.

Comenzaremos definiendo una entidad plana que contenga las propiedades tanto del Instructor como del curso.

public class InstructorComposed
{
    public int PersonID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }

    //Fecha de contratación
    public DateTime? HireDate { get; set; }
    public string Location { get; set; }

    public int CourseID { get; set; }
    public string Title { get; set; }
}

Pero además se deberá tener la entidad que nos interesa devolver como respuesta, esta si tiene estructura, o sea una lista de cursos

public abstract class PersonEntity
{
    public int PersonID {get; set;}

    public string LastName {get; set;}
    public string FirstName {get; set;}
}

public class InstructorEntity : PersonEntity
{
    public InstructorEntity()
    {
        this.Courses = new List<CourseEntity>();
    }

    //Fecha de contratación
    public DateTime? HireDate { get; set; }

    public string Location { get; set; }

    public List<CourseEntity> Courses { get; set; }

}


public class CourseEntity
{
    public int CourseID { get; set; }
    public string Title { get; set; }
}

 

El reto será lograr convertir una entidad de propiedades simples a una de propiedades complejas. Seguramente se preguntaran porque no se recupera esto directo con una query, el tema es que una consulta no devuelve como resultado registros con estructuras anidadas, sino que solo nos proporciona dos dimensiones, filas y columnas.

Se ha creado el método GetByKeyWithRelations() para obtener la entidad Instructor con sus cursos relacionados:

/// <summary>
/// Devuelve el instructor incluyendo las relaciones con las demas entidades
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static InstructorEntity GetByKeyWithRelations(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,
                                C.CourseID,
                                C.Title
                        FROM Person P 
                            INNER JOIN OfficeAssignment OA 
                            ON P.PersonID = OA.InstructorID
                                INNER JOIN CourseInstructor CI 
                                ON CI.PersonID = P.PersonID
                                    LEFT JOIN Course C
                                    ON C.CourseID = CI.CourseID
                        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);

        item = ConvertInstructorWithRelations(reader);
        
    }

    return item;
}

Es importante destacar como en la consulta sql hace uso de un LEFT JOIN para recuperar todos los datos del instructor tengan o no cursos asociados, el SELECT incluirá los campos de la entidad Instructor y también los del Curso, serán coincidentes con la definición de la clase InstructorComposed.

El siguiente paso será procesar los datos y darles formato:

private static InstructorEntity ConvertInstructorWithRelations(IDataReader reader)
{

    List<InstructorComposed> list = new List<InstructorComposed>();

    while(reader.Read())
    {
        InstructorComposed item = new InstructorComposed(){
            PersonID = Convert.ToInt32(reader["PersonID"]),
            LastName = Convert.ToString(reader["LastName"]),
            FirstName = Convert.ToString(reader["FirstName"]),
            HireDate = reader["HireDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader["HireDate"]),
            Location = Convert.ToString(reader["Location"]),

            CourseID = Convert.ToInt32(reader["CourseID"]),
            Title = Convert.ToString(reader["Title"])
        };

        list.Add(item);
    }

    if (list.Count == 0)
        return null;

    InstructorEntity instructor = (from item in list
                                   group item by item.PersonID into g
                                   select new InstructorEntity()
                                   {
                                       PersonID = g.Key,
                                       LastName = g.First().LastName,
                                       FirstName = g.First().FirstName,
                                       HireDate = g.First().HireDate,
                                       Location = g.First().Location,
                                       Courses = g.Select(x=> new CourseEntity()
                                                            {
                                                                CourseID = x.CourseID,
                                                                Title = x.Title
                                                            }).ToList()
                                   }).First();

    return instructor;

}

 

La primer parte es bien conocida, se convierte el reader volcando los datos de los campos a la instancia de la entidad. Pero la segunda parte es la mas interesante, porque es allí donde mediante la utilización de linq que damos estructura al objeto plano que se recupera de la query.

En este caso se hace uso de la capacidad de agrupar que brinda linq para poder juntar todos los cursos que pertenecen al instructor. En el ejemplo solo  se necesito recupera un único instructor pero podría haberse utilizado la misma técnica para trabajar una colección de estos.

 

Código


El código se encuentra en el articulo anterior.

jueves, 14 de marzo de 2013

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.

SNAGHTML248b611

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

image

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:

image

 

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#]