Création de procédures stockées et de fonctions définies par l’utilisateur avec du code managé (VB)

par Scott Mitchell

Télécharger le PDF

Microsoft SQL Server 2005 s’intègre au Common Language Runtime .NET pour permettre aux développeurs de créer des objets de base de données via du code managé. Ce tutoriel montre comment créer des procédures stockées managées et des fonctions gérées définies par l’utilisateur avec votre code Visual Basic ou C#. Nous voyons également comment ces éditions de Visual Studio vous permettent de déboguer ces objets de base de données managés.

Introduction

Les bases de données telles que Microsoft SQL Server 2005 utilisent l’langage SQL Transact-langage SQL (T-SQL) pour l’insertion, la modification et la récupération de données. La plupart des systèmes de base de données incluent des constructions pour regrouper une série d’instructions SQL qui peuvent ensuite être exécutées en tant qu’unité unique et réutilisable. Les procédures stockées sont un exemple. Une autre est les fonctions définies par l’utilisateur (UDF), une construction que nous examinerons plus en détail à l’étape 9.

À son cœur, SQL est conçu pour utiliser des jeux de données. Les SELECTinstructions et les DELETE UPDATEinstructions s’appliquent intrinsèquement à tous les enregistrements de la table correspondante et ne sont limitées que par leurs WHERE clauses. Pourtant, il existe de nombreuses fonctionnalités de langage conçues pour utiliser un enregistrement à la fois et pour manipuler des données scalaires. CURSOR permet à un ensemble d’enregistrements d’être en boucle à la fois. Les fonctions de manipulation de chaîne comme LEFT, CHARINDEXet PATINDEX fonctionnent avec des données scalaires. SQL inclut également des instructions de flux de contrôle comme IF et WHILE.

Avant Microsoft SQL Server 2005, les procédures stockées et les fonctions définies par l’utilisateur ne peuvent être définies qu’en tant que collection d’instructions T-SQL. TOUTEFOIS, SQL Server 2005 a été conçu pour fournir une intégration au Common Language Runtime (CLR), qui est le runtime utilisé par tous les assemblys .NET. Par conséquent, les procédures stockées et les fonctions définies par l’utilisateur dans une base de données SQL Server 2005 peuvent être créées à l’aide du code managé. Autrement dit, vous pouvez créer une procédure stockée ou une fonction UDF en tant que méthode dans une classe Visual Basic. Cela permet à ces procédures stockées et aux fonctions définies par l’utilisateur d’utiliser des fonctionnalités dans le .NET Framework et à partir de vos propres classes personnalisées.

Dans ce tutoriel, nous allons examiner comment créer des procédures stockées managées et des fonctions définies par l’utilisateur et comment les intégrer à notre base de données Northwind. Commençons !

Remarque

Les objets de base de données managés offrent certains avantages par rapport à leurs équivalents SQL. La richesse du langage et la connaissance et la capacité à réutiliser le code et la logique existants sont les principaux avantages. Toutefois, les objets de base de données managés sont susceptibles d’être moins efficaces lors de l’utilisation de jeux de données qui n’impliquent pas beaucoup de logique procédurale. Pour une discussion plus approfondie sur les avantages de l’utilisation du code managé par rapport à T-SQL, consultez les avantages de l’utilisation du code managé pour créer des objets de base de données.

Étape 1 : Déplacement de la base de données Northwind hors de App_Data

Tous nos didacticiels ont déjà utilisé un fichier de base de données Microsoft SQL Server 2005 Express Edition dans le dossier de l’application App_Data web. Placer la base de données dans App_Data la distribution simplifiée et l’exécution de ces didacticiels, car tous les fichiers se trouvaient dans un répertoire et ne nécessitaient aucune procédure de configuration supplémentaire pour tester le didacticiel.

Pour ce tutoriel, nous allons toutefois déplacer la base de données Northwind hors de l’instance de base de données SQL Server 2005 Express Edition et l’inscrire explicitement auprès de App_Data l’instance de base de données SQL Server 2005 Express Edition. Bien que nous puissions effectuer les étapes de ce didacticiel avec la base de données dans le App_Data dossier, un certain nombre de ces étapes sont beaucoup plus simples en inscrivant explicitement la base de données auprès de l’instance de base de données SQL Server 2005 Express Edition.

Le téléchargement de ce didacticiel contient les deux fichiers de base de données - NORTHWND.MDF et NORTHWND_log.LDF - placés dans un dossier nommé DataFiles. Si vous suivez avec votre propre implémentation des didacticiels, fermez Visual Studio et déplacez les fichiers et les NORTHWND.MDF NORTHWND_log.LDF fichiers du dossier du App_Data site web vers un dossier en dehors du site web. Une fois les fichiers de base de données déplacés vers un autre dossier, nous devons inscrire la base de données Northwind auprès de l’instance de base de données SQL Server 2005 Express Edition. Cette opération peut être effectuée à partir de SQL Server Management Studio. Si vous avez installé une édition non Express de SQL Server 2005 sur votre ordinateur, vous avez probablement déjà installé Management Studio. Si vous disposez uniquement de SQL Server 2005 Express Edition sur votre ordinateur, prenez un moment pour télécharger et installer Microsoft SQL Server Management Studio.

Lancez SQL Server Management Studio. Comme le montre la figure 1, Management Studio commence par demander à quel serveur se connecter. Entrez localhost\SQLExpress pour le nom du serveur, choisissez Authentification Windows dans la liste déroulante Authentification, puis cliquez sur Se connecter.

Figure 1 : Se connecter à l’instance de base de données appropriée

Une fois connecté, la fenêtre Explorateur d’objets répertorie les informations sur l’instance de base de données SQL Server 2005 Express Edition, notamment ses bases de données, ses informations de sécurité, ses options de gestion, etc.

Nous devons attacher la base de données Northwind dans le DataFiles dossier (ou où que vous l’ayez déplacée) à l’instance de base de données SQL Server 2005 Express Edition. Cliquez avec le bouton droit sur le dossier Bases de données et choisissez l’option Attacher dans le menu contextuel. La boîte de dialogue Attacher des bases de données s’affiche. Cliquez sur le bouton Ajouter, accédez au fichier approprié NORTHWND.MDF , puis cliquez sur OK. À ce stade, votre écran doit ressembler à la figure 2.

Figure 2 : Se connecter à l’instance de base de données appropriée (cliquez pour afficher l’image de taille complète)

Remarque

Lorsque vous vous connectez à l’instance SQL Server 2005 Express Edition via Management Studio, la boîte de dialogue Attacher des bases de données ne vous permet pas d’explorer les répertoires de profil utilisateur, tels que Mes documents. Par conséquent, veillez à placer les fichiers et NORTHWND_log.LDF les NORTHWND.MDF fichiers dans un répertoire de profil non utilisateur.

Cliquez sur le bouton OK pour joindre la base de données. La boîte de dialogue Attacher des bases de données se ferme et l’Explorateur d’objets doit maintenant répertorier la base de données juste jointe. Les chances sont que la base de données Northwind a un nom comme 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF. Renommez la base de données en Northwind en cliquant avec le bouton droit sur la base de données et en choisissant Renommer.

Figure 3 : Renommer la base de données en Northwind

Étape 2 : Création d’une solution et d’un projet SQL Server dans Visual Studio

Pour créer des procédures stockées managées ou des fonctions définies par l’utilisateur dans SQL Server 2005, nous allons écrire la procédure stockée et la logique UDF en tant que code Visual Basic dans une classe. Une fois le code écrit, nous devons compiler cette classe dans un assembly (un .dll fichier), inscrire l’assembly auprès de la base de données SQL Server, puis créer une procédure stockée ou un objet UDF dans la base de données qui pointe vers la méthode correspondante dans l’assembly. Ces étapes peuvent toutes être effectuées manuellement. Nous pouvons créer le code dans n’importe quel éditeur de texte, le compiler à partir de la ligne de commande à l’aide du compilateur Visual Basic (vbc.exe), l’inscrire auprès de la base de données à l’aide de la CREATE ASSEMBLY commande ou de Management Studio, et ajouter la procédure stockée ou l’objet UDF par le biais de moyens similaires. Heureusement, les versions Professional et Team Systems de Visual Studio incluent un type de projet SQL Server qui automatise ces tâches. Dans ce tutoriel, nous allons suivre l’utilisation du type de projet SQL Server pour créer une procédure stockée managée et une fonction UDF.

Remarque

Si vous utilisez Visual Web Developer ou l’édition Standard de Visual Studio, vous devez utiliser l’approche manuelle à la place. L’étape 13 fournit des instructions détaillées pour effectuer ces étapes manuellement. Je vous encourage à lire les étapes 2 à 12 avant de lire l’étape 13, car ces étapes incluent des instructions de configuration SQL Server importantes qui doivent être appliquées quelle que soit la version de Visual Studio que vous utilisez.

Commencez par ouvrir Visual Studio. Dans le menu Fichier, choisissez Nouveau projet pour afficher la boîte de dialogue Nouveau projet (voir la figure 4). Accédez au type de projet de base de données, puis, dans les modèles répertoriés à droite, choisissez de créer un projet SQL Server. J’ai choisi de nommer ce projet ManagedDatabaseConstructs et de le placer dans une solution nommée Tutorial75.

Figure 4 : Créer un projet SQL Server (cliquez pour afficher l’image de taille complète)

Cliquez sur le bouton OK dans la boîte de dialogue Nouveau projet pour créer la solution et le projet SQL Server.

Un projet SQL Server est lié à une base de données particulière. Par conséquent, après avoir créé le projet SQL Server, nous sommes immédiatement invités à spécifier ces informations. La figure 5 montre la boîte de dialogue Nouvelle référence de base de données qui a été renseignée pour pointer vers la base de données Northwind que nous avons inscrite dans l’instance de base de données SQL Server 2005 Express Edition à l’étape 1.

Figure 5 : Associer le projet SQL Server à la base de données Northwind

Pour déboguer les procédures stockées gérées et les fonctions définies par l’utilisateur que nous allons créer dans ce projet, nous devons activer la prise en charge du débogage SQL/CLR pour la connexion. Chaque fois que vous associez un projet SQL Server à une nouvelle base de données (comme nous l’avons fait dans la figure 5), Visual Studio nous demande si nous voulons activer le débogage SQL/CLR sur la connexion (voir la figure 6). Sélectionnez Oui.

Figure 6 : Activer le débogage SQL/CLR

À ce stade, le nouveau projet SQL Server a été ajouté à la solution. Il contient un dossier nommé Test Scripts avec un fichier nommé Test.sql, qui est utilisé pour déboguer les objets de base de données managés créés dans le projet. Nous allons examiner le débogage à l’étape 12.

Nous pouvons maintenant ajouter de nouvelles procédures stockées managées et des fonctions définies par l’utilisateur à ce projet, mais avant d’inclure d’abord notre application web existante dans la solution. Dans le menu Fichier, sélectionnez l’option Ajouter et choisissez Site web existant. Accédez au dossier de site web approprié, puis cliquez sur OK. Comme le montre la figure 7, cette opération met à jour la solution pour inclure deux projets : le site web et le ManagedDatabaseConstructs projet SQL Server.

Figure 7 : Le Explorateur de solutions comprend désormais deux projets

La NORTHWNDConnectionString valeur dans Web.config laquelle il fait référence actuellement au NORTHWND.MDF fichier dans le App_Data dossier. Étant donné que nous avons supprimé cette base de App_Data données et l’avons inscrite explicitement dans l’instance de base de données SQL Server 2005 Express Edition, nous devons mettre à jour la NORTHWNDConnectionString valeur correspondante. Ouvrez le Web.config fichier dans le site web et modifiez la NORTHWNDConnectionString valeur pour que le chaîne de connexion lise : Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Après cette modification, votre <connectionStrings> section Web.config doit ressembler à ce qui suit :

<connectionStrings>
    <add name="NORTHWNDConnectionString" connectionString=
        "Data Source=localhost\SQLExpress;Initial Catalog=Northwind;
            Integrated Security=True;Pooling=false"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Remarque

Comme indiqué dans le tutoriel précédent, lors du débogage d’un objet SQL Server à partir d’une application cliente, tel qu’un site web ASP.NET, nous devons désactiver le regroupement de connexions. Le chaîne de connexion indiqué ci-dessus désactive le regroupement de connexions ( Pooling=false ). Si vous ne prévoyez pas de déboguer les procédures stockées gérées et les fonctions définies par l’utilisateur à partir du site web ASP.NET, activez le regroupement de connexions.

Étape 3 : Création d’une procédure stockée gérée

Pour ajouter une procédure stockée managée à la base de données Northwind, nous devons d’abord créer la procédure stockée en tant que méthode dans le projet SQL Server. Dans le Explorateur de solutions, cliquez avec le bouton droit sur le nom du ManagedDatabaseConstructs projet et choisissez d’ajouter un nouvel élément. Cette opération affiche la boîte de dialogue Ajouter un nouvel élément, qui répertorie les types d’objets de base de données managés qui peuvent être ajoutés au projet. Comme le montre la figure 8, cela inclut les procédures stockées et les fonctions définies par l’utilisateur, entre autres.

Commençons par ajouter une procédure stockée qui retourne simplement tous les produits qui ont été supprimés. Nommez le nouveau fichier de GetDiscontinuedProducts.vbprocédure stockée .

Figure 8 : Ajouter une nouvelle procédure stockée nommée GetDiscontinuedProducts.vb (cliquez pour afficher l’image de taille complète)

Cela crée un fichier de classe Visual Basic avec le contenu suivant :

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub  GetDiscontinuedProducts ()
        ' Add your code here
    End Sub
End Class

Notez que la procédure stockée est implémentée en tant que Shared méthode dans un Partial fichier de classe nommé StoredProcedures. De plus, la GetDiscontinuedProducts méthode est décorée avec l’attributSqlProcedure, qui marque la méthode comme une procédure stockée.

Le code suivant crée un SqlCommand objet et définit sa CommandText valeur sur une SELECT requête qui retourne toutes les colonnes de la Products table pour les produits dont Discontinued le champ est égal à 1. Il exécute ensuite la commande et renvoie les résultats à l’application cliente. Ajoutez ce code à la méthode GetDiscontinuedProducts.

' Create the command
Dim myCommand As New SqlCommand()
myCommand.CommandText = _
    "SELECT ProductID, ProductName, SupplierID, CategoryID, " & _
    "       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, " & _
    "       ReorderLevel, Discontinued " & _
    "FROM Products " & _
    "WHERE Discontinued = 1"
' Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand)

Tous les objets de base de données managés ont accès à un SqlContext objet qui représente le contexte de l’appelant. Il SqlContext fournit l’accès à un SqlPipe objet via sa Pipe propriété. Cet SqlPipe objet est utilisé pour transporter des informations entre la base de données SQL Server et l’application appelante. Comme son nom l’indique, la ExecuteAndSend méthode exécute un objet passé SqlCommand et renvoie les résultats à l’application cliente.

Remarque

Les objets de base de données managés conviennent mieux aux procédures stockées et aux fonctions définies qui utilisent la logique procédurale plutôt que la logique définie. La logique procédurale implique l’utilisation de jeux de données sur une base ligne par ligne ou l’utilisation de données scalaires. Toutefois, la GetDiscontinuedProducts méthode que nous venons de créer n’implique aucune logique procédurale. Par conséquent, elle serait idéalement implémentée en tant que procédure stockée T-SQL. Il est implémenté en tant que procédure stockée managée pour illustrer les étapes nécessaires à la création et au déploiement de procédures stockées managées.

Étape 4 : Déploiement de la procédure stockée gérée

Avec ce code terminé, nous sommes prêts à le déployer dans la base de données Northwind. Le déploiement d’un projet SQL Server compile le code dans un assembly, inscrit l’assembly auprès de la base de données et crée les objets correspondants dans la base de données, les liant aux méthodes appropriées dans l’assembly. L’ensemble exact de tâches effectuées par l’option Déployer est plus précisément orthographié à l’étape 13. Cliquez avec le bouton droit sur le nom du ManagedDatabaseConstructs projet dans le Explorateur de solutions et choisissez l’option Déployer. Toutefois, le déploiement échoue avec l’erreur suivante : Syntaxe incorrecte près de « EXTERNAL ». Vous devrez peut-être affecter au niveau de compatibilité de la base de données actuelle une valeur plus élevée pour activer cette fonctionnalité. Consultez l’aide de la procédure sp_dbcmptlevelstockée.

Ce message d’erreur se produit lors de la tentative d’inscription de l’assembly auprès de la base de données Northwind. Pour inscrire un assembly auprès d’une base de données SQL Server 2005, le niveau de compatibilité de la base de données doit être défini sur 90. Par défaut, les nouvelles bases de données SQL Server 2005 ont un niveau de compatibilité de 90. Toutefois, les bases de données créées à l’aide de Microsoft SQL Server 2000 ont un niveau de compatibilité par défaut de 80. Étant donné que la base de données Northwind était initialement une base de données Microsoft SQL Server 2000, son niveau de compatibilité est actuellement défini sur 80 et doit donc être augmenté à 90 pour inscrire des objets de base de données managés.

Pour mettre à jour le niveau de compatibilité de la base de données, ouvrez une fenêtre Nouvelle requête dans Management Studio et entrez :

exec sp_dbcmptlevel 'Northwind', 90

Cliquez sur l’icône Exécuter dans la barre d’outils pour exécuter la requête ci-dessus.

Figure 9 : Mettre à jour le niveau de compatibilité de la base de données Northwind (cliquez pour afficher l’image de taille complète)

Après avoir mis à jour le niveau de compatibilité, redéployez le projet SQL Server. Cette fois, le déploiement doit se terminer sans erreur.

Revenez à SQL Server Management Studio, cliquez avec le bouton droit sur la base de données Northwind dans l’Explorateur d’objets, puis choisissez Actualiser. Ensuite, explorez le dossier Programmability, puis développez le dossier Assemblys. Comme le montre la figure 10, la base de données Northwind inclut désormais l’assembly généré par le ManagedDatabaseConstructs projet.

Figure 10 : L’assembly ManagedDatabaseConstructs est maintenant inscrit auprès de la base de données Northwind

Développez également le dossier Procédures stockées. Vous verrez une procédure stockée nommée GetDiscontinuedProducts. Cette procédure stockée a été créée par le processus de déploiement et pointe vers la GetDiscontinuedProducts méthode dans l’assembly ManagedDatabaseConstructs . Lorsque la GetDiscontinuedProducts procédure stockée est exécutée, elle exécute à son tour la GetDiscontinuedProducts méthode. Étant donné qu’il s’agit d’une procédure stockée managée, elle ne peut pas être modifiée via Management Studio (par conséquent, l’icône de verrouillage en regard du nom de la procédure stockée).

Figure 11 : La GetDiscontinuedProducts procédure stockée est répertoriée dans le dossier Procédures stockées

Il existe encore un autre obstacle que nous devons surmonter avant de pouvoir appeler la procédure stockée managée : la base de données est configurée pour empêcher l’exécution du code managé. Vérifiez cela en ouvrant une nouvelle fenêtre de requête et en exécutant la GetDiscontinuedProducts procédure stockée. Vous recevrez le message d’erreur suivant : l’exécution du code utilisateur dans le .NET Framework est désactivée. Activez l’option de configuration activée par clr.

Pour examiner les informations de configuration de la base de données Northwind, entrez et exécutez la commande exec sp_configure dans la fenêtre de requête. Cela montre que le paramètre clr activé est actuellement défini sur 0.

Figure 12 : Le paramètre clr activé est actuellement défini sur 0 (Cliquez pour afficher l’image pleine taille)

Notez que chaque paramètre de configuration de la figure 12 comporte quatre valeurs répertoriées : les valeurs minimales et maximales, ainsi que les valeurs de configuration et d’exécution. Pour mettre à jour la valeur de configuration du paramètre clr activé, exécutez la commande suivante :

exec sp_configure 'clr enabled', 1

Si vous réexécutez l’instruction exec sp_configure ci-dessus, la valeur de configuration du paramètre clr est mise à jour sur 1, mais la valeur d’exécution est toujours définie sur 0. Pour que cette modification de configuration prenne effet, nous devons exécuter la RECONFIGURE commande, ce qui définit la valeur d’exécution sur la valeur de configuration actuelle. Entrez RECONFIGURE simplement dans la fenêtre de requête, puis cliquez sur l’icône Exécuter dans la barre d’outils. Si vous exécutez exec sp_configure maintenant, vous devez voir une valeur de 1 pour la configuration du paramètre clr activé et les valeurs d’exécution.

Une fois la configuration activée clr terminée, nous sommes prêts à exécuter la procédure stockée gérée GetDiscontinuedProducts . Dans la fenêtre de requête, entrez et exécutez la commande exec GetDiscontinuedProducts. L’appel de la procédure stockée entraîne l’exécution du code managé correspondant dans la GetDiscontinuedProducts méthode. Ce code émet une SELECT requête pour renvoyer tous les produits qui sont supprimés et retourne ces données à l’application appelante, qui est SQL Server Management Studio dans cette instance. Management Studio reçoit ces résultats et les affiche dans la fenêtre Résultats.

Figure 13 : La GetDiscontinuedProducts procédure stockée retourne tous les produits supprimés (cliquez pour afficher l’image de taille complète)

Étape 5 : Création de procédures stockées gérées qui acceptent les paramètres d’entrée

La plupart des requêtes et procédures stockées que nous avons créées dans ces didacticiels ont utilisé des paramètres. Par exemple, dans le didacticiel Création de procédures stockées pour le didacticiel TableAdapters de Typed DataSet, nous avons créé une procédure stockée nommée GetProductsByCategoryID qui a accepté un paramètre d’entrée nommé @CategoryID. La procédure stockée a ensuite retourné tous les produits dont CategoryID le champ correspond à la valeur du paramètre fourni @CategoryID .

Pour créer une procédure stockée managée qui accepte les paramètres d’entrée, spécifiez simplement ces paramètres dans la définition de la méthode. Pour illustrer cela, nous allons ajouter une autre procédure stockée gérée au ManagedDatabaseConstructs projet nommé GetProductsWithPriceLessThan. Cette procédure stockée gérée accepte un paramètre d’entrée spécifiant un prix et retourne tous les produits dont UnitPrice le champ est inférieur à la valeur du paramètre.

Pour ajouter une nouvelle procédure stockée au projet, cliquez avec le bouton droit sur le nom du ManagedDatabaseConstructs projet et choisissez d’ajouter une nouvelle procédure stockée. Nommez le fichier GetProductsWithPriceLessThan.vb. Comme nous l’avons vu à l’étape 3, cela crée un fichier de classe Visual Basic avec une méthode nommée GetProductsWithPriceLessThan placée dans la Partial classe StoredProcedures.

Mettez à jour la définition de la GetProductsWithPriceLessThan méthode afin qu’elle accepte un SqlMoney paramètre d’entrée nommé price et écrive le code pour exécuter et retourner les résultats de la requête :

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub GetProductsWithPriceLessThan(ByVal price As SqlMoney)
    'Create the command
    Dim myCommand As New SqlCommand()
    myCommand.CommandText = _
        "SELECT ProductID, ProductName, SupplierID, CategoryID, " & _
        "       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, " & _
        "       ReorderLevel, Discontinued " & _
        "FROM Products " & _
        "WHERE UnitPrice < @MaxPrice"
    myCommand.Parameters.AddWithValue("@MaxPrice", price)
    ' Execute the command and send back the results
    SqlContext.Pipe.ExecuteAndSend(myCommand)
End Sub

La GetProductsWithPriceLessThan définition et le code de la méthode ressemblent étroitement à la définition et au code de la méthode créée à l’étape GetDiscontinuedProducts 3. Les seules différences sont que la GetProductsWithPriceLessThan méthode accepte en tant que paramètre d’entrée (price), la SqlCommand requête s’inclut un paramètre (@MaxPrice), et un paramètre est ajouté à la SqlCommand collection s’effectue Parameters et affecte la valeur de la price variable.

Après avoir ajouté ce code, redéployez le projet SQL Server. Ensuite, revenez à SQL Server Management Studio et actualisez le dossier Procédures stockées. Vous devriez voir une nouvelle entrée, GetProductsWithPriceLessThan. Dans une fenêtre de requête, entrez et exécutez la commande exec GetProductsWithPriceLessThan 25, qui répertorie tous les produits inférieurs à 25 $, comme l’illustre la figure 14.

Figure 14 : Les produits de moins de 25 $ sont affichés (cliquez pour afficher l’image de taille complète)

Étape 6 : Appel de la procédure stockée gérée à partir de la couche d’accès aux données

À ce stade, nous avons ajouté les GetDiscontinuedProducts procédures stockées gérées au ManagedDatabaseConstructs projet et GetProductsWithPriceLessThan les avons inscrites auprès de la base de données Northwind SQL Server. Nous avons également appelé ces procédures stockées gérées à partir de SQL Server Management Studio (voir les figures 13 et 14). Pour que notre application ASP.NET utilise ces procédures stockées managées, toutefois, nous devons les ajouter aux couches d’accès aux données et de logique métier dans l’architecture. Dans cette étape, nous allons ajouter deux nouvelles méthodes au ProductsTableAdapter jeu NorthwindWithSprocs de données typé, qui a été initialement créé dans le didacticiel Création de procédures stockées pour le didacticiel TableAdapters de Typed DataSet. À l’étape 7, nous allons ajouter des méthodes correspondantes à la BLL.

Ouvrez l’ensemble NorthwindWithSprocs de données typé dans Visual Studio et commencez par ajouter une nouvelle méthode au ProductsTableAdapter nom GetDiscontinuedProducts. Pour ajouter une nouvelle méthode à un TableAdapter, cliquez avec le bouton droit sur le nom de TableAdapter dans le Concepteur et choisissez l’option Ajouter une requête dans le menu contextuel.

Remarque

Étant donné que nous avons déplacé la base de données Northwind du App_Data dossier vers l’instance de base de données SQL Server 2005 Express Edition, il est impératif que les chaîne de connexion correspondantes dans Web.config soient mises à jour pour refléter cette modification. À l’étape 2, nous avons discuté de la mise à jour de la NORTHWNDConnectionString valeur dans Web.config. Si vous avez oublié d’effectuer cette mise à jour, le message d’erreur n’a pas pu être ajouté à la requête. Impossible de trouver la connexion NORTHWNDConnectionString de l’objet Web.config dans une boîte de dialogue lors de la tentative d’ajout d’une nouvelle méthode à TableAdapter. Pour résoudre cette erreur, cliquez sur OK, puis accédez à la valeur et mettez à Web.config jour la valeur comme indiqué à l’étape NORTHWNDConnectionString 2. Essayez ensuite de rajouter la méthode à TableAdapter. Cette fois, elle doit fonctionner sans erreur.

L’ajout d’une nouvelle méthode lance l’Assistant Configuration des requêtes TableAdapter, que nous avons utilisé plusieurs fois dans les didacticiels précédents. La première étape nous demande de spécifier comment TableAdapter doit accéder à la base de données : via une instruction SQL ad hoc ou via une procédure stockée nouvelle ou existante. Étant donné que nous avons déjà créé et inscrit la GetDiscontinuedProducts procédure stockée managée avec la base de données, choisissez l’option Utiliser la procédure stockée existante et appuyez sur Suivant.

Figure 15 : Choisir l’option Utiliser la procédure stockée existante (cliquez pour afficher l’image de taille complète)

L’écran suivant nous invite à entrer la procédure stockée que la méthode appellera. Choisissez la GetDiscontinuedProducts procédure stockée gérée dans la liste déroulante et appuyez sur Suivant.

Figure 16 : Sélectionner la procédure stockée gérée (cliquez pour afficher l’image GetDiscontinuedProductsde taille complète)

Nous sommes ensuite invités à spécifier si la procédure stockée retourne des lignes, une seule valeur ou rien. Dans la mesure GetDiscontinuedProducts où retourne l’ensemble de lignes de produit abandonnées, choisissez la première option (données tabulaires) et cliquez sur Suivant.

Figure 17 : Sélectionner l’option de données tabulaires (cliquez pour afficher l’image de taille complète)

L’écran final de l’Assistant nous permet de spécifier les modèles d’accès aux données utilisés et les noms des méthodes résultantes. Laissez les deux cases cochées et nommez les méthodes FillByDiscontinued et GetDiscontinuedProducts. Cliquez sur Terminer pour terminer l’Assistant.

Figure 18 : Nommer les méthodes FillByDiscontinued et GetDiscontinuedProducts (Cliquez pour afficher l’image de taille complète)

Répétez ces étapes pour créer des méthodes nommées FillByPriceLessThan et GetProductsWithPriceLessThan dans la GetProductsWithPriceLessThan ProductsTableAdapter procédure stockée gérée.

La figure 19 montre une capture d’écran du Concepteur DataSet après avoir ajouté les méthodes aux ProductsTableAdapter GetDiscontinuedProducts procédures stockées gérées et GetProductsWithPriceLessThan gérées.

Figure 19 : Les ProductsTableAdapter nouvelles méthodes ajoutées à cette étape (cliquez pour afficher l’image de taille complète)

Étape 7 : Ajout de méthodes correspondantes à la couche logique métier

Maintenant que nous avons mis à jour la couche d’accès aux données pour inclure des méthodes d’appel des procédures stockées gérées ajoutées aux étapes 4 et 5, nous devons ajouter des méthodes correspondantes à la couche logique métier. Ajoutez les deux méthodes suivantes à la ProductsBLLWithSprocs classe :

<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetDiscontinuedProducts() As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetDiscontinuedProducts()
End Function
<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsWithPriceLessThan(ByVal priceLessThan As Decimal) _
    As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetProductsWithPriceLessThan(priceLessThan)
End Function

Les deux méthodes appellent simplement la méthode DAL correspondante et retournent l’instance ProductsDataTable . Le DataObjectMethodAttribute balisage au-dessus de chaque méthode entraîne l’inclusion de ces méthodes dans la liste déroulante sous l’onglet SELECT de l’Assistant Configuration de la source de données ObjectDataSource.

Étape 8 : Appel des procédures stockées gérées à partir de la couche Présentation

Avec la logique métier et les couches d’accès aux données augmentées pour inclure la prise en charge de l’appel des GetDiscontinuedProducts procédures stockées gérées et GetProductsWithPriceLessThan gérées, nous pouvons maintenant afficher ces résultats de procédures stockées via une page de ASP.NET.

Ouvrez la ManagedFunctionsAndSprocs.aspx page dans le AdvancedDAL dossier et, à partir de la boîte à outils, faites glisser un GridView sur le Concepteur. Définissez la propriété GridView ID sur DiscontinuedProducts et, à partir de sa balise active, liez-la à un nouvel ObjectDataSource nommé DiscontinuedProductsDataSource. Configurez ObjectDataSource pour extraire ses données de la ProductsBLLWithSprocs méthode de GetDiscontinuedProducts classe.

Figure 20 : Configurer ObjectDataSource pour utiliser la classe (Cliquez pour afficher l’image ProductsBLLWithSprocsde taille complète)

Figure 21 : Choisir la GetDiscontinuedProducts méthode dans la liste déroulante sous l’onglet SELECT (Cliquez pour afficher l’image de taille complète)

Étant donné que cette grille est utilisée pour afficher uniquement les informations de produit, définissez les listes déroulantes dans les onglets UPDATE, INSERT et DELETE sur (Aucun), puis cliquez sur Terminer.

Une fois l’Assistant terminé, Visual Studio ajoute automatiquement un Objet BoundField ou CheckBoxField pour chaque champ de données dans le ProductsDataTable. Prenez un moment pour supprimer tous ces champs, à l’exception ProductName et Discontinued, à quel point votre balisage déclaratif GridView et ObjectDataSource doit ressembler à ce qui suit :

<asp:GridView ID="DiscontinuedProducts" runat="server" 
    AutoGenerateColumns="False" DataKeyNames="ProductID" 
    DataSourceID="DiscontinuedProductsDataSource">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
            SortExpression="ProductName" />
        <asp:CheckBoxField DataField="Discontinued" 
            HeaderText="Discontinued" 
            SortExpression="Discontinued" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="DiscontinuedProductsDataSource" runat="server" 
    OldValuesParameterFormatString="original_{0}"
    SelectMethod="GetDiscontinuedProducts" TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>

Prenez un moment pour afficher cette page via un navigateur. Lorsque la page est visitée, ObjectDataSource appelle la ProductsBLLWithSprocs méthode de GetDiscontinuedProducts classe. Comme nous l’avons vu à l’étape 7, cette méthode appelle la méthode s ProductsDataTable de GetDiscontinuedProducts classe DAL, qui appelle la GetDiscontinuedProducts procédure stockée. Cette procédure stockée est une procédure stockée gérée et exécute le code que nous avons créé à l’étape 3, en retournant les produits supprimés.

Les résultats retournés par la procédure stockée gérée sont empaquetés dans un ProductsDataTable fichier DAL, puis retournés à la BLL, qui les renvoie ensuite à la couche de présentation où elles sont liées à GridView et affichées. Comme prévu, la grille répertorie les produits qui ont été supprimés.

Figure 22 : Les produits supprimés sont répertoriés (cliquez pour afficher l’image de taille complète)

Pour plus d’informations, ajoutez un TextBox et un autre GridView à la page. Faites en sorte que GridView affiche les produits inférieurs à la quantité entrée dans la zone de texte en appelant la méthode s de GetProductsWithPriceLessThan la ProductsBLLWithSprocs classe.

Étape 9 : Création et appel de fonctions définies par l’utilisateur T-SQL

Les fonctions définies par l’utilisateur ou les fonctions définies par l’utilisateur sont des objets de base de données qui imitent étroitement la sémantique des fonctions dans les langages de programmation. Comme une fonction dans Visual Basic, les fonctions définies par l’utilisateur peuvent inclure un nombre variable de paramètres d’entrée et retourner une valeur d’un type particulier. Une fonction UDF peut retourner des données scalaires ( une chaîne, un entier, etc.) ou des données tabulaires. Examinons rapidement les deux types de fonctions définies par l’utilisateur, en commençant par une fonction UDF qui retourne un type de données scalaire.

La fonction UDF suivante calcule la valeur estimée de l’inventaire pour un produit particulier. Il le fait en prenant trois paramètres d’entrée - les valeurs UnitsInStocket Discontinued les UnitPricevaleurs d’un produit particulier - et retourne une valeur de type money. Il calcule la valeur estimée de l’inventaire en multipliant le UnitPrice par le UnitsInStock. Pour les éléments supprimés, cette valeur est divisée en deux.

CREATE FUNCTION udf_ComputeInventoryValue
(
    @UnitPrice money,
    @UnitsInStock smallint,
    @Discontinued bit
)
RETURNS money
AS
BEGIN
    DECLARE @Value decimal
    SET @Value = ISNULL(@UnitPrice, 0) * ISNULL(@UnitsInStock, 0)
    IF @Discontinued = 1
        SET @Value = @Value * 0.5
    
    RETURN @Value
END

Une fois que cette fonction UDF a été ajoutée à la base de données, elle est disponible via Management Studio en développant le dossier Programmability, puis Functions, puis Functions scalaire. Il peut être utilisé dans une SELECT requête comme suit :

SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue
    (UnitPrice, UnitsInStock, Discontinued) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

J’ai ajouté la udf_ComputeInventoryValue fonction UDF à la base de données Northwind ; La figure 23 montre la sortie de la requête ci-dessus SELECT lors de l’affichage via Management Studio. Notez également que la fonction UDF est répertoriée sous le dossier Functions Scalar-value dans l’Explorateur d’objets.

Figure 23 : Les valeurs d’inventaire de chaque produit sont répertoriées (cliquez pour afficher l’image de taille complète)

Les fonctions définies par l’utilisateur peuvent également retourner des données tabulaires. Par exemple, nous pouvons créer une fonction UDF qui retourne des produits appartenant à une catégorie particulière :

CREATE FUNCTION dbo.udf_GetProductsByCategoryID
(    
    @CategoryID int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ProductID, ProductName, SupplierID, CategoryID, 
           QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
           ReorderLevel, Discontinued
    FROM Products
    WHERE CategoryID = @CategoryID
)

La udf_GetProductsByCategoryID fonction UDF accepte un paramètre d’entrée @CategoryID et retourne les résultats de la requête spécifiée SELECT . Une fois créée, cette fonction UDF peut être référencée dans la FROM clause (ou JOIN) d’une SELECT requête. L’exemple suivant retourne les ProductIDvaleurs , ProductNameet CategoryID les valeurs pour chacune des boissons.

SELECT ProductID, ProductName, CategoryID
FROM dbo.udf_GetProductsByCategoryID(1)

J’ai ajouté la udf_GetProductsByCategoryID fonction UDF à la base de données Northwind ; La figure 24 montre la sortie de la requête ci-dessus SELECT lors de l’affichage via Management Studio. Les fonctions définies par l’utilisateur qui retournent des données tabulaires se trouvent dans le dossier Fonctions table de l’Explorateur d’objets.

Figure 24 : The ProductID, ProductNameand CategoryID are List for Each Beverage (Click to view full-size image)

Remarque

Pour plus d’informations sur la création et l’utilisation des fonctions définies par l’utilisateur, consultez l’introduction aux fonctions définies par l’utilisateur. Découvrez également les avantages et inconvénients des fonctions définies par l’utilisateur.

Étape 10 : Création d’une fonction UDF managée

Les udf_ComputeInventoryValue fonctions définies par l’utilisateur créées udf_GetProductsByCategoryID dans les exemples ci-dessus sont des objets de base de données T-SQL. SQL Server 2005 prend également en charge les fonctions définies par l’utilisateur managé, qui peuvent être ajoutées au ManagedDatabaseConstructs projet, tout comme les procédures stockées gérées des étapes 3 et 5. Pour cette étape, implémentez la fonction UDF dans le udf_ComputeInventoryValue code managé.

Pour ajouter une fonction UDF managée au ManagedDatabaseConstructs projet, cliquez avec le bouton droit sur le nom du projet dans Explorateur de solutions et choisissez d’ajouter un nouvel élément. Sélectionnez le modèle défini par l’utilisateur dans la boîte de dialogue Ajouter un nouvel élément et nommez le nouveau fichier udf_ComputeInventoryValue_Managed.vbUDF.

Figure 25 : Ajouter une nouvelle fonction UDF managée au ManagedDatabaseConstructs projet (cliquez pour afficher l’image de taille complète)

Le modèle de fonction définie par l’utilisateur crée une Partial classe nommée UserDefinedFunctions avec une méthode dont le nom est identique au nom du fichier de classe (udf_ComputeInventoryValue_Manageddans cette instance). Cette méthode est décorée à l’aide de l’attributSqlFunction, qui signale la méthode en tant qu’UDF managée.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function udf_ComputeInventoryValue_Managed() As SqlString
        ' Add your code here
        Return New SqlString("Hello")
    End Function
End Class

La udf_ComputeInventoryValue méthode retourne actuellement un SqlString objet et n’accepte aucun paramètre d’entrée. Nous devons mettre à jour la définition de méthode afin qu’elle accepte trois paramètres d’entrée , UnitPriceUnitsInStocket - et Discontinued retourne un SqlMoney objet. La logique de calcul de la valeur d’inventaire est identique à celle de la fonction UDF T-SQL udf_ComputeInventoryValue .

<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function udf_ComputeInventoryValue_Managed _
    (UnitPrice As SqlMoney, UnitsInStock As SqlInt16, Discontinued As SqlBoolean) _
    As SqlMoney
    Dim inventoryValue As SqlMoney = 0
    If Not UnitPrice.IsNull AndAlso Not UnitsInStock.IsNull Then
        inventoryValue = UnitPrice * UnitsInStock
        If Discontinued = True Then
            inventoryValue = inventoryValue * New SqlMoney(0.5)
        End If
    End If
    Return inventoryValue
End Function

Notez que les paramètres d’entrée de la méthode UDF sont de leurs types SQL correspondants : SqlMoney pour le UnitPrice champ, SqlInt16 pour UnitsInStocket SqlBoolean pour Discontinued. Ces types de données reflètent les types définis dans la Products table : la UnitPrice colonne est de type money, la UnitsInStock colonne de type smallintet la Discontinued colonne de type bit.

Le code commence par créer une SqlMoney instance nommée inventoryValue qui a la valeur 0. La Products table autorise les valeurs de base de données NULL dans les colonnes et UnitsInStock les UnitsInPrice colonnes. Par conséquent, nous devons d’abord vérifier si ces valeurs contiennent NULL des s, que nous faisons par le biais de la SqlMoney propriété de l’objetIsNull. Si les deux UnitPrice et UnitsInStock contiennent des valeurs non-NULL , nous calculons le inventoryValue produit des deux. Ensuite, si Discontinued c’est vrai, nous avons la moitié de la valeur.

Remarque

L’objet SqlMoney permet uniquement à deux SqlMoney instances d’être multipliées ensemble. Il n’autorise pas la multiplication d’une SqlMoney instance par un nombre à virgule flottante littérale. Par conséquent, pour la moitié inventoryValue , nous la multiplions par une nouvelle SqlMoney instance qui a la valeur 0,5.

Étape 11 : Déploiement de la fonction UDF managée

Maintenant que l’UDF managée a été créée, nous sommes prêts à le déployer dans la base de données Northwind. Comme nous l’avons vu à l’étape 4, les objets managés d’un projet SQL Server sont déployés en cliquant avec le bouton droit sur le nom du projet dans le Explorateur de solutions et en choisissant l’option Déployer dans le menu contextuel.

Une fois que vous avez déployé le projet, revenez à SQL Server Management Studio et actualisez le dossier Functions scalaire. Vous devez maintenant voir deux entrées :

• dbo.udf_ComputeInventoryValue - la fonction UDF T-SQL créée à l’étape 9 et
• dbo.udf ComputeInventoryValue_Managed - L’UDF managée créée à l’étape 10 qui vient d’être déployée.

Pour tester cette fonction UDF managée, exécutez la requête suivante à partir de Management Studio :

SELECT ProductID, ProductName, 
       dbo.udf_ComputeInventoryValue_Managed(
                 UnitPrice, 
                 UnitsInStock, 
                 Discontinued
              ) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

Cette commande utilise la fonction UDF managée udf ComputeInventoryValue_Managed au lieu de la fonction UDF T-SQL udf_ComputeInventoryValue , mais la sortie est la même. Reportez-vous à la figure 23 pour voir une capture d’écran de la sortie de la fonction utilisateur.

Étape 12 : Débogage des objets de base de données managés

Dans le didacticiel de débogage des procédures stockées, nous avons abordé les trois options de débogage de SQL Server via Visual Studio : débogage direct de base de données, débogage d’applications et débogage à partir d’un projet SQL Server. Les objets de base de données managés ne peuvent pas être débogués via le débogage direct de base de données, mais peuvent être débogués à partir d’une application cliente et directement à partir du projet SQL Server. Toutefois, pour que le débogage fonctionne, la base de données SQL Server 2005 doit autoriser le débogage SQL/CLR. Rappelez-vous que lorsque nous avons créé le projet Visual Studio nous a demandé si nous voulions activer le ManagedDatabaseConstructs débogage SQL/CLR (voir la figure 6 à l’étape 2). Ce paramètre peut être modifié en cliquant avec le bouton droit sur la base de données à partir de la fenêtre Explorateur de serveurs.

Figure 26 : Vérifier que la base de données autorise le débogage SQL/CLR

Imaginez que nous voulions déboguer la GetProductsWithPriceLessThan procédure stockée gérée. Nous allons commencer par définir un point d’arrêt dans le code de la GetProductsWithPriceLessThan méthode.

Figure 27 : Définir un point d’arrêt dans la GetProductsWithPriceLessThan méthode (Cliquez pour afficher l’image de taille complète)

Examinons d’abord le débogage des objets de base de données managés à partir du projet SQL Server. Étant donné que notre solution comprend deux projets ( le ManagedDatabaseConstructs projet SQL Server ainsi que notre site web) afin de déboguer à partir du projet SQL Server, nous devons demander à Visual Studio de lancer le projet SQL Server au démarrage du ManagedDatabaseConstructs débogage. Cliquez avec le bouton droit sur le ManagedDatabaseConstructs projet dans Explorateur de solutions et choisissez l’option Définir comme projet de démarrage dans le menu contextuel.

Lorsque le ManagedDatabaseConstructs projet est lancé à partir du débogueur, il exécute les instructions SQL dans le Test.sql fichier, qui se trouve dans le Test Scripts dossier. Par exemple, pour tester la GetProductsWithPriceLessThan procédure stockée managée, remplacez le contenu de fichier existant Test.sql par l’instruction suivante, qui appelle la GetProductsWithPriceLessThan procédure stockée managée passant la @CategoryID valeur 14.95 :

exec GetProductsWithPriceLessThan 14.95

Une fois que vous avez entré le script Test.sqlci-dessus, commencez le débogage en accédant au menu Débogage et en choisissant Démarrer le débogage ou en appuyant sur F5 ou l’icône de lecture verte dans la barre d’outils. Cela génère les projets dans la solution, déploie les objets de base de données managés dans la base de données Northwind, puis exécute le Test.sql script. À ce stade, le point d’arrêt est atteint et nous pouvons parcourir la GetProductsWithPriceLessThan méthode, examiner les valeurs des paramètres d’entrée, et ainsi de suite.

Figure 28 : Point d’arrêt dans la GetProductsWithPriceLessThan méthode a été atteint (cliquez pour afficher l’image de taille complète)

Pour qu’un objet de base de données SQL soit débogué via une application cliente, il est impératif que la base de données soit configurée pour prendre en charge le débogage d’application. Cliquez avec le bouton droit sur la base de données dans l’Explorateur de serveurs et vérifiez que l’option Débogage d’application est cochée. En outre, nous devons configurer l’application ASP.NET pour l’intégrer au débogueur SQL et désactiver le regroupement de connexions. Ces étapes ont été décrites en détail à l’étape 2 du didacticiel sur le débogage des procédures stockées.

Une fois que vous avez configuré l’application et la base de données ASP.NET, définissez le site web ASP.NET comme projet de démarrage et démarrez le débogage. Si vous visitez une page qui appelle l’un des objets managés qui ont un point d’arrêt, l’application s’arrête et le contrôle est remis au débogueur, où vous pouvez parcourir le code comme illustré dans la figure 28.

Étape 13 : Compilation et déploiement manuels d’objets de base de données managés

Les projets SQL Server facilitent la création, la compilation et le déploiement d’objets de base de données managés. Malheureusement, les projets SQL Server ne sont disponibles que dans les éditions Professional et Team Systems de Visual Studio. Si vous utilisez Visual Web Developer ou le Édition Standard de Visual Studio et que vous souhaitez utiliser des objets de base de données managés, vous devez les créer et les déployer manuellement. Cela implique quatre étapes :

1. Créez un fichier qui contient le code source de l’objet de base de données managée,
2. Compilez l’objet dans un assembly,
3. Inscrire l’assembly auprès de la base de données SQL Server 2005 et
4. Créez un objet de base de données dans SQL Server qui pointe vers la méthode appropriée dans l’assembly.

Pour illustrer ces tâches, nous allons créer une procédure stockée managée qui retourne ces produits dont UnitPrice la valeur est supérieure à celle spécifiée. Créez un fichier sur votre ordinateur nommé GetProductsWithPriceGreaterThan.vb et entrez le code suivant dans le fichier (vous pouvez utiliser Visual Studio, le Bloc-notes ou n’importe quel éditeur de texte pour effectuer cette opération) :

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub GetProductsWithPriceGreaterThan(ByVal price As SqlMoney)
        'Create the command
        Dim myCommand As New SqlCommand()
        myCommand.CommandText = _
            "SELECT ProductID, ProductName, SupplierID, CategoryID, " & _
            "       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, " & _
            "       ReorderLevel, Discontinued " & _
            "FROM Products " & _
            "WHERE UnitPrice > @MinPrice"
        myCommand.Parameters.AddWithValue("@MinPrice", price)
        ' Execute the command and send back the results
        SqlContext.Pipe.ExecuteAndSend(myCommand)
    End Sub
End Class

Ce code est presque identique à celui de la méthode créée à l’étape GetProductsWithPriceLessThan 5. Les seules différences sont les noms de méthode, la WHERE clause et le nom de paramètre utilisé dans la requête. De retour dans la GetProductsWithPriceLessThan méthode, la WHERE clause lit : WHERE UnitPrice < @MaxPrice. Ici, dans GetProductsWithPriceGreaterThan, nous utilisons : WHERE UnitPrice > @MinPrice .

Nous devons maintenant compiler cette classe dans un assembly. À partir de la ligne de commande, accédez au répertoire dans lequel vous avez enregistré le GetProductsWithPriceGreaterThan.vb fichier et utilisez le compilateur C# (csc.exe) pour compiler le fichier de classe dans un assembly :

vbc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.vb

Si le dossier contenant v bc.exe ne se trouve pas dans les systèmes, PATHvous devrez référencer entièrement son chemin d’accès, %WINDOWS%\Microsoft.NET\Framework\version\comme suit :

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.vb

Figure 29 : Compiler GetProductsWithPriceGreaterThan.vb dans un assembly (cliquez pour afficher l’image de taille complète)

L’indicateur /t spécifie que le fichier de classe Visual Basic doit être compilé dans une DLL (plutôt qu’un exécutable). L’indicateur /out spécifie le nom de l’assembly résultant.

Remarque

Au lieu de compiler le GetProductsWithPriceGreaterThan.vb fichier de classe à partir de la ligne de commande, vous pouvez également utiliser Visual Basic Express Edition ou créer un projet de bibliothèque de classes distinct dans Visual Studio Édition Standard. S ren Jacob Lauritsen a fourni un tel projet Visual Basic Express Edition avec du code pour la GetProductsWithPriceGreaterThan procédure stockée et les deux procédures stockées gérées et UDF créées aux étapes 3, 5 et 10. Le projet S ren inclut également les commandes T-SQL nécessaires pour ajouter les objets de base de données correspondants.

Avec le code compilé dans un assembly, nous sommes prêts à inscrire l’assembly dans la base de données SQL Server 2005. Cette opération peut être effectuée via T-SQL, à l’aide de la commande CREATE ASSEMBLYou de SQL Server Management Studio. Concentrons-nous sur l’utilisation de Management Studio.

À partir de Management Studio, développez le dossier Programmability dans la base de données Northwind. L’un de ses sous-dossiers est Assemblys. Pour ajouter manuellement un nouvel assembly à la base de données, cliquez avec le bouton droit sur le dossier Assemblys et choisissez Nouvel assembly dans le menu contextuel. La boîte de dialogue Nouvel assembly s’affiche (voir la figure 30). Cliquez sur le bouton Parcourir, sélectionnez l’assembly ManuallyCreatedDBObjects.dll que nous venons de compiler, puis cliquez sur OK pour ajouter l’assembly à la base de données. Vous ne devez pas voir l’assembly ManuallyCreatedDBObjects.dll dans l’Explorateur d’objets.

Figure 30 : Ajouter l’assembly ManuallyCreatedDBObjects.dll à la base de données (cliquez pour afficher l’image de taille complète)

Figure 31 : L’objet ManuallyCreatedDBObjects.dll est répertorié dans l’Explorateur d’objets

Bien que nous avons ajouté l’assembly à la base de données Northwind, nous n’avons pas encore associé une procédure stockée à la GetProductsWithPriceGreaterThan méthode dans l’assembly. Pour ce faire, ouvrez une nouvelle fenêtre de requête et exécutez le script suivant :

CREATE PROCEDURE [dbo].[GetProductsWithPriceGreaterThan] 
( 
    @price money 
) 
WITH EXECUTE AS CALLER 
AS 
EXTERNAL NAME [ManuallyCreatedDBObjects].[StoredProcedures].[GetProductsWithPriceGreaterThan] 
GO

Cela crée une procédure stockée dans la base de données Northwind nommée GetProductsWithPriceGreaterThan et l’associe à la méthode GetProductsWithPriceGreaterThan managée (qui se trouve dans la classe StoredProcedures, qui se trouve dans l’assembly ManuallyCreatedDBObjects).

Après avoir exécuté le script ci-dessus, actualisez le dossier Procédures stockées dans l’Explorateur d’objets. Vous devez voir une nouvelle entrée de procédure stockée , GetProductsWithPriceGreaterThan qui a une icône de verrouillage en regard de celle-ci. Pour tester cette procédure stockée, entrez et exécutez le script suivant dans la fenêtre de requête :

exec GetProductsWithPriceGreaterThan 24.95

Comme le montre la figure 32, la commande ci-dessus affiche des informations pour ces produits dont la UnitPrice valeur est supérieure à 24,95 $.

Figure 32 : L’objet ManuallyCreatedDBObjects.dll est répertorié dans l’Explorateur d’objets (cliquez pour afficher l’image de taille complète)

Résumé

Microsoft SQL Server 2005 fournit une intégration au Common Language Runtime (CLR), qui permet de créer des objets de base de données à l’aide du code managé. Auparavant, ces objets de base de données ne pouvaient être créés qu’à l’aide de T-SQL, mais nous pouvons maintenant créer ces objets à l’aide de langages de programmation .NET comme Visual Basic. Dans ce tutoriel, nous avons créé deux procédures stockées gérées et une fonction gérée définie par l’utilisateur.

Le type de projet SQL Server de Visual Studio facilite la création, la compilation et le déploiement d’objets de base de données managés. De plus, il offre une prise en charge complète du débogage. Toutefois, les types de projet SQL Server ne sont disponibles que dans les éditions Professional et Team Systems de Visual Studio. Pour ceux qui utilisent Visual Web Developer ou le Édition Standard de Visual Studio, les étapes de création, de compilation et de déploiement doivent être effectuées manuellement, comme nous l’avons vu à l’étape 13.

Bonne programmation !

Pour aller plus loin

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

• Avantages et inconvénients des fonctions définies par l’utilisateur
• Création d’objets SQL Server 2005 dans le code managé
• Procédure : créer et exécuter une procédure stockée CLR SQL Server
• Guide pratique pour créer et exécuter une fonction CLR SQL Server définie par l’utilisateur
• Procédure : modifier le Test.sql script pour exécuter des objets SQL
• Présentation des fonctions définies par l’utilisateur
• Code managé et SQL Server 2005 (vidéo)
• Référence Transact-SQL
• Procédure pas à pas : création d’une procédure stockée dans le code managé

À 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. Il peut être accessible à mitchell@4GuysFromRolla.com. ou via son blog, qui peut être trouvé à http://ScottOnWriting.NET.

Merci spécial à

Cette série de tutoriels a été examinée par de nombreux réviseurs utiles. Le réviseur principal de ce didacticiel était S ren Jacob Lauritsen. Outre la révision de cet article, S ren a également créé le projet Visual C# Express Edition inclus dans ce téléchargement pour compiler manuellement les objets de base de données managés. Vous souhaitez consulter mes prochains articles MSDN ? Si c’est le cas, déposez-moi une ligne à mitchell@4GuysFromRolla.com.

Précédent