Stored procedure CLR

Le stored procedure sono routine che non possono essere usate nelle espressioni scalari. A differenza delle funzioni scalari, possono restituire messaggi e risultati tabulari al client, richiamare istruzioni DDL (Data Definition Language) e DML (Data Manipulation Language) e restituire parametri di output. Per informazioni sui vantaggi dell'integrazione con CLR e sulla scelta tra codice gestito e Transact-SQL, vedere Panoramica dell'integrazione con 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 di Microsoft.NET Framework. Il metodo statico può essere dichiarato come void o restituire un valore intero. Se restituisce un valore intero, l'intero restituito viene considerato come codice restituito dalla routine. Per esempio:

EXECUTE @return_status = procedure_name

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

Se il metodo accetta parametri, il numero di parametri nell'implementazione di .NET Framework deve corrispondere al numero di parametri usati nella dichiarazione Transact-SQL della stored procedure.

I parametri passati a una stored procedure CLR possono essere uno dei tipi nativi di SQL Server che hanno un equivalente nel codice gestito. Per la sintassi Transact-SQL per creare la procedura, questi tipi devono essere specificati con il tipo di SQL Server nativo più appropriato equivalente. 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. I provider di servizi di visualizzazione offrono funzionalità simili alle 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. Non è possibile passare un tipo di tabella definito dall'utente come parametro con valori di tabella a o essere restituito da una stored procedure gestita o una funzione in esecuzione nel processo di SQL Server. Per altre informazioni sui provider di servizi di configurazione, 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. Sono inclusi i parametri di output, i risultati tabulari e i messaggi.

Parametri OUTPUT e stored procedure CLR

Come per Transact-SQL stored procedure, le informazioni possono essere restituite dalle stored procedure di .NET Framework usando i parametri OUTPUT. La sintassi DML Transact-SQL usata per la creazione di stored procedure .NET Framework è identica a quella usata per la creazione di stored procedure scritte in Transact-SQL. Il parametro corrispondente nel codice di implementazione nella classe .NET Framework deve usare un parametro pass-by-reference 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 illustrato di seguito:

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

Di seguito 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 che l'assembly contenente la stored procedure CLR precedente è stato compilato e creato nel server, viene usato il 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 int tipo di dati di SQL Server e che il parametro value 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 messaggi e risultati tabulari

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

Si tratta di diversi overload del SqlPipe.Send metodo, tra cui uno che invia un SqlDataReader oggetto e un altro che invia semplicemente una stringa di testo.

Restituzione di messaggi

Usare SqlPipe.Send(string) per inviare messaggi all'applicazione client. Il testo del messaggio è limitato a 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 Execute metodo sull'oggetto SqlPipe . Questo è il modo più efficiente per restituire i risultati al client, poiché i dati vengono trasferiti nei buffer di rete senza essere copiati nella memoria gestita. Per 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 usando un'implementazione personalizzata di SqlDataReader), usare l'overload del Send metodo che accetta un oggetto SqlDataReader. Questo metodo è leggermente più lento rispetto al metodo diretto descritto in precedenza, ma offre maggiore flessibilità per la modifica dei dati prima dell'invio 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 usando 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 è riportato 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 Send invia un messaggio al client, mentre il secondo invia un risultato tabulare usando SqlDataReader.

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

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

Annotazioni

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

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

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

In alternativa, se il codice Visual Basic precedente viene salvato in un file MyFirstUdp.vb e compilato con:

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

Annotazioni

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 con il 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 User-Defined
Tipi clr User-Defined
Trigger CLR