Tri de données paginées personnalisées (VB)

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 via les données de plusieurs ordres de grandeur, faisant de la pagination personnalisée le choix de l’implémentation de la 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 à la combinaison. 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 tutoriel précédent (EfficientPaging.aspx) et la coller entre l’élément <asp:Content> de la SortParameter.aspx page. Pour une discussion plus détaillée sur la réplication des fonctionnalités d’une page ASP.NET à une autre, reportez-vous à l’étape 1 du tutoriel Ajout de contrôles de validation au didacticiel Modification et insertion d’interfaces .

É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 avons examiné cette opération à l’aide de la nouvelle ROW_NUMBER() fonction de classement 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 qui est 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 peut ê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.
• Utilisez une CASE instruction pour fournir des expressions dynamiques ORDER BY basées sur le @sortExpressio paramètre d’entrée n ; 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 certains inconvénients. La première option n’est pas aussi gérable 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 certaines 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 souffrent également des mêmes problèmes de maintenance que la première. Et le troisième choix, qui utilise un SQL dynamique, présente le risque d’une attaque par injection 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 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 sont injectés directement après le ORDER BY texte de la OVER clause ; et @startRowIndex et @maximumRows, les deux mêmes paramètres d’entrée entier 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 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 fournisseurs 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 s, car TableAdapter ne peut pas créer automatiquement les méthodes d’insertion, de mise à jour et de suppression associées pour de telles requêtes. Toutefois GetProductsPagedAndSorted , la procédure stockée doit utiliser JOIN des 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é @startRowIndex que et @maximumRows sont des paramètres entiers, ils doivent être convertis en nvarchars pour être correctement concaténés. Une fois cette requête SQL dynamique 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 et 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 figure 1). Pour trier les résultats par nom de catégorie, utilisez CategoryName pour la valeur du @sortExpression paramètre ; pour trier par nom d’entreprise 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 entre 11 et 20 lors de la UnitPrice commande par ordre 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 s’affichent dans la fenêtre Sortie (cliquer pour afficher l’image en taille réelle)

Notes

Lorsque vous classez les résultats par 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 la ou les colonnes dont 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 sous forme de résultats, de sorte que, dans l’écran suivant, indique qu’elle retourne des données tabulaires.

Figure 5 : Indiquez 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 à nous tourner vers le 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 doit simplement appeler la méthode DAL s GetProductsPagedAndSorted , comme suit :

<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

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

Après avoir augmenté le DAL et le BLL pour inclure les 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 à transmettre le SortExpression paramètre en fonction de la colonne sur laquelle l’utilisateur a demandé de trier les résultats.

Commencez par remplacer objectDataSource par SelectMethodGetProductsPagedGetProductsPagedAndSorted. Cela peut être effectué via l’Assistant Configurer 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é GridView SortExpression 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 S GetProductsPagedAndSorted BLL a le paramètre d’entrée d’expression de tri nommé sortExpression, définissez la propriété s 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 tutoriel 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 d’case activée la case Activer le tri dans la balise active 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 sous la forme d’un LinkButton. Lorsque l’utilisateur final clique sur l’un des en-têtes LinkButtons, une publication s’ensuit et les étapes suivantes s’affichent :

1. GridView met à jour sa SortExpression propriété avec la valeur du SortExpression champ sur lequel un clic a été fait sur le lien d’en-tête
2. ObjectDataSource appelle la méthode BLL, GetProductsPagedAndSorted en transmettant la propriété GridView comme SortExpression valeur du paramètre d’entrée de sortExpression la méthode (avec les valeurs de paramètre d’entrée 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 (ainsi que 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 le retourne à l’ObjetDataSource ; 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 le dans l’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, une tentative de classement des résultats par nom de fournisseur entraîne une exception d’exécution (voir la 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 car 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é nous avons été alias CompanyName 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 de colonne réel. Par conséquent, remplacez les SupplierName valeurs BoundField de SortExpression SupplierName par CompanyName (voir la 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 signifiait 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 à partir de la première pour inclure un @sortExpression paramètre d’entrée permettant de trier les résultats.

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 Hours. Il est accessible à l’adressemitchell@4GuysFromRolla.com . ou via son blog, qui se trouve à l’adresse http://ScottOnWriting.NET.

Remerciements spéciaux à

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