sábado, 17 de septiembre de 2011

Filtros Condicionales (2/2) – Implementar filtros múltiples

 

Introducción


Este artículo representa continuación del anterior

Filtros Condicionales (1/2)

en realidad se podría ver como una extensión, ya que aquí el objetivo consiste en poder aplicar múltiples filtro usando de cursos, y no solo uno como fue en el artículo previo

La interacción en la pantalla, para esta funcionalidad en concreto, esta dada por los siguientes pasos:

image 

Algo que hay que aclarar antes de abordar las soluciones planteadas es que la instrucción IN de sql no soporta de forma directa el uso de parámetros, es por eso que existen actualmente varios caminos a tomar ante esta situación, aquí solo expondré dos de ellos, pero existen algunos otros.

Entre los temas tratados se podrán encontrar

  1. Comunicación entre formularios
  2. Filtro IN, concatenado el string
  3. Filtro por medio de XML

 

1- Comunicación entre formularios


En el formulario de búsqueda (frmBusqueda) encontrar un código como el siguiente

private void btnBuscarCursos_Click(object sender, EventArgs e)
{
    List<CourseEntity> selectedCourses = txtCursos.Tag as List<CourseEntity>;

    using (frmSeleccionarCursos frmcursos = new frmSeleccionarCursos(selectedCourses))
    {
        if (frmcursos.ShowDialog(this) == DialogResult.OK)
        {
            txtCursos.Tag = frmcursos.CursosSeleccionados;
            txtCursos.Text = string.Join(", ", frmcursos.CursosSeleccionados.Select(x => x.Title).ToArray());
        }
    }
}

Como frmSeleccionarCursos se abre de forma modal puede esperarse en el ShowDialog() hasta tanto el Form sea cerrado, cuando esta operación se lleve a cabo y se detecte la aceptación satisfactoria del Form se procede a tomar los cursos seleccionados y asignarlos al control que contendrá la información.

En este caso se hace uso de la propiedad Tag del Textbox para mantener la información seleccionada

En el formulario de selección dispone de una propiedad para que el formulario que lo invoco pueda acceder a la información sin necesidad de recurrir directo de los controles del propio Form. A su vez solo el evento del botón Aceptar es que el cierra el form con una resultado aceptado para procesar la selección.

public List<CourseEntity> CursosSeleccionados
{
    get
    {
        return lstCursosSelected.Items.Cast<CourseEntity>().ToList();
    }
}

private void btnAceptar_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.OK;
}

private void btnCancelar_Click(object sender, EventArgs e)
{
  this.DialogResult = DialogResult.Cancel;
}

 

2 -Filtro IN, concatenado el string


Esta primera implementación se podría decir que es la mas estándar y directa, aunque hay que remarcar que no es la mas bonita.

En la clase PersonDAL se cuenta el método Select()

public static List<PersonEntity> Select(PersonCriteria filter)
{
    string sql = @"SELECT   P.PersonID,
                            P.LastName,
                            P.FirstName,
                            P.HireDate,
                            P.EnrollmentDate
                   FROM Person P
                        LEFT JOIN CourseInstructor CI 
                        ON P.PersonID = CI.PersonID
                   WHERE ((@FirstName IS NULL) OR (P.FirstName LIKE '%' + @FirstName + '%'))
                    AND ((@LastName IS NULL) OR (P.LastName LIKE '%' + @LastName + '%'))
                    AND ((@HireDateFrom IS NULL) OR (P.HireDate >= @HireDateFrom))
                    AND ((@HireDateTo IS NULL) OR (P.HireDate <= @HireDateTo))
                    AND ((@EnrollmentDateFrom IS NULL) OR (P.EnrollmentDate >= @EnrollmentDateFrom))
                    AND ((@EnrollmentDateTo IS NULL) OR (P.EnrollmentDate <= @EnrollmentDateTo))
                    AND ((@Course IS NULL) OR (CI.CourseID IN ({0})))";

    if (filter.Course != null)
    {
        string courseFilter = string.Join(",", filter.Course.ConvertAll(x => x.CourseID.ToString()).ToArray());
        sql = sql.Replace("{0}", courseFilter);
    }

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

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

        SqlCommand cmd = new SqlCommand(sql, conn);

        if (string.IsNullOrEmpty(filter.FirstName))
            cmd.Parameters.AddWithValue("@FirstName", DBNull.Value);
        else
            cmd.Parameters.AddWithValue("@FirstName",  filter.FirstName);

        cmd.Parameters.AddWithValue("@LastName", string.IsNullOrEmpty(filter.FirstName) ? (object)DBNull.Value : filter.LastName);

        cmd.Parameters.AddWithValue("@HireDateFrom", filter.HireDateFrom.HasValue ? filter.HireDateFrom.Value.Date : (object)DBNull.Value);
        cmd.Parameters.AddWithValue("@HireDateTo", filter.HireDateTo.HasValue ? filter.HireDateTo.Value.Date : (object)DBNull.Value);

        cmd.Parameters.AddWithValue("@EnrollmentDateFrom", filter.EnrollmentDateFrom.HasValue ? filter.EnrollmentDateFrom.Value.Date : (object)DBNull.Value);
        cmd.Parameters.AddWithValue("@EnrollmentDateTo", filter.EnrollmentDateTo.HasValue ? filter.EnrollmentDateTo.Value.Date : (object)DBNull.Value);

        cmd.Parameters.AddWithValue("@Course", filter.Course == null ? (object)DBNull.Value : "");

        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            list.Add(LoadPerson(reader));
        }

        return list;
    }

}

El mismo sigue todas las reglas mencionadas en la parte 1 del articulo, solo que filtro de cursos tiene una particularidad.

Es preciso notar las líneas 19-23, en estas es cuando se une al string de la consulta principal, la lista de cursos seleccionados.

La línea 46, sigue representando la anulación o no del filtro del cursos, esto es necesario en caso de no enviarse ningún ítem en la selección.

 

3 -Filtro por medio de XML


Si bien esta implementación no es estandar para todas las base de datos, ya que requiere de soporte para xml, si es la que mejor cierra en cuanto al uso de parámetros.

En este caso la lista de cursos seleccionado es convertida a un xml, el cual se asigna al parámetro para luego unirlo al join de la consulta.

public static List<PersonEntity> SelectByXml(PersonCriteria filter)
        {
            string sql = @"
                           DECLARE @idoc  int
                           EXEC sp_xml_preparedocument @idoc OUTPUT, @Course

                           SELECT   P.PersonID,
                                    P.LastName,
                                    P.FirstName,
                                    P.HireDate,
                                    P.EnrollmentDate
                           FROM Person P
                                LEFT JOIN CourseInstructor CI 
                                ON P.PersonID = CI.PersonID
                                    LEFT JOIN OPENXML(@idoc, '/courses/course', 2)
                                    WITH (id  int 'text()') AS CL ON CI.CourseID = CL.id 
                           WHERE ((@FirstName IS NULL) OR (P.FirstName LIKE '%' + @FirstName + '%'))
                            AND ((@LastName IS NULL) OR (P.LastName LIKE '%' + @LastName + '%'))
                            AND ((@HireDateFrom IS NULL) OR (P.HireDate >= @HireDateFrom))
                            AND ((@HireDateTo IS NULL) OR (P.HireDate <= @HireDateTo))
                            AND ((@EnrollmentDateFrom IS NULL) OR (P.EnrollmentDate >= @EnrollmentDateFrom))
                            AND ((@EnrollmentDateTo IS NULL) OR (P.EnrollmentDate <= @EnrollmentDateTo))
                            AND ((@Course IS NULL) OR (CL.id IS NOT NULL))";


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

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

                SqlCommand cmd = new SqlCommand(sql, conn);

                if (string.IsNullOrEmpty(filter.FirstName))
                    cmd.Parameters.AddWithValue("@FirstName", DBNull.Value);
                else
                    cmd.Parameters.AddWithValue("@FirstName", filter.FirstName);

                cmd.Parameters.AddWithValue("@LastName", string.IsNullOrEmpty(filter.FirstName) ? (object)DBNull.Value : filter.LastName);

                cmd.Parameters.AddWithValue("@HireDateFrom", filter.HireDateFrom.HasValue ? filter.HireDateFrom.Value.Date : (object)DBNull.Value);
                cmd.Parameters.AddWithValue("@HireDateTo", filter.HireDateTo.HasValue ? filter.HireDateTo.Value.Date : (object)DBNull.Value);

                cmd.Parameters.AddWithValue("@EnrollmentDateFrom", filter.EnrollmentDateFrom.HasValue ? filter.EnrollmentDateFrom.Value.Date : (object)DBNull.Value);
                cmd.Parameters.AddWithValue("@EnrollmentDateTo", filter.EnrollmentDateTo.HasValue ? filter.EnrollmentDateTo.Value.Date : (object)DBNull.Value);

                if (filter.Course != null)
                {
                    XElement root = new XElement("courses");
                    List<XElement> couseList = filter.Course.ConvertAll(x => new XElement("course", x.CourseID));
                    root.Add(couseList.ToArray());

                    cmd.Parameters.AddWithValue("@Course", root.ToString());
                }
                else
                {
                    cmd.Parameters.AddWithValue("@Course", DBNull.Value);
                }

                
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadPerson(reader));
                }

                return list;
            }

        }

Las líneas 47-58, implementan la conversión a xml de los cursos, esta toman la forma

<courses>
  <course>1045</course>
  <course>1061</course>
</courses>

o sea cada curso seleccionado representa un tag en el xml

La consulta tiene algunas particularidades como ser las dos primeras líneas

DECLARE @idoc  int
EXEC sp_xml_preparedocument @idoc OUTPUT, @Course

cuya finalidad es inicializar el xml que luego es usado en el join

LEFT JOIN OPENXML(@idoc, '/courses/course', 2)
WITH (id  int 'text()') AS CL ON CI.CourseID = CL.id

Aquí el text() representa justamente el contenido del tag, y por medio del /courses/course (el cual es un selector de XPath), se toma cada tag de curso.

 

Para mas información sobre como trabajar el xml en T-SQL, un excelente recurso es el MSDN Library

OPEN XML

Usar OPENXML

Se puede además realizar pruebas puntuales del xml para entender su funcionamiento, por ejemplo

DECLARE  @Course As VARCHAR(1000)
SET  @Course = N'<courses>
  <course>1045</course>
  <course>1061</course>
</courses>'

DECLARE @idoc  int
EXEC sp_xml_preparedocument @idoc OUTPUT, @Course

SELECT * FROM OPENXML(@idoc, '/courses/course', 2)
          WITH (id  int 'text()')

Estas podrían ser ejecutadas en el Sql Server Management Studio

 

Links Útiles


Arrays and Lists in SQL Server 2000 and Earlier

 

Ejemplo de Código


La base de datos fue creada con Sql Server Express 2008 R2, en caso de tener problemas con al misma en el proyecto “DataAccess” esta la carpeta “script” con el .sql que puede usar para crear estructura y datos.

[C#] 
 

domingo, 11 de septiembre de 2011

Filtros Condicionales (1/2)

 

Introducción


Al desarrollar una aplicación un aspecto del cual seguramente habrá que pensar es como otorgar al usuario información que el sea útil y simple de analizar.

En este punto juega un papel fundamental como diseñar los filtros aplicado a las entidades, para poder se lo mas preciso posible en la búsqueda.

Es por eso que en este artículo se plantea la implementación de búsqueda por medio de clases que denominare Criteria, ya que esta serán las encargadas de proporcionar los datos seleccionados por el usuario en la pantalla.

Como punto ventajoso de esta técnica se podría mencionar que la clase evita tener que redefinir los parámetros del método de búsqueda cada vez que se quiera extender la funcionalidad. Esto es muy importante si se tiene la idea de aplicar algún patrón como ser el Repository.

Los requerimientos cambiantes en las aplicaciones hacen que en un primer momento un filtro simple de dos campos cubra la necesidad, pero el uso diario por parte del usuario ira cambiando esta visión, solicitando agregar nuevo filtros, el uso de una clase como témplate de filtro facilita esta tarea ya que el método no cambia en sus parámetros, solo la clase Criteria se vera afectada y por supuesto la query que aplique el filtro.

 

Estructura de la solución


Analicemos un poco como esta estructurado el ejemplo.

 image

Solo son dos capas, una de acceso a datos y otro de presentación, se realizo de esta forma para no complicar el desarrollo del ejemplo, porque aquí no se pretendía mostrar una arquitectura completa, sino solo apuntar a entender como aplicar filtros de forma correcta.

Es muy importante remarcar la clase de nombre PersonCriteria, la cual actuara solo cuando los filtros entran en accion, y la clase PersonEntity, quien representa de la entidad de negocio.

Analicemos la diferencia entre las dos entidades:

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

    public DateTime? EnrollmentDateFrom { get; set; }
    public DateTime? EnrollmentDateTo { get; set; }

    public DateTime? HireDateFrom { get; set; }
    public DateTime? HireDateTo { get; set; }

    public CourseEntity Curse { get; set; }
}
public class PersonEntity
{
    public int PersonID {get; set;}
    public string LastName { get; set; } 
    public string FirstName { get; set; } 

    public DateTime? HireDate { get; set; }
    public DateTime? EnrollmentDate { get; set; } 
}

Las propiedades de cada clase tienen unas cuantas diferencias porque sus responsabilidades y lo que representantas son diferentes.

Análisis de la Presentación


La pantalla que se presentara al usuario incluirá varios filtros que actúan de forma combinada.

image

El punto clave en al presentación será la lógica encargada de cargar el criterio de búsqueda:

private void btnFiltrar_Click(object sender, EventArgs e)
{
    PersonCriteria filter = new PersonCriteria()
    {
        FirstName = txtNombre.Text,
        LastName = txtApellido.Text,
        EnrollmentDateFrom = chkFechaInscripcionDesde.Checked ? (DateTime?)dtpFechaInscripcionDesde.Value : null,
        EnrollmentDateTo = chkFechaInscripcionHasta.Checked ? (DateTime?)dtpFechaInscripcionHasta.Value : null,
        HireDateFrom = chkFechaContratacionDesde.Checked ? (DateTime?)dtpFechaContratacionDesde.Value : null,
        HireDateTo = chkFechaContratacionHasta.Checked ? (DateTime?)dtpFechaContratacionHasta.Value : null,
        Curse = Convert.ToInt32(cmbCourse.SelectedValue)== -1 ? null : new CourseEntity() { CourseID = Convert.ToInt32(cmbCourse.SelectedValue) }
    };

    dgvPersonList.DataSource = PersonDAL.Select(filter);

}

La presentación conoce como asignar la propiedad con el dato correcto, es porque eso que allí se observan validaciones para determinar si asignar el dato o no.

 

Acceso a Datos


Esta es la capa con mayor responsabilidad, lógicamente porque será la encargada de aplicar el filtro, pero todo se reduce a un simple truco en la query que habilita o no el filtro en el WHERE

 

public static List<PersonEntity> Select(PersonCriteria filter)
{
    string sql = @"SELECT   P.PersonID,
                            P.LastName,
                            P.FirstName,
                            P.HireDate,
                            P.EnrollmentDate
                   FROM Person P
                        LEFT JOIN CourseInstructor CI 
                        ON P.PersonID = CI.PersonID
                   WHERE ((@FirstName IS NULL) OR (P.FirstName LIKE '%' + @FirstName + '%'))
                    AND ((@LastName IS NULL) OR (P.LastName LIKE '%' + @LastName + '%'))
                    AND ((@HireDateFrom IS NULL) OR (P.HireDate >= @HireDateFrom))
                    AND ((@HireDateTo IS NULL) OR (P.HireDate <= @HireDateTo))
                    AND ((@EnrollmentDateFrom IS NULL) OR (P.EnrollmentDate >= @EnrollmentDateFrom))
                    AND ((@EnrollmentDateTo IS NULL) OR (P.EnrollmentDate <= @EnrollmentDateTo))
                    AND ((@Course IS NULL) OR (CI.CourseID = @Course))";

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

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

        SqlCommand cmd = new SqlCommand(sql, conn);

        if (string.IsNullOrEmpty(filter.FirstName))
            cmd.Parameters.AddWithValue("@FirstName", DBNull.Value);
        else
            cmd.Parameters.AddWithValue("@FirstName",  filter.FirstName);

        cmd.Parameters.AddWithValue("@LastName", string.IsNullOrEmpty(filter.FirstName) ? (object)DBNull.Value : filter.LastName);

        cmd.Parameters.AddWithValue("@HireDateFrom", filter.HireDateFrom.HasValue ? filter.HireDateFrom.Value.Date : (object)DBNull.Value);
        cmd.Parameters.AddWithValue("@HireDateTo", filter.HireDateTo.HasValue ? filter.HireDateTo.Value.Date : (object)DBNull.Value);

        cmd.Parameters.AddWithValue("@EnrollmentDateFrom", filter.EnrollmentDateFrom.HasValue ? filter.EnrollmentDateFrom.Value.Date : (object)DBNull.Value);
        cmd.Parameters.AddWithValue("@EnrollmentDateTo", filter.EnrollmentDateTo.HasValue ? filter.EnrollmentDateTo.Value.Date : (object)DBNull.Value);

        cmd.Parameters.AddWithValue("@Course", filter.Curse == null ? (object)DBNull.Value: filter.Curse.CourseID);


        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            list.Add(LoadPerson(reader));
        }

        return list;
    }

}

Analicemos una sección pequeña para entenderlo:

((@FirstName IS NULL) OR (P.FirstName LIKE '%' + @FirstName + '%'))

en esta se tienen dos parte, la primera compara el parámetro con NULL, por lo tanto si desde el código .net enviamos un DbNull.Value estaríamos anulando este filtro, porque esta comparación aplicaría siempre para todos los registros.

En la segunda mitad será donde el filtro se aplica, este al tener un valor no se vera afectado por la primer sección.

Además quise mostrar que existen varias formas desde código de asignar el parámetro, es por eso que la línea 27 la asignación del parámetro se efectúa con un if completo, mientras que el resto lo hace en sola linea, esto fue solo para remarcar que no hay una única forma de asignar el parámetro, si la comparación en una línea queda compleja (o poco clara) se puede pasar a varias líneas.

Este es todo el truco, no es nada difícil de implementar, y conocerlo puede ayudar brindar al usuario filtros mas potentes que agreguen valor a la aplicación.

Ejemplo de Código


La base de datos fue creada con Sql Server Express 2008 R2, en caso de tener problemas con al misma en el proyecto “DataAccess” esta la carpeta “script” con el .sql que puede usar para crear estructura y datos.

 

[C#] 
[VB.NET]