Ajout de colonnes de DataTable supplémentaires (VB)

par Scott Mitchell

Télécharger le PDF

Lorsque vous utilisez l’Assistant TableAdapter pour créer un Jeu de données typé, le DataTable correspondant contient les colonnes retournées par la requête de base de données principale. Toutefois, il existe des occasions où dataTable doit 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.

Présentation

Lors de l’ajout d’un TableAdapter à un Jeu de données typé, le schéma de DataTable correspondant est déterminé par la requête principale de TableAdapter. Par exemple, si la requête principale retourne les champs de données A, B et C, dataTable aura trois colonnes correspondantes nommées A, B et C. En plus de sa requête principale, un TableAdapter peut inclure des requêtes supplémentaires qui retournent, peut-être, un sous-ensemble des données en fonction d’un paramètre. Par exemple, en plus de la ProductsTableAdapter requête principale, qui retourne des informations sur tous les produits, elle contient également des méthodes telles GetProductsByCategoryID(categoryID) que et GetProductByProductID(productID), qui retournent des informations de produit spécifiques basées sur un paramètre fourni.

Le modèle selon lequel le schéma du DataTable reflète la requête principale du TableAdapter fonctionne bien si toutes les méthodes du TableAdapter retournent le même nombre ou moins de champs de données que ceux spécifiés dans la requête principale. Si une méthode TableAdapter doit retourner des champs de données supplémentaires, nous devons développer le schéma de 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 à la CategoriesTableAdapter qui retournait les champs de données CategoryID, CategoryName et Description définis dans la requête principale, ainsi que NumberOfProducts, un champ de données supplémentaire rapportant le nombre de produits associés à chaque catégorie. Nous avons ajouté manuellement une nouvelle colonne à CategoriesDataTable pour capturer la valeur du champ de données NumberOfProducts à partir de cette nouvelle méthode.

Comme indiqué dans le didacticiel Uploading Files , vous devez prendre soin de TableAdapters qui utilisent des instructions SQL ad hoc et qui ont des méthodes dont les champs de données ne correspondent pas précisément à la requête principale. 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 principale. Par conséquent, toutes les méthodes avec des listes de colonnes personnalisées rétabliront la liste de colonnes de la requête principale et ne retourneront 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 examiner 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. Reportez-vous aux didacticiels sur la création de nouvelles procédures stockées pour les TableAdapters de Typed DataSet et à l'utilisation des procédures stockées existantes pour les TableAdapters de Typed DataSet pour plus d'informations sur la configuration d'un TableAdapter afin d'utiliser des procédures stockées.

Étape 1 : Ajout d’unePriceQuartilecolonne à laProductsDataTable

Dans le didacticiel Création de nouvelles procédures stockées pour les TableAdapters du jeu de données typé, nous avons créé un jeu de données typé nommé NorthwindWithSprocs. Ce DataSet contient actuellement deux DataTables : ProductsDataTable et EmployeesDataTable. Le ProductsTableAdapter a les trois méthodes suivantes :

• GetProducts - la requête principale, qui retourne tous les enregistrements de la Products table
• GetProductsByCategoryID(categoryID) - retourne tous les produits avec l’ID de catégorie spécifié.
• GetProductByProductID(productID) - retourne le produit particulier avec l’ID de produit spécifié.

La requête principale et les deux méthodes supplémentaires retournent tous le même jeu de champs de données, à savoir toutes les colonnes de la Products table. Il n'existe aucune sous-requête corrélée ni JOIN tirant des données associées à partir des tables Categories ou Suppliers. Par conséquent, la ProductsDataTable colonne correspond à chaque champ de la Products table.

Pour ce tutoriel, nous allons ajouter une méthode au ProductsTableAdapter nom GetProductsWithPriceQuartile qui retourne tous les produits. Outre les champs de données de produit standard, GetProductsWithPriceQuartile inclura également un PriceQuartile champ de données qui indique sous quel quartile le prix du produit tombe. Par exemple, les produits dont les prix se trouvent dans les 25% les plus chers auront une PriceQuartile valeur de 1, tandis que ceux dont les prix tombent dans le bas 25% auront une valeur de 4. Avant de nous soucier de la création de la procédure stockée pour retourner ces informations, nous devons toutefois d’abord mettre à jour la ProductsDataTable pour inclure une colonne qui contiendra les résultats PriceQuartile lorsque la méthode GetProductsWithPriceQuartile est utilisée.

Ouvrez le NorthwindWithSprocs DataSet et faites un clic droit sur le ProductsDataTable. Choisissez Ajouter dans le menu contextuel, puis sélectionnez Colonne.

Figure 1 : Ajouter une nouvelle colonne à l’image ProductsDataTable (cliquez pour afficher l’image de taille complète)

Cela ajoute une nouvelle colonne au DataTable nommé Column1 de type System.String. Nous devons mettre à jour le nom de cette colonne vers PriceQuartile et son type, System.Int32 car il sera utilisé pour contenir un nombre compris entre 1 et 4. Sélectionnez la colonne nouvellement ajoutée dans la ProductsDataTable et, dans la fenêtre Propriétés, définissez la propriété Name à PriceQuartile et la propriété DataType à System.Int32.

Figure 2 : Définir les propriétés des nouvelles colonnes Name et DataType (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émentée automatiquement, si les valeurs de base de données NULL sont autorisées ou non, etc. Laissez ces valeurs définies sur leurs valeurs par défaut.

Étape 2 : Création de laGetProductsWithPriceQuartileméthode

Maintenant que la ProductsDataTable colonne a été mise à 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 affiche l’Assistant Configuration des requêtes TableAdapter, qui nous demande d’abord si nous voulons utiliser des instructions SQL ad hoc ou une procédure stockée nouvelle ou déjà existante. Étant donné que nous n’avons pas encore de procédure stockée qui retourne les données de quartile de prix, permettez à TableAdapter de 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 de taille complète)

Sur l’écran suivant, illustré à 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 l’option lignes, puis cliquez sur Suivant.

Figure 4 : Notre requête est une SELECT instruction qui renvoie plusieurs lignes (cliquez pour afficher l’image de taille complète)

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 la nouvelle NTILE fonction de SQL Server 2005 pour diviser les résultats en quatre groupes où les groupes sont déterminés par les UnitPrice valeurs triées dans l’ordre décroissant.

Malheureusement, le Générateur de requêtes ne sait pas comment analyser le 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.

Remarque

Pour plus d’informations sur les autres fonctions de classement NTILE et SQL Server 2005, consultez ROW_NUMBER (Transact-SQL) et la section Fonctions de classement dans 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 : Nommer la procédure Products_SelectWithPriceQuartile stockée (cliquez pour afficher l’image de taille complète)

Enfin, nous sommes invités à nommer les méthodes TableAdapter. Laissez à la fois la case Remplir un DataTable et renvoyer une table de données cochée et nommez les méthodes FillWithPriceQuartile et GetProductsWithPriceQuartile.

Figure 6 : Nommer les méthodes de TableAdapter et Click Finish (Cliquez pour afficher l’image de taille complète)

Une fois la requête SELECT spécifiée, la procédure stockée et les méthodes TableAdapter nommées, cliquez sur Terminer pour achever l'Assistant. À ce stade, vous pourriez recevoir un ou deux avertissements de l'assistant indiquant que la construction ou l'instruction SQL OVER n'est pas prise en charge. Ces avertissements peuvent être ignorés.

Une fois l’Assistant terminé, le TableAdapter doit inclure les méthodes FillWithPriceQuartile et GetProductsWithPriceQuartile, et la base de données doit inclure une procédure stockée nommée Products_SelectWithPriceQuartile. Prenez un moment pour vérifier que TableAdapter contient effectivement 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 procédure stockée (Products_SelectWithPriceQuartile de taille complète)

Remarque

L’un des avantages de l’utilisation de procédures stockées au lieu d’instructions SQL ad hoc est que la réexécutation 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écutation de l’Assistant Configuration de TableAdapter aurait rétabli la liste des colonnes de cette requête pour qu’elle corresponde à la liste de colonnes de requête principale, supprimant ainsi l’instruction NTILE de la requête utilisée par la GetProductsWithPriceQuartile méthode.

Lorsque la méthode GetProductsWithPriceQuartile de couche d’accès 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 associés aux colonnes ProductsDataTable. Étant donné qu’il existe un PriceQuartile champ de données retourné par la procédure stockée, sa valeur est affectée à la colonne ProductsDataTablePriceQuartile.

Pour ces méthodes TableAdapter dont les requêtes ne retournent pas de PriceQuartile champ de données, la valeur de la PriceQuartile colonne est la valeur spécifiée par sa DefaultValue propriété. Comme le montre la figure 2, cette valeur est définie sur DBNull, la valeur par défaut. Si vous préférez une valeur par défaut différente, définissez simplement la DefaultValue propriété en conséquence. Assurez-vous simplement que la DefaultValue valeur est valide en fonction de la colonne DataType (c’est-à-dire pour System.Int32 la colonne PriceQuartile).

À 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, nous allons créer une page ASP.NET qui affiche le nom, le prix et le quartile de prix de chaque produit. Avant de le faire, toutefois, nous devons d’abord mettre à jour la couche logique métier pour inclure une méthode qui appelle la méthode DAL.GetProductsWithPriceQuartile Nous allons ensuite mettre à jour la BLL à l’étape 3, puis créer la page ASP.NET à l’étape 4.

Étape 3 : Augmenter la couche logique métier

Avant d’utiliser la nouvelle GetProductsWithPriceQuartile méthode de la couche présentation, nous devons d’abord ajouter une méthode correspondante à la BLL. Ouvrez le ProductsBLLWithSprocs fichier de 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 de quartile de prix dans une page web ASP.NET

Avec 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 page AddingColumns.aspx dans le dossier AdvancedDAL et faites glisser un GridView à partir de la boîte à outils vers le Concepteur, en définissant sa propriété ID sur Products. À partir de la balise intelligente GridView, liez-la à un nouvel ObjectDataSource nommé ProductsDataSource. Configurez ObjectDataSource pour utiliser la méthode ProductsBLLWithSprocs de la classe GetProductsWithPriceQuartile. Comme 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 classe (ProductsBLLWithSprocs de taille complète)

Figure 10 : Récupérer des informations sur le produit à partir de la GetProductsWithPriceQuartile méthode (Cliquez pour afficher l’image de taille complète)

Une fois l’Assistant Configuration de la source de données terminé, Visual Studio ajoute automatiquement un BoundField ou CheckBoxField au 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 dans la ProductsDataTable à l'étape 1.

Modifiez les champs du GridView, en supprimant tous les champs sauf les BoundFields ProductName, UnitPrice, et PriceQuartile. Configurez le UnitPrice BoundField pour formater sa valeur en devise et alignez respectivement les UnitPrice et PriceQuartile BoundFields à droite et au centre. Enfin, mettez à jour les propriétés BoundFields HeaderText restantes en Produit, Prix et Quartile de prix, respectivement. Cochez également la case Activer le tri à partir de la balise intelligente de 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 via un navigateur. Notez que, initialement, les produits sont commandés par leur prix en ordre décroissant avec chaque produit attribué une valeur appropriée PriceQuartile . Bien sûr, ces données peuvent être triées par d’autres critères avec la valeur de colonne Du 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 par leur prix (cliquez pour afficher l’image de taille complète)

Figure 12 : Les produits sont commandés par leurs noms (cliquez pour afficher l’image de taille complète)

Remarque

Avec quelques lignes de code, nous pourrions augmenter GridView afin qu’elle colore les lignes de 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 selon 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 orthographiés par la requête principale, nous pouvons ajouter des colonnes correspondantes à DataTable. Toutefois, une telle approche fonctionne bien uniquement s’il existe un petit nombre de méthodes dans TableAdapter qui retournent différents champs de données et si ces champs de données alternatifs ne varient pas trop de la requête principale.

Au lieu d’ajouter des colonnes à DataTable, vous pouvez à la place ajouter un autre TableAdapter au DataSet qui contient les méthodes de la première TableAdapter qui retourne des champs de données différents. Pour ce tutoriel, au lieu d’ajouter la PriceQuartile colonne à la ProductsDataTable colonne (où elle est utilisée uniquement par la GetProductsWithPriceQuartile méthode), nous pourrions avoir ajouté un Autre TableAdapter au DataSet nommé ProductsWithPriceQuartileTableAdapter qui a utilisé la Products_SelectWithPriceQuartile procédure stockée comme requête principale. Les pages ASP.NET qui doivent obtenir des informations sur le produit avec le quartile de prix utiliseraient le ProductsWithPriceQuartileTableAdapter, tandis que celles qui n'avaient pas besoin pourraient continuer à utiliser le ProductsTableAdapter.

En ajoutant un nouveau TableAdapter, les DataTables restent inchangés et leurs colonnes reflètent 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 ces pages ASP.NET qui affichaient la colonne PriceQuartile doivent également fournir une prise en charge de l’insertion, de la mise à jour et de la suppression, le ProductsWithPriceQuartileTableAdapter doit avoir ses propriétés InsertCommand, UpdateCommand, et DeleteCommand correctement configurées. Bien que ces propriétés reflètent les ProductsTableAdapter s, cette configuration introduit une étape supplémentaire. De plus, il existe maintenant deux façons de mettre à jour, supprimer ou ajouter un produit à la base de données : via les classes ProductsTableAdapter et ProductsWithPriceQuartileTableAdapter.

Le téléchargement de ce didacticiel inclut une ProductsWithPriceQuartileTableAdapter classe dans DataSet NorthwindWithSprocs qui illustre cette autre approche.

Résumé

Dans la plupart des scénarios, toutes les méthodes d’un TableAdapter retournent le même jeu de champs de données, mais il existe des moments où une méthode particulière ou deux peuvent avoir besoin de retourner un champ supplémentaire. Par exemple, dans le didacticiel Maître/Détail utilisant une liste à puces d’enregistrements maîtres avec un Details DataList, nous avons ajouté une méthode à ce CategoriesTableAdapter qui, en plus des champs de données de la requête principale, retournait un champ NumberOfProducts qui indiquait le nombre de produits associés à chaque catégorie. Dans ce tutoriel, nous avons examiné l’ajout d’une méthode dans le ProductsTableAdapter champ qui a retourné un PriceQuartile champ en plus des champs de données de la requête principale. Pour capturer des champs de données supplémentaires retournés par les méthodes de TableAdapter, nous devons ajouter des colonnes correspondantes à DataTable.

Si vous envisagez d’ajouter manuellement des colonnes à 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 est exécuté, toutes les listes de champs de données de méthodes reviennent aux champs de données retournés par la requête principale. Ce problème ne s’étend pas aux procédures stockées, c’est pourquoi ils sont recommandés et ont été utilisés 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 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. Les réviseurs principaux 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.

Précédent Suivant