Ordenar datos paginados personalizados (C#)

de Scott Mitchell

Descargar PDF

En el tutorial anterior hemos 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 paginación personalizada puede mejorar el rendimiento de la paginación a través de 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 al paginar a través de grandes cantidades de datos. La implementación de la paginación personalizada es más compleja que la implementación de la paginación predeterminada, especialmente al agregar la ordenación al conjunto. En este tutorial extenderemos el ejemplo del anterior para incluir compatibilidad con la ordenación y la paginación personalizada.

Nota:

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

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

Para que la paginación personalizada funcione correctamente, debemos 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. Hay una serie de técnicas que se pueden usar para lograr este objetivo. En el tutorial anterior, hemos visto cómo hacerlo con la nueva ROW_NUMBER() función de clasificación de Microsoft SQL Server 2005. En resumen, la ROW_NUMBER() función de clasificación asigna un número de fila a cada fila devuelta por una consulta clasificada por un criterio de ordenación especificado. A continuación, se obtiene el subconjunto adecuado de registros devolviendo 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 de 11 a 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 (ProductName ordenado alfabéticamente, en este caso), pero la consulta debe modificarse para mostrar los resultados ordenados por una expresión de ordenación diferente. Lo ideal es que la consulta anterior se vuelva a escribir para usar un parámetro en la OVER cláusula , 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 parametrizadas ORDER BY . En su lugar, debemos crear un procedimiento almacenado que acepte un @sortExpression parámetro de entrada, pero use una de las siguientes soluciones alternativas:

• Escribir consultas fijas para cada una de las expresiones de ordenación que se pueden usar: después, use IF/ELSE instrucciones T-SQL para determinar qué consulta ejecutar.
• Utilice una CASE instrucción para proporcionar expresiones dinámicas ORDER BY basadas en el @sortExpressio parámetro de entrada n; vea la sección Usada para ordenar dinámicamente los resultados de la consulta en instrucciones T-SQL CASE para obtener más información.
• Cree la consulta adecuada como una cadena en el procedimiento almacenado y, a continuación, use el procedimiento almacenado del sp_executesql sistema 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 requiere que cree una consulta para cada expresión de ordenación posible. Por lo 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 sufren los mismos problemas de mantenimiento que el primero. Y la tercera opción, que 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 pasando los valores de parámetro de entrada de su elección.

Aunque ninguno de estos enfoques es perfecto, creo que la tercera opción es la mejor de las tres. Con su uso de SQL dinámico, ofrece un nivel de flexibilidad que los otros dos no. Además, un ataque por inyección de código SQL solo se puede aprovechar si un atacante puede ejecutar el procedimiento almacenado pasando los parámetros de entrada de su elección. Dado que dal usa consultas con parámetros, ADO.NET protegerá esos parámetros que se envían a la base de datos a través de 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 nuevo procedimiento almacenado en la base de datos Northwind denominada 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 se inserta directamente después del ORDER BY texto de la OVER cláusula ; y , @startRowIndex@maximumRowslos mismos dos parámetros de entrada enteros del GetProductsPaged procedimiento almacenado examinado en el tutorial anterior. Cree el GetProductsPagedAndSorted procedimiento almacenado 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

El procedimiento almacenado comienza asegurándose de que se ha especificado un valor para el @sortExpression parámetro . Si falta, los resultados se clasifican por ProductID. A continuación, se construye la consulta SQL dinámica. Tenga en cuenta que la consulta SQL dinámica aquí difiere ligeramente de nuestras consultas anteriores usadas para recuperar todas las filas de la tabla Products. En ejemplos anteriores, hemos obtenido los nombres de las categorías y proveedores asociados a cada producto mediante una subconsulta. Esta decisión se tomó de nuevo en el tutorial Creación de una capa de acceso a datos y se realizó en lugar de usar JOIN s porque TableAdapter no puede crear automáticamente los métodos de inserción, actualización y eliminación asociados para dichas consultas. Sin embargo, el GetProductsPagedAndSorted procedimiento almacenado debe usar JOIN s para que los resultados se ordenan por los nombres de categoría o proveedor.

Esta consulta dinámica se crea mediante la concatenación de las partes de consulta estáticas y los parámetros @sortExpression, @startRowIndex y @maximumRows. Dado que @startRowIndex y @maximumRows son parámetros enteros, deben convertirse en nvarchars para que se concatenen correctamente. Una vez construida esta consulta SQL dinámica, se ejecuta a través de sp_executesql.

Dedique un momento a probar este procedimiento almacenado con valores diferentes para los @sortExpressionparámetros , @startRowIndexy @maximumRows . En 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 @sortExpression parámetro; 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 resultados 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 en UnitPrice orden descendente.

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

Figura 2: Los resultados del procedimiento almacenado se muestran en la ventana de salida (haga clic para ver la imagen de tamaño completo)

Nota:

Al clasificar los resultados por la columna especificada ORDER BY en la OVER cláusula , 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 de lo contrario. Para mejorar el rendimiento de las 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 obtener más información.

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

Con el GetProductsPagedAndSorted procedimiento almacenado creado, nuestro siguiente paso es proporcionar un medio para ejecutar ese procedimiento almacenado a través de nuestra arquitectura de aplicación. Esto implica agregar un método adecuado tanto a DAL como a BLL. Comencemos por agregar un método a la DAL. Abra el Northwind.xsd DataSet tipado, haga clic con el botón derecho en ProductsTableAdapter y elija la opción Agregar consulta en el menú contextual. Como hicimos en el tutorial anterior, queremos configurar este nuevo método DAL para usar un procedimiento almacenado existente: GetProductsPagedAndSorted, en este caso. Empiece por indicar que desea que el nuevo método TableAdapter use un procedimiento almacenado existente.

Figura 3: Elegir usar un procedimiento almacenado existente

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

Figura 4: Utilice el 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: Indicar que el procedimiento almacenado devuelve datos tabulares

Por último, cree métodos DAL que utilicen los patrones de Llenar un DataTable y Devolver un DataTable, nombrando los métodos como FillPagedAndSorted y GetProductsPagedAndSorted, respectivamente.

Figura 6: Elegir los nombres de métodos

Ahora que hemos ampliado la DAL, estamos listos para volver a la BLL. Abra el archivo de ProductsBLL clase y agregue un nuevo método, GetProductsPagedAndSorted. Este método debe aceptar tres parámetros de entrada sortExpression, startRowIndex y maximumRows, y simplemente debe llamar al método del DAL GetProductsPagedAndSorted, de forma similar a:

[System.ComponentModel.DataObjectMethodAttribute(
    System.ComponentModel.DataObjectMethodType.Select, false)]
public Northwind.ProductsDataTable GetProductsPagedAndSorted(
    string sortExpression, int startRowIndex, int maximumRows)
{
    return Adapter.GetProductsPagedAndSorted
        (sortExpression, startRowIndex, maximumRows);
}

Paso 3: Configurar objectDataSource para pasar el parámetro SortExpression

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

Empiece cambiando ObjectDataSource s SelectMethod de GetProductsPaged a GetProductsPagedAndSorted. Esto se puede hacer mediante el Asistente para configurar orígenes de datos, desde la ventana Propiedades o directamente a través de la sintaxis declarativa. A continuación, es necesario proporcionar un valor para la propiedad ObjectDataSource.SortParameterName Si se establece esta propiedad, ObjectDataSource intenta pasar la propiedad GridView s SortExpression a SelectMethod. En concreto, ObjectDataSource busca un parámetro de entrada cuyo nombre sea igual al valor de la SortParameterName propiedad . Dado que el método BLL tiene el parámetro de entrada de expresión de ordenación llamado GetProductsPagedAndSorted, 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:

Al igual que con 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, lo que establece la propiedad AllowSorting de GridView en true y hace que el texto del encabezado de cada columna se represente como LinkButton. Cuando el usuario final hace clic en uno de los LinkButtons del encabezado, se produce un postback y se llevan a cabo los siguientes pasos:

1. GridView actualiza su SortExpression propiedad al valor del campo SortExpression cuyo vínculo de encabezado se hizo clic en
2. ObjectDataSource invoca el método GetProductsPagedAndSorted de BLL, pasando como valor del parámetro de entrada la propiedad SortExpression de GridView (junto con los valores de parámetro de entrada adecuados sortExpression y startRowIndex).
3. La BLL invoca el método de DAL GetProductsPagedAndSorted.
4. El DAL ejecuta el GetProductsPagedAndSorted procedimiento almacenado, transmitiendo el @sortExpression parámetro (junto con los valores de los parámetros de entrada @startRowIndex y @maximumRows).
5. El procedimiento almacenado devuelve el subconjunto adecuado de datos al 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 de 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 (consulte la figura 8).

Figura 8: Intentar ordenar los resultados por los resultados del proveedor en la siguiente excepción en tiempo de ejecución

Esta excepción se produce porque SortExpression de GridView s SupplierName BoundField está establecido en SupplierName. Sin embargo, el nombre del proveedor en la tabla Suppliers se denomina CompanyName, y en realidad hemos asignado un alias a este nombre de columna como SupplierName. Sin embargo, la OVER cláusula usada por la ROW_NUMBER() función no puede usar el alias y debe usar el nombre de columna real. Por lo tanto, cambie boundField SupplierName s SortExpression de SupplierName a CompanyName (vea la figura 9). Como se muestra en la figura 10, después de este cambio, el proveedor puede ordenar los resultados.

Figura 9: Cambiar el valor de SupplierName BoundField s SortExpression a CompanyName

Figura 10: Los resultados ahora se pueden ordenar por proveedor (haga clic para ver la imagen de tamaño completo)

Resumen

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

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

¡Feliz programación!

Acerca del autor

Scott Mitchell, autor de siete libros de ASP/ASP.NET y fundador de 4GuysFromRolla.com, ha estado trabajando 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 en 24 horas. Se puede contactar con él en mitchell@4GuysFromRolla.com.

Agradecimientos especiales a

Esta serie de tutoriales contó con la revisión de muchos revisores que fueron de gran ayuda. El revisor principal de este tutorial fue Carlos Santos. ¿Le interesa revisar mis próximos artículos de MSDN? Si es así, mándame un mensaje a mitchell@4GuysFromRolla.com.

Siguiente anterior