Procédures stockées CLR

Les procédures stockées sont des routines qui ne peuvent pas être utilisées dans les expressions scalaires. Contrairement aux fonctions scalaires, elles peuvent retourner des résultats tabulaires 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 clR et 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 être déclarée comme void ou 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 void, le code de retour est 0.

Si la méthode accepte des paramètres, le nombre de paramètres dans l’implémentation de .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 passés à une procédure stockée CLR peuvent être l’un des types SQL Server natifs qui ont un équivalent dans le code managé. Pour que la syntaxe Transact-SQL crée la procédure, ces types doivent être spécifiés avec l’équivalent de type SQL Server natif le plus approprié. Pour plus d’informations sur les conversions de types, consultez Mappage des données de paramètre 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 tvps offrent 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 transmis en tant que paramètre table à une procédure stockée managée ou à une fonction exécutée dans le processus SQL Server. Pour plus d’informations sur les tvps, consultez Utiliser les paramètres 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 de procédures stockées .NET Framework de plusieurs façons. Cela inclut les paramètres de sortie, les résultats tabulaires et les messages.

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

Comme avec Transact-SQL procédures stockées, les informations peuvent être retournées à partir de procédures stockées .NET Framework à l’aide de paramètres OUTPUT. La syntaxe DML Transact-SQL utilisée pour créer des procédures stockées .NET Framework est identique à celle utilisée pour la création de procédures stockées écrites dans Transact-SQL. Le paramètre correspondant dans le code d’implémentation de la classe .NET Framework doit utiliser un paramètre de référence pass-by-reference comme argument. Notez que Visual Basic ne prend pas en charge les paramètres de sortie de la même façon 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 les éléments suivants :

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

Voici 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, l'Transact-SQL suivant est utilisé pour créer la procédure dans la base de données et spécifie la 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 la somme est déclarée en tant que int type de données SQL Server et que le paramètre valeur 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 SqlInt32 type de 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ètre CLR.

Retour des résultats tabulaires et des messages

Le renvoi de résultats tabulaires et de messages au client est effectué via l’objet SqlPipe , obtenu à l’aide de la Pipe propriété de la SqlContext classe. L’objet SqlPipe a une Send méthode. En appelant la Send méthode, vous pouvez transmettre des données via le canal à l’application appelante.

Il s’agit de plusieurs surcharges de la SqlPipe.Send méthode, y compris une qui envoie une SqlDataReader et une autre qui envoie simplement une chaîne de texte.

Retour de messages

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

Retour des résultats tabulaires

Pour envoyer les résultats d’une requête directement au client, utilisez l’une des surcharges de la Execute méthode sur l’objet SqlPipe . Il s’agit du moyen le plus efficace de retourner les résultats au client, car les données sont transférées vers les mémoires 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 Send méthode qui prend un SqlDataReader. Cette méthode est légèrement plus lente que la méthode directe décrite précédemment, mais elle offre une plus grande flexibilité pour manipuler les données avant d’être envoyées 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, remplissez-le et envoyez-le au client, vous pouvez créer des enregistrements à partir de la connexion actuelle et les envoyer à l’aide SqlPipe.Sendde .

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 tabulaire 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 Send envoie un message au client, tandis que la seconde envoie un résultat tabulaire à l’aide SqlDataReaderde .

Notez que ces exemples sont uniquement à des fins d’illustration. Les fonctions CLR sont plus appropriées que les instructions de Transact-SQL simples pour les applications nécessitant beaucoup de calcul. Une procédure stockée Transact-SQL presque équivalente à l’exemple précédent est la suivante :

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

Si le code Visual C# ci-dessus 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   

L’assembly résultant peut être inscrit et le point d’entrée appelé, avec la DDL suivante :

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

Voir aussi

Fonctions User-Defined CLR
CLR User-Defined Types
Déclencheurs CLR