Ajout de colonnes de DataTable supplémentaires (VB)
par Scott Mitchell
Lorsque vous utilisez l’Assistant TableAdapter pour créer un DataSet typé, le DataTable correspondant contient les colonnes retournées par la requête de base de données main. Toutefois, il arrive que le DataTable ait besoin d’inclure des colonnes supplémentaires. Dans ce tutoriel, nous apprenons pourquoi les procédures stockées sont recommandées lorsque nous avons besoin de colonnes DataTable supplémentaires.
Introduction
Lors de l’ajout d’un TableAdapter à un DataSet typé, le schéma DataTable correspondant est déterminé par la requête main TableAdapter. Par exemple, si la requête main retourne les champs de données A, B et C, le DataTable aura trois colonnes correspondantes nommées A, B et C. En plus de sa requête main, un TableAdapter peut inclure des requêtes supplémentaires qui retournent, peut-être, un sous-ensemble des données basées sur un paramètre. Par instance, en plus de la ProductsTableAdapter requête s main, qui retourne des informations sur tous les produits, elle contient également des méthodes telles que GetProductsByCategoryID(categoryID) et GetProductByProductID(productID), qui retournent des informations de produit spécifiques basées sur un paramètre fourni.
Le modèle de schéma dataTable reflète la requête de main TableAdapter fonctionne correctement si toutes les méthodes TableAdapter retournent les mêmes champs de données que ceux spécifiés dans la requête main. Si une méthode TableAdapter doit retourner des champs de données supplémentaires, nous devons développer le schéma dataTable en conséquence. Dans le didacticiel Master/Detail Using a Bulleted List of Master Records with a Details DataList , nous avons ajouté une méthode au CategoriesTableAdapter qui a retourné les CategoryIDchamps de données , CategoryNameet Description définis dans la requête main plus NumberOfProducts, un champ de données supplémentaire qui indique le nombre de produits associés à chaque catégorie. Nous avons ajouté manuellement une nouvelle colonne à afin CategoriesDataTable de capturer la valeur du NumberOfProducts champ de données à partir de cette nouvelle méthode.
Comme indiqué dans le didacticiel Chargement de fichiers, vous devez faire très attention à TableAdapters qui utilisent des instructions SQL ad hoc et ont des méthodes dont les champs de données ne correspondent pas précisément à la requête main. Si l’Assistant Configuration de TableAdapter est réexécuté, il met à jour toutes les méthodes de TableAdapter afin que leur liste de champs de données corresponde à la requête main. Par conséquent, toutes les méthodes avec des listes de colonnes personnalisées retournent à la liste de colonnes de la requête main et ne retournent pas les données attendues. Ce problème ne se produit pas lors de l’utilisation de procédures stockées.
Dans ce tutoriel, nous allons voir comment étendre un schéma DataTable pour inclure des colonnes supplémentaires. En raison de la fragilité de TableAdapter lors de l’utilisation d’instructions SQL ad hoc, dans ce tutoriel, nous allons utiliser des procédures stockées. Pour plus d’informations sur la configuration d’un TableAdapter pour utiliser des procédures stockées, reportez-vous aux didacticiels Création de procédures stockées pour les tablesTableAdapters du DataSet typé et Utilisation de procédures stockées existantes .
Étape 1 : Ajout d’unePriceQuartilecolonne auProductsDataTable
Dans le didacticiel Création de procédures stockées pour les TableAdapters du DataSet typé , nous avons créé un DataSet typé nommé NorthwindWithSprocs. Ce DataSet contient actuellement deux DataTables : ProductsDataTable et EmployeesDataTable. Le ProductsTableAdapter comporte les trois méthodes suivantes :
GetProducts- requête main, qui retourne tous les enregistrements de laProductstableGetProductsByCategoryID(categoryID)- retourne tous les produits avec le categoryID spécifié.GetProductByProductID(productID)- retourne le produit particulier avec le productID spécifié.
La requête main et les deux méthodes supplémentaires retournent toutes le même ensemble de champs de données, à savoir toutes les colonnes de la Products table. Il n’existe aucune sous-requête corrélée ou JOIN aucune extraction de données associées à partir des Categories tables ou Suppliers . Par conséquent, a ProductsDataTable une colonne correspondante pour chaque champ de la Products table.
Pour ce tutoriel, nous allons ajouter une méthode au ProductsTableAdapter nommé GetProductsWithPriceQuartile qui retourne tous les produits. En plus des champs de données de produit standard, GetProductsWithPriceQuartile inclut également un PriceQuartile champ de données qui indique sous quel quartile le prix du produit chute. Par exemple, les produits dont les prix sont dans les 25 % les plus chers auront une PriceQuartile valeur de 1, tandis que ceux dont les prix tombent dans les 25 % les plus bas auront une valeur de 4. Toutefois, avant de nous soucier de la création de la procédure stockée pour retourner ces informations, nous devons d’abord mettre à jour le ProductsDataTable pour inclure une colonne qui contiendra les PriceQuartile résultats lorsque la GetProductsWithPriceQuartile méthode est utilisée.
Ouvrez le NorthwindWithSprocs DataSet et cliquez avec le bouton droit sur .ProductsDataTable Choisissez Ajouter dans le menu contextuel, puis choisissez Colonne.
Figure 1 : Ajouter une nouvelle colonne à (ProductsDataTableCliquer pour afficher l’image en taille réelle)
Cette opération ajoute une nouvelle colonne au DataTable nommée Column1 de type System.String. Nous devons mettre à jour le nom de cette colonne sur PriceQuartile et son type sur System.Int32 , car il sera utilisé pour contenir un nombre compris entre 1 et 4. Sélectionnez la colonne nouvellement ajoutée dans etProductsDataTable, à partir de la Fenêtre Propriétés, définissez la Name propriété sur PriceQuartile et la propriété sur DataTypeSystem.Int32.
Figure 2 : Définir les nouvelles colonnes Name et DataType propriétés (cliquez pour afficher l’image en taille réelle)
Comme le montre la figure 2, il existe des propriétés supplémentaires qui peuvent être définies, telles que si les valeurs de la colonne doivent être uniques, si la colonne est une colonne à incrémentation automatique, si les valeurs de base de données NULL sont autorisées ou non, et ainsi de suite. Laissez ces valeurs définies par défaut.
Étape 2 : Création de laGetProductsWithPriceQuartileméthode
Maintenant que le ProductsDataTable a été mis à jour pour inclure la PriceQuartile colonne, nous sommes prêts à créer la GetProductsWithPriceQuartile méthode . Commencez par cliquer avec le bouton droit sur TableAdapter et choisissez Ajouter une requête dans le menu contextuel. Cela fait apparaître l’Assistant Configuration de la requête TableAdapter, qui nous indique d’abord si nous voulons utiliser des instructions SQL ad hoc ou une procédure stockée nouvelle ou existante. Étant donné que nous n’avons pas encore de procédure stockée qui retourne les données de quartile de prix, nous allons autoriser TableAdapter à créer cette procédure stockée pour nous. Sélectionnez l’option Créer une procédure stockée, puis cliquez sur Suivant.
Figure 3 : Demander à l’Assistant TableAdapter de créer la procédure stockée pour nous (cliquez pour afficher l’image en taille réelle)
Dans l’écran suivant, illustré dans la figure 4, l’Assistant nous demande quel type de requête ajouter. Étant donné que la GetProductsWithPriceQuartile méthode retourne toutes les colonnes et enregistrements de la Products table, sélectionnez l’option SELECT qui retourne des lignes, puis cliquez sur Suivant.
Figure 4 : Notre requête sera une SELECT instruction qui renvoie plusieurs lignes (cliquer pour afficher l’image en taille réelle)
Ensuite, nous sommes invités à entrer la SELECT requête. Entrez la requête suivante dans l’Assistant :
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued,
NTILE(4) OVER (ORDER BY UnitPrice DESC) as PriceQuartile
FROM Products
La requête ci-dessus utilise SQL Server nouvelle NTILE fonction de 2005 pour diviser les résultats en quatre groupes où les groupes sont déterminés par les valeurs triées dans l’ordre UnitPrice décroissant.
Malheureusement, le Générateur de requêtes ne sait pas comment analyser les OVER mot clé et affiche une erreur lors de l’analyse de la requête ci-dessus. Par conséquent, entrez la requête ci-dessus directement dans la zone de texte de l’Assistant sans utiliser le Générateur de requêtes.
Notes
Pour plus d’informations sur les fonctions de classement NTILE et SQL Server 2005, consultez ROW_NUMBER (Transact-SQL) et la section Fonctions de classement de la documentation en ligne de SQL Server 2005.
Après avoir entré la SELECT requête et cliqué sur Suivant, l’Assistant nous demande de fournir un nom pour la procédure stockée qu’il va créer. Nommez la nouvelle procédure Products_SelectWithPriceQuartile stockée, puis cliquez sur Suivant.
Figure 5 : Nommez la procédure Products_SelectWithPriceQuartile stockée (cliquez pour afficher l’image en taille réelle)
Enfin, nous sommes invités à nommer les méthodes TableAdapter. Laissez les cases à cocher Remplir un DataTable et Retourner un DataTable cochées et nommez les méthodes FillWithPriceQuartile et GetProductsWithPriceQuartile.
Figure 6 : Nommez les méthodes TableAdapter et cliquez sur Terminer (cliquez pour afficher l’image en taille réelle)
Une fois la SELECT requête spécifiée et la procédure stockée et les méthodes TableAdapter nommées, cliquez sur Terminer pour terminer l’Assistant. À ce stade, vous pouvez recevoir un ou deux avertissements de l’Assistant indiquant que la OVER construction ou l’instruction SQL n’est pas prise en charge. Ces avertissements peuvent être ignorés.
Une fois l’Assistant terminé, TableAdapter doit inclure les FillWithPriceQuartile méthodes et GetProductsWithPriceQuartile et la base de données doit inclure une procédure stockée nommée Products_SelectWithPriceQuartile. Prenez le temps de vérifier que TableAdapter contient bien cette nouvelle méthode et que la procédure stockée a été correctement ajoutée à la base de données. Lorsque vous vérifiez la base de données, si vous ne voyez pas la procédure stockée, essayez de cliquer avec le bouton droit sur le dossier Procédures stockées et de choisir Actualiser.
Figure 7 : Vérifier qu’une nouvelle méthode a été ajoutée à TableAdapter
Figure 8 : Vérifier que la base de données contient la Products_SelectWithPriceQuartile procédure stockée (cliquer pour afficher l’image en taille réelle)
Notes
L’un des avantages de l’utilisation de procédures stockées au lieu d’instructions SQL ad hoc est que la ré-exécution de l’Assistant Configuration de TableAdapter ne modifie pas les listes de colonnes de procédures stockées. Vérifiez cela en cliquant avec le bouton droit sur TableAdapter, en choisissant l’option Configurer dans le menu contextuel pour démarrer l’Assistant, puis en cliquant sur Terminer pour le terminer. Ensuite, accédez à la base de données et affichez la Products_SelectWithPriceQuartile procédure stockée. Notez que sa liste de colonnes n’a pas été modifiée. Si nous utilisions des instructions SQL ad hoc, la ré-exécution de l’Assistant Configuration de TableAdapter aurait rétabli la liste de colonnes de cette requête pour qu’elle corresponde à la liste de colonnes de requête main, supprimant ainsi l’instruction NTILE de la requête utilisée par la GetProductsWithPriceQuartile méthode .
Lorsque la méthode de couche d’accès GetProductsWithPriceQuartile aux données est appelée, TableAdapter exécute la Products_SelectWithPriceQuartile procédure stockée et ajoute une ligne au ProductsDataTable pour chaque enregistrement retourné. Les champs de données retournés par la procédure stockée sont mappés aux ProductsDataTable colonnes de . Étant donné qu’un PriceQuartile champ de données est retourné par la procédure stockée, sa valeur est affectée à la ProductsDataTable colonne s PriceQuartile .
Pour les méthodes TableAdapter dont les requêtes ne retournent pas de PriceQuartile champ de données, la valeur de colonne PriceQuartile est la valeur spécifiée par sa DefaultValue propriété. Comme le montre la figure 2, cette valeur est définie sur DBNull, valeur par défaut. Si vous préférez une autre valeur par défaut, définissez simplement la DefaultValue propriété en conséquence. Assurez-vous simplement que la DefaultValue valeur est valide en fonction des DataType colonnes (c’est-à-dire pour System.Int32 la PriceQuartile colonne).
À ce stade, nous avons effectué les étapes nécessaires pour ajouter une colonne supplémentaire à un DataTable. Pour vérifier que cette colonne supplémentaire fonctionne comme prévu, créons une page ASP.NET qui affiche le nom, le prix et le quartile de prix de chaque produit. Toutefois, avant cela, nous devons d’abord mettre à jour la couche de logique métier pour inclure une méthode qui appelle la méthode dal s GetProductsWithPriceQuartile . Nous allons mettre à jour la BLL à l’étape 3, puis créer la page ASP.NET à l’étape 4.
Étape 3 : Augmentation de la couche de logique métier
Avant d’utiliser la nouvelle GetProductsWithPriceQuartile méthode de la couche de présentation, nous devons d’abord ajouter une méthode correspondante à la BLL. Ouvrez le fichier de ProductsBLLWithSprocs classe et ajoutez le code suivant :
<System.ComponentModel.DataObjectMethodAttribute_
(System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsWithPriceQuartile() As NorthwindWithSprocs.ProductsDataTable
Return Adapter.GetProductsWithPriceQuartile()
End Function
Comme les autres méthodes de récupération de données dans ProductsBLLWithSprocs, la GetProductsWithPriceQuartile méthode appelle simplement la méthode correspondante GetProductsWithPriceQuartile de DAL et retourne ses résultats.
Étape 4 : Affichage des informations sur le quartile de prix dans une page web ASP.NET
Une fois l’ajout BLL terminé, nous sommes prêts à créer une page ASP.NET qui affiche le quartile de prix pour chaque produit. Ouvrez la AddingColumns.aspx page dans le AdvancedDAL dossier et faites glisser un GridView de la boîte à outils vers le Designer, en affectant à sa ID propriété la valeur Products. À partir de la balise active de GridView, liez-la à un nouvel ObjetDataSource nommé ProductsDataSource. Configurez ObjectDataSource pour utiliser la ProductsBLLWithSprocs méthode class s GetProductsWithPriceQuartile . Étant donné qu’il s’agit d’une grille en lecture seule, définissez les listes déroulantes dans les onglets UPDATE, INSERT et DELETE sur (Aucun).
Figure 9 : Configurer ObjectDataSource pour utiliser la ProductsBLLWithSprocs classe (cliquer pour afficher l’image en taille réelle)
Figure 10 : Récupérer des informations sur le produit à partir de la GetProductsWithPriceQuartile méthode (cliquer pour afficher l’image en taille réelle)
Une fois l’Assistant Configuration de la source de données terminé, Visual Studio ajoute automatiquement un objet BoundField ou CheckBoxField à GridView pour chacun des champs de données retournés par la méthode . L’un de ces champs de données est PriceQuartile, qui est la colonne que nous avons ajoutée à l’étape ProductsDataTable 1.
Modifiez les champs gridView, en supprimant tous les champs sauf , ProductNameUnitPriceet PriceQuartile BoundFields. Configurez boundField UnitPrice pour mettre en forme sa valeur en tant que devise et que les UnitPricePriceQuartile champs et sont alignés à droite et au centre, respectivement. Enfin, mettez à jour les propriétés BoundFields HeaderText restantes sur Product, Price et Price Quartile, respectivement. En outre, case activée la case Activer le tri à partir de la balise active GridView.
Après ces modifications, le balisage déclaratif gridView et ObjectDataSource doit ressembler à ce qui suit :
<asp:GridView ID="Products" runat="server" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="ProductID"
DataSourceID="ProductsDataSource">
<Columns>
<asp:BoundField DataField="ProductName" HeaderText="Product"
SortExpression="ProductName" />
<asp:BoundField DataField="UnitPrice" DataFormatString="{0:c}"
HeaderText="Price" HtmlEncode="False"
SortExpression="UnitPrice">
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:BoundField DataField="PriceQuartile" HeaderText="Price Quartile"
SortExpression="PriceQuartile">
<ItemStyle HorizontalAlign="Center" />
</asp:BoundField>
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server"
OldValuesParameterFormatString="original_{0}"
SelectMethod="GetProductsWithPriceQuartile"
TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>
La figure 11 montre cette page lorsqu’elle est visitée par le biais d’un navigateur. Notez que, initialement, les produits sont classés par prix dans l’ordre décroissant, chaque produit ayant une valeur appropriée PriceQuartile . Bien sûr, ces données peuvent être triées selon d’autres critères, la valeur de la colonne Quartile de prix reflétant toujours le classement du produit par rapport au prix (voir la figure 12).
Figure 11 : Les produits sont commandés en fonction de leurs prix (cliquez pour afficher l’image en taille réelle)
Figure 12 : Les produits sont commandés en fonction de leur nom (cliquer pour afficher l’image en taille réelle)
Notes
Avec quelques lignes de code, nous avons pu augmenter le GridView afin qu’il colore les lignes du produit en fonction de leur PriceQuartile valeur. Nous pourrions colorer ces produits dans le premier quartile un vert clair, ceux du deuxième quartile un jaune clair, et ainsi de suite. Je vous encourage à prendre un moment pour ajouter cette fonctionnalité. Si vous avez besoin d’un rappel sur la mise en forme d’un GridView, consultez le didacticiel Mise en forme personnalisée basée sur les données .
Une autre approche : création d’un autre tableAdapter
Comme nous l’avons vu dans ce tutoriel, lors de l’ajout d’une méthode à un TableAdapter qui retourne des champs de données autres que ceux décrits par la requête main, nous pouvons ajouter des colonnes correspondantes au DataTable. Toutefois, une telle approche ne fonctionne bien que s’il existe un petit nombre de méthodes dans TableAdapter qui retournent différents champs de données et si ces autres champs de données ne varient pas trop par rapport à la requête main.
Au lieu d’ajouter des colonnes au DataTable, vous pouvez ajouter un autre TableAdapter au DataSet qui contient les méthodes du premier TableAdapter qui retournent différents champs de données. Pour ce tutoriel, au lieu d’ajouter la PriceQuartile colonne à (ProductsDataTableoù elle est utilisée uniquement par la GetProductsWithPriceQuartile méthode), nous aurions pu ajouter un TableAdapter supplémentaire au DataSet nommé ProductsWithPriceQuartileTableAdapter qui utilisait la Products_SelectWithPriceQuartile procédure stockée comme requête main. ASP.NET pages qui avaient besoin d’obtenir des informations sur le produit avec le quartile de prix utiliseraient le ProductsWithPriceQuartileTableAdapter, tandis que celles qui ne pouvaient pas continuer à utiliser le ProductsTableAdapter.
En ajoutant un nouveau TableAdapter, les DataTables restent intacts et leurs colonnes miroir précisément les champs de données retournés par leurs méthodes TableAdapter. Toutefois, d’autres TableAdapters peuvent introduire des tâches et des fonctionnalités répétitives. Par exemple, si les pages ASP.NET qui affichaient la PriceQuartile colonne devaient également fournir une prise en charge de l’insertion, de la mise à jour et de la ProductsWithPriceQuartileTableAdapter suppression, ses InsertCommandpropriétés , UpdateCommandet DeleteCommand doivent être correctement configurées. Bien que ces propriétés miroir les ProductsTableAdapter s, cette configuration introduit une étape supplémentaire. En outre, il existe désormais deux façons de mettre à jour, de supprimer ou d’ajouter un produit à la base de données : par le biais des ProductsTableAdapter classes et ProductsWithPriceQuartileTableAdapter .
Le téléchargement de ce tutoriel inclut une ProductsWithPriceQuartileTableAdapter classe dans le NorthwindWithSprocs DataSet qui illustre cette autre approche.
Résumé
Dans la plupart des scénarios, toutes les méthodes d’un Objet TableAdapter retournent le même jeu de champs de données, mais il arrive qu’une ou deux méthodes particulières devront retourner un champ supplémentaire. Par exemple, dans le didacticiel Master/Detail Using a Bulleted List of Master Records with a Details DataList , nous avons ajouté une méthode au CategoriesTableAdapter qui, en plus des champs de données de la requête main, a retourné un NumberOfProducts champ qui indique le nombre de produits associés à chaque catégorie. Dans ce tutoriel, nous avons examiné l’ajout d’une méthode dans le ProductsTableAdapter qui a retourné un PriceQuartile champ en plus des champs de données de la requête main. Pour capturer des champs de données supplémentaires retournés par les méthodes TableAdapter, nous devons ajouter des colonnes correspondantes au DataTable.
Si vous envisagez d’ajouter manuellement des colonnes au DataTable, il est recommandé que TableAdapter utilise des procédures stockées. Si TableAdapter utilise des instructions SQL ad hoc, chaque fois que l’Assistant Configuration de TableAdapter exécute toutes les listes de champs de données de méthodes, les champs de données retournent aux champs de données retournés par la requête main. Ce problème ne s’étend pas aux procédures stockées, c’est pourquoi elles sont recommandées et ont été utilisées dans ce didacticiel.
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. Les principaux réviseurs de ce tutoriel étaient Randy Schmidt, Jacky Goor, Bernadette Leigh et Hilton Giesenow. Vous souhaitez consulter mes prochains articles MSDN ? Si c’est le cas, déposez-moi une ligne à mitchell@4GuysFromRolla.com.