Tri personnalisé de données paginées (C#)

par Scott Mitchell

Télécharger le PDF

Dans le tutoriel précédent, nous avons appris à implémenter la pagination personnalisée lors de la présentation de données sur une page web. Dans ce tutoriel, nous voyons comment étendre l’exemple précédent pour inclure la prise en charge du tri de la pagination personnalisée.

Introduction

Par rapport à la pagination par défaut, la pagination personnalisée peut améliorer les performances de la pagination dans les données de plusieurs ordres de grandeur, faisant de la pagination personnalisée le choix de l’implémentation de pagination de facto lors de la pagination de grandes quantités de données. Toutefois, l’implémentation de la pagination personnalisée est plus impliquée que l’implémentation de la pagination par défaut, en particulier lors de l’ajout du tri au mélange. Dans ce tutoriel, nous allons étendre l’exemple du précédent pour inclure la prise en charge du tri et de la pagination personnalisée.

Notes

Étant donné que ce tutoriel s’appuie sur le précédent, avant de commencer, prenez un moment pour copier la syntaxe déclarative dans l’élément <asp:Content> de la page web du didacticiel précédent (EfficientPaging.aspx) et la coller entre l’élément <asp:Content> de la SortParameter.aspx page. Reportez-vous à l’étape 1 du didacticiel Ajout de contrôles de validation au didacticiel d’édition et d’insertion d’interfaces pour obtenir une discussion plus détaillée sur la réplication des fonctionnalités d’une page ASP.NET à une autre.

Étape 1 : Réexaminer la technique de pagination personnalisée

Pour que la pagination personnalisée fonctionne correctement, nous devons implémenter une technique qui peut récupérer efficacement un sous-ensemble particulier d’enregistrements en fonction des paramètres Start Row Index et Maximum Rows. Il existe quelques techniques qui peuvent être utilisées pour atteindre cet objectif. Dans le tutoriel précédent, nous nous sommes penchés sur l’utilisation de la nouvelle ROW_NUMBER() fonction de classement de Microsoft SQL Server 2005. En bref, la ROW_NUMBER() fonction de classement affecte un numéro de ligne à chaque ligne retournée par une requête classée selon un ordre de tri spécifié. Le sous-ensemble d’enregistrements approprié est ensuite obtenu en retournant une section particulière des résultats numérotés. La requête suivante montre comment utiliser cette technique pour retourner les produits numérotés de 11 à 20 lors du classement des résultats classés par ordre alphabétique par :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

Cette technique fonctionne bien pour la pagination à l’aide d’un ordre de tri spécifique (ProductName trié par ordre alphabétique, dans ce cas), mais la requête doit être modifiée pour afficher les résultats triés par une autre expression de tri. Dans l’idéal, la requête ci-dessus pourrait être réécrite pour utiliser un paramètre dans la OVER clause , comme suit :

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

Malheureusement, les clauses paramétrables ORDER BY ne sont pas autorisées. Au lieu de cela, nous devons créer une procédure stockée qui accepte un @sortExpression paramètre d’entrée, mais utilise l’une des solutions de contournement suivantes :

• Écrire des requêtes codées en dur pour chacune des expressions de tri qui peuvent être utilisées ; ensuite, utilisez des IF/ELSE instructions T-SQL pour déterminer la requête à exécuter.
• Pour plus d’informations, utilisez une CASE instruction pour fournir des expressions dynamiques ORDER BY basées sur n @sortExpressio paramètre d’entrée. Pour plus d’informations, consultez la section Utilisé pour trier dynamiquement les résultats de la requête dans Instructions T-SQLCASE.
• Créez la requête appropriée en tant que chaîne dans la procédure stockée, puis utilisez la sp_executesql procédure stockée système pour exécuter la requête dynamique.

Chacune de ces solutions de contournement présente des inconvénients. La première option n’est pas aussi maintenable que les deux autres, car elle nécessite la création d’une requête pour chaque expression de tri possible. Par conséquent, si vous décidez par la suite d’ajouter de nouveaux champs triables à GridView, vous devrez également revenir en arrière et mettre à jour la procédure stockée. La deuxième approche présente des subtilités qui introduisent des problèmes de performances lors du tri par colonnes de base de données autres que les chaînes et souffre également des mêmes problèmes de maintenance que la première. Et le troisième choix, qui utilise le SQL dynamique, introduit le risque d’une attaque par injection de code SQL si un attaquant est en mesure d’exécuter la procédure stockée en passant les valeurs de paramètre d’entrée de son choix.

Bien qu’aucune de ces approches ne soit parfaite, je pense que la troisième option est la meilleure des trois. Avec son utilisation de SQL dynamique, il offre un niveau de flexibilité que les deux autres n’ont pas. En outre, une attaque par injection SQL ne peut être exploitée que si un attaquant est en mesure d’exécuter la procédure stockée en passant les paramètres d’entrée de son choix. Étant donné que le DAL utilise des requêtes paramétrables, ADO.NET protège les paramètres envoyés à la base de données via l’architecture, ce qui signifie que la vulnérabilité d’attaque par injection DE SQL existe uniquement si l’attaquant peut exécuter directement la procédure stockée.

Pour implémenter cette fonctionnalité, créez une procédure stockée dans la base de données Northwind nommée GetProductsPagedAndSorted. Cette procédure stockée doit accepter trois paramètres d’entrée : @sortExpression, un paramètre d’entrée de type nvarchar(100) qui spécifie comment les résultats doivent être triés et est injecté directement après le ORDER BY texte dans la OVER clause ; et @startRowIndex et @maximumRows, les deux mêmes paramètres d’entrée entiers de la GetProductsPaged procédure stockée examinée dans le tutoriel précédent. Créez la GetProductsPagedAndSorted procédure stockée à l’aide du script suivant :

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

La procédure stockée commence par s’assurer qu’une valeur pour le @sortExpression paramètre a été spécifiée. S’il est manquant, les résultats sont classés par ProductID. Ensuite, la requête SQL dynamique est construite. Notez que la requête SQL dynamique ici diffère légèrement de nos requêtes précédentes utilisées pour récupérer toutes les lignes de la table Products. Dans les exemples précédents, nous avons obtenu des noms de catégorie et de fournisseur associés à chaque produit à l’aide d’une sous-requête. Cette décision a été prise dans le didacticiel Création d’une couche d’accès aux données et a été prise au lieu d’utiliser JOIN des, car TableAdapter ne peut pas créer automatiquement les méthodes d’insertion, de mise à jour et de suppression associées pour ces requêtes. Toutefois GetProductsPagedAndSorted , la procédure stockée doit utiliser JOIN s pour que les résultats soient classés par catégorie ou par nom de fournisseur.

Cette requête dynamique est générée en concaténant les parties de requête statiques et les @sortExpressionparamètres , @startRowIndexet @maximumRows . Étant donné que @startRowIndex et @maximumRows sont des paramètres entiers, ils doivent être convertis en nvarchars pour être correctement concaténés. Une fois que cette requête SQL dynamique a été construite, elle est exécutée via sp_executesql.

Prenez un moment pour tester cette procédure stockée avec des valeurs différentes pour les @sortExpressionparamètres , @startRowIndexet @maximumRows . Dans le Explorer Serveur, cliquez avec le bouton droit sur le nom de la procédure stockée, puis choisissez Exécuter. La boîte de dialogue Exécuter la procédure stockée s’affiche dans laquelle vous pouvez entrer les paramètres d’entrée (voir la figure 1). Pour trier les résultats par nom de catégorie, utilisez CategoryName pour la valeur du @sortExpression paramètre ; pour trier en fonction du nom de la société du fournisseur, utilisez CompanyName. Après avoir fourni les valeurs des paramètres, cliquez sur OK. Les résultats sont affichés dans la fenêtre Sortie. La figure 2 montre les résultats lors du retour de produits classés de 11 à 20 lors de la commande par ordre UnitPrice décroissant.

Figure 1 : Essayer différentes valeurs pour les trois paramètres d’entrée de la procédure stockée

Figure 2 : Les résultats de la procédure stockée sont affichés dans la fenêtre Sortie (cliquer pour afficher l’image en taille réelle)

Notes

Lorsque vous classez les résultats en fonction de la colonne spécifiée ORDER BY dans la OVER clause, SQL Server devez trier les résultats. Il s’agit d’une opération rapide s’il existe un index cluster sur les colonnes par laquelle les résultats sont classés ou s’il existe un index de couverture, mais peut être plus coûteux dans le cas contraire. Pour améliorer les performances des requêtes suffisamment volumineuses, envisagez d’ajouter un index non cluster pour la colonne selon laquelle les résultats sont triés. Pour plus d’informations, consultez Fonctions de classement et performances dans SQL Server 2005.

Étape 2 : Augmentation des couches d’accès aux données et de logique métier

Une fois la GetProductsPagedAndSorted procédure stockée créée, l’étape suivante consiste à fournir un moyen d’exécuter cette procédure stockée via notre architecture d’application. Cela implique l’ajout d’une méthode appropriée à la fois au DAL et au BLL. Commençons par ajouter une méthode au DAL. Ouvrez le Northwind.xsd DataSet typé, cliquez avec le bouton droit sur , ProductsTableAdapterpuis choisissez l’option Ajouter une requête dans le menu contextuel. Comme nous l’avons fait dans le tutoriel précédent, nous voulons configurer cette nouvelle méthode DAL pour utiliser une procédure stockée existante , GetProductsPagedAndSorteddans ce cas. Commencez par indiquer que vous souhaitez que la nouvelle méthode TableAdapter utilise une procédure stockée existante.

Figure 3 : Choisir d’utiliser une procédure stockée existante

Pour spécifier la procédure stockée à utiliser, sélectionnez la GetProductsPagedAndSorted procédure stockée dans la liste déroulante de l’écran suivant.

Figure 4 : Utiliser la procédure stockée GetProductsPagedAndSorted

Cette procédure stockée retourne un jeu d’enregistrements en tant que résultats, de sorte que, dans l’écran suivant, indiquent qu’elle retourne des données tabulaires.

Figure 5 : Indiquer que la procédure stockée retourne des données tabulaires

Enfin, créez des méthodes DAL qui utilisent à la fois les modèles Fill a DataTable et Return a DataTable, en nommant les méthodes FillPagedAndSorted et GetProductsPagedAndSorted, respectivement.

Figure 6 : Choisir les noms des méthodes

Maintenant que nous avons étendu le DAL, nous sommes prêts à passer à la BLL. Ouvrez le fichier de ProductsBLL classe et ajoutez une nouvelle méthode, GetProductsPagedAndSorted. Cette méthode doit accepter trois paramètres sortExpressiond’entrée , startRowIndexet maximumRows et doit simplement appeler la méthode dal s GetProductsPagedAndSorted , comme suit :

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

Étape 3 : Configuration de ObjectDataSource pour passer le paramètre SortExpression

Après avoir augmenté le DAL et le BLL pour inclure des méthodes qui utilisent la GetProductsPagedAndSorted procédure stockée, il ne reste plus qu’à configurer ObjectDataSource dans la SortParameter.aspx page pour utiliser la nouvelle méthode BLL et passer le SortExpression paramètre en fonction de la colonne sur laquelle l’utilisateur a demandé de trier les résultats.

Commencez par modifier les ObjectDataSource de SelectMethod en GetProductsPagedAndSortedGetProductsPaged . Cela peut être effectué via l’Assistant Configuration de la source de données, à partir du Fenêtre Propriétés ou directement via la syntaxe déclarative. Ensuite, nous devons fournir une valeur pour la propriété ObjectDataSourceSortParameterName. Si cette propriété est définie, ObjectDataSource tente de passer la propriété de SortExpression GridView au SelectMethod. En particulier, ObjectDataSource recherche un paramètre d’entrée dont le nom est égal à la valeur de la SortParameterName propriété . Étant donné que la méthode BLL s GetProductsPagedAndSorted a le paramètre d’entrée d’expression de tri nommé sortExpression, définissez la propriété de SortExpression ObjectDataSource sur sortExpression .

Après avoir apporté ces deux modifications, la syntaxe déclarative d’ObjectDataSource doit ressembler à ce qui suit :

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

Notes

Comme dans le didacticiel précédent, assurez-vous que ObjectDataSource n’inclut pas les paramètres d’entrée sortExpression, startRowIndex ou maximumRows dans sa collection SelectParameters.

Pour activer le tri dans GridView, il vous suffit de case activée la case à cocher Activer le tri dans la balise intelligente de GridView, ce qui définit la propriété true de AllowSorting GridView sur et entraîne le rendu du texte d’en-tête de chaque colonne en tant que LinkButton. Lorsque l’utilisateur final clique sur l’un des en-têtes LinkButtons, une publication s’ensuit et les étapes suivantes s’effectuent :

1. GridView met à jour sa SortExpression propriété à la valeur du SortExpression champ dont le lien d’en-tête a été cliqué
2. ObjectDataSource appelle la méthode S GetProductsPagedAndSorted BLL, en transmettant la propriété s GridView SortExpression comme valeur pour le paramètre d’entrée de sortExpression la méthode (avec les valeurs de paramètre appropriées startRowIndex et maximumRows d’entrée)
3. Le BLL appelle la méthode DAL s GetProductsPagedAndSorted
4. Le DAL exécute la GetProductsPagedAndSorted procédure stockée, en transmettant le @sortExpression paramètre (avec les valeurs de paramètre d’entrée @startRowIndex et @maximumRows )
5. La procédure stockée retourne le sous-ensemble de données approprié à la BLL, qui les renvoie à ObjectDataSource ; ces données sont ensuite liées à GridView, rendues en HTML et envoyées à l’utilisateur final

La figure 7 montre la première page de résultats lorsqu’elle est triée par ordre UnitPrice croissant.

Figure 7 : Les résultats sont triés par unitPrice (cliquer pour afficher l’image en taille réelle)

Bien que l’implémentation actuelle puisse trier correctement les résultats par nom de produit, nom de catégorie, quantité par unité et prix unitaire, la tentative de classement des résultats par nom de fournisseur aboutit à une exception d’exécution (voir figure 8).

Figure 8 : Tentative de tri des résultats par les résultats du fournisseur dans l’exception d’exécution suivante

Cette exception se produit parce que le SortExpression de l’objet BoundField de SupplierName GridView est défini sur SupplierName. Toutefois, le nom du fournisseur dans la Suppliers table est en fait appelé CompanyName nous avons été alias ce nom de colonne en tant que SupplierName. Toutefois, la OVER clause utilisée par la ROW_NUMBER() fonction ne peut pas utiliser l’alias et doit utiliser le nom réel de la colonne. Par conséquent, remplacez les SupplierName valeurs BoundField de SortExpression SupplierName par CompanyName (voir figure 9). Comme le montre la figure 10, après cette modification, les résultats peuvent être triés par le fournisseur.

Figure 9 : Remplacez le SortExpression de SupplierName BoundField par CompanyName

Figure 10 : Les résultats peuvent maintenant être triés par fournisseur (cliquez pour afficher l’image en taille réelle)

Résumé

L’implémentation de pagination personnalisée que nous avons examinée dans le tutoriel précédent exigeait que l’ordre de tri des résultats soit spécifié au moment de la conception. En bref, cela signifie que l’implémentation de pagination personnalisée que nous avons implémentée ne pouvait pas, en même temps, fournir des fonctionnalités de tri. Dans ce tutoriel, nous avons surmonté cette limitation en étendant la procédure stockée de la première pour inclure un paramètre d’entrée @sortExpression selon lequel les résultats peuvent être triés.

Après avoir créé cette procédure stockée et créé de nouvelles méthodes dans dal et BLL, nous avons pu implémenter un GridView qui offrait à la fois le tri et la pagination personnalisée en configurant ObjectDataSource pour passer la propriété actuelle SortExpression de GridView à la BLL SelectMethod.

Bonne programmation !

À propos de l’auteur

Scott Mitchell, auteur de sept livres ASP/ASP.NET et fondateur de 4GuysFromRolla.com, travaille avec les technologies Web Microsoft depuis 1998. Scott travaille comme consultant indépendant, formateur et écrivain. Son dernier livre est Sams Teach Yourself ASP.NET 2.0 in 24 Heures. Il est accessible à l’adressemitchell@4GuysFromRolla.com . ou via son blog, qui peut être trouvé à l’adresse http://ScottOnWriting.NET.

Un merci spécial à

Cette série de tutoriels a été examinée par de nombreux réviseurs utiles. Le réviseur principal de ce tutoriel était Carlos Santos. Vous souhaitez consulter mes prochains articles MSDN ? Si c’est le cas, déposez-moi une ligne à mitchell@4GuysFromRolla.com.

PrécédentSuivant