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] 

27 comentarios:

  1. mm cual seria la principal diferencia entre PersonCriteriay PersonEntity
    ya que son muy similares y no logre estabelcer la diferencia entre ambas clases....

    ResponderEliminar
  2. hola Ruben

    Puede que sean similares lo cual no quiere decir que esten incorrecto, todo depende de que tan complejo y variado es el filtro que vas a implementar sabre la entidad.

    Aunque sean iguales no definas la entidad de filtro a la entidad del negocio porque sus responsabilidades son muy distintas

    Veras un claro ejemplo cuando debes pasarle el curso a la entidad para filtrar, pero la entidad de negocio no posee esta informacion

    Tambien en la definicion de rangos de fecha que claramente la entidad de negocio no requiere, pero el filtro si

    saludos

    ResponderEliminar
  3. hola leandro

    me gustaria que me ayudes en este caso:

    1 tengo un textbox y un boton luego debajo hay cuatro textbox, quiero que en el textbox que esta al lado del boton yo le introdusca un nombre y que cuando purse el boton este me ponga el nombre en cualquiera de los 4 textbox que estan debajo.

    ResponderEliminar
  4. hola Joel

    que relacion tiene la pregunta con los filtros que plantea el articulo ?

    lo que planteas es bastante simple
    solo validas los textbox si tienes contenido o no y pones el valor alli

    private void button1_click(..){

    if(string.IsNullOrEmpty(TextBox2.Text)){
    TextBox2.Text = TextBox1.Text;
    return;
    }

    if(string.IsNullOrEmpty(TextBox3.Text)){
    TextBox3.Text = TextBox1.Text;
    return;
    }


    if(string.IsNullOrEmpty(TextBox4.Text)){
    TextBox4.Text = TextBox1.Text;
    return;
    }

    if(string.IsNullOrEmpty(TextBox5.Text)){
    TextBox5.Text = TextBox1.Text;
    return;
    }

    }

    como veras validas cual esta vacion y poens el contenido alli

    salidos

    ResponderEliminar
  5. hola leandro

    no tiene ninguna relacion, lo que pasa es que estaba buscando en el blog donde hacerte una pregunta aparte y no encontre donde solo eso

    saludos

    ResponderEliminar
  6. Buen Ejemplo, me fue de mucha ayuda !!! la mayoría de las veces que necesito ayuda sobre un caso en programación lo encuentro en tu blog.... realmente son muy buenos los articulos sobre todo por los ejemplos y la explicación del código.... Felicidades, sigue apoyando a la comunidad...

    ResponderEliminar
  7. Hola Lenadro!
    Soy Hugo, sabes tengo una duda, lo que pasa es que estoy tratando de filtrar un gridview que contiene datos de una base, pero quiero que al escribir sobre un texbox, los datos del gridview bayan cambiado segun lo que se escriba.
    ME gustaria mucho que me ayudaras porfavor.
    GRacias..........

    ResponderEliminar
  8. hola Hugo

    la aplicacion es web o winforms ?
    porque dices que se trata de un gridview o sea es asp.net

    si es web filtrar mientras se escribe puede no ser muy recomendable ya que cada cambio realizara un postback y recarga

    porque no dejas que la persona escriba lo que quiere buscar y de ultima detectas que se presione enter o algun boton para ahcer efectiva la busqueda

    saludos

    ResponderEliminar
  9. hola Leandro!
    Efectivamente es web, con asp.net
    y pues si me gustaria que cada vez que se teclee, cambie el gridview, ya lo de la recarga pues lo manejare con ajax, para qu eno se note mucho.
    Agradeceria me ayudes.
    Saludos.

    ResponderEliminar
  10. hola Hugo

    entonces solo seria definri el evento TextChanged del textbox asignando ademas el AutoPostBack en true, capturando el evento simplemente usas lo que tomas de alli para el LIKE del WHERE del SELECT que filtraria los datos

    saludos

    ResponderEliminar
  11. Hola Leandro!
    Sabes, ahora me surgio otro problema,
    Me gustaria saber si aun HiddenField
    se le puede hacer un postback.
    Gracias
    Saludos.

    ResponderEliminar
  12. Buenas tardes Leandro, tengo una consulta.:
    He estado viendo su blog y los ejemplos que Ud. propone que estoy seguro a muchos nos han sido de utilidad en gran ayuda, acá la consulta por que Ud. Utiliza métodos static en la mayoría de sus ejemplos hablo de N-Capas con Entity Frameword por ejemplo.
    Por lo cual me puse a leer un poco y dice que estos dan mayor velocidad a la aplicación ¿Es correcto esta interrogante?
    Otra Ud. siempre utiliza el bloque Using para cada inyección SQL
    También estuve leyendo algo de esto me dice que el bloque using te garantiza abrir, cerrar y librera memoria por las inyecciones SQL que uno realice sin nada más que decir me despido gracias.

    ResponderEliminar
  13. hola Roberto

    no creo que usar static haga mas rapido o lento el acceso, simplemente es una forma de definir la capa, en este caso uso static para ahcer directo la invocaicon, pero se podria usar instancias con algun framework de IoC (invertion of Control) en ese caso si se usan instancias

    el using de la conexion no tiene nada que ver con la inyeccion de sql, como bien comentaste el using se usa para asegurar que se deconecte y destruya los objetos de forma correcta
    la inyeccion de sql se evita usando parametros en la query

    saludos

    ResponderEliminar
  14. Leandro la verdad es que no soy muy fans de poner código SQL en la aplicación, me preguntaba si hay forma de realizar el mismo filtro usando directamente el EntityFramework

    ResponderEliminar
  15. hola Luchex

    estoy en la duda si permitira la conversion a sql de una query linq que use alfo como ser

    var result = from item in context.tabla
    where ((valor == null) || (item.propiedad == valor))
    select item;

    no estoy seguro si ese valor == null lo va a tomar, seria cuestion de probarlo

    saludos

    ResponderEliminar
  16. Excelente la explicacion, muy buen ejemplo, pero eso es a nivel de codigo SQL, yo antes de leer tu post pensaba hacerlo a nivel de lenguaje C# y elegir un select por cada if, al final concatenar todo el enunciado en un string.

    ResponderEliminar
  17. hola Omar

    la verdad concatenar string no lo recomendaria no queda muy bueno en el codigo, se termina armando una ensalada de codigo para armar ese string

    ademas de tener problemas para definir correctamente los tipos de datos
    por eso usar parametros es una mejor opcion

    saludos

    ResponderEliminar
  18. Hola Leandro muy bueno y util este post,si se quisiera hacer con menos datos para comprender su funcionamiento,digamos sacando la clase personentity y los respectivos componentes ,que segun interpreto son los que estan comprendidos en el groupbox que dice solo instituciones. Que partes se deberia quitar de lo que hay en la consulta sql donde filtras? y en esta linea PersonDAL.Select(filter); que le pasas como datasource a la grilla,que seria PerdonDAL ,un datatable? Espero que me puedas ayudar ya que busco realizar algo similar ,que sean opcionales los filtros,contemplando los respectivos null que puedan llegar a quedar en algun txt o datetimepicker.
    Saludos desde Argentina.

    ResponderEliminar
  19. hola Andres

    si la idea es filtrar por menos campos deberias quitar propiedades de la clase PersonCriteria, o sea si solo vas a filtrar por el nombre y apellido solo dejarias esas dos propiedades y quitas el resto
    con eso podrias reducir la parte del WHERE en la query sql para pasar solo esos dos parametro

    el PersonaDAL no es ningun datatable, es una clase que "representa" la capa de datos, lo puse entre comillas porque alli no esta del todo como una capa ya que es un ejemplo simple, pero la idea seria esa, es una clase que tiene la responsabilidad de interactuar con la db

    saludos

    ResponderEliminar
  20. Muchisimias gracias por su aporte.
    me surgio un problema al quererlo implementar para una BD Mysql, el problema es que no me compara el ((@FirstName IS NULL) OR (P.FirstName LIKE '%' + @FirstName + '%'))
    lo e cambiado para la forma mysql peo no resulta, sera que con mysql no c puede leandro.
    mis agradecimientos

    ResponderEliminar
  21. hola VICTOR

    con mysql el like lo debes implementar con el CONCAT

    consulta select a mysql

    saludos

    ResponderEliminar
  22. ESTA ES MI CONSULTA mysql:
    string sql = "SELECT * FROM registrados WHERE"+
    "((?nombre IS NULL) OR (Nombre LIKE CONCAT ('%' ?nombre '%'))) AND"+
    "((?apellido IS NULL) OR (Apellido LIKE CONCAT ('%' ?apellido '%')))";

    este es la exception qe sale:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL '%')))' at line 1

    ResponderEliminar
  23. Solucionado muchas gracias leandro por dar conocimiento sin recibir nada a cambio. Saludos.
    solucion:
    string sql = "SELECT * FROM registrados WHERE"+
    "((?nombre IS NULL) OR (Nombre LIKE CONCAT ('%', ?nombre , '%'))) AND"+
    "((?apellido IS NULL) OR (Apellido LIKE CONCAT ('%' , ?apellido , '%')))";

    ResponderEliminar
  24. Hola Leandro he aprendido mucho con tus cursos y tutoriales pero me gustaría saber como hacer para validar un parámetro de tipo int en la siguiente linea

    cmd.Parameters.AddWithValue("@long", string.IsNullOrEmpty(Obj.NOMBREARCHIVO) ? (object)DBNull.Value : Obj.long);

    de antemano muchas gracias

    ResponderEliminar
    Respuestas
    1. hola
      Lo que noto es que Obj.long no deberias usarlo, sino definir una propiedad que sea del tipo long
      Si usas long es una palabra reservada por c#, porque no le cambias de nombre a esa propiedad
      saludos

      Eliminar
  25. hola leandro, te ruego me muestres un ejemplo de como calcular la celda mediana de un cierto numero de datos que yo ingrese al datagridview manualmente, es decir sin usar sql, y luego como filtrar la celda mediana (el numero de celda que me resulte del calculo de la celda mediana). muchos saludos...(la fórmula para calcular la mediana de un grupo de datos es: si n es par 1/2(n/2 + (n+2)/2), y si n es impar (n+1)/2), n es número de datos.

    ResponderEliminar
    Respuestas
    1. hola
      Lo que no mencionas es como asignas los datos en el grid, si lo haces desde codigo o si asignas el DataSource
      El tema es que deberias definir una columnas extra para poder realizar el calculo, si usas un datatable podrias crear una columna calculada
      Calculated Columns in .NET DataTables (C#)
      saludos

      Eliminar