Mise en route avec l'intégration du CLR

Article
06/13/2017

Cette rubrique fournit une vue d’ensemble des espaces de noms et des bibliothèques nécessaires à la compilation d’objets de base de données à l’aide de l’intégration SQL Server au Common Language Runtime (CLR) .NET Framework. Elle montre également comment écrire, compiler et exécuter une simple procédure stockée CLR écrite en Microsoft Visual C#.

Espaces de noms requis

À compter de SQL Server. Les fonctionnalités d'intégration du CLR sont exposées dans un assembly appelé system.data.dll, qui fait partie du .NET Framework. Cet assembly se trouve dans le Global Assembly Cache (GAC), ainsi que dans le répertoire .NET Framework. Une référence à cet assembly est généralement ajoutée automatiquement par les outils de ligne de commande et Microsoft Visual Studio, de sorte qu'il n'est pas nécessaire de l'ajouter manuellement.

L'assembly system.data.dll contient les espaces de noms suivants, qui sont requis pour compiler les objets de base de données CLR :

System.Data

System.Data.Sql

Microsoft.SqlServer.Server

System.Data.SqlTypes

Écriture d'une procédure stockée "Hello World" simple

Copiez et collez le code Visual C# ou Microsoft Visual Basic suivant dans un éditeur de texte, puis enregistrez-le dans un fichier nommé « helloworld.cs » ou « helloworld.vb ».

using System;  
using System.Data;  
using Microsoft.SqlServer.Server;  
using System.Data.SqlTypes;  
  
public class HelloWorldProc  
{  
    [Microsoft.SqlServer.Server.SqlProcedure]  
    public static void HelloWorld(out string text)  
    {  
        SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);  
        text = "Hello world!";  
    }  
}

Imports System  
Imports System.Data  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlTypes  
Imports System.Runtime.InteropServices  
  
Public Class HelloWorldProc  
    <Microsoft.SqlServer.Server.SqlProcedure> _   
    Public Shared  Sub HelloWorld(<Out()> ByRef text as String)  
        SqlContext.Pipe.Send("Hello world!" & Environment.NewLine)  
        text = "Hello world!"  
    End Sub  
End Class

Ce programme simple contient une méthode statique unique sur une classe publique. Cette méthode utilise deux nouvelles classes, SqlContext et SqlPipe, pour créer des objets de base de données managés afin d'obtenir un message textuel simple. La méthode attribue également la chaîne "Hello world!" comme valeur d'un paramètre de sortie. Cette méthode peut être déclarée en tant que procédure stockée dans la procédure stockée Transact-SQL.

Nous allons à présent compiler ce programme comme une bibliothèque, le charger dans SQL Server, puis l'exécuter comme procédure stockée.

Compilation de la procédure stockée "Hello World"

Fichiers de redistribution Microsoft .NET Framework par défaut. Ces fichiers incluent csc.exe et vbc.exe, les compilateurs de ligne de commande pour les programmes Visual C# et Visual Basic. Pour compiler notre exemple, vous devez modifier votre variable de chemin d'accès pour pointer sur le répertoire qui contient csc.exe ou vbc.exe. Le chemin d'installation par défaut du .NET Framework est le suivant :

C:\Windows\Microsoft.NET\Framework\(version)

La version contient le numéro de version du programme redistribuable .NET Framework installé. Par exemple :

C:\Windows\Microsoft.NET\Framework\v2.0.31113

Une fois que vous avez ajouté le répertoire .NET Framework à votre chemin d'accès, vous pouvez compiler l'exemple de procédure stockée en assembly à l'aide de la commande ci-dessous. L'option /target vous permet de le compiler en assembly.

Pour les fichiers sources Visual C# :

csc /target:library helloworld.cs

Pour les fichiers sources Visual Basic :

vbc /target:library helloworld.vb

Ces commandes lancent le compilateur Visual C# ou Visual Basic en utilisant l'option /target pour spécifier la génération d'une DLL de bibliothèque.

Chargement et exécution de la procédure stockée "Hello World" dans SQL Server

Une fois l’exemple de procédure correctement compilé, vous pouvez le tester dans SQL Server Management Studio et créer une requête, en vous connectant à une base de données de test appropriée (par exemple, l’exemple de base de données AdventureWorks).

La possibilité d’exécuter du code CLR (Common Language Runtime) est définie sur OFF par défaut dans SQL Server. Le code CLR peut être activé à l’aide de la procédure stockée système sp_configure . Pour plus d’informations, consultez Activation de l’intégration du CLR.

Il est nécessaire de créer l'assembly afin de pouvoir accéder à la procédure stockée. Pour cet exemple, nous supposerons que vous avez créé l'assembly helloworld.dll dans le répertoire C:\. Ajoutez l'instruction Transact-SQL suivante à votre requête.

CREATE ASSEMBLY helloworld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE

Une fois l'assembly créé, il est possible d'accéder à la méthode HelloWorld en utilisant l'instruction CREATE PROCEDURE. Nous appellerons la procédure stockée "hello" :

  
CREATE PROCEDURE hello  
@i nchar(25) OUTPUT  
AS  
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld  
-- if the HelloWorldProc class is inside a namespace (called MyNS),  
-- the last line in the create procedure statement would be  
-- EXTERNAL NAME helloworld.[MyNS.HelloWorldProc].HelloWorld

Une fois la procédure créée, il est possible de l'exécuter comme une procédure stockée normale écrite en Transact-SQL. Exécutez la commande suivante :

DECLARE @J nchar(25)  
EXEC hello @J out  
PRINT @J

Cela devrait générer la sortie suivante dans la fenêtre des messages de SQL Server Management Studio.

Hello world!  
Hello world!

Suppression de l'exemple de procédure stockée "Hello World"

Lorsque vous avez terminé d'exécuter l'exemple de procédure stockée, vous pouvez supprimer la procédure et l'assembly de votre base de données de test.

En premier lieu, supprimez la procédure à l'aide de la commande drop procedure.

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'hello')  
   drop procedure hello

Une fois la procédure supprimée, vous pouvez supprimer l'assembly qui contient votre exemple de code.

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'helloworld')  
   drop assembly helloworld

Voir aussi

Procédures stockées du CLR
Extensions spécifiques in-process de SQL Server à ADO.NET
Débogage d'objets de base de données CLR
Sécurité de l'intégration du CLR

Share via