CLR-Skalarwertfunktionen

Gilt für:SQL Server

Eine Skalarwertfunktion (SVF) gibt einen einzelnen Wert zurück, z. B. eine Zeichenfolge, eine ganze Zahl oder einen Bitwert. Sie können benutzerdefinierte Skalarfunktionen in verwaltetem Code mithilfe einer beliebigen .NET Framework Programmiersprache erstellen. Auf diese Funktionen kann mit Transact-SQL oder anderem verwalteten Code zugegriffen werden. Informationen zu den Vorteilen der CLR-Integration und zur Auswahl zwischen verwaltetem Code und Transact-SQL finden Sie unter Übersicht über die CLR-Integration.

Anforderungen für CLR-Skalarwertfunktionen

.NET Framework-Skalarwertfunktionen werden als Methoden einer Klasse in einer .NET Framework-Assembly implementiert. Bei den Eingabeparametern und dem typ, der von einer SVF zurückgegeben wird, kann es sich um einen beliebigen skalaren Datentyp handeln, der von SQL Server unterstützt wird, mit Ausnahme von varchar, char, rowversion, text, ntext, image, timestamp, table oder cursor. SVFs müssen eine Übereinstimmung zwischen dem SQL Server Datentyp und dem Rückgabedatentyp der Implementierungsmethode sicherstellen. Weitere Informationen zu Typkonvertierungen finden Sie unter Zuordnen von CLR-Parameterdaten.

Beim Implementieren eines .NET Framework SVF in einer .NET Framework Sprache kann das benutzerdefinierte SqlFunction-Attribut angegeben werden, um zusätzliche Informationen zur Funktion einzuschließen. Das SqlFunction-Attribut gibt an, ob die Funktion auf Daten zugreift oder diese ändert, ob sie deterministisch ist und ob die Funktion Gleitkommavorgänge umfasst.

Benutzerdefinierte Skalarwertfunktionen können deterministisch oder nicht deterministisch sein. Eine deterministische Funktion gibt immer dieselben Ergebnisse zurück, wenn sie mit einem bestimmten Satz an Eingabeparametern aufgerufen wird. Eine nicht deterministische Funktion kann unterschiedliche Ergebnisse zurückgeben, wenn sie mit einem bestimmten Satz an Eingabeparametern aufgerufen wird.

Tabellenwertparameter

Tabellenwertparameter (Table Valued Parameters, TVPs), benutzerdefinierte Tabellentypen, die an eine Prozedur oder Funktion übergeben werden, bieten eine effiziente Methode zum Übergeben mehrerer Datenzeilen an den Server. TVPs bieten ähnliche Funktionen wie Parameterarrays, bieten jedoch eine größere Flexibilität und eine engere Integration in Transact-SQL. Außerdem verfügen sie auch über ein besseres Leistungspotenzial. TVPs helfen auch, die Anzahl von Roundtrips zum Server zu reduzieren. Anstatt mehrere Anforderungen an den Server zu senden, z. B. mit einer Liste von skalaren Parametern, können Daten als TVP an den Server gesendet werden. Ein benutzerdefinierter Tabellentyp kann nicht als Tabellenwertparameter an eine verwaltete gespeicherte Prozedur oder Funktion übergeben werden, die im SQL Server-Prozess ausgeführt wird. Weitere Informationen zu TVPs finden Sie unter Verwenden von Table-Valued-Parametern (Datenbank-Engine).

Beispiel für eine CLR-Skalarwertfunktion

Es folgt eine einfache Skalarwertfunktion, die auf Daten zugreift und einen ganzzahligen Wert zurückgibt:

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  

Die erste Codezeile verweist auf Microsoft.SqlServer.Server für den Zugriff auf Attribute und System.Data.SqlClient für den Zugriff auf den ADO.NET Namespace. (Dieser Namespace enthält SqlClient, den .NET Framework-Datenanbieter für SQL Server.)

Als Nächstes empfängt die Funktion das benutzerdefinierte SqlFunction-Attribut , das sich im Microsoft.SqlServer.Server-Namespace befindet. Das benutzerdefinierte Attribut gibt an, ob die benutzerdefinierte Funktion (UDF) den prozessinternen Anbieter verwendet, um Daten im Server zu lesen. SQL Server erlaubt udFs nicht, Daten zu aktualisieren, einzufügen oder zu löschen. SQL Server können die Ausführung einer UDF optimieren, die nicht den prozessinternen Anbieter verwendet. Dies wird durch Festlegen von DataAccessKind auf DataAccessKind.None angegeben. Die Zielmethode in der nächsten Zeile ist eine öffentliche statische Methode (shared in Visual Basic .NET).

Die SqlContext-Klasse, die sich im Microsoft.SqlServer.Server-Namespace befindet, kann dann auf ein SqlCommand-Objekt mit einer Verbindung mit dem bereits eingerichteten SQL Server instance zugreifen. Der aktuelle Transaktionskontext wird hier zwar nicht verwendet, ist aber auch über die Anwendungsprogrammierschnittstelle ( API) von System.Transactions verfügbar.

Die meisten Codezeilen im Funktionstext sollten Entwicklern vertraut sein, die Clientanwendungen geschrieben haben, die die Typen im System.Data.SqlClient-Namespace verwenden.

[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  

Der entsprechende Befehlstext wird durch Initialisieren des SqlCommand-Objekts angegeben. Im vorherigen Beispiel wird die Anzahl der Zeilen in der SalesOrderHeader-Tabelle gezählt. Als Nächstes wird die ExecuteScalar-Methode des cmd-Objekts aufgerufen. Dadurch wird basierend auf der Abfrage ein Wert vom Typ int zurückgegeben. Abschließend wird die Anzahl der Bestellungen an den Aufrufer zurückgegeben.

Wenn dieser Code in einer Datei namens FirstUdf.cs gespeichert wird, kann er wie folgt als Assembly kompiliert werden:

[C#]

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

[Visual Basic]

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

Es folgen die Transact-SQL-Abfrage und ein Beispielaufruf zum Registrieren der Assembly und UDF:

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

Beachten Sie, dass der Funktionsname, wie er in Transact-SQL verfügbar gemacht wird, nicht mit dem Namen der öffentlichen statischen Zielmethode übereinstimmen muss.

Weitere Informationen

Zuordnen von CLR-Parameterdaten
Übersicht über benutzerdefinierte Attribute der CLR-Integration
Benutzerdefinierte Funktionen
Data Access from CLR Database Objects