Gespeicherte CLR-Prozeduren

Gespeicherte Prozeduren sind Routinen, die nicht in Skalarausdrücken verwendet werden können. Im Gegensatz zu Skalarfunktionen können sie tabellarische Ergebnisse und Meldungen an den Client zurückgeben, Anweisungen in Datendefinitionssprache (DDL, Data Definition Language) und in Datenbearbeitungssprache (DML, Data Manipulation Language) aufrufen und Ausgabeparameter zurückgeben. 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 gespeicherte CLR-Prozeduren

In der Common Language Runtime (CLR) werden gespeicherte Prozeduren als öffentliche statische Methoden für eine Klasse in einer Microsoft.NET Framework-Assembly implementiert. Die statische Methode kann entweder als ungültig deklariert werden oder gibt einen ganzzahligen Wert zurück. Wenn sie einen ganzzahligen Wert zurückgibt, wird die ganze Zahl von der Prozedur als Rückgabecode behandelt. Beispiel:

EXECUTE @return_status = procedure_name

Die @return_status Variable enthält den von der -Methode zurückgegebenen Wert. Wenn die Methode als ungültig deklariert wird, ist der Rückgabecode 0.

Wenn die Methode Parameter akzeptiert, sollte die Anzahl der Parameter in der .NET Framework-Implementierung mit der Anzahl der Parameter in der Transact-SQL-Deklaration der gespeicherten Prozedur übereinstimmen.

Parameter, die einer gespeicherten CLR-Prozedur übergeben werden, können von einem beliebigen systemeigenen SQL Server-Typ sein, der über eine Entsprechung in verwaltetem Code verfügt. Für die Transact-SQL-Syntax zum Erstellen der Prozedur sollten diese Typen mit der am besten geeigneten systemeigenen SQL Server-Typentsprechung angegeben werden. Weitere Informationen zu Typkonvertierungen finden Sie unter Zuordnen von CLR-Parameterdaten.

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).

Zurückgeben von Ergebnissen von gespeicherten CLR-Prozeduren

Informationen können von .NET Framework gespeicherten Prozeduren auf verschiedene Arten zurückgegeben werden. Dies schließt Ausgabeparameter, Tabellenergebnisse und Meldungen ein.

OUTPUT-Parameter und gespeicherte CLR-Prozeduren

Wie bei gespeicherten Transact-SQL-Prozeduren können Informationen von .NET Framework gespeicherten Prozeduren mithilfe von OUTPUT-Parametern zurückgegeben werden. Die zum Erstellen von gespeicherten .NET Framework-Prozeduren verwendete Transact-SQL-DML-Syntax ist dieselbe wie die zum Erstellen von in Transact-SQL geschriebenen gespeicherten Prozeduren. Der entsprechende Parameter im Implementierungscode der .NET Framework-Klasse sollte einen als Verweis übergebenen Parameter als Argument verwenden. Beachten Sie, dass Visual Basic Ausgabeparameter nicht auf die gleiche Weise unterstützt wie C#. Sie müssen den Parameter als Verweis angeben und das <Out()> -Attribut anwenden, um einen OUTPUT-Parameter darzustellen, wie im folgenden Beispiel:

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

Das folgende Beispiel zeigt eine gespeicherte Prozedur, die Informationen über einen OUTPUT-Parameter zurückgibt.

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  

Nachdem die Assembly mit der oben genannten gespeicherten CLR-Prozedur erstellt und auf dem Server erstellt wurde, wird die folgende Transact-SQL-Instanz verwendet, um die Prozedur in der Datenbank zu erstellen, und gibt summe als OUTPUT-Parameter an.

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  

Beachten Sie, dass sum als int SQL Server Datentyp deklariert wird und dass der in der gespeicherten CLR-Prozedur definierte value-Parameter als SqlInt32 CLR-Datentyp angegeben wird. Wenn ein aufrufende Programm die gespeicherte CLR-Prozedur ausführt, konvertiert SQL Server den SqlInt32 CLR-Datentyp automatisch in einen intSQL Server Datentyp. Weitere Informationen dazu, welche CLR-Datentypen konvertiert werden können und welche nicht konvertiert werden können, finden Sie unter Zuordnung von CLR-Parameterdaten.

Zurückgeben von Tabellenergebnissen und Meldungen

Das Zurückgeben von tabellarischen Ergebnissen und Meldungen an den Client erfolgt durch das SqlPipe-Objekt, das mithilfe der Pipe-Eigenschaft der SqlContext-Klasse abgerufen wird. Das SqlPipe-Objekt verfügt über eine Send-Methode. Durch das Aufrufen der Send-Methode können Sie Daten durch die Pipe zur aufrufenden Anwendung senden.

Dies sind verschiedene Überladungen der SqlPipe.Send-Methode, darunter eine, die SqlDataReader sendet und eine andere, die einfach eine Textzeichenfolge sendet.

Zurückgeben von Meldungen

Verwenden Sie SqlPipe.Send(string), um Meldungen an die Clientanwendung zu senden. Der Text der Meldung ist auf 8000 Zeichen beschränkt. Wenn die Meldung 8000 Zeichen überschreitet, wird sie abgeschnitten.

Zurückgeben von tabellarischen Ergebnissen

Um die Ergebnisse einer Abfrage direkt an den Client zu senden, verwenden Sie eine Überladung der Execute-Methode des SqlPipe-Objekts. Dies ist die effizienteste Methode zum Zurückgeben von Ergebnissen an den Client, da die Daten zu den Netzwerkpuffern übertragen werden, ohne in den verwalteten Arbeitsspeicher kopiert zu werden. Beispiel:

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  

Um eine zuvor ausgeführte Abfrage über den prozessinternen Anbieter zu senden (oder um die Daten mithilfe einer benutzerdefinierten Implementierung von SqlDataReader vorab zu verarbeiten), verwenden Sie die Überladung der Send-Methode, die SqlDataReader verwendet. Diese Methode ist etwas langsamer als die zuvor beschriebene direkte Methode, bietet aber größere Flexibilität zum Ändern der Daten, bevor sie an den Client gesendet werden.

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  

Um ein dynamisches Resultset zu erstellen, füllen Sie es, und senden Sie es an den Client. Sie können Datensätze aus der aktuellen Verbindung erstellen und sie mithilfe von SqlPipe.Send senden.

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   

Hier ist ein Beispiel für das Senden eines tabellarischen Ergebnisses und einer Meldung durch 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   

Das erste Send sendet eine Meldung an den Client, während das zweite ein tabellarisches Ergebnis mithilfe von SqlDataReader sendet.

Beachten Sie, dass diese Beispiele lediglich zu Illustrationszwecken dienen. CLR-Funktionen sind für rechenintensive Anwendungen besser geeignet als einfache Transact-SQL-Anweisungen. Eine fast gleichwertige gespeicherte Transact-SQL-Prozedur wie im vorherigen Beispiel ist:

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

Wenn der oben erwähnte Visual C#-Code in einer Datei MyFirstUdp.cs gespeichert und mit Folgendem kompiliert wird:

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

Oder wenn der oben erwähnte Visual Basic-Code in einer Datei MyFirstUdp.vb gespeichert und mit Folgendem kompiliert wird:

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

Die resultierende Assembly kann mit folgender DLL registriert und der Einstiegspunkt aufgerufen werden:

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

Weitere Informationen

CLR-benutzerdefinierte Funktionen
Benutzerdefinierte CLR-Typen
CLR-Auslöser