Procédures stockées CLR

Article
04/27/2017

Les procédures stockées sont des routines que vous ne pouvez pas utiliser dans des expressions scalaires. Contrairement aux fonctions scalaires, elles peuvent retourner des résultats scalaires et des messages au client, appeler des instructions DDL (Data Definition Language) et DML (Data Manipulation Language) et retourner des paramètres de sortie. Pour plus d’informations sur les avantages de l’intégration du CLR et sur le choix entre le code managé et Transact-SQL, consultez Vue d’ensemble de l’intégration CLR.

Configuration requise pour les procédures stockées CLR

Dans le Common Language Runtime (CLR), les procédures stockées sont implémentées en tant que méthodes statiques publiques sur une classe dans un assembly Microsoft.NET Framework. La méthode statique peut soit être déclarée de type « void », soit retourner une valeur entière. Si elle retourne une valeur entière, l'entier retourné est traité comme le code de retour de la procédure. Par exemple :

EXECUTE @return_status = procedure_name

La @return_status variable contient la valeur retournée par la méthode . Si la méthode est déclarée de type void, le code de retour est 0.

Si la méthode accepte des paramètres, le nombre de paramètres dans l'implémentation .NET Framework doit être identique au nombre de paramètres utilisés dans la déclaration Transact-SQL de la procédure stockée.

Les paramètres transmis à une procédure stockée CLR peuvent correspondre à tout type SQL Server natif ayant un équivalent en code managé. Concernant la syntaxe Transact-SQL utilisée pour créer la procédure, ces types doivent être spécifiés avec l'équivalent le plus approprié du type SQL Server natif. Pour plus d’informations sur les conversions de type, consultez Mappage des données de paramètres CLR.

Paramètres table

Les paramètres table (types de tables définis par l'utilisateur et passés dans une procédure ou une fonction) offrent un moyen efficace pour passer plusieurs lignes de données au serveur. Les fournisseurs de services de sécurité fournissent des fonctionnalités similaires aux tableaux de paramètres, mais offrent une plus grande flexibilité et une intégration plus étroite avec Transact-SQL. Ils sont également susceptibles de générer de meilleures performances. Les paramètres table aident également à réduire le nombre d'allers-retours au serveur. Au lieu d'envoyer plusieurs demandes au serveur, comme avec une liste de paramètres scalaires, les données peuvent être envoyées au serveur en tant que paramètres table. Un type de table défini par l’utilisateur ne peut pas être passé en tant que paramètre table à une procédure stockée managée ou à partir d’une fonction s’exécutant dans le processus de SQL Server. Pour plus d’informations sur les fournisseurs de données, consultez Utiliser des paramètres de Table-Valued (moteur de base de données).

Retour des résultats des procédures stockées CLR

Les informations peuvent être retournées à partir des procédures stockées .NET Framework de plusieurs manières. Il peut s'agir notamment de paramètres de sortie, de résultats sous forme de tableau et de messages.

Paramètres OUTPUT et procédures stockées CLR

Comme pour les procédures stockées Transact-SQL, des informations peuvent être retournées à partir de procédures stockées .NET Framework à l’aide des paramètres OUTPUT. La syntaxe DML Transact-SQL utilisée pour la création de procédures stockées .NET Framework est identique à celle utilisée pour la création de procédures stockées écrites en Transact-SQL. Le paramètre correspondant dans le code d'implémentation dans la classe .NET Framework doit utiliser un paramètre passé par référence comme argument. Notez que Visual Basic ne prend pas en charge les paramètres de sortie de la même manière que C#. Vous devez spécifier le paramètre par référence et appliquer l’attribut <Out()> pour représenter un paramètre OUTPUT, comme dans ce qui suit :

Imports System.Runtime.InteropServices  
...  
Public Shared Sub PriceSum ( <Out()> ByRef value As SqlInt32)

Le code ci-dessous présente une procédure stockée qui retourne des informations par le biais d'un paramètre OUTPUT :

using System;  
using System.Data.SqlTypes;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void PriceSum(out SqlInt32 value)  
   {  
      using(SqlConnection connection = new SqlConnection("context connection=true"))   
      {  
         value = 0;  
         connection.Open();  
         SqlCommand command = new SqlCommand("SELECT Price FROM Products", connection);  
         SqlDataReader reader = command.ExecuteReader();  
  
         using (reader)  
         {  
            while( reader.Read() )  
            {  
               value += reader.GetSqlInt32(0);  
            }  
         }           
      }  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
Imports System.Runtime.InteropServices  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Executes a query and iterates over the results to perform a summation.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub PriceSum( <Out()> ByRef value As SqlInt32)  
  
        Using connection As New SqlConnection("context connection=true")  
           value = 0  
           Connection.Open()  
           Dim command As New SqlCommand("SELECT Price FROM Products", connection)  
           Dim reader As SqlDataReader  
           reader = command.ExecuteReader()  
  
           Using reader  
              While reader.Read()  
                 value += reader.GetSqlInt32(0)  
              End While  
           End Using  
        End Using          
    End Sub  
End Class

Une fois que l’assembly contenant la procédure stockée CLR ci-dessus a été généré et créé sur le serveur, le transact-SQL suivant est utilisé pour créer la procédure dans la base de données et spécifie somme en tant que paramètre OUTPUT.

CREATE PROCEDURE PriceSum (@sum int OUTPUT)  
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum  
-- if StoredProcedures class was inside a namespace, called MyNS,  
-- you would use:  
-- AS EXTERNAL NAME TestStoredProc.[MyNS.StoredProcedures].PriceSum

Notez que somme est déclarée en tant que int type de données SQL Server et que le paramètre value défini dans la procédure stockée CLR est spécifié en tant que SqlInt32 type de données CLR. Lorsqu’un programme appelant exécute la procédure stockée CLR, SQL Server convertit automatiquement le type de SqlInt32 données CLR en inttype de données SQL Server. Pour plus d’informations sur les types de données CLR qui peuvent et ne peuvent pas être convertis, consultez Mappage des données de paramètres CLR.

Retour de résultats sous forme de tableau et de messages

Le retour au client de résultats sous forme de tableau et de messages s'effectue via l'objet SqlPipe obtenu en utilisant la propriété Pipe de la classe SqlContext. L'objet SqlPipe emploie une méthode Send. En appelant la méthode Send, vous pouvez transmettre des données à l'application appelante par l'intermédiaire du canal.

Il existe plusieurs surcharges de la méthode SqlPipe.Send. L'une d'elles permet d'envoyer un SqlDataReader, une autre de simplement transmettre une chaîne de texte.

Retour de messages

Utilisez SqlPipe.Send(string) pour envoyer des messages à l'application cliente. Le texte du message est limité à 8 000 caractères. Si le message dépasse cette limite, il sera tronqué.

Retour de résultats sous forme de tableau

Pour envoyer directement les résultats d'une requête au client, appliquez l'une des surcharges de la méthode Execute à l'objet SqlPipe. C'est le moyen le plus efficace de retourner des résultats au client puisque les données sont transférées vers les tampons réseau sans être copiées dans la mémoire managée. Par exemple :

using System;  
using System.Data;  
using System.Data.SqlTypes;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   /// <summary>  
   /// Execute a command and send the results to the client directly.  
   /// </summary>  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void ExecuteToClient()  
   {  
   using(SqlConnection connection = new SqlConnection("context connection=true"))   
   {  
      connection.Open();  
      SqlCommand command = new SqlCommand("select @@version", connection);  
      SqlContext.Pipe.ExecuteAndSend(command);  
      }  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Execute a command and send the results to the client directly.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub ExecuteToClient()  
        Using connection As New SqlConnection("context connection=true")  
            connection.Open()  
            Dim command As New SqlCommand("SELECT @@VERSION", connection)  
            SqlContext.Pipe.ExecuteAndSend(command)  
        End Using  
    End Sub  
End Class

Pour envoyer les résultats d'une requête exécutée précédemment via le fournisseur in-process (ou pour pré-traiter les données à l'aide d'une implémentation personnalisée de SqlDataReader), utilisez la surcharge de la méthode Send qui accepte un SqlDataReader. Cette méthode s'avère légèrement plus lente que la méthode directe décrite ci-avant mais offre une plus grande souplesse pour manipuler les données avant qu'elles ne soient transmises au client.

using System;  
using System.Data;  
using System.Data.SqlTypes;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   /// <summary>  
   /// Execute a command and send the resulting reader to the client  
   /// </summary>  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SendReaderToClient()  
   {  
      using(SqlConnection connection = new SqlConnection("context connection=true"))   
      {  
         connection.Open();  
         SqlCommand command = new SqlCommand("select @@version", connection);  
         SqlDataReader r = command.ExecuteReader();  
         SqlContext.Pipe.Send(r);  
      }  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Execute a command and send the results to the client directly.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub SendReaderToClient()  
        Using connection As New SqlConnection("context connection=true")  
            connection.Open()  
            Dim command As New SqlCommand("SELECT @@VERSION", connection)  
            Dim reader As SqlDataReader  
            reader = command.ExecuteReader()  
            SqlContext.Pipe.Send(reader)  
        End Using  
    End Sub  
End Class

Pour créer un jeu de résultats dynamique, le remplir et le transmettre au client, vous pouvez créer des enregistrements de la connexion actuelle et les envoyer à l'aide de la méthode SqlPipe.Send.

using System.Data;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
using System.Data.SqlTypes;  
  
public class StoredProcedures   
{  
   /// <summary>  
   /// Create a result set on the fly and send it to the client.  
   /// </summary>  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SendTransientResultSet()  
   {  
      // Create a record object that represents an individual row, including it's metadata.  
      SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));  
  
      // Populate the record.  
      record.SetSqlString(0, "Hello World!");  
  
      // Send the record to the client.  
      SqlContext.Pipe.Send(record);  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Create a result set on the fly and send it to the client.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub SendTransientResultSet()  
        ' Create a record object that represents an individual row, including it's metadata.  
        Dim record As New SqlDataRecord(New SqlMetaData("stringcol", SqlDbType.NVarChar, 128) )  
  
        ' Populate the record.  
        record.SetSqlString(0, "Hello World!")  
  
        ' Send the record to the client.  
        SqlContext.Pipe.Send(record)          
    End Sub  
End Class

Voici un exemple d'envoi d'un résultat sous forme de tableau et d'un message via SqlPipe.

using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void HelloWorld()  
   {  
      SqlContext.Pipe.Send("Hello world! It's now " + System.DateTime.Now.ToString()+"\n");  
      using(SqlConnection connection = new SqlConnection("context connection=true"))   
      {  
         connection.Open();  
         SqlCommand command = new SqlCommand("SELECT ProductNumber FROM ProductMaster", connection);  
         SqlDataReader reader = command.ExecuteReader();  
         SqlContext.Pipe.Send(reader);  
      }  
   }  
}

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Execute a command and send the results to the client directly.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub HelloWorld()  
        SqlContext.Pipe.Send("Hello world! It's now " & System.DateTime.Now.ToString() & "\n")  
        Using connection As New SqlConnection("context connection=true")  
            connection.Open()  
            Dim command As New SqlCommand("SELECT ProductNumber FROM ProductMaster", connection)  
            Dim reader As SqlDataReader  
            reader = command.ExecuteReader()  
            SqlContext.Pipe.Send(reader)  
        End Using  
    End Sub  
End Class

La première méthode Send envoie un message au client tandis que la deuxième transmet un résultat sous forme de tableau à l'aide de SqlDataReader.

Notez que ces exemples sont uniquement fournis à des fins d'illustration. Les fonctions CLR sont plus appropriées que les instructions Transact-SQL simples pour les applications gourmandes en calculs. Une procédure stockée Transact-SQL presque équivalente à l’exemple précédent est :

CREATE PROCEDURE HelloWorld() AS  
BEGIN  
PRINT('Hello world!')  
SELECT ProductNumber FROM ProductMaster  
END;

Notes

Les messages et les jeux de résultats sont extraits différemment dans l'application cliente. Par instance, SQL Server Management Studio jeux de résultats s’affichent dans la vue Résultats et les messages s’affichent dans le volet Messages.

Si le code Visual C# ci-avant est enregistré dans un fichier MyFirstUdp.cs et compilé avec :

csc /t:library /out:MyFirstUdp.dll MyFirstUdp.cs

Ou si le code Visual Basic ci-dessus est enregistré dans un fichier MyFirstUdp.vb et compilé avec :

vbc /t:library /out:MyFirstUdp.dll MyFirstUdp.vb

Notes

À compter de SQL Server 2005, les objets de base de données Visual C++ (tels que les procédures stockées) compilés avec /clr:pure ne sont pas pris en charge pour l’exécution.

L'assembly obtenu peut être inscrit et le point d'entrée appelé avec le DDL suivant :

CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll';  
CREATE PROCEDURE HelloWorld  
AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld;  
EXEC HelloWorld;

Voir aussi

Fonctions CLR définies par l’utilisateur
Types CLR définis par l’utilisateur
Déclencheurs CLR

Share via