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:

 

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