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.

Présentation

Par rapport à la pagination par défaut, la pagination personnalisée peut améliorer les performances de manière significative, ce qui en fait le choix par défaut pour l'implémentation de la pagination pour gérer de grandes quantités de données. L’implémentation de la pagination personnalisée est plus impliquée que l’implémentation de la pagination par défaut, toutefois, en particulier lors de l’ajout de tri à la combinaison. Dans ce tutoriel, nous allons étendre l’exemple de l’exemple précédent afin d’inclure la prise en charge du tri et de la pagination personnalisée.

Remarque

Étant donné que ce tutoriel s’appuie sur le précédent, avant de commencer, prenez un moment pour copier la syntaxe déclarative de l’élément <asp:Content> de la page web du didacticiel précédent (EfficientPaging.aspx) et collez-la dans l’élément <asp:Content> de la page SortParameter.aspx. Reportez-vous à l’étape 1 du didacticiel Ajout de contrôles de validation au didacticiel d’édition et d’insertion d’interfaces pour une discussion plus détaillée sur la réplication des fonctionnalités d’une page ASP.NET vers 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 d’index de ligne de démarrage et de ligne maximale. Il existe une poignée de 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 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 par un ordre de tri spécifié. Le sous-ensemble approprié d’enregistrements 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 par ordre alphabétique selon le 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 ; utilisez ensuite des IF/ELSE instructions T-SQL pour déterminer la requête à exécuter.
• Utilisez une instruction pour fournir des CASE expressions dynamiques ORDER BY basées sur le @sortExpressio paramètre d’entrée n . Pour plus d’informations, consultez la section Utilisée pour trier dynamiquement les résultats des requêtes dans les 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 ultérieurement d’ajouter de nouveaux champs triables à GridView, vous devez également revenir en arrière et mettre à jour la procédure stockée. La deuxième approche présente des subtilités qui présentent des problèmes de performances lors du tri par colonnes de base de données non-chaînes et qui souffrent également des mêmes problèmes de facilité de maintenance que le premier. Et le troisième choix, qui utilise sql dynamique, introduit le risque d’attaque par injection SQL si un attaquant est en mesure d’exécuter la procédure stockée en passant les valeurs des paramètres d’entrée de leur choix.

Même si aucune de ces approches n’est 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é les deux autres ne le font 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égera ces paramètres envoyés à la base de données par le biais de 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, paramètre d’entrée de type nvarchar(100) qui spécifie la façon dont les résultats doivent être triés et est injecté directement après le ORDER BY texte de la OVER clause ; et , les @startRowIndex@maximumRowsdeux 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 procédure stockée à l'aide du script suivant GetProductsPagedAndSorted.

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 chaque nom de catégorie et de fournisseur associé à 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é effectuée 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 ces requêtes. Toutefois, la procédure stockée GetProductsPagedAndSorted doit utiliser JOIN pour que les résultats soient classés par les noms de catégorie ou de fournisseur.

Cette requête dynamique est générée en concaténant les parties de requête statiques et les paramètres @sortExpression, @startRowIndex, et @maximumRows. Dans la mesure @startRowIndex où et @maximumRows sont des paramètres entiers, ils doivent être convertis en nvarchars afin d’être correctement concaténés. Une fois cette requête SQL dynamique construite, elle est exécutée via sp_executesql.

Prenez le temps de tester cette procédure stockée avec différentes valeurs pour les paramètres @sortExpression, @startRowIndex, et @maximumRows. Dans l’Explorateur de serveurs, cliquez avec le bouton droit sur le nom de la procédure stockée et choisissez Exécuter. Cette opération affiche la boîte de dialogue Exécuter la procédure stockée 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 par nom de 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 classement des produits retournés de 11 à 20 lorsqu'ils sont ordonnés par UnitPrice dans un 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 sont affichés dans la fenêtre sortie (cliquez pour afficher l’image de taille complète)

Remarque

Lors du classement des résultats par la colonne spécifiée ORDER BY dans la OVER clause, SQL Server doit trier les résultats. Il s’agit d’une opération rapide s’il existe un index clusterisé sur la ou les colonnes par lesquelles les résultats sont classés ou s’il existe un index de couverture, mais peut être plus coûteux sinon. Pour améliorer les performances des requêtes suffisamment volumineuses, envisagez d’ajouter un index non cluster pour la colonne par laquelle les résultats sont triés. Pour plus d’informations, consultez fonctions de classement et performances dans SQL Server 2005 .

Étape 2 : Augmenter l’accès aux données et les couches logiques métier

Une fois la GetProductsPagedAndSorted procédure stockée créée, notre é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 BLL et au DAL. Commençons par ajouter une méthode au dal. Ouvrez le DataSet typé, cliquez avec le bouton droit sur le Northwind.xsd, puis 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 dans 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. Dans l’écran suivant, indiquez 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 élargi le DAL, nous sommes prêts à aborder la BLL. Ouvrez le ProductsBLL fichier de classe et ajoutez une nouvelle méthode. GetProductsPagedAndSorted Cette méthode doit accepter trois paramètres d'entrée sortExpression, startRowIndex et maximumRows et doit simplement appeler la méthode GetProductsPagedAndSorted du DAL 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 de ObjectDataSource pour passer le paramètre SortExpression

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

Commencez par modifier les objectDataSource s SelectMethod de GetProductsPaged vers GetProductsPagedAndSorted. Vous pouvez effectuer cette opération via l’Assistant Configurer la source de données, à partir de la 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 va essayer de passer la propriété GridView SortExpression à la propriété 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 GetProductsPagedAndSorted du BLL possède un paramètre d’entrée nommé sortExpression pour l'expression de tri, définissez la propriété SortExpression de ObjectDataSource sur "sortExpression".

Après avoir apporté ces deux modifications, la syntaxe déclarative de 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>

Remarque

Comme avec 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, cochez simplement la case Activer le tri dans la balise active GridView, qui définit la propriété AllowSorting GridView sur true et provoque le rendu du texte d’en-tête de chaque colonne sous forme de LinkButton. Lorsque l’utilisateur final clique sur l’un des en-têtes LinkButtons, un postback se produit et les étapes suivantes s’affichent :

1. GridView met à jour sa SortExpression propriété à la valeur du champ dont le SortExpression lien d’en-tête a été cliqué
2. ObjectDataSource appelle la méthode GetProductsPagedAndSorted de BLL, en passant la propriété SortExpression de GridView comme valeur pour le paramètre d'entrée sortExpression de la méthode, ainsi que les valeurs appropriées des paramètres d'entrée startRowIndex et maximumRows.
3. La BLL appelle la méthode DAL GetProductsPagedAndSorted
4. Le DAL exécute la procédure stockée GetProductsPagedAndSorted, en passant le paramètre @sortExpression (ainsi que les paramètres d'entrée @startRowIndex et les valeurs @maximumRows)
5. La procédure stockée retourne le sous-ensemble approprié de données à la BLL, qui la 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 des résultats lorsqu’elles sont triées par ordre UnitPrice croissant.

Figure 7 : Les résultats sont triés par unitPrice (cliquez pour afficher l’image de taille complète)

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, en tentant de commander les résultats par le nom du fournisseur aboutit à une exception d’exécution (voir la figure 8).

Figure 8 : Tentative de tri des résultats par fournisseur entraîne l'exception d'exécution suivante

Cette exception se produit parce que l'objet BoundField du GridView est défini sur SortExpression. Toutefois, le nom du fournisseur dans la Suppliers table est en fait nommé CompanyName et nous avons renommé cette colonne en 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 boundField SupplierName s SortExpression de 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 du champ BoundField de SupplierName par celui de CompanyName

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

Résumé

L’implémentation de pagination personnalisée que nous avons examinée dans le tutoriel précédent exigeait que l’ordre par lequel les résultats devaient être triés au moment du design. En bref, cela signifie que l’implémentation de pagination personnalisée que nous avons implémentée n’a pas pu, en même temps, fournir des fonctionnalités de tri. Dans ce tutoriel, nous avons dépassé cette limitation en étendant la procédure stockée du premier pour inclure un @sortExpression paramètre d’entrée par 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 transmettre 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 en tant que consultant indépendant, formateur et écrivain. Son dernier livre est Sams Teach Yourself ASP.NET 2.0 en 24 heures. On peut le joindre à mitchell@4GuysFromRolla.com.

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