Procédures stockées du CLR

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 le choix entre code managé et Transact-SQL, consultez vue d’ensemble de l’intégration du 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 comme des méthodes statiques publiques sur une classe dans un Microsoft .NET Framework assembly. 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. Exemple :

EXECUTE @return_status = procedure_name

Le @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 employé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 de n'importe quel type SQL Server doté d'un équivalent en code managé. Pour que la syntaxe Transact-SQL crée la procédure, ces types doivent être spécifiés avec le type SQL Server natif équivalent le mieux approprié. Pour plus d’informations sur les conversions de type, consultez la page de mappage de 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. Ils procurent une fonctionnalité semblable aux tableaux de paramètres, mais offrent une meilleure souplesse et une intégration plus étroite à 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 fonction ou à une procédure stockée managée s'exécutant dans le processus SQL Server , ni être retourné à partir de ces dernières. Pour plus d’informations sur les paramètres table, consultez Use Table-Valued paramètres (moteur de base de données).

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

Plusieurs moyens permettent de retourner des informations des procédures stockées .NET Framework. 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

Tout comme avec les procédures stockées Transact-SQL, des informations peuvent être retournées 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 le même que celle employée pour créer des procédures stockées écrites dans Transact-SQL. Le paramètre correspondant du code d'implémentation dans la classe .NET Framework doit utiliser un paramètre passé par référence en guise d'argument. Notez que Visual Basic ne prend pas en charge les paramètres de sortie de la même manière que Visual C#. Vous devez spécifier le paramètre par référence :

Imports System.Runtime.InteropServices  
…  
Public Shared Sub PriceSum (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 :

c#

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);  
            }  
         }           
      }  
   }  
}  

Visual Basic

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( 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 le CLR ci-dessus stockées procédure a été créée et créée sur le serveur, ce qui suit Transact-SQL est utilisé pour créer la procédure dans la base de données et spécifie somme comme paramètre de sortie.

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é comme un int type de données SQL Server et que le valeur paramètre défini dans la procédure stockée CLR est spécifié comme un 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 à un int SQL Server type de données. Pour plus d’informations sur le CLR des types de données peuvent et ne peuvent pas être convertis, consultez de mappage de données de paramètre CLR.

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

Retour de résultats tabulaires et des messages au client s’effectue via la SqlPipe objet, qui est obtenue à l’aide de la canal propriété de la SqlContext classe. Le SqlPipe objet a un envoyer méthode. En appelant le envoyer (méthode), vous pouvez transmettre les données via le canal à l’application appelante.

Il existe plusieurs surcharges de la SqlPipe.Send méthode, dont une qui envoie un SqlDataReader et une autre qui envoie simplement 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 les résultats d’une requête directement au client, utilisez une des surcharges de la Execute méthode sur le SqlPipe objet. 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. Exemple :

[C#]

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);  
      }  
   }  
}  

[Visual Basic]

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 envoyer méthode qui prend 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);  
      }  
   }  
}  

[Visual Basic]

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 dynamiques, remplir et l’envoyer au client, vous pouvez créer des enregistrements de la connexion actuelle et les envoyer à l’aide de 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);  
   }  
}  

[Visual Basic]

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 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);  
      }  
   }  
}  

[Visual Basic]

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 envoyer envoie un message au client, tandis que le second envoie un résultat tabulaire à l’aide de SqlDataReader.

Notez que ces exemples sont uniquement fournis à des fins d'illustration. Pour les applications qui exigent des calculs intensifs, les fonctions CLR conviennent mieux que de simples instructions Transact-SQL. Une procédure stockée Transact-SQL quasi équivalente de 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-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   

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