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 con CLR.
Requisiti per le stored procedure CLR
In CLR le stored procedure vengono implementate come metodi statici pubblici su 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. Esempio:
EXECUTE @return_status = procedure_name
La variabile @return_status conterrà il valore restituito dal metodo. Se il metodo viene dichiarato come void, il codice restituito è 0.
Se il metodo accetta parametri, il numero di parametri nell'implementazione di .NET Framework deve coincidere con il numero di parametri utilizzato nella dichiarazione Transact-SQL della stored procedure.
I parametri passati a una stored procedure possono essere di uno dei tipi nativi di SQL Server per cui è presente un equivalente nel codice gestito. Affinché la sintassi Transact-SQL crei la procedura, questi tipi devono essere specificati con l'equivalente più appropriato del tipo nativo di SQL Server. Per ulteriori 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. Pur essendo caratterizzati da funzionalità simili alle matrici di parametri, i parametri con valori di tabella offrono più flessibilità e una maggiore integrazione con Transact-SQL e consentono 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 una stored procedure gestita o a una funzione in esecuzione nel processo SQL Server, né può essere restituito dalle stesse. Per ulteriori informazioni sui parametri con valori di tabella, vedere Parametri con valori di tabella (Motore di database).
Restituzione di risultati da stored procedure CLR
Le informazioni possono essere restituite in diversi modi dalle stored procedure .NET Framework, ad esempio come parametri di output, risultati tabulari e messaggi.
Parametri di output e stored procedure CLR
Analogamente alle stored procedure Transact-SQL, le informazioni possono essere restituite dalle stored procedure .NET Framework utilizzando parametri OUTPUT. La sintassi DML di Transact-SQL utilizzata per la creazione di stored procedure .NET Framework è la stessa utilizzata per la creazione di stored procedure scritte in Transact-SQL. Il parametro corrispondente nel codice di implementazione nella classe .NET Framework deve utilizzare un parametro di passaggio per riferimento come argomento. Si noti che Visual Basic non supporta parametri di output nello stesso modo in cui tali parametri sono supportati in Visual C#. Per rappresentare un parametro OUTPUT, è necessario specificare il parametro per ogni riferimento e applicare l'attributo <Out()>, come nell'esempio seguente:
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:
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( <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 che contiene la stored procedure CLR precedente è stato compilato e creato nel server, viene utilizzata la sintassi Transact-SQL seguente per creare la procedura nel database e per specificare sum come parametro OUTPUT.
CREATE PROCEDURE PriceSum (@sum int OUTPUT)
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum
Si noti che sum viene dichiarato come tipo di dati int di SQL Server e che il parametro value definito nella stored procedure CLR viene specificato come tipo di dati SqlInt32 CLR. Quando un programma chiamante esegue la stored procedure CLR, SQL Server converte automaticamente il tipo di dati SqlInt32 CLR in un tipo di dati int di SQL Server. Per ulteriori informazioni sui tipi di dati CLR che possono o meno 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. Esempi:
[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
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);
}
}
}
[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
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);
}
}
[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
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);
}
}
}
[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
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 istruzioni Transact-SQL semplici per le applicazioni che richiedono un elevato carico di elaborazione. Una stored procedure Transact-SQL quasi equivalente all'esempio precedente è la seguente:
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. I set di risultati di SQL Server Management Studio, ad esempio, vengono visualizzati nella vista Risultati, mentre 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, l'esecuzione di oggetti di database Visual C++, ad esempio le stored procedure, compilati con /clr:pure non è più supportata.
L'assembly risultante può essere registrato e il punto di ingresso può essere richiamato, utilizzando l'istruzione DLL seguente:
CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll'
CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld
EXEC HelloWorld
Nota
A partire da SQL Server 2005, in un database di SQL Server con livello di compatibilità pari a 80 non è possibile creare tipi, stored procedure, funzioni, aggregazioni o trigger gestiti definiti dall'utente. Per sfruttare le funzionalità di integrazione CLR di SQL Server, è necessario utilizzare la stored procedure sp_dbcmptlevel per impostare il livello di compatibilità del database su 100.
Vedere anche