Condividi tramite

Funzioni a valori scalari CLR

  • Articolo

Si applica a: SQL Server

Una funzione con valori scalari (SVF) restituisce un singolo valore, ad esempio una stringa, un numero intero o un valore di bit. È possibile creare funzioni scalari definite dall'utente in codice gestito usando qualsiasi linguaggio di programmazione .NET Framework. Queste funzioni sono accessibili a Transact-SQL o ad altro codice gestito. 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 funzioni a valori scalari CLR

Le funzioni a valori scalari di .NET Framework vengono implementate come metodi in una classe di un assembly .NET Framework. I parametri di input e il tipo restituito da un SVF possono essere uno qualsiasi dei tipi di dati scalari supportati da SQL Server, ad eccezione di varchar, char, rowversion, text, ntext, image, timestamp, tabella o cursore. Le funzioni svfs devono garantire una corrispondenza tra il tipo di dati di SQL Server e il tipo di dati restituito del metodo di implementazione. Per altre informazioni sulle conversioni dei tipi, vedere Mapping dei dati dei parametri CLR.

Quando si implementa un SVF di .NET Framework in un linguaggio .NET Framework, è possibile specificare l'attributo personalizzato SqlFunction per includere informazioni aggiuntive sulla funzione. L'attributo SqlFunction indica se la funzione accede o modifica i dati, se deterministica e se la funzione prevede operazioni a virgola mobile.

Le funzioni definite dall'utente a valori scalari possono essere deterministiche o non deterministiche. Una funzione deterministica restituisce sempre lo stesso risultato quando viene chiamata con un set specifico di parametri di input. Una funzione non deterministica può restituire risultati diversi quando viene chiamata con un set specifico di parametri di input.

Nota

Non contrassegnare una funzione come deterministica se non produce sempre gli stessi valori di output, dati gli stessi valori di input e lo stesso stato del database. Una funzione non propriamente deterministica ma contrassegnata come tale può causare danni a viste indicizzate e colonne calcolate. Per contrassegnare una funzione come deterministica, impostare la proprietà IsDeterministic su true.

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 visualizzazione, vedere Usare parametri con valori di tabella (motore di database).

Esempio di una funzione a valori scalari CLR

Di seguito è riportata una semplice funzione a valori scalari che accede ai dati e restituisce un valore integer:

using Microsoft.SqlServer.Server;  
using System.Data.SqlClient;  
  
public class T  
{  
    [SqlFunction(DataAccess = DataAccessKind.Read)]  
    public static int ReturnOrderCount()  
    {  
        using (SqlConnection conn   
            = new SqlConnection("context connection=true"))  
        {  
            conn.Open();  
            SqlCommand cmd = new SqlCommand(  
                "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);  
            return (int)cmd.ExecuteScalar();  
        }  
    }  
}  

Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
Public Class T  
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _  
    Public Shared Function ReturnOrderCount() As Integer  
        Using conn As New SqlConnection("context connection=true")  
            conn.Open()  
            Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)  
            Return CType(cmd.ExecuteScalar(), Integer)  
        End Using  
    End Function  
End Class

La prima riga di codice fa riferimento a Microsoft.SqlServer.Server per accedere agli attributi e System.Data.SqlClient per accedere allo spazio dei nomi ADO.NET. Questo spazio dei nomi contiene SqlClient, .NET Framework provider di dati per SQL Server.

Successivamente, la funzione riceve l'attributo personalizzato SqlFunction , disponibile nello spazio dei nomi Microsoft.SqlServer.Server . L'attributo personalizzato indica se la funzione definita dall'utente utilizza il provider in-process per leggere dati nel server. SQL Server non consente alle funzioni definite dall'utente di aggiornare, inserire o eliminare dati. SQL Server può ottimizzare l'esecuzione di una funzione definita dall'utente che non usa il provider in-process. Ciò è indicato impostando DataAccessKind su DataAccessKind.None. Sulla riga successiva il metodo di destinazione è un metodo statico pubblico (condiviso in Visual Basic .NET).

La classe SqlContext , che si trova nello spazio dei nomi Microsoft.SqlServer.Server , può quindi accedere a un oggetto SqlCommand con una connessione all'istanza di SQL Server già configurata. Anche se non usato qui, il contesto della transazione corrente è disponibile anche tramite l'API (Application Programming Interface) System.Transactions .

La maggior parte delle righe di codice nel corpo della funzione dovrebbe avere un aspetto familiare agli sviluppatori che hanno scritto applicazioni client che usano i tipi trovati nello spazio dei nomi System.Data.SqlClient .

[C#]

using(SqlConnection conn = new SqlConnection("context connection=true"))   
{  
   conn.Open();  
   SqlCommand cmd = new SqlCommand(  
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);  
   return (int) cmd.ExecuteScalar();  
}

[Visual Basic]

Using conn As New SqlConnection("context connection=true")  
   conn.Open()  
   Dim cmd As New SqlCommand( _  
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)  
   Return CType(cmd.ExecuteScalar(), Integer)  
End Using

Il testo del comando appropriato viene specificato inizializzando l'oggetto SqlCommand . Nell'esempio precedente viene conteggiato il numero di righe nella tabella SalesOrderHeader. Viene quindi chiamato il metodo ExecuteScalar dell'oggetto cmd . Viene restituito un valore di tipo int in base alla query. Infine, il numero di ordini viene restituito al chiamante.

Se questo codice viene salvato in un file chiamato FirstUdf.cs, può essere compilato come assembly nel modo seguente:

[C#]

csc.exe /t:library /out:FirstUdf.dll FirstUdf.cs

[Visual Basic]

vbc.exe /t:library /out:FirstUdf.dll FirstUdf.vb

Nota

/t:library indica che è necessario generare una libreria anziché un file eseguibile. I file eseguibili non possono essere registrati in SQL Server.

Nota

Gli oggetti di database visual C++ compilati con /clr:pure non sono supportati per l'esecuzione in SQL Server. Questo tipo di oggetti di database include, ad esempio, funzioni a valori scalari.

La query Transact-SQL e una chiamata di esempio per registrare l'assembly e la funzione definita dall'utente sono:

CREATE ASSEMBLY FirstUdf FROM 'FirstUdf.dll';  
GO  
  
CREATE FUNCTION CountSalesOrderHeader() RETURNS INT   
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount;   
GO  
  
SELECT dbo.CountSalesOrderHeader();  
GO

Si noti che il nome della funzione esposto in Transact-SQL non deve corrispondere al nome del metodo statico pubblico di destinazione.

Vedi anche

Mapping dei dati dei parametri CLR
Panoramica degli attributi personalizzati dell'integrazione con CLR
Funzioni definite dall'utente
Accesso ai dati da oggetti di database CLR