Procédures stockées du CLR

Article
05/07/2013

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 à faire 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 CLR (Common Language Runtime), les procédures stockées sont implémentées en tant que méthodes statiques publiques sur une classe au sein d'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 variable @return\_status doit contenir 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 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. 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 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 Utiliser les paramètres table (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 et appliquer l'attribut <Out()> pour représenter un paramètre OUTPUT comme 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);
            }
         }         
      }
   }
}

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( <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 l'assembly contenant la procédure stockée CLR ci-dessus généré er créé sur le serveur, le code Transact-SQL suivant est utilisé pour créer la procédure dans la base de données et spécifie sum 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 sum est déclaré en tant que type de données SQL Server int et que le paramètre value défini dans la procédure stockée CLR est spécifié en tant que type de données SqlInt32 CLR. Lorsqu'un programme appelant exécute la procédure stockée CLR, SQL Server convertit automatiquement le type de données CLR SqlInt32 en type de données SQL Server int. Pour plus d'informations sur les types de données CLR qu'il est possible ou non de convertir, 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 :

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

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

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

[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 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. 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;

[!REMARQUE]

Les messages et les jeux de résultats sont extraits différemment dans l'application cliente. Par exemple, les jeux de résultats SQL Server Management Studio apparaissent 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

[!REMARQUE]

Depuis SQL Server 2005, les objets de base de données Visual C++ (notamment 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

Concepts

Types CLR définis par l'utilisateur

Déclencheurs CLR

Autres ressources

Fonctions CLR définies par l'utilisateur

Partager via

Procédures stockées du CLR

Configuration requise pour les procédures stockées CLR

Paramètres table

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

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

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

Retour de messages

Retour de résultats sous forme de tableau

Voir aussi

Concepts

Autres ressources

Ressources supplémentaires