Ordenar datos paginados personalizados (VB)

por Scott Mitchell

Descargar PDF

En el tutorial anterior ha aprendido a implementar la paginación personalizada al presentar datos en una página web. En este tutorial se muestra cómo ampliar el ejemplo anterior para incluir compatibilidad con la ordenación de paginación personalizada.

Introducción

En comparación con la paginación predeterminada, la personalizada puede mejorar el rendimiento de la paginación de los datos por varios órdenes de magnitud, lo que hace que la paginación personalizada sea la opción de implementación de paginación de facto para grandes cantidades de datos. La implementación de la paginación personalizada es más compleja que la de la paginación predeterminada, especialmente al agregar la ordenación a la mezcla. En este tutorial, se ampliará el ejemplo del anterior para incluir compatibilidad con la ordenación y la paginación personalizada.

Nota:

Como este tutorial se basa en el anterior, antes de empezar, dedique un momento a copiar la sintaxis declarativa dentro del elemento <asp:Content> de la página web (EfficientPaging.aspx) del tutorial anterior y péguelo entre el elemento <asp:Content> de la página SortParameter.aspx. Consulte el paso 1 del tutorial Adición de controles de validación a las interfaces Editar e Insertar para obtener una explicación más detallada sobre cómo replicar la funcionalidad de una página de ASP.NET en otra.

Paso 1: Nuevo examen de la técnica de paginación personalizada

Para que la paginación personalizada funcione correctamente, debe implementar alguna técnica que pueda capturar eficazmente un subconjunto determinado de registros según los parámetros Índice de fila inicial y Número máximo de filas. Se pueden usar varias técnicas para conseguir este objetivo. En el tutorial anterior, ha visto cómo hacerlo con la nueva función de clasificación ROW_NUMBER() de Microsoft SQL Server 2005. En resumen, la función de clasificación ROW_NUMBER() asigna un número de fila a cada fila devuelta por una consulta clasificada por un criterio de ordenación especificado. Después, para obtener el subconjunto adecuado de registros se devuelve una sección determinada de los resultados numerados. En la consulta siguiente se muestra cómo usar esta técnica para devolver esos productos numerados del 11 al 20 al clasificar los resultados ordenados alfabéticamente por ProductName:

SELECT ProductID, ProductName, ...
FROM
   (SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
    (ORDER BY ProductName) AS RowRank
    FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20

Esta técnica funciona bien para la paginación mediante un criterio de ordenación específico (en este caso, ProductName ordenado alfabéticamente), pero la consulta se debe modificar para mostrar los resultados ordenados por otra expresión de ordenación. Lo ideal es volver a escribir la consulta anterior para que use un parámetro en la cláusula OVER, de la siguiente manera:

SELECT ProductID, ProductName, ...
FROM
   (SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
    (ORDER BY @sortExpression) AS RowRank
    FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20

Desafortunadamente, no se permiten cláusulas ORDER BY parametrizadas. En su lugar, debe crear un procedimiento almacenado que acepte un parámetro @sortExpression de entrada, pero usar una de las siguientes soluciones alternativas:

• Escriba consultas codificadas de forma rígida para cada una de las expresiones de ordenación que se pueden usar; después, use instrucciones T-SQL IF/ELSE para determinar qué consulta se va a ejecutar.
• Use una instrucción CASE para proporcionar expresiones ORDER BY dinámicas basadas en el parámetro de entrada @sortExpressio; vea la sección Uso para ordenar dinámicamente los resultados de la consulta en instruccionesCASE T-SQL para más información.
• Cree la consulta adecuada como una cadena en el procedimiento almacenado y, después, use el procedimiento almacenado del sistema sp_executesql para ejecutar la consulta dinámica.

Cada una de estas soluciones alternativas tiene algunas desventajas. La primera opción no es tan fácil de mantener como las otras dos, ya que necesita que se cree una consulta para cada expresión de ordenación posible. Por tanto, si más adelante decide agregar nuevos campos ordenables a GridView, también tendrá que volver atrás y actualizar el procedimiento almacenado. El segundo enfoque tiene algunas sutilezas que presentan problemas de rendimiento al ordenar por columnas de base de datos que no son de cadena y también sufre los mismos problemas de mantenimiento que el primero. Y la tercera opción, en la que se usa SQL dinámico, presenta el riesgo de un ataque por inyección de código SQL si un atacante puede ejecutar el procedimiento almacenado y pasar los valores de parámetro de entrada que elija.

Aunque ninguno de estos enfoques es perfecto, se considera que la tercera opción es la mejor de las tres. Al usar SQL dinámico, ofrece un nivel de flexibilidad que los otros dos no tienen. Además, un ataque por inyección de código SQL solo es eficaz si un atacante puede ejecutar el procedimiento almacenado y pasar los parámetros de entrada que elija. Como en DAL se usan consultas con parámetros, ADO.NET protegerá esos parámetros que se envían a la base de datos mediante la arquitectura, lo que significa que la vulnerabilidad de ataque por inyección de código SQL solo existe si el atacante puede ejecutar directamente el procedimiento almacenado.

Para implementar esta funcionalidad, cree un procedimiento almacenado en la base de datos Northwind con el nombre GetProductsPagedAndSorted. Este procedimiento almacenado debe aceptar tres parámetros de entrada: @sortExpression, un parámetro de entrada de tipo nvarchar(100, que especifica cómo se deben ordenar los resultados y que se inserta directamente después del texto ORDER BY de la cláusula OVER; y @startRowIndex y @maximumRows, los mismos dos parámetros de entrada enteros del procedimiento almacenado GetProductsPaged que se ha examinado en el tutorial anterior. Cree el procedimiento almacenado GetProductsPagedAndSorted mediante el siguiente script:

CREATE PROCEDURE dbo.GetProductsPagedAndSorted
(
    @sortExpression nvarchar(100),
    @startRowIndex int,
    @maximumRows int
)
AS
-- Make sure a @sortExpression is specified
IF LEN(@sortExpression) = 0
    SET @sortExpression = 'ProductID'
-- Issue query
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
                   UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
                   CategoryName, SupplierName
            FROM (SELECT ProductID, ProductName, p.SupplierID, p.CategoryID,
                         QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
                         ReorderLevel, Discontinued,
                  c.CategoryName, s.CompanyName AS SupplierName,
                   ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowRank
            FROM Products AS p
                    INNER JOIN Categories AS c ON
                        c.CategoryID = p.CategoryID
                    INNER JOIN Suppliers AS s ON
                        s.SupplierID = p.SupplierID) AS ProductsWithRowNumbers
            WHERE     RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) +
                ' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
                + CONVERT(nvarchar(10), @maximumRows) + ')'
-- Execute the SQL query
EXEC sp_executesql @sql

Para empezar, el procedimiento almacenado se aseguran de que se ha especificado un valor para el parámetro @sortExpression. Si falta, los resultados se clasifican por ProductID. A continuación, se construye la consulta SQL dinámica. Tenga en cuenta que esta consulta SQL dinámica difiere ligeramente de las consultas anteriores usadas para recuperar todas las filas de la tabla Products. En los ejemplos anteriores, se ha obtenido la categoría asociada a cada producto y los nombres de proveedor mediante una subconsulta. Esta decisión se ha tomado de nuevo en el tutorial Creación de una capa de acceso a datos en lugar de usar JOIN porque TableAdapter no puede crear automáticamente los métodos de inserción, actualización y eliminación asociados para esas consultas. Pero el procedimiento almacenado GetProductsPagedAndSorted debe usar JOIN para que los resultados se ordenen por los nombres de categoría o proveedor.

Esta consulta dinámica se crea mediante la concatenación de los elementos estáticos de la consulta y los parámetros @sortExpression, @startRowIndexy @maximumRows. Como @startRowIndex y @maximumRows son parámetros enteros, se deben convertirse en nvarchar para que se concatenen correctamente. Una vez que se construye esta consulta SQL dinámica, se ejecuta mediante sp_executesql.

Dedique un momento a probar este procedimiento almacenado con valores diferentes para los parámetros @sortExpression, @startRowIndexy @maximumRows. Desde el Explorador de servidores, haga clic con el botón derecho en el nombre del procedimiento almacenado y elija Ejecutar. Se abrirá el cuadro de diálogo Ejecutar procedimiento almacenado, en el que puede escribir los parámetros de entrada (vea la figura 1). Para ordenar los resultados por el nombre de categoría, use CategoryName para el valor del parámetro @sortExpression; para ordenar por el nombre de la empresa del proveedor, use CompanyName. Después de proporcionar los valores de parámetros, haga clic en Aceptar. Los errores se muestran en la ventana Salida. En la figura 2 se muestran los resultados al devolver productos clasificados entre 11 y 20 al ordenar por UnitPrice en orden descendente.

Figura 1: Prueba de valores diferentes para los tres parámetros de entrada del procedimiento almacenado

Figura 2: Los resultados del procedimiento almacenado se muestran en la ventana Salida (Haga clic para ver la imagen a tamaño completo)

Nota:

Al clasificar los resultados por la columna ORDER BY especificada en la cláusula OVER, SQL Server debe ordenar los resultados. Se trata de una operación rápida si hay un índice agrupado sobre las columnas por las que se ordenan los resultados, o si hay un índice de cobertura, pero puede ser más costoso. Para mejorar el rendimiento de consultas suficientemente grandes, considere la posibilidad de agregar un índice no agrupado para la columna por la que se ordenan los resultados. Consulte Funciones de clasificación y rendimiento en SQL Server 2005 para más información.

Paso 2: Aumento de las capas de acceso a datos y lógica de negocios

Después de crear el procedimiento almacenado GetProductsPagedAndSorted, el siguiente paso consiste en proporcionar un medio para ejecutarlo desde la arquitectura de la aplicación. Esto implica agregar un método adecuado tanto a DAL y BLL. Para empezar, agregará un método a DAL. Abra el conjunto de datos con tipo Northwind.xsd, haga clic con el botón derecho en ProductsTableAdapter y elija la opción Agregar consulta en el menú contextual. Como en el tutorial anterior, quiere configurar este nuevo método DAL para usar un procedimiento almacenado existente: GetProductsPagedAndSorted, en este caso. Empiece por indicar que quiere que el nuevo método TableAdapter use un procedimiento almacenado existente.

Figura 3: Elección de un procedimiento almacenado existente

Para especificar el procedimiento almacenado que se va a usar, seleccione el procedimiento almacenado GetProductsPagedAndSorted en la lista desplegable de la pantalla siguiente.

Figura 4: Uso del procedimiento almacenado GetProductsPagedAndSorted

Este procedimiento almacenado devuelve un conjunto de registros como sus resultados, por lo que, en la siguiente pantalla, indica que devuelve datos tabulares.

Figura 5: Indicación de que el procedimiento almacenado devuelve datos tabulares

Por último, cree métodos DAL que usen los patrones Fill a DataTable y Return a DataTable, y asigne los nombres FillPagedAndSorted y GetProductsPagedAndSorted, respectivamente.

Figura 6: Elección de los nombres de método

Ahora que ha ampliado DAL, ya puede volver a BLL. Abra el archivo de clase ProductsBLL y agregue un método nuevo, GetProductsPagedAndSorted. Este método debe aceptar tres parámetros de entrada sortExpression, startRowIndex y maximumRows, y llamar al método GetProductsPagedAndSorted de DAL, de la siguiente manera:

<System.ComponentModel.DataObjectMethodAttribute( _
    System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsPagedAndSorted(ByVal sortExpression As String, _
    ByVal startRowIndex As Integer, ByVal maximumRows As Integer) _
    As Northwind.ProductsDataTable
    Return Adapter.GetProductsPagedAndSorted(sortExpression, startRowIndex, maximumRows)
End Function

Paso 3: Configuración de ObjectDataSource para pasar el parámetro SortExpression

Después de aumentar DAL y BLL para incluir métodos que utilizan el procedimiento almacenadoGetProductsPagedAndSorted, solo tiene que configurar ObjectDataSource en la página SortParameter.aspx para usar el nuevo método de BLL y pasar el parámetro SortExpression en función de la columna por la que el usuario ha solicitado ordenar los resultados.

Para empezar, cambie SelectMethod en ObjectDataSource de GetProductsPaged a GetProductsPagedAndSorted. Esto se puede hacer mediante el Asistente para configurar orígenes de datos, desde la ventana Propiedades o directamente mediante sintaxis declarativa. A continuación, es necesario proporcionar un valor para la propiedad SortParameterName de ObjectDataSource. Si se establece esta propiedad, ObjectDataSource intenta pasar la propiedad SortExpression de GridView a SelectMethod. En concreto, ObjectDataSource busca un parámetro de entrada cuyo nombre sea igual al valor de la propiedad SortParameterName. Como el método GetProductsPagedAndSorted de BLL tiene el parámetro de entrada de expresión de ordenación sortExpression, establezca la propiedad SortExpression de ObjectDataSource en sortExpression.

Después de realizar estos dos cambios, la sintaxis declarativa de ObjectDataSource debe ser similar a la siguiente:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    OldValuesParameterFormatString="original_{0}" TypeName="ProductsBLL"
    SelectMethod="GetProductsPagedAndSorted" EnablePaging="True"
    SelectCountMethod="TotalNumberOfProducts" SortParameterName="sortExpression">
</asp:ObjectDataSource>

Nota:

Como en el tutorial anterior, asegúrese de que ObjectDataSource no incluya los parámetros de entrada sortExpression, startRowIndex o maximumRows en su colección SelectParameters.

Para habilitar la ordenación en GridView, simplemente active la casilla Habilitar ordenación en la etiqueta inteligente de GridView, que establece la propiedad AllowSorting de GridView en true y hace que el texto de encabezado de cada columna se represente como un elemento LinkButton. Cuando el usuario final hace clic en uno de los encabezados LinkButton, se produce un postback y los pasos siguientes:

1. GridView actualiza su propiedad SortExpression al valor del campo SortExpression en cuyo vínculo de encabezado se ha hecho clic
2. ObjectDataSource invoca el método GetProductsPagedAndSorted de BLL, y pasa la propiedad SortExpression de GridView como el valor del parámetro de entrada sortExpression del método (junto con los valores de parámetro de entrada startRowIndex y adecuadosmaximumRows)
3. BLL invoca el método GetProductsPagedAndSorted de DAL
4. DAL ejecuta el procedimiento almacenado GetProductsPagedAndSorted y pasa el parámetro @sortExpression (junto con los valores de parámetro de entrada @startRowIndex y @maximumRows)
5. El procedimiento almacenado devuelve el subconjunto adecuado de datos a BLL, que lo devuelve a ObjectDataSource; estos datos se enlazan a GridView, se representan en HTML y se envían al usuario final

En la figura 7 se muestra la primera página de resultados cuando se ordena por UnitPrice en orden ascendente.

Figura 7: Los resultados se ordenan por UnitPrice (Haga clic para ver la imagen a tamaño completo)

Aunque la implementación actual puede ordenar correctamente los resultados por nombre de producto, nombre de categoría, cantidad por unidad y precio unitario, al intentar ordenar los resultados por el nombre del proveedor se produce una excepción en tiempo de ejecución (vea la figura 8).

Figura 8: Al intentar ordenar los resultados por proveedor se inicia la siguiente excepción en tiempo de ejecución

Esta excepción se inicia porque el elemento SortExpression de BoundField SupplierName en GridView está establecido en SupplierName. Pero el nombre del proveedor en la tabla Suppliers en realidad es CompanyName; se ha utilizado el alias de nombre de columna SupplierName. Pero la cláusula OVER usada por la función ROW_NUMBER() no puede utilizar el alias y debe usar el nombre de columna real. Por tanto, cambie el valor SortExpression de la instancia de BoundField SupplierName de SupplierName a CompanyName (vea la figura 9). Como se muestra en la figura 10, después de este cambio, los resultados se pueden ordenar por proveedor.

Figura 9: Cambio de SortExpression en la instancia BoundField SupplierName a CompanyName

Figura 10: Ahora los resultados se pueden ordenar por proveedor (Haga clic para ver la imagen a tamaño completo)

Resumen

En la implementación de paginación personalizada que se ha examinado en el tutorial anterior era necesario que el orden de los resultados se especificara en tiempo de diseño. En resumen, esto significaba que la implementación de paginación personalizada implementada no podía, al mismo tiempo, proporcionar funcionalidades de ordenación. En este tutorial se ha superado esta limitación al extender el procedimiento almacenado del primero para incluir un parámetro de entrada @sortExpression por el que se podrían ordenar los resultados.

Después de crear este procedimiento almacenado y crear métodos en DAL y BLL, ha podido implementar un control GridView que ofrecía paginación personalizada y ordenación mediante la configuración de ObjectDataSource para pasar la propiedad SortExpression actual de GridView a SelectMethod de BLL.

¡Feliz programación!

Acerca del autor

Scott Mitchell, autor de siete libros de ASP/ASP.NET y fundador de 4GuysFromRolla.com, trabaja con tecnologías web de Microsoft desde 1998. Scott trabaja como consultor independiente, entrenador y escritor. Su último libro es Sams Teach Yourself ASP.NET 2.0 in 24 Hours. Puede ponerse en contacto con él en mitchell@4GuysFromRolla.com. o en su blog, que se puede encontrar en http://ScottOnWriting.NET.

Agradecimientos especiales a

Muchos revisores han evaluado esta serie de tutoriales. El revisor principal de este tutorial ha sido Carlos Santos. ¿Le interesa revisar mis próximos artículos de MSDN? Si es así, escríbame a mitchell@4GuysFromRolla.com.

AnteriorSiguiente