Exporter (0) Imprimer
Développer tout

Utilisation de procédures stockées dans Visual Basic .NET

Billy Hollis

Téléchargez le fichier d'exemple StoredProcVB.NET.exe à partir du MSDN Code Center (leave-msdn france Site en anglais).

Résumé : Billy Hollis décrit les avantages de l'utilisation de procédures stockées dans des systèmes complexes, en allant bien au-delà de la simple démonstration, et fournit des exemples concrets sur la façon d'accéder aux procédures stockées et de les utiliser dans les applications.

Nous avons tendance, nous autres les programmeurs, à diviser les logiciels en deux catégories—realware et demoware. Le realware, c'est ce qui fonctionne en vrai, alors que le demoware est développé pour illustrer des concepts de programmation.

La plupart du code que vous voyez dans les articles et les manuels correspond à de la démonstration. Il est censé être simplifié par rapport au realware, pour éviter que le lecteur ne se perde dans des détails non pertinents pour le concept illustré. Mais parfois, ces versions de démonstration sont trop simplifiées. Certains détails, essentiels pour les développeurs lors de la programmation des realware, sont laissés de côté.

J'ai été confronté, récemment, à ce problème à propos de l'accès aux données. Presque tous les exemples d'accès aux données que j'ai rencontrés utilisaient des instructions SQL pour lire ou écrire dans une base de données relationnelle, telle que Microsoft SQL Server™. Cependant, dans le monde réel, cette pratique de programmation est totalement déconseillée, sauf peut-être pour des systèmes de faible capacité et limités. Une application n-couche correctement conçue utilise des procédures stockées à la place des instructions SQL pour l'accès aux données.

Les procédures stockées sont, en termes de concept, similaires aux fonctions de votre programme. Elles utilisent des paramètres d'entrée, fonctionnent comme une boîte noire et renvoient les informations appropriées. Á la différence des fonctions, les procédures stockées sont exécutées par le moteur de la base de données et non pas depuis votre programme. Ce qui implique que pour entrer et sortir des informations des procédures stockées, il faut passer par une technologie qui sert d'interface avec la base de données. Dans Microsoft Visual Basic® 6.0, c'est ADO qui faisait l'interface, remplacé par ADO.NET dans Visual Basic .NET.

Comme pour de nombreuses autres tâches de programmation, accéder aux données avec des procédures stockées devient un jeu d'enfant avec Visual Basic .NET, comparé à Visual Basic 6.0. Des assistants sont fournis pour accompagner le process, et même développer à partir de zéro ce type de logique avec ADO.NET n'est plus si complexe, une fois que avez réussi à éviter quelques pièges.

Dans cet article, vous trouverez des techniques de base permettant d'utiliser les procédures stockées avec ADO.NET, en commençant par une opération en lecture seule, puis en abordant progressivement les procédures stockées utilisées pour insérer, supprimer et mettre à jour des données.

Inutile d'être un as de la programmation des procédures stockées pour exploiter cet article. La majorité des développeurs travaillant dans des équipes de programmation importantes, doivent être capables d'utiliser les procédures stockées programmées par quelqu'un d'autre. Dans l'un des exemples cités plus bas, nous insérerons une procédure stockée dans des exemples de bases de données ; mais procédons par étapes.

ADO.NET en bref

J'ai supposé, en rédigeant cet article, que vous maîtrisiez les bases d'ADO.NET. Si vous n'avez pas encore travaillé avec les objets DataAdapters, DataSets et Command dans ADO.NET, jetez un coup d'œil aux divers articles d'introduction à ADO.NET, notamment à celui rédigé par Rocky pour cette rubrique, intitulé ADO.NET and You (leave-msdn france Site en anglais).

Pour résumer, les DataSets servent de conteneurs aux données dans ADO.NET et sont utilisés après avoir été déconnectés de la base de données. Un DataSet contient un ou plusieurs DataTables, qui contiennent, chacun, un ensemble de lignes. Pour ceux qui arrivent d'un environnement ADO classique, un objet DataTable peut être assimilé, en gros, à un Recordset déconnecté.

Les DataAdapters doivent être connectés à une base de données pour pouvoir fonctionner. Un DataAdapter pris isolément permet de remplir un DataTable avec les données de la base, ou d'écrire les modifications d'un DataTable dans la base de données, ou bien les deux.

Les DataAdapters utilisent des objets Command pour exécuter les différentes opérations de base de données. Les objets Command contiennent soit une instruction SQL, soit un nom de procédure stockée spécifiant le mode d'accès aux données. Chaque DataAdapter possède quatre propriétés qui indiquent l'objet command utilisé pour chacun des quatre types d'accès aux données :

  • SelectCommand : Cet objet Command permet de sélectionner des données à partir de la base de données.
  • UpdateCommand : Cet objet Command permet de mettre à jour les enregistrements existants dans la base de données.
  • InsertCommand : Cet objet Command permet d'insérer un nouvel enregistrement dans la base de données.
  • DeleteCommand : Cet objet Command permet de supprimer les enregistrements existants de la base de données.

Représentons maintenant ces objets et leurs relations par un schéma, comme illustré à la Figure 1.

Principales classes ADO.NET d'accès aux procédures stockées et relations entre ces classes

Figure 1. Principales classes ADO.NET d'accès aux procédures stockées et relations entre ces classes

Les exemples de démonstration que vous avez été amenés à voir jusqu'à maintenant configurent certainement leurs objets Command pour l'accès aux données avec des instructions SQL. En fait, certains d'entre eux ont vraisemblablement contourné la création d'objets Command, étant donné que l'un des constructeurs des DataAdapters permet à l'objet Command de sélectionner des données à créer de façon masquée. Avant d'aborder l'utilisation des procédures stockées, prenons un exemple et comparons.

Tous les exemples de cet article sont tirés d'exemples de la base de données Northwind fournie avec SQL Server. Nous utiliserons également les classes ADO.NET créées spécialement pour SQL Server au lieu des classes génériques OLE DB. Pour faciliter l'accès aux classes SQL Server, la ligne ci-dessous doit être ajoutée dans tous les exemples, au début du code de l'application :

Imports System.Data.SQLClient

Voyons maintenant comment accéder aux données sans procédure stockée. Dans ce premier exemple, vous allez pouvoir extraire tous les produits de la table Products de la base de données Northwind. Créez une nouvelle application Windows, puis ajoutez un bouton et un DataGrid au formulaire vierge Form1 qui apparaît. Ancrez les quatre côtés du DataGrid (propriété Anchor) de façon à ce que l'objet soit redimensionné en fonction du formulaire. Dans l'événement Click du bouton, ajoutez le code suivant :

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim sSQL As String = "SELECT * FROM Products"
Dim daGetProducts As New SqlDataAdapter(sSQL, sConnectionString)
Dim dsProducts As New DataSet()
daGetProducts.Fill(dsProducts, "Products")
DataGrid1.DataSource = dsProducts.Tables("Products")

Selon la configuration de votre ordinateur, vous devrez peut-être changer la chaîne de connexion. Une fois la base de données connectée, le reste du code devrait normalement fonctionner sans problème. Ce logiciel de démonstration indique la méthode la plus simple pour remplir la base et utiliser un DataSet.

Vous remarquerez que le code ne permet pas de créer d'objet Connection, ni d'objet Command. Dans les applications réelles, ADO.NET ne peut pas fonctionner sans ces objets, qui sont en fait créés et utilisés en arrière-plan. La ligne qui instancie l'objet SqlDataAdapter passe dans une chaîne SQL (pour configurer l'objet Command en arrière-plan) et dans une chaîne de connexion (pour configurer l'objet Connection en arrière-plan).

Nous pouvons modifier ce code pour utiliser des objets Connection et Command explicites, et vous éloigner ainsi de la simple démonstration. Ajoutez un autre bouton au formulaire et insérez le code ci-dessous dans l'événement Click du bouton :

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim sSQL As String = "SELECT * FROM Products"

Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdProducts As New SqlCommand(sSQL, cnNorthwind)

Dim daGetProducts As New SqlDataAdapter(cmdProducts)
Dim dsProducts As New DataSet()
daGetProducts.Fill(dsProducts, "Products")
DataGrid1.DataSource = dsProducts.Tables("Products")

Ce code indique les usages les plus courants des objets DataAdapters : en créant de façon explicite des objets Connection et Command et en liant ces objets au DataAdapter. En passant dans cmdProducts au moment où l'objet DataAdapter est instancié, l'objet SelectCommand du DataAdapter est automatiquement défini. Le DataAdapter peut alors être immédiatement utilisé pour accéder à la base de données.

Les résultats de ce code sont identiques aux résultats obtenus dans l'exemple précédent. Même si ce code se rapproche légèrement de la réalité, il reste néanmoins un code de démonstration puisque l'accès aux données est effectué via des instructions SQL.

Accès aux données avec une procédure stockée simple

Comment adapter ce demoware de façon à utiliser une procédure stockée ? Il suffit juste de modifier quelques lignes. Ajoutez un autre bouton au formulaire et insérez le code ci-dessous dans l'événement Click du bouton :

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdProducts As New _
 SqlCommand("Ten Most Expensive Products", cnNorthwind)
cmdProducts.CommandType = CommandType.StoredProcedure

Dim daGetProducts As New SqlDataAdapter(cmdProducts)
Dim dsProducts As New DataSet()
daGetProducts.Fill(dsProducts, "Products")
DataGrid1.DataSource = dsProducts.Tables("Products")

Ce code remplace l'instruction SQL par le nom de la procédure stockée à utiliser lors de l'instanciation de l'objet Command. De même, la propriété CommandType de l'objet Command doit être définie sur StoredProcedure.

Le code ressemble maintenant à l'exemple précédent, mais renvoie des données différentes. La procédure stockée recherche les dix produits les plus chers et renvoie uniquement le nom et le prix de chaque produit.

Procédures stockées avec paramètres d'entrée

L'exemple que nous venons de voir est simple puisqu'il ne fait intervenir aucun paramètre d'entrée. En d'autres termes, la recherche de ces dix produits n'implique aucune information extérieure, cette procédure stockée peut récupérer les données sans aide extérieure. Toutefois, la plupart des procédures stockées nécessitent des paramètres d'entrée pour exécuter leur fonction. L'exemple qui suit va nous permettre de voir comment passer un paramètre d'entrée dans une procédure stockée. Pour cela, nous utiliserons un CustomerID pour extraire toutes les commandes liées à un client donné, à l'aide d'une procédure appelée CustOrderHist existant déjà dans la base de données Northwind.

Ajoutez un autre bouton au formulaire utilisé et insérez le code ci-dessous derrière l'événement Click du bouton :

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
' Définir paramètre pour la procédure stockée 
Dim prmCustomerID As New SqlParameter()
prmCustomerID.ParameterName = "@CustomerID"
prmCustomerID.SqlDbType = SqlDbType.VarChar
prmCustomerID.Size = 5
prmCustomerID.Value = "ALFKI"

cmdOrders.Parameters.Add(prmCustomerID)

Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

cmdOrders.Parameters.Add(prmCustomerID)

Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

Ce code ressemble beaucoup au code du précédent exemple, à l'exception suivante : une fois l'objet Command créé, un objet Parameter est configuré pour cet objet et ajouté à la collection des paramètres de Command. Nous avons, dans cet exemple, pré-programmé une ID client (davantage demoware), et la propriété Value du paramètre doit normalement être définie sur certaines données d'entrée utilisateur. Cependant, les autres propriétés du paramètre doivent être définies exactement comme illustré dans cet exemple.

Cet exemple explicite tous les réglages des paramètres. Certains développeurs préfèrent ce style, plus avantageux pour des raisons didactiques. D'autres, au contraire, choisissent l'alternative suivante, qui possède moins de lignes de code :

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure

cmdOrders.Parameters.Add(New _ 
 SqlParameter("@CustomerID", SqlDbType.VarChar, 5))
cmdOrders.Parameters("@CustomerID").Value = "ALFKI"

Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

Ce code se comporte exactement comme le précédent exemple. Seulement, maintenant, deux lignes de code suffisent pour chaque paramètre, au lieu de six. Lorsqu'une procédure stockée possède beaucoup de paramètres (comme vous avez pu le constater dans certains exemples ci-dessus), cela peut jouer sur les lignes de code requises ; par conséquent, à partir de maintenant, nous utiliserons ce formulaire.

Utilisation des procédures stockées pour mettre à jour les bases de données

Les exemples ci-dessus utilisent des procédures stockées pour extraire des informations de la base de données. Les procédures stockées sont également fréquemment utilisées dans les applications complexes pour mettre à jour, insérer et supprimer des enregistrements. Voyons un peu comment procéder avec ADO.NET.

Dans le premier exemple que nous traiterons, les assistants Visual Studio® .NET se chargeront d'écrire à notre place un ensemble de procédures stockées et de créer le code permettant d'utiliser ces procédures. Même si, pour cet exemple, nous n'avons pas à écrire beaucoup de code, examiner le code créé par l'assistant peut nous aider à comprendre le processus d'interfaçage avec les procédures stockées pour des opérations autres que l'extraction de données.

Dans l'exemple suivant, nous utiliserons la table Customers de l'exemple de base de données Northwind. La base Northwind n'inclut pas de procédures stockées permettant de mettre à jour, d'insérer ou de supprimer des clients lorsqu'elle est installée, mais l'assistant configuration d'adaptateur de données de Visual Studio .NET peut facilement écrire ces procédures stockées à notre place.

Lancez un nouveau projet d'application Windows. Sur le formulaire vierge Form1, ajoutez un objet DataGrid et deux boutons. Comme pour les exemples précédents, ancrez le DataGrid aux quatre côtés à l'aide de la propriété Anchor. Nommez les boutons btnFill et btnUpdate et remplacez respectivement leur propriété Text par Fill et Update.

Á partir de l'onglet Données de la Boîte à outils, faites glisser un contrôle SqlDataAdapter sur un formulaire. Cette action va lancer l'assistant configuration d'adaptateur de données. Cliquez sur le bouton Suivant pour commencer à entrer des informations dans l'assistant.

Vous devrez tout d'abord sélectionner une connexion à la base de données Northwind ; si l'une des connexions de la liste n'est pas disponible, cliquez sur le bouton Nouvelle connexion pour en créer une. Cliquez ensuite sur Suivant.

Le nouvel écran qui s'affiche propose trois types d'accès aux données. Vous pouvez vous reporter à la Figure 2 pour voir à quoi ressemble cet écran.

Sélection du type d'accès aux données pour un DataAdapter

Figure 2. Sélection du type d'accès aux données pour un DataAdapter

Á ce stade, la majorité des exemples de démonstration reviennent à la première option, à savoir l'ajout d'instructions SQL. Mais nous avons préféré opter pour la deuxième option et laisser l'assistant créer des procédures stockées à notre place. Sélectionnez l'option Créer de nouvelles procédures stockées, puis cliquez sur Suivant.

L'écran qui apparaît ensuite nécessite une instruction SQL pour indiquer les données initiales à extraire de la base de données. Cette instruction SQL ne sera, toutefois, pas utilisée directement. Les informations de l'instruction SQL permettront de créer des procédures stockées pour accéder directement aux données. En termes plus simples, entrez l'instruction SQL SELECT * FROM Customers, puis appuyez sur Suivant.

L'assistant demande alors le nom des procédures stockées qu'il est sur le point de créer. Il existe quatre procédures : sélection, mise à jour, insertion et suppression. Nommez-les comme suit :

  • Select : MSDNSelectCustomers
  • Update : MSDNUpdateCustomer
  • Insert : MSDNInsertCustomer
  • Delete : MSDNDeleteCustomer

Laissez l'option Oui, les créer dans la base de données sélectionnée. L'écran de l'assistant devrait se présenter maintenant comme à la Figure 3.

Désignation des procédures stockées à créer par l'assistant DataAdapter

Figure 3. Désignation des procédures stockées à créer par l'assistant DataAdapter

Cliquez sur Suivant. L'assistant crée les procédures stockées et indique le déroulement de la procédure sur un écran d'état. Une fois l'opération de création terminée, cliquez sur le bouton Terminer pour quitter l'assistant.

L'assistant a créé un DataAdapter entièrement configuré, mais pas d'objet DataSet pour héberger les données. Cela fera l'objet de la prochaine étape. Á partir de l'onglet Données de la Boîte à outils, faites glisser un contrôle DataSet. Lorsque l'écran de configuration apparaît, sélectionnez Groupe de données non typé.

Nous pouvons désormais utiliser le DataAdapter pour remplir le groupe de données. Dans l'événement Click du bouton btnFill, ajoutez les deux lignes de code suivantes :

SqlDataAdapter1.Fill(DataSet1, "Customers")
DataGrid1.DataSource = DataSet1.Tables("Customers")

Dans l'événement Click du bouton btnUpdate, ajoutez la ligne suivante :

SqlDataAdapter1.Update(DataSet1, "Customers")

Nous avons programmé maintenant un segment fonctionnel du logiciel de démonstration qui utilise des procédures stockées pour accéder aux données. Vous pouvez exécuter le programme et cliquer sur Fill pour obtenir une liste des clients de la grille. Vous pouvez ensuite modifier les enregistrements client de la grille et sélectionner Update pour valider les modifications dans la base de données.

Remarque    Si vous modifiez la première colonne, qui correspond à CustomerID, vous rencontrerez un obstacle puisqu'il est impossible, sous SQL Server, de mettre à jour la clé primaire dans un enregistrement de base de données.

Il est très intéressant d'examiner le code généré par l'assistant, qui est initialement masqué dans la zone Code généré par le concepteur Windows Form. Cliquez sur le signe plus de cette zone pour développer le code. Observez le code ci-dessous qui instancie le SQLDataAdapter et les quatre objets Command qu'il utilise :

Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter()
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand()
Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand()
Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand()
Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand()

Nous traiterons ultérieurement du code qui nous permettra de configurer chaque objet Command et de créer une collection de paramètres pour chacun de ces objets. Ce code ressemble au code développé précédemment, puisqu'il utilise une procédure stockée avec des paramètres. Cependant, le code créé par l'assistant utilise certaines propriétés supplémentaires des paramètres pour qu'ils puissent fonctionner avec des procédures stockées qui modifient les données. Voici un exemple de code créant un paramètre CompanyName pour SQLInsertCommand1 :

Me.SqlInsertCommand1.Parameters.Add(New _
 System.Data.SqlClient.SqlParameter("@CompanyName", _
 System.Data.SqlDbType.NVarChar, 40, "CompanyName"))

Pour cet exemple, nous avons uniquement défini les propriétés du nom, du type de données et de la longueur du paramètre. Ce code permet également de définir la propriété SourceColumn du paramètre sur la valeur CompanyName. Cette propriété indique le champ Customers DataTable du DataSet correspondant à ce paramètre. La valeur de DataTable est alors automatiquement insérée dans la propriété Value du paramètre lors d'une opération d'insertion. Mais précisons un peu.

En appelant la méthode Update de SQLDataAdapter, un seul DataTable est alors mis à jour dans le DataSet. L'objet DataTable est examiné ligne par ligne, afin de rechercher les lignes devant être mises à jour, insérées ou supprimées. Lorsqu'une nouvelle ligne à insérer dans la base de données est trouvée, le SQLDataAdapter utilise l'objet Command défini par sa propriété InsertCommand. Dans cet exemple, l'objet Command accède à la procédure stockée MSDNInsertCustomer.

Avant d'exécuter cette procédure stockée, vous devez remplir la propriété Value pour chacun des paramètres, à partir de la ligne insérée. Le code qui configure SQLDataAdapter1 associe chaque paramètre de la procédure stockée au champ approprié dans DataTable. Les données peuvent ainsi être transférées automatiquement de la nouvelle ligne DataTable vers les paramètres de la procédure stockée.

Les paramètres des autres procédures stockées sont configurés de façon identique. Á cette différence près : les autres procédures stockées passent dans les valeurs initiales des données de DataTable ; ces valeurs sont utilisées pour vérifier que les données n'ont pas été modifiées accidentellement. En d'autres termes, si vous extrayez des données et que quelqu'un modifie ces données avant que vous n'ayez réussi à les mettre à jour, cela générera un conflit d'accès concurrentiel. Démonstration : démarrez le programme ci-dessus, extrayez les clients, puis, à l'aide d'un outil tel que SQL Enterprise Manager, modifiez un enregistrement. Si vous modifiez le même enregistrement dans le programme modèle et que vous essayez le mettre à jour, cela générera un conflit d'accès concurrentiel.

Retour d'une valeur depuis une procédure stockée

L'exemple donné ci-dessus présente un point faible. La table Customers de Northwind utilise des clés primaires alphanumériques qui doivent être générées par l'application d'insertion des données. Donc, si vous insérez un nouvel enregistrement via le programme ci-dessus, vous devez créer votre propre valeur à cinq caractères pour CustomerID.

En termes de développement réel, la pratique courante consiste à générer automatiquement des clés primaires pour un nouvel enregistrement. Ces clés sont généralement des entiers longs assignés de façon séquentielle.

Deux techniques de base permettent de définir la clé primaire pour un nouvel enregistrement. L'application peut appeler une procédure stockée qui génère la prochaine ID disponible, puis placer cette ID directement sur la nouvelle ligne de DataSet. Avec la seconde technique, la procédure stockée utilisée pour insérer un enregistrement peut dériver la nouvelle ID pour l'enregistrement, puis la repasser dans l'application comme une valeur renvoyée.

La première technique nécessite une dose de logique supplémentaire pour obtenir la nouvelle ID et la placer dans la zone appropriée pour un nouvel enregistrement. L'utilisation d'une procédure stockée pour exécuter l'opération d'insertion est une méthode similaire à l'exemple ci-dessus.

La seconde technique nécessite, quant à elle, l'utilisation d'un nouveau type de paramètres avec la procédure stockée. Tous les paramètres que nous avons abordés jusqu'à maintenant sont des paramètres par défaut, correspondant à des paramètres d'entrée. Il existe en fait quatre types de paramètres :

Input Ce paramètre est utilisé uniquement pour le transfert d'informations de l'application vers la procédure stockée.
InputOutput Ce paramètre peut transférer des informations dans la procédure stockée, puis de la procédure stockée vers l'application.
Output Ce paramètre peut uniquement transférer des informations de la procédure stockée vers l'application.
ReturnValue Ce paramètre correspond à une valeur renvoyée par une procédure stockée. Il n'apparaît pas dans la liste de paramètres de la procédure stockée dans SQL Server. Il est lié uniquement à une valeur de l'instruction RETURN de la procédure stockée.

Lorsqu'une procédure stockée génère une nouvelle valeur pour une clé primaire, cette valeur est généralement retournée avec une instruction RETURN dans la procédure stockée ; par conséquent, le paramètre utilisé pour accéder à cette valeur est de type ReturnValue.

Il existe une différence importante entre les paramètres ReturnValue et les autres types de paramètres. Généralement, l'ordre des paramètres configurés pour un objet Command n'est pas pris en compte par ADO.NET. Les noms des paramètres permettent de les rapprocher des paramètres correspondants dans la procédure stockée. Cependant, le paramètre ReturnValue doit nécessairement apparaître en tête de liste.

Ce qui signifie que, pour configurer un paramètre ReturnValue pour un objet Command, vous devez le configurer en premier dans votre code de façon à ce qu'il soit affecté au premier index numérique dans la collection. Si vous commencez par configurer un autre paramètre, le paramètre ReturnValue ne fonctionnera pas correctement.

Nous allons maintenant illustrer l'utilisation d'une procédure stockée avec une valeur renvoyée par un exemple qui insère un enregistrement dans la table Products de Northwind. Cette table est configurée pour créer automatiquement de nouvelles ID produits, via une colonne Identity. Malheureusement, l'exemple de base de données Northwind ne contient pas la procédure stockée dont nous avons besoin ; nous devons donc insérer la procédure stockée manquante dans la base de données avant de poursuivre notre exemple.

Accédez à Explorateur de serveurs, dans Visual Studio .NET. Ouvrez tout d'abord le nœud correspondant aux serveurs SQL, puis le nœud correspondant à votre instance SQL Server. Ouvrez ensuite le nœud de la base de données Northwind.

Cliquez sur le nœud Procédures stockées avec le bouton droit de la souris et sélectionnez Nouvelle procédure stockée. Une fenêtre d'édition s'affiche dans laquelle vous devez remplacer tout le texte par ce qui suit :

ALTER PROCEDURE dbo.MSDNInsertProduct
(
 @ProductName nvarchar(40),
 @SupplierID int,
 @CategoryID int,
 @QuantityPerUnit nvarchar(20),
 @UnitPrice money,
 @UnitsInStock smallint,
 @UnitsOnOrder smallint,
 @ReorderLevel smallint,
 @Discontinued bit
)
AS
 declare @ProductID int

 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 = @@IDENTITY

RETURN @ProductID
)
AS
 declare @ProductID int

 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 = @@IDENTITY

RETURN @ProductID

Fermez la fenêtre d'édition. Une invite apparaît vous demandant de sauvegarder vos modifications : cliquez sur Oui. La procédure stockée est désormais enregistrée dans la base de données, sous le nom MSDNInsertProduct.

Nous pouvons maintenant écrire le code qui nous permettra d'utiliser cette procédure stockée. Créez une nouvelle application Windows et, sur le formulaire vierge Form1, ajoutez un objet DataGrid ancré au niveau des quatre côtés. Ajoutez également deux boutons btnFill et btnInsertProduct. Définissez la propriété Text du bouton btnFill sur Fill et la propriété Text de btnInsertProduct sur Insert Product.

Dans l'événement click du bouton btnFill, ajoutez le code suivant :

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim sSQL As String = "SELECT * FROM Products"
Dim daGetProducts As New SqlDataAdapter(sSQL, sConnectionString)
Dim dsProducts As New DataSet()
daGetProducts.Fill(dsProducts, "Products")
DataGrid1.DataSource = dsProducts

Ce code est pratiquement identique au code traité précédemment dans cet article, nous ne l'expliquerons donc pas. N'oubliez cependant pas de changer la chaîne de connexion si nécessaire, ni d'ajouter l'instruction Imports de l'espace de noms SQLClient au début du code du projet. Insérez ensuite le code suivant dans l'événement Click du bouton btnInsertProduct :

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdInsertProduct As New SqlCommand("MSDNInsertProduct", cnNorthwind)
cmdInsertProduct.CommandType = CommandType.StoredProcedure
' Définir les paramètres pour la procédure stockée 
cmdInsertProduct.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, "ProductID"))
cmdInsertProduct.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue

cmdInsertProduct.Parameters.Add(New SqlParameter("@ProductName", _
 SqlDbType.NVarChar, 40, "ProductName"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@SupplierID", _
 SqlDbType.Int, 4, "SupplierID"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@CategoryID", _
 SqlDbType.Int, 4, "CategoryID"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@QuantityPerUnit", _
 SqlDbType.NVarChar, 20, "QuantityPerUnit"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@UnitPrice", _
 SqlDbType.Money, 8, "UnitPrice"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@UnitsInStock", _
 SqlDbType.SmallInt, 2, "UnitsInStock"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@UnitsOnOrder", _
 SqlDbType.SmallInt, 2, "UnitsOnOrder"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@ReorderLevel", _
 SqlDbType.SmallInt, 2, "ReorderLevel"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@Discontinued", _
 SqlDbType.Bit, 1, "Discontinued"))

Dim daInsertProduct As New SqlDataAdapter()
daInsertProduct.InsertCommand = cmdInsertProduct
Dim dsProducts As DataSet = CType(DataGrid1.DataSource, DataSet)

Dim drNewProduct As DataRow
drNewProduct = dsProducts.Tables("Products").NewRow
drNewProduct.Item("ProductName") = "Billy's Sesame Oil"
drNewProduct.Item("SupplierID") = 4
drNewProduct.Item("CategoryID") = 7
drNewProduct.Item("QuantityPerUnit") = "6 10oz bottles"
drNewProduct.Item("UnitPrice") = 69
drNewProduct.Item("UnitsInStock") = 12
drNewProduct.Item("UnitsOnOrder") = 0
drNewProduct.Item("ReorderLevel") = 6
drNewProduct.Item("Discontinued") = False
dsProducts.Tables("Products").Rows.Add(drNewProduct)

daInsertProduct.Update(dsProducts.Tables("Products"))

MsgBox(drNewProduct.Item("ProductID"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@UnitsOnOrder", _
 SqlDbType.SmallInt, 2, "UnitsOnOrder"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@ReorderLevel", _
 SqlDbType.SmallInt, 2, "ReorderLevel"))
cmdInsertProduct.Parameters.Add(New SqlParameter("@Discontinued", _
 SqlDbType.Bit, 1, "Discontinued"))

Dim daInsertProduct As New SqlDataAdapter()
daInsertProduct.InsertCommand = cmdInsertProduct
Dim dsProducts As DataSet = CType(DataGrid1.DataSource, DataSet)

Dim drNewProduct As DataRow
drNewProduct = dsProducts.Tables("Products").NewRow
drNewProduct.Item("ProductName") = "Billy's Sesame Oil"
drNewProduct.Item("SupplierID") = 4
drNewProduct.Item("CategoryID") = 7
drNewProduct.Item("QuantityPerUnit") = "6 10oz bottles"
drNewProduct.Item("UnitPrice") = 69
drNewProduct.Item("UnitsInStock") = 12
drNewProduct.Item("UnitsOnOrder") = 0
drNewProduct.Item("ReorderLevel") = 6
drNewProduct.Item("Discontinued") = False
dsProducts.Tables("Products").Rows.Add(drNewProduct)

daInsertProduct.Update(dsProducts.Tables("Products"))

MsgBox(drNewProduct.Item("ProductID"))

Ce code ressemble à l'un des exemples traités précédemment, à l'exception des lignes de configuration du paramètre pour la valeur renvoyée. Vous remarquez qu'il s'agit du premier paramètre et qu'il est défini pour réinsérer la valeur renvoyée dans la zone ProductID.

Le code qui permet d'insérer une nouvelle ligne dans le dataset correspond à du code ADO.NET standard, que nous ne détaillerons pas ici. Ce code crée une nouvelle ligne de structure appropriée pour un enregistrement de produit (via la méthode NewRow de l'objet DataTable des produits), place les données dans la ligne et ajoute la ligne à la collection Rows de l'objet DataTable des produits.

Exécutez maintenant le programme pour le tester. Cliquez sur le bouton Fill, sans modifier les données de la grille. Appuyez ensuite sur Insert Product. Le nouvel enregistrement produits de la variable « Billy's Sesame Oil » est inséré et un message apparaît pour vous indiquer que les données ProductID ont été retournées. Vous pouvez également ouvrir la table Products de la grille, la faire défiler jusqu'en bas et vérifier que le nouvel enregistrement a bien été ajouté.

Utilisation de l'Explorateur de serveurs pour l'écriture d'un code de paramètre

Le code ci-dessus est assez rébarbatif à écrire. L'assistant configuration d'adaptateur de données nous a donc suggéré de laisser Visual Studio écrire ce code à notre place. L'assistant configuration d'adaptateur de données a généré du code pour les quatre procédures stockées requises dans une configuration complète (pour les commandes Sélectionner, Mettre à jour, Insérer et Supprimer). Comment faire pour programmer uniquement une seule procédure stockée, comme dans l'exemple ci-dessus ? Vous pouvez toujours prendre un raccourci. Pour obtenir du code pré-programmé qui sert d'interface à une seule procédure stockée, il vous suffit de développer l'Explorateur de serveurs pour afficher la procédure stockée à laquelle vous souhaitez accéder, puis de faire glisser cette procédure sur votre surface de dessin. Un objet DataAdapter et un objet Command sont alors créés juste pour cette procédure stockée, et la section concepteur du code contient tout le code nécessaire pour configurer les paramètres de la procédure stockée. Vous pouvez utiliser ce code tel qu'il se présente ou le copier pour le modifier à votre guise.

Conclusion

Les exemples abordés tout au long de cet article restent des exemples de démonstration, mais ils sont suffisamment détaillés pour que vous puissiez bien comprendre comment accéder aux procédures stockées et commencer la programmation de votre propre logiciel. Si vous souhaitez approfondir votre compréhension des procédures stockées, vous pouvez contacter un administrateur de base de données (DBA) ou tout autre membre de l'équipe, qui vous fournira les informations dont vous avez besoin.

Les procédures stockées présentent de nombreux avantages pour les systèmes complexes. Nous espérons que cet article vous a permis de vous familiariser avec les procédures stockées et leur utilisation. Pour vos débuts de programmation, vous vous précipiterez probablement sur l'assistant configuration d'adaptateur de données ou sur l'Explorateur de serveurs pour qu'il écrive le code à votre place, mais essayez d'écrire vous-même votre code d'accès, cela vous permettra de progresser plus rapidement dans l'utilisation des procédures stockées.



Dernière mise à jour le lundi 18 novembre 2002



Pour en savoir plus
Microsoft réalise une enquête en ligne pour recueillir votre opinion sur le site Web de MSDN. Si vous choisissez d’y participer, cette enquête en ligne vous sera présentée lorsque vous quitterez le site Web de MSDN.

Si vous souhaitez y participer,
Afficher:
© 2014 Microsoft