Stored procedure CLR

Le stored procedure sono routine che non possono essere utilizzate in espressioni scalari. Diversamente dalle funzioni scalari, possono restituire risultati tabulari e messaggi al client, richiamare istruzioni DDL (Data Definition Language) e DML (Data Manipulation Language) e restituire parametri di output. Per informazioni sui vantaggi dell'integrazione CLR e sulla scelta tra codice gestito e Transact-SQL, vedere Panoramica dell'integrazione CLR.

Requisiti per le stored procedure CLR

In Common Language Runtime (CLR), le stored procedure vengono implementate come metodi statici pubblici in una classe in un assembly Microsoft.NET Framework. Il metodo statico può essere dichiarato come void o restituisce un valore integer. Se restituisce un valore integer, il numero intero restituito viene considerato come codice restituito dalla procedura. Ad esempio:

EXECUTE @return_status = procedure_name

La @return_status variabile conterrà il valore restituito dal metodo . Se il metodo viene dichiarato come void, il codice restituito è 0.

Se il metodo utilizza parametri, il numero di parametri nell'implementazione di .NET Framework dovrà essere uguale al numero di parametri utilizzati nella dichiarazione Transact-SQL della stored procedure.

I parametri passati a una stored procedure CLR possono essere di qualsiasi tipo nativo di SQL Server che disponga di un equivalente in forma di codice gestito. Affinché la sintassi Transact-SQL possa creare la procedura, è necessario specificare questi tipi con l'equivalente più appropriato del tipo nativo di SQL Server. Per altre informazioni sulle conversioni dei tipi, vedere Mapping dei dati dei parametri CLR.

Parametri con valori di tabella

I parametri con valori di tabella, ovvero tipi di tabella definiti dall'utente passati in una procedura o in una funzione, consentono di passare in modo efficiente più righe di dati al server. Le TVP offrono funzionalità simili a matrici di parametri, ma offrono maggiore flessibilità e maggiore integrazione con Transact-SQL. Consentono inoltre di ottenere prestazioni potenzialmente migliori. I parametri con valori di tabella consentono inoltre di ridurre il numero di round trip al server. Anziché inviare più richieste al server, ad esempio con un elenco di parametri scalari, è possibile inviare i dati al server sotto forma di parametro con valori di tabella. Un tipo di tabella definito dall'utente non può essere passato come parametro con valori di tabella a o restituito da, una stored procedure gestita o una funzione in esecuzione nel processo di SQL Server. Per altre informazioni sugli indirizzi TV, vedere Usare parametri Table-Valued (motore di database).

Restituzione di risultati da stored procedure CLR

Le informazioni possono essere restituite dalle stored procedure di .NET Framework in diversi modi. ad esempio come parametri di output, risultati tabulari e messaggi.

Parametri di output e stored procedure CLR

Come per le stored procedure Transact-SQL, le informazioni possono essere restituite dalle stored procedure di .NET Framework usando parametri OUTPUT. La sintassi DML Transact-SQL utilizzata per la creazione di stored procedure di .NET Framework è la stessa utilizzata per la creazione di stored procedure scritte in Transact-SQL. È necessario che il parametro corrispondente nel codice di implementazione nella classe .NET Framework utilizzi un parametro di passaggio per riferimento come argomento. Si noti che Visual Basic non supporta i parametri di output nello stesso modo in cui C# esegue. È necessario specificare il parametro per riferimento e applicare l'attributo <Out()> per rappresentare un parametro OUTPUT, come indicato di seguito:

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

Nell'esempio seguente viene illustrata una stored procedure che restituisce informazioni tramite un parametro 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  

Dopo aver creato e creato l'assembly contenente la stored procedure CLR precedente nel server, viene usato transact-SQL seguente per creare la routine nel database e specifica la somma come parametro 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  

Si noti che la somma viene dichiarata come tipo int di dati SQL Server e che il parametro valore definito nella stored procedure CLR viene specificato come SqlInt32 tipo di dati CLR. Quando un programma chiamante esegue la stored procedure CLR, SQL Server converte automaticamente il SqlInt32 tipo di dati CLR in un inttipo di dati SQL Server. Per altre informazioni sui tipi di dati CLR che possono e non possono essere convertiti, vedere Mapping dei dati dei parametri CLR.

Restituzione di risultati tabulari e messaggi

La restituzione di risultati tabulari e messaggi al client viene eseguita tramite l'oggetto SqlPipe, ottenuto tramite la proprietà Pipe della classe SqlContext. L'oggetto SqlPipe include un metodo Send. Chiamando il metodo Send, è possibile trasmettere dati tramite la pipe all'applicazione chiamante.

Sono disponibili diversi overload del metodo SqlPipe.Send, incluso uno che invia un oggetto SqlDataReader e un altro che invia semplicemente una stringa di testo.

Restituzione di messaggi

Utilizzare SqlPipe.Send(string) per inviare messaggi all'applicazione client. Il testo del messaggio ha un limite di 8000 caratteri. Se il messaggio supera 8000 caratteri, verrà troncato.

Restituzione di risultati tabulari

Per inviare i risultati di una query direttamente al client, utilizzare uno degli overload del metodo Execute sull'oggetto SqlPipe. Si tratta della soluzione più efficiente per restituire risultati al client, in quanto i dati vengono trasferiti ai buffer di rete senza essere copiati nella memoria gestita. Ad esempio:

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  

Per inviare i risultati di una query eseguita in precedenza tramite il provider in-process, o per pre-elaborare i dati utilizzando un'implementazione personalizzata di SqlDataReader, utilizzare l'overload del metodo Send che accetta un oggetto SqlDataReader. Questo metodo è leggermente più lento del metodo diretto descritto in precedenza, ma offre una maggiore flessibilità per modificare i dati prima di inviarli al 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  

Per creare un set di risultati dinamico, popolarlo e inviarlo al client, è possibile creare record dalla connessione corrente e inviarli tramite 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   

Di seguito viene fornito un esempio di invio di un risultato tabulare e di un messaggio tramite 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   

Il primo metodo Send invia un messaggio al client, mentre il secondo invia un risultato tabulare tramite SqlDataReader.

Si noti che questi esempi vengono forniti esclusivamente a scopo illustrativo. Le funzioni CLR sono più appropriate rispetto alle semplici istruzioni Transact-SQL per le applicazioni a elevato utilizzo di calcolo. Una stored procedure Transact-SQL quasi equivalente all'esempio precedente è:

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

Nota

Messaggi e set di risultati vengono recuperati in modo diverso nell'applicazione client. Ad esempio, SQL Server Management Studio set di risultati vengono visualizzati nella visualizzazione Risultati e i messaggi vengono visualizzati nel riquadro Messaggi.

Se il codice di Visual C# precedente viene salvato in un file MyFirstUdp.cs e compilato con:

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

O se il codice di Visual Basic precedente viene salvato in un file MyFirstUdp.cs e compilato con:

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

Nota

A partire da SQL Server 2005, gli oggetti di database Visual C++ (ad esempio stored procedure) compilati con /clr:pure non sono supportati per l'esecuzione.

L'assembly risultante può essere registrato e il punto di ingresso richiamato utilizzando l'istruzione DDL seguente:

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

Vedere anche

Funzioni CLR definite dall'utente
Tipi CLR definiti dall'utente
Trigger CLR