Création de procédures stockées pour les TableAdapters de dataset typé (C#)

par Scott Mitchell

Télécharger le PDF

Dans les didacticiels précédents, nous avons créé des instructions SQL dans notre code et transmis les instructions à la base de données à exécuter. Une autre approche consiste à utiliser des procédures stockées, où les instructions SQL sont prédéfinis sur la base de données. Dans ce tutoriel, nous apprenons à faire en sorte que l’Assistant TableAdapter génère de nouvelles procédures stockées pour nous.

Présentation

La couche d’accès aux données (DAL) pour ces didacticiels utilise des jeux de données typés. Comme indiqué dans le didacticiel Création d’une couche d’accès aux données , les DataSets typés se composent de DataTables fortement typés et de TableAdapters. Les DataTables représentent les entités logiques dans le système, tandis que les TableAdapters s'interface avec la base de données sous-jacente pour effectuer le travail d'accès aux données. Cela inclut la remplissage des DataTables avec des données, l’exécution de requêtes qui retournent des données scalaires et l’insertion, la mise à jour et la suppression d’enregistrements de la base de données.

Les commandes SQL exécutées par les TableAdapters peuvent être des instructions SQL ad hoc, telles que SELECT columnList FROM TableName, ou des procédures stockées. Les TableAdapters de notre architecture utilisent des instructions SQL ad hoc. Toutefois, de nombreux développeurs et administrateurs de base de données préfèrent les procédures stockées par rapport aux instructions SQL ad hoc pour des raisons de sécurité, de maintenance et de mise à jour. D’autres préfèrent vivement les instructions SQL ad hoc pour leur flexibilité. Dans mon propre travail, je privilégie les procédures stockées par rapport aux instructions SQL ad hoc, mais j’ai choisi d’utiliser des instructions SQL ad hoc pour simplifier les didacticiels précédents.

Lors de la définition d’une TableAdapter ou de l’ajout de nouvelles méthodes, l’Assistant TableAdapter facilite la création de procédures stockées ou l’utilisation de procédures stockées existantes comme il le fait pour utiliser des instructions SQL ad hoc. Dans ce tutoriel, nous allons examiner comment l'Assistant TableAdapter peut générer automatiquement les procédures stockées. Dans le tutoriel suivant, nous allons examiner comment configurer les méthodes de TableAdapter pour utiliser des procédures stockées existantes ou créées manuellement.

Remarque

Consultez l’entrée de blog de Rob Howard Don’t Use Stored Procedures Yet ? et l’entrée de blog de Frans Bouma intitulée Stored Procedures are Bad, M Kay ? pour un débat animé sur les avantages et les inconvénients des procédures stockées et du SQL ad hoc.

Principes de base des procédures stockées

Les fonctions sont une construction commune à tous les langages de programmation. Une fonction est une collection d’instructions exécutées lorsque la fonction est appelée. Les fonctions peuvent accepter les paramètres d’entrée et retourner éventuellement une valeur. Les procédures stockées sont des constructions de base de données qui partagent de nombreuses similitudes avec les fonctions dans les langages de programmation. Une procédure stockée est constituée d’un ensemble d’instructions T-SQL exécutées lorsque la procédure stockée est appelée. Une procédure stockée peut accepter zéro à de nombreux paramètres d’entrée et retourner des valeurs scalaires, des paramètres de sortie ou, le plus souvent, des jeux de résultats à partir de SELECT requêtes.

Remarque

Les procédures stockées sont souvent appelées sprocs ou PS.

Les procédures stockées sont créées à l’aide de l’instruction CREATE PROCEDURE T-SQL. Par exemple, le script T-SQL suivant crée une procédure stockée nommée GetProductsByCategoryID qui prend un paramètre unique nommé @CategoryID et retourne les champs ProductID, ProductName, UnitPrice et Discontinued de ces colonnes dans la table Products qui ont une valeur correspondante CategoryID.

CREATE PROCEDURE GetProductsByCategoryID
(
    @CategoryID int
)
AS
SELECT ProductID, ProductName, UnitPrice, Discontinued
FROM Products
WHERE CategoryID = @CategoryID

Une fois cette procédure stockée créée, elle peut être appelée à l’aide de la syntaxe suivante :

EXEC GetProductsByCategory categoryID

Remarque

Dans le tutoriel suivant, nous allons examiner la création de procédures stockées via l’IDE Visual Studio. Pour ce tutoriel, toutefois, nous allons laisser l’Assistant TableAdapter générer automatiquement les procédures stockées pour nous.

En plus de retourner simplement des données, les procédures stockées sont souvent utilisées pour effectuer plusieurs commandes de base de données dans l’étendue d’une seule transaction. Une procédure stockée nommée DeleteCategory, par exemple, peut prendre dans un @CategoryID paramètre et exécuter deux DELETE instructions : d’abord, une pour supprimer les produits associés et une deuxième suppression de la catégorie spécifiée. Plusieurs instructions au sein d’une procédure stockée ne sont pas automatiquement encapsulées dans une transaction. Des commandes T-SQL supplémentaires doivent être émises pour s’assurer que les plusieurs commandes de la procédure stockée sont traitées comme une opération atomique. Nous allons voir comment encapsuler les commandes d’une procédure stockée dans le cadre d’une transaction dans le prochain didacticiel.

Lorsque vous utilisez des procédures stockées dans une architecture, les méthodes de la couche d’accès aux données appellent une procédure stockée particulière plutôt que d’émettre une instruction SQL ad hoc. Cela centralise l’emplacement des instructions SQL exécutées (sur la base de données) plutôt que de les définir dans l’architecture de l’application. Cette centralisation facilite sans doute la recherche, l’analyse et l’optimisation des requêtes et fournit une image beaucoup plus claire de l’emplacement et de l’utilisation de la base de données.

Pour plus d’informations sur les notions de base des procédures stockées, consultez les ressources de la section Autres lectures à la fin de ce tutoriel.

Étape 1 : Création des pages web de scénarios de couche d’accès aux données avancées

Avant de commencer notre discussion sur la création d’un DAL à l’aide de procédures stockées, prenons d’abord un moment pour créer les pages ASP.NET dans notre projet de site web dont nous aurons besoin pour cela et les prochains tutoriels. Commencez par ajouter un nouveau dossier nommé AdvancedDAL. Ensuite, ajoutez les ASP.NET pages suivantes à ce dossier, en veillant à associer chaque page à la Site.master page maître :

• Default.aspx
• NewSprocs.aspx
• ExistingSprocs.aspx
• JOINs.aspx
• AddingColumns.aspx
• ComputedColumns.aspx
• EncryptingConfigSections.aspx
• ManagedFunctionsAndSprocs.aspx

Figure 1 : Ajouter les pages ASP.NET pour les didacticiels sur les scénarios de couche d’accès aux données avancés

Comme dans les autres dossiers, Default.aspx dans le AdvancedDAL dossier répertorie les didacticiels de sa section. Rappelez-vous que le SectionLevelTutorialListing.ascx contrôle utilisateur fournit cette fonctionnalité. Par conséquent, ajoutez ce contrôle utilisateur en Default.aspx le faisant glisser de l’Explorateur de solutions vers l’affichage Création de la page.

Figure 2 : Ajouter le contrôle utilisateur à SectionLevelTutorialListing.ascx (Default.aspx de taille complète)

Enfin, ajoutez ces pages en tant qu’entrées au Web.sitemap fichier. Plus précisément, ajoutez le balisage suivant après le travail avec les données groupées <siteMapNode> :

<siteMapNode url="~/AdvancedDAL/Default.aspx" 
    title="Advanced DAL Scenarios" 
    description="Explore a number of advanced Data Access Layer scenarios.">
    
    <siteMapNode url="~/AdvancedDAL/NewSprocs.aspx" 
        title="Creating New Stored Procedures for TableAdapters" 
        description="Learn how to have the TableAdapter wizard automatically 
            create and use stored procedures." />
    <siteMapNode url="~/AdvancedDAL/ExistingSprocs.aspx" 
        title="Using Existing Stored Procedures for TableAdapters" 
        description="See how to plug existing stored procedures into a 
            TableAdapter." />
    <siteMapNode url="~/AdvancedDAL/JOINs.aspx" 
        title="Returning Data Using JOINs" 
        description="Learn how to augment your DataTables to work with data 
            returned from multiple tables via a JOIN query." />
    <siteMapNode url="~/AdvancedDAL/AddingColumns.aspx" 
        title="Adding DataColumns to a DataTable" 
        description="Master adding new columns to an existing DataTable." />
    <siteMapNode url="~/AdvancedDAL/ComputedColumns.aspx" 
        title="Working with Computed Columns" 
        description="Explore how to work with computed columns when using 
            Typed DataSets." />
    <siteMapNode url="~/AdvancedDAL/EncryptingConfigSections.aspx" 
        title="Protected Connection Strings in Web.config" 
        description="Protect your connection string information in 
            Web.config using encryption." />
    <siteMapNode url="~/AdvancedDAL/ManagedFunctionsAndSprocs.aspx" 
        title="Creating Managed SQL Functions and Stored Procedures" 
        description="See how to create SQL functions and stored procedures 
            using managed code." />
</siteMapNode>

Après la mise à jour Web.sitemap, prenez un moment pour afficher le site web des didacticiels via un navigateur. Le menu de gauche inclut désormais des éléments pour les didacticiels avancés sur les scénarios DAL.

Figure 3 : La carte de site inclut désormais des entrées pour les didacticiels avancés sur les scénarios DAL

Étape 2 : Configuration d’un TableAdapter pour créer de nouvelles procédures stockées

Pour illustrer la création d’une couche d’accès aux données qui utilise des procédures stockées au lieu d’instructions SQL ad hoc, nous allons créer un jeu de données typé dans le ~/App_Code/DAL dossier nommé NorthwindWithSprocs.xsd. Étant donné que nous avons parcouru ce processus en détail dans les didacticiels précédents, nous allons passer rapidement aux étapes décrites ici. Si vous êtes bloqué ou avez besoin d’instructions pas à pas supplémentaires dans la création et la configuration d’un Jeu de données typé, reportez-vous au didacticiel Création d’une couche d’accès aux données .

Ajoutez un nouveau DataSet au projet en cliquant avec le bouton droit sur le DAL dossier, en choisissant Ajouter un nouvel élément et en sélectionnant le modèle DataSet comme illustré dans la figure 4.

Figure 4 : Ajouter un nouveau jeu de données typé au projet nommé NorthwindWithSprocs.xsd (cliquez pour afficher l’image de taille complète)

Cela créera le nouveau jeu de données typé, ouvrira son concepteur, créera un TableAdapter et lancera l’Assistant Configuration de TableAdapter. La première étape de l’Assistant Configuration TableAdapter nous demande de sélectionner la base de données à utiliser. La chaîne de connexion à la base de données Northwind doit être répertoriée dans la liste déroulante. Sélectionnez cette option, puis cliquez sur Suivant.

À partir de cet écran suivant, nous pouvons choisir la façon dont TableAdapter doit accéder à la base de données. Dans les didacticiels précédents, nous avons sélectionné la première option, Utiliser des instructions SQL. Pour ce tutoriel, sélectionnez la deuxième option, Créer de nouvelles procédures stockées, puis cliquez sur Suivant.

Figure 5 : Indiquez à TableAdapter de créer des procédures stockées (cliquez pour afficher l’image de taille complète)

Tout comme avec l’utilisation d’instructions SQL ad hoc, à l’étape suivante, nous sommes invités à fournir l’instruction SELECT pour la requête principale de TableAdapter. Mais au lieu d’utiliser l’instruction SELECT entrée ici pour effectuer une requête ad hoc directement, l’Assistant TableAdapter crée une procédure stockée qui contient cette SELECT requête.

Utilisez la requête suivante SELECT pour cette TableAdapter :

SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products

Figure 6 : Entrer la requête (SELECT de taille complète)

Remarque

La requête ci-dessus diffère légèrement de la requête principale de ProductsTableAdapter dans le Typed DataSet Northwind. Rappelez-vous que le jeu de données typé ProductsTableAdapter dans Northwind comprend deux sous-requêtes corrélées pour récupérer le nom de la catégorie et le nom de la société de chaque catégorie de produit et fournisseur. Dans la prochaine mise à jour de TableAdapter pour utiliser le didacticiel JOINs , nous allons examiner l’ajout de ces données associées à ce TableAdapter.

Prenez un moment pour cliquer sur le bouton Options avancées. À partir de là, nous pouvons spécifier si l’Assistant doit également générer des instructions d’insertion, de mise à jour et de suppression pour TableAdapter, s’il faut utiliser la concurrence optimiste et si la table de données doit être actualisée après les insertions et mises à jour. L’option Générer des instructions Insert, Update et Delete est cochée par défaut. Laissez-le activé. Pour ce tutoriel, laissez les options d’accès concurrentiel optimiste désactivées.

Lorsque les procédures stockées sont créées automatiquement par l'Assistant TableAdapter, il semble que l'option Actualiser la table de données soit ignorée. Que cette case soit cochée, les procédures stockées d’insertion et de mise à jour obtenues récupèrent l’enregistrement juste inséré ou mis à jour, comme nous le verrons à l’étape 3.

Figure 7 : Laisser l'option Générer l'insertion, la mise à jour et la suppression cochée

Remarque

Si l’option Utiliser l’accès concurrentiel optimiste est cochée, l’Assistant ajoute des conditions supplémentaires à la WHERE clause qui empêchent la mise à jour des données en cas de modifications dans d’autres champs. Pour plus d'informations sur l'utilisation de la fonctionnalité intégrée de contrôle de concurrence optimiste de TableAdapter, reportez-vous au didacticiel Implémentation de la concurrence optimiste.

Après avoir entré la SELECT requête et confirmé que l’option Générer l’insertion, la mise à jour et la suppression des instructions est cochée, cliquez sur Suivant. Cet écran suivant, illustré à la figure 8, demande les noms des procédures stockées que l'assistant va créer pour sélectionner, insérer, mettre à jour et supprimer des données. Remplacez ces noms de procédures stockées par Products_Select, Products_Insert, Products_Updateet Products_Delete.

Figure 8 : Renommer les procédures stockées (cliquez pour afficher l’image de taille complète)

Pour voir le T-SQL que l'assistant TableAdapter utilisera pour créer les quatre procédures stockées, cliquez sur le bouton Aperçu du script SQL. Dans la boîte de dialogue Aperçu du script SQL, vous pouvez enregistrer le script dans un fichier ou le copier dans le Presse-papiers.

Figure 9 : Aperçu du script SQL utilisé pour générer les procédures stockées

Après avoir nommé les procédures stockées, cliquez sur Suivant pour nommer les méthodes correspondantes de TableAdapter. Tout comme lors de l’utilisation d’instructions SQL ad hoc, nous pouvons créer des méthodes qui remplissent un DataTable existant ou retournent une nouvelle instruction. Nous pouvons également spécifier si TableAdapter doit inclure le modèle DB-Direct pour l’insertion, la mise à jour et la suppression d’enregistrements. Laissez les trois cases à cocher cochées, mais renommez la méthode GetProducts Return a DataTable (comme illustré dans la figure 10).

Figure 10 : Nommer les méthodes Fill et GetProducts (Cliquez pour afficher l’image de taille complète)

Cliquez sur Suivant pour afficher un résumé des étapes que l’Assistant effectuera. Terminez l’Assistant en cliquant sur le bouton Terminer. Une fois l’Assistant terminé, vous êtes retourné au Concepteur de DataSet, qui doit maintenant inclure le ProductsDataTable.

Figure 11 : Le concepteur de jeux de données affiche l’image nouvellement ajoutée ProductsDataTable (cliquez pour afficher l’image de taille complète)

Étape 3 : Vérification des procédures stockées nouvellement créées

L’Assistant TableAdapter utilisé à l’étape 2 a créé automatiquement les procédures stockées pour sélectionner, insérer, mettre à jour et supprimer des données. Ces procédures stockées peuvent être consultées ou modifiées via Visual Studio en accédant à l’Explorateur de serveurs et en explorant le dossier Procédures stockées de la base de données. Comme le montre la figure 12, la base de données Northwind contient quatre nouvelles procédures stockées : Products_Delete, , Products_InsertProducts_Selectet Products_Update.

Figure 12 : Les quatre procédures stockées créées à l’étape 2 se trouvent dans le dossier procédures stockées de la base de données

Remarque

Si vous ne voyez pas l’Explorateur de serveurs, accédez au menu Affichage et choisissez l’option Explorateur de serveurs. Si vous ne voyez pas les procédures stockées liées au produit ajoutées à l’étape 2, essayez de cliquer avec le bouton droit sur le dossier Procédures stockées et de choisir Actualiser.

Pour afficher ou modifier une procédure stockée, double-cliquez sur son nom dans l’Explorateur de serveurs ou, sinon, cliquez avec le bouton droit sur la procédure stockée et choisissez Ouvrir. La figure 13 montre la Products_Delete procédure stockée, lorsqu’elle est ouverte.

Figure 13 : Les procédures stockées peuvent être ouvertes et modifiées à partir de Visual Studio (cliquez pour afficher l’image de taille complète)

Le contenu des procédures stockées Products_Delete et Products_Select est assez simple. Les procédures stockées Products_Insert et Products_Update, en revanche, nécessitent une inspection plus approfondie, car elles effectuent toutes deux une instruction SELECT après leurs instructions INSERT et UPDATE. Par exemple, le code SQL suivant compose la Products_Insert procédure stockée :

ALTER PROCEDURE dbo.Products_Insert
(
    @ProductName nvarchar(40),
    @SupplierID int,
    @CategoryID int,
    @QuantityPerUnit nvarchar(20),
    @UnitPrice money,
    @UnitsInStock smallint,
    @UnitsOnOrder smallint,
    @ReorderLevel smallint,
    @Discontinued bit
)
AS
    SET NOCOUNT OFF;
INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], 
    [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) 
VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, 
    @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
    
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, 
    UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued 
FROM Products 
WHERE (ProductID = SCOPE_IDENTITY())

La procédure stockée accepte en tant que paramètres d’entrée les Products colonnes retournées par la requête SELECT spécifiée dans l'Assistant TableAdapter et ces valeurs sont utilisées dans une instruction INSERT. Après l’instruction INSERT , une SELECT requête est utilisée pour retourner les Products valeurs de colonne (y compris le ProductID) de l’enregistrement nouvellement ajouté. Cette fonctionnalité d’actualisation est utile lors de l’ajout d’un nouvel enregistrement à l’aide du modèle Batch Update, car elle met automatiquement à jour les propriétés d’instances ProductRow nouvellement ajoutées ProductID avec les valeurs incrémentées automatiquement affectées par la base de données.

Le code suivant illustre cette fonctionnalité. Il contient un ProductsTableAdapter et un ProductsDataTable créé pour le NorthwindWithSprocs jeu de données typé. Un nouveau produit est ajouté à la base de données en créant une instance ProductsRow, en fournissant ses valeurs et en appelant la méthode Update de TableAdapter, en passant ProductsDataTable en argument. En interne, la méthode TableAdapter Update énumère les ProductsRow instances du DataTable transmis (dans cet exemple, il n’y en a qu’une seule - celle que nous venons d’ajouter) et exécute la commande d’insertion, de mise à jour ou de suppression appropriée. Dans ce cas, la Products_Insert procédure stockée est exécutée, ce qui ajoute un nouvel enregistrement à la Products table et retourne les détails de l’enregistrement nouvellement ajouté. L’instance ProductsRow voit ensuite sa valeur ProductID mise à jour. Une fois la Update méthode terminée, nous pouvons accéder à la valeur de l’enregistrement ProductID nouvellement ajouté via la ProductsRow propriété s ProductID .

// Create the ProductsTableAdapter and ProductsDataTable
NorthwindWithSprocsTableAdapters.ProductsTableAdapter productsAPI = 
    new NorthwindWithSprocsTableAdapters.ProductsTableAdapter();
NorthwindWithSprocs.ProductsDataTable products = 
    new NorthwindWithSprocs.ProductsDataTable();
// Create a new ProductsRow instance and set its properties
NorthwindWithSprocs.ProductsRow product = products.NewProductsRow();
product.ProductName = "New Product";
product.CategoryID = 1;  // Beverages
product.Discontinued = false;
// Add the ProductsRow instance to the DataTable
products.AddProductsRow(product);
// Update the DataTable using the Batch Update pattern
productsAPI.Update(products);
// At this point, we can determine the value of the newly-added record's ProductID
int newlyAddedProductIDValue = product.ProductID;

La Products_Update procédure stockée inclut de même une SELECT instruction après son UPDATE instruction.

ALTER PROCEDURE dbo.Products_Update
(
    @ProductName nvarchar(40),
    @SupplierID int,
    @CategoryID int,
    @QuantityPerUnit nvarchar(20),
    @UnitPrice money,
    @UnitsInStock smallint,
    @UnitsOnOrder smallint,
    @ReorderLevel smallint,
    @Discontinued bit,
    @Original_ProductID int,
    @ProductID int
)
AS
    SET NOCOUNT OFF;
UPDATE [Products] 
SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, 
    [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, 
    [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, 
    [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, 
    [Discontinued] = @Discontinued 
WHERE (([ProductID] = @Original_ProductID));
    
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, 
    UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued 
FROM Products 
WHERE (ProductID = @ProductID)

Notez que cette procédure stockée inclut deux paramètres d’entrée pour ProductID: @Original_ProductID et @ProductID. Cette fonctionnalité permet des scénarios où la clé primaire peut être modifiée. Par exemple, dans une base de données d’employé, chaque enregistrement d’employé peut utiliser le numéro de sécurité sociale de l’employé comme clé primaire. Pour modifier le numéro de sécurité sociale d’un employé existant, le nouveau numéro de sécurité sociale et le numéro d’origine doivent être fournis. Pour la Products table, cette fonctionnalité n’est pas nécessaire, car la ProductID colonne est une IDENTITY colonne et ne doit jamais être modifiée. En fait, l’instruction UPDATE de la procédure stockée Products_Update ne comprend pas la colonne ProductID dans sa liste de colonnes. Par conséquent, bien que @Original_ProductID soit utilisé dans l'instruction UPDATE clause WHERE, il est superflu pour la table Products et peut être remplacé par le paramètre @ProductID. Lors de la modification des paramètres d’une procédure stockée, il est important que les méthodes TableAdapter qui utilisent cette procédure stockée soient également mises à jour.

Étape 4 : Modification des paramètres d’une procédure stockée et mise à jour de TableAdapter

Étant donné que le @Original_ProductID paramètre est superflu, nous allons le supprimer complètement de la Products_Update procédure stockée. Ouvrez la Products_Update procédure stockée, supprimez le @Original_ProductID paramètre et, dans la clause WHERE de l'instruction UPDATE, remplacez le nom du paramètre utilisé de @Original_ProductID par @ProductID. Après avoir apporté ces modifications, le T-SQL dans la procédure stockée doit ressembler à ce qui suit :

ALTER PROCEDURE dbo.Products_Update
(
    @ProductName nvarchar(40),
    @SupplierID int,
    @CategoryID int,
    @QuantityPerUnit nvarchar(20),
    @UnitPrice money,
    @UnitsInStock smallint,
    @UnitsOnOrder smallint,
    @ReorderLevel smallint,
    @Discontinued bit,
    @ProductID int
)
AS
    SET NOCOUNT OFF;
UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, 
    [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, 
    [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, 
    [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, 
    [Discontinued] = @Discontinued 
WHERE (([ProductID] = @ProductID));
    
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, 
    UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued 
FROM Products 
WHERE (ProductID = @ProductID)

Pour enregistrer ces modifications dans la base de données, cliquez sur l’icône Enregistrer dans la barre d’outils ou appuyez sur Ctrl+S. À ce stade, la Products_Update procédure stockée ne s’attend pas à un paramètre d’entrée @Original_ProductID , mais TableAdapter est configurée pour passer ce paramètre. Vous pouvez voir les paramètres que le TableAdapter enverra à la Products_Update procédure stockée en sélectionnant le TableAdapter dans le Concepteur DataSet, en accédant à la fenêtre Propriétés, puis en cliquant sur les points de suspension de la collection UpdateCommandParameters. La boîte de dialogue Parameters Collection Editor s’affiche et est illustrée à la figure 14.

Figure 14 : L’éditeur de collection Parameters répertorie les paramètres utilisés et transmis à la Products_Update procédure stockée

Vous pouvez supprimer ce paramètre ici en sélectionnant simplement le @Original_ProductID paramètre dans la liste des membres et en cliquant sur le bouton Supprimer.

Vous pouvez également actualiser les paramètres utilisés pour toutes les méthodes en cliquant avec le bouton droit sur TableAdapter dans le Concepteur et en choisissant Configurer. Cela affiche l’Assistant Configuration de TableAdapter, répertoriant les procédures stockées utilisées pour sélectionner, insérer, mettre à jour et supprimer, ainsi que les paramètres que les procédures stockées s’attendent à recevoir. Si vous cliquez sur la liste déroulante Mettre à jour, vous pouvez voir les Products_Update paramètres d’entrée attendus des procédures stockées, qui n’incluent @Original_ProductID plus (voir la figure 15). Cliquez simplement sur Terminer pour mettre à jour automatiquement la collection de paramètres utilisée par TableAdapter.

Figure 15 : Vous pouvez également utiliser l’Assistant Configuration de TableAdapter pour actualiser ses collections de paramètres de méthodes (cliquez pour afficher l’image de taille complète)

Étape 5 : Ajout de méthodes TableAdapter supplémentaires

Comme l’illustre l’étape 2, lors de la création d’un tableAdapter, il est facile de générer automatiquement les procédures stockées correspondantes. Il en va de même lors de l’ajout de méthodes supplémentaires à un TableAdapter. Pour illustrer cela, ajoutons une méthode GetProductByProductID(productID) au ProductsTableAdapter créé à l'étape 2. Cette méthode prend comme entrée une ProductID valeur et retourne des détails sur le produit spécifié.

Commencez par cliquer avec le bouton droit sur TableAdapter et choisissez Ajouter une requête dans le menu contextuel.

Figure 16 : Ajouter une nouvelle requête à TableAdapter

Cela démarrera l'assistant Configuration des requêtes TableAdapter, qui sollicite d'abord comment le TableAdapter doit accéder à la base de données. Pour créer une procédure stockée, choisissez l’option Créer une procédure stockée, puis cliquez sur Suivant.

Figure 17 : Choisir l’option Créer une procédure stockée (cliquez pour afficher l’image de taille complète)

L'écran suivant nous demande d'identifier le type de requête à exécuter : renvoie-t-elle un ensemble de lignes ou une valeur scalaire unique, ou effectue-t-elle une des instructions UPDATE, INSERT ou DELETE. Étant donné que la GetProductByProductID(productID) méthode retourne une ligne, laissez l’option SELECT qui retourne l’option de ligne sélectionnée et appuyez sur Suivant.

Figure 18 : Choisissez l’option SELECT qui retourne l’option de ligne (cliquez pour afficher l’image de taille complète)

L’écran suivant affiche la requête principale de TableAdapter, qui répertorie simplement le nom de la procédure stockée (dbo.Products_Select). Remplacez le nom de la procédure stockée par l’instruction suivante SELECT , qui retourne tous les champs de produit pour un produit spécifié :

SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID

Figure 19 : Remplacer le nom de la procédure stockée par une SELECT requête (cliquez pour afficher l’image de taille complète)

L’écran suivant vous demande de nommer la procédure stockée qui sera créée. Entrez le nom Products_SelectByProductID , puis cliquez sur Suivant.

Figure 20 : Nommer la nouvelle procédure Products_SelectByProductID stockée (cliquez pour afficher l’image de taille complète)

L'étape finale de l'Assistant nous permet de modifier les noms de méthode générés, ainsi que d'indiquer s'il faut utiliser le modèle de remplissage d'un DataTable, le modèle de retour d'un DataTable, ou les deux. Pour cette méthode, laissez les deux options activées, mais renommez les méthodes en FillByProductID et GetProductByProductID. Cliquez sur Suivant pour afficher un résumé des étapes que l’Assistant effectuera, puis cliquez sur Terminer pour terminer l’Assistant.

Figure 21 : Renommer les méthodes TableAdapter vers FillByProductID et GetProductByProductID (Cliquez pour afficher l’image de taille complète)

Une fois l’Assistant terminé, TableAdapter dispose d’une nouvelle méthode disponible GetProductByProductID(productID) qui, lorsqu’elle est appelée, exécute la procédure stockée Products_SelectByProductID qui vient d’être créée. Prenez un moment pour afficher cette nouvelle procédure stockée à partir de l’Explorateur de serveurs en explorant le dossier Procédures stockées et en ouvrant Products_SelectByProductID (si vous ne le voyez pas, cliquez avec le bouton droit sur le dossier Procédures stockées et choisissez Actualiser).

Notez que la procédure stockée SelectByProductID prend @ProductID comme paramètre d’entrée et exécute l’instruction SELECT que nous avons saisie dans l’assistant.

ALTER PROCEDURE dbo.Products_SelectByProductID
(
    @ProductID int
)
AS
    SET NOCOUNT ON;
SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID

Étape 6 : Création d’une classe de couche logique métier

Tout au long de la série de tutoriels, nous nous efforçons de maintenir une architecture en couches dans laquelle la couche présentation a effectué tous ses appels à la couche logique métier (BLL). Pour respecter cette décision de conception, nous devons d’abord créer une classe BLL pour le nouveau Jeu de données typé avant de pouvoir accéder aux données de produit à partir de la couche Présentation.

Créez un fichier de classe nommé ProductsBLLWithSprocs.cs dans le ~/App_Code/BLL dossier et ajoutez-le au code suivant :

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using NorthwindWithSprocsTableAdapters;
[System.ComponentModel.DataObject]
public class ProductsBLLWithSprocs
{
    private ProductsTableAdapter _productsAdapter = null;
    protected ProductsTableAdapter Adapter
    {
        get
        {
            if (_productsAdapter == null)
                _productsAdapter = new ProductsTableAdapter();
            return _productsAdapter;
        }
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Select, true)]
    public NorthwindWithSprocs.ProductsDataTable GetProducts()
    {
        return Adapter.GetProducts();
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Select, false)]
    public NorthwindWithSprocs.ProductsDataTable GetProductByProductID(int productID)
    {
        return Adapter.GetProductByProductID(productID);
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Insert, true)]
    public bool AddProduct
        (string productName, int? supplierID, int? categoryID, 
         string quantityPerUnit, decimal? unitPrice, short? unitsInStock, 
         short? unitsOnOrder, short? reorderLevel, bool discontinued)
    {
        // Create a new ProductRow instance
        NorthwindWithSprocs.ProductsDataTable products = 
            new NorthwindWithSprocs.ProductsDataTable();
        NorthwindWithSprocs.ProductsRow product = products.NewProductsRow();
        product.ProductName = productName;
        if (supplierID == null) 
            product.SetSupplierIDNull(); 
        else 
            product.SupplierID = supplierID.Value;
        if (categoryID == null) 
            product.SetCategoryIDNull(); 
        else 
            product.CategoryID = categoryID.Value;
        if (quantityPerUnit == null) 
            product.SetQuantityPerUnitNull(); 
        else 
            product.QuantityPerUnit = quantityPerUnit;
        if (unitPrice == null) 
            product.SetUnitPriceNull(); 
        else 
            product.UnitPrice = unitPrice.Value;
        if (unitsInStock == null) 
            product.SetUnitsInStockNull(); 
        else 
            product.UnitsInStock = unitsInStock.Value;
        if (unitsOnOrder == null) 
            product.SetUnitsOnOrderNull(); 
        else 
            product.UnitsOnOrder = unitsOnOrder.Value;
        if (reorderLevel == null)
            product.SetReorderLevelNull(); 
        else 
            product.ReorderLevel = reorderLevel.Value;
        product.Discontinued = discontinued;
        // Add the new product
        products.AddProductsRow(product);
        int rowsAffected = Adapter.Update(products);
        // Return true if precisely one row was inserted, otherwise false
        return rowsAffected == 1;
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Update, true)]
    public bool UpdateProduct
        (string productName, int? supplierID, int? categoryID, string quantityPerUnit,
        decimal? unitPrice, short? unitsInStock, short? unitsOnOrder, 
        short? reorderLevel, bool discontinued, int productID)
    {
        NorthwindWithSprocs.ProductsDataTable products = 
            Adapter.GetProductByProductID(productID);
        if (products.Count == 0)
            // no matching record found, return false
            return false;
        NorthwindWithSprocs.ProductsRow product = products[0];
        product.ProductName = productName;
        if (supplierID == null) 
            product.SetSupplierIDNull(); 
        else 
            product.SupplierID = supplierID.Value;
        if (categoryID == null) 
            product.SetCategoryIDNull(); 
        else 
            product.CategoryID = categoryID.Value;
        if (quantityPerUnit == null) 
            product.SetQuantityPerUnitNull(); 
        else 
            product.QuantityPerUnit = quantityPerUnit;
        if (unitPrice == null) 
            product.SetUnitPriceNull(); 
        else 
            product.UnitPrice = unitPrice.Value;
        if (unitsInStock == null) 
            product.SetUnitsInStockNull(); 
        else 
            product.UnitsInStock = unitsInStock.Value;
        if (unitsOnOrder == null) 
            product.SetUnitsOnOrderNull(); 
        else 
            product.UnitsOnOrder = unitsOnOrder.Value;
        if (reorderLevel == null) 
            product.SetReorderLevelNull(); 
        else 
            product.ReorderLevel = reorderLevel.Value;
        product.Discontinued = discontinued;
        // Update the product record
        int rowsAffected = Adapter.Update(product);
        // Return true if precisely one row was updated, otherwise false
        return rowsAffected == 1;
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Delete, true)]
    public bool DeleteProduct(int productID)
    {
        int rowsAffected = Adapter.Delete(productID);
        // Return true if precisely one row was deleted, otherwise false
        return rowsAffected == 1;
    }
}

Cette classe imite la sémantique de la classe ProductsBLL des didacticiels précédents, mais utilise les objets ProductsTableAdapter et ProductsDataTable du NorthwindWithSprocs DataSet. Par exemple, au lieu d'avoir une using NorthwindTableAdapters instruction au début du fichier de classe, comme le fait ProductsBLL, la classe ProductsBLLWithSprocs utilise using NorthwindWithSprocsTableAdapters. De même, les objets ProductsDataTable et ProductsRow utilisés dans cette classe sont préfixés par l'espace de noms NorthwindWithSprocs. La ProductsBLLWithSprocs classe fournit deux méthodes d’accès aux données, GetProducts ainsi que GetProductByProductIDdes méthodes pour ajouter, mettre à jour et supprimer une seule instance de produit.

Étape 7 : Utilisation duNorthwindWithSprocsDataSet à partir de la couche Présentation

À ce stade, nous avons créé un dal qui utilise des procédures stockées pour accéder aux données de base de données sous-jacentes et les modifier. Nous avons également créé une BLL rudimentaire avec des méthodes pour récupérer tous les produits ou un produit particulier, ainsi que des méthodes d’ajout, de mise à jour et de suppression de produits. Pour arrondir ce didacticiel, nous allons créer une page ASP.NET qui utilise la classe BLL ProductsBLLWithSprocs pour afficher, mettre à jour et supprimer des enregistrements.

Ouvrez la NewSprocs.aspx page dans le dossier AdvancedDAL et faites glisser un GridView à partir de la boîte à outils sur le Concepteur, et nommez-le Products. À partir de la balise intelligente de GridView, choisissez de la lier à un nouvel ObjectDataSource nommé ProductsDataSource. Configurez ObjectDataSource pour utiliser la classe, comme illustré dans la ProductsBLLWithSprocs figure 22.

Figure 22 : Configurer ObjectDataSource pour utiliser la classe (ProductsBLLWithSprocs de taille complète)

La liste déroulante de l’onglet SELECT comporte deux options et GetProductsGetProductByProductID. Comme nous voulons afficher tous les produits dans GridView, choisissez la GetProducts méthode. Les listes déroulantes des onglets UPDATE, INSERT et DELETE n’ont qu’une seule méthode. Vérifiez que chacune de ces listes déroulantes a sa méthode appropriée sélectionnée, puis cliquez sur Terminer.

Une fois l’Assistant ObjectDataSource terminé, Visual Studio ajoute des BoundFields et un CheckBoxField au GridView pour les champs de données de produit. Activez les fonctionnalités d’édition et de suppression intégrées de GridView en vérifiant les options Activer l’édition et Activer la suppression présentes dans la balise active.

Figure 23 : La page contient une grille avec les fonctionnalités d'édition et de suppression activées (Cliquez pour voir l'image en taille réelle)

Comme nous l’avons vu dans les didacticiels précédents, une fois l’Assistant ObjectDataSource terminé, Visual Studio définit OldValuesParameterFormatString sur original_{0}. Cela doit être rétabli à sa valeur par défaut pour que les fonctionnalités de {0} modification des données fonctionnent correctement en fonction des paramètres attendus par les méthodes de notre BLL. Par conséquent, veillez à définir la propriété OldValuesParameterFormatString à une valeur de {0} ou à supprimer complètement la propriété de la syntaxe déclarative.

Une fois l’Assistant Configuration de la source de données terminé, la prise en charge de la modification et de la suppression dans GridView activée et la propriété ObjectDataSource OldValuesParameterFormatString revenue à sa valeur par défaut, le balisage déclaratif de votre page ressemblera à ceci :

<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False" 
    DataKeyNames="ProductID" DataSourceID="ProductsDataSource">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
        <asp:BoundField DataField="ProductID" HeaderText="ProductID" 
            InsertVisible="False" ReadOnly="True" 
            SortExpression="ProductID" />
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
            SortExpression="ProductName" />
        <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" 
            SortExpression="SupplierID" />
        <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" 
            SortExpression="CategoryID" />
        <asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" 
            SortExpression="QuantityPerUnit" />
        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" 
            SortExpression="UnitPrice" />
        <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" 
            SortExpression="UnitsInStock" />
        <asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder" 
            SortExpression="UnitsOnOrder" />
        <asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel" 
            SortExpression="ReorderLevel" />
        <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" 
            SortExpression="Discontinued" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server" 
    DeleteMethod="DeleteProduct" InsertMethod="AddProduct" 
    SelectMethod="GetProducts" TypeName="ProductsBLLWithSprocs" 
    UpdateMethod="UpdateProduct">
    <DeleteParameters>
        <asp:Parameter Name="productID" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Name="productName" Type="String" />
        <asp:Parameter Name="supplierID" Type="Int32" />
        <asp:Parameter Name="categoryID" Type="Int32" />
        <asp:Parameter Name="quantityPerUnit" Type="String" />
        <asp:Parameter Name="unitPrice" Type="Decimal" />
        <asp:Parameter Name="unitsInStock" Type="Int16" />
        <asp:Parameter Name="unitsOnOrder" Type="Int16" />
        <asp:Parameter Name="reorderLevel" Type="Int16" />
        <asp:Parameter Name="discontinued" Type="Boolean" />
        <asp:Parameter Name="productID" Type="Int32" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="productName" Type="String" />
        <asp:Parameter Name="supplierID" Type="Int32" />
        <asp:Parameter Name="categoryID" Type="Int32" />
        <asp:Parameter Name="quantityPerUnit" Type="String" />
        <asp:Parameter Name="unitPrice" Type="Decimal" />
        <asp:Parameter Name="unitsInStock" Type="Int16" />
        <asp:Parameter Name="unitsOnOrder" Type="Int16" />
        <asp:Parameter Name="reorderLevel" Type="Int16" />
        <asp:Parameter Name="discontinued" Type="Boolean" />
    </InsertParameters>
</asp:ObjectDataSource>

À ce stade, nous pourrions améliorer le GridView en personnalisant l'interface d'édition pour inclure la validation, en rendant les colonnes CategoryID et SupplierID sous forme de listes déroulantes, et ainsi de suite. Nous pourrions également ajouter une confirmation côté client au bouton Supprimer, et je vous encourage à prendre le temps d’implémenter ces améliorations. Étant donné que ces rubriques ont été abordées dans les didacticiels précédents, nous ne les aborderons pas encore ici.

Peu importe si vous améliorez gridView ou non, testez les fonctionnalités principales de la page dans un navigateur. Comme le montre la figure 24, la page répertorie les produits d’un GridView qui fournit des fonctionnalités de modification et de suppression par ligne.

Figure 24 : Les produits peuvent être consultés, modifiés et supprimés de GridView (cliquez pour afficher l’image de taille complète)

Résumé

Les TableAdapters d’un Jeu de données typé peuvent accéder aux données de la base de données à l’aide d’instructions SQL ad hoc ou via des procédures stockées. Lorsque vous travaillez avec des procédures stockées, vous pouvez utiliser des procédures stockées existantes ou l’Assistant TableAdapter peut être invité à créer de nouvelles procédures stockées en fonction d’une SELECT requête. Dans ce tutoriel, nous avons découvert comment créer automatiquement les procédures stockées pour nous.

Bien que les procédures stockées générées automatiquement permettent de gagner du temps, il existe certains cas où la procédure stockée créée par l’Assistant ne s’aligne pas sur ce que nous aurions créé par nous-mêmes. L’un des exemples est la Products_Update procédure stockée, qui attendait à la fois @Original_ProductID et @ProductID les paramètres d’entrée, même si le @Original_ProductID paramètre était superflu.

Dans de nombreux scénarios, les procédures stockées peuvent déjà avoir été créées, ou nous souhaitons peut-être les créer manuellement afin d’avoir un degré de contrôle plus précis sur les commandes de la procédure stockée. Dans les deux cas, nous souhaitons demander à TableAdapter d’utiliser des procédures stockées existantes pour ses méthodes. Nous verrons comment procéder dans le tutoriel suivant.

Bonne programmation !

Pour aller plus loin

Pour plus d’informations sur les sujets abordés dans ce tutoriel, consultez les ressources suivantes :

• Création et maintenance de procédures stockées
• Procédures stockées : vue d’ensemble
• Créer une procédure stockée

À 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 Hilton Geisenow. Vous souhaitez consulter mes prochains articles MSDN ? Si c’est le cas, déposez-moi une ligne à mitchell@4GuysFromRolla.com.

Prochain