Artikel
09/24/2010

CLR-gespeicherte 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 über die Vorteile der CLR-Integration und das Auswählen zwischen verwaltetem Code und Transact-SQL finden Sie unter Übersicht über die CLR-Integration.

Anforderungen für gespeicherte CLR-Prozeduren

In CLR (Common Language Runtime) werden gespeicherte Prozeduren als statische öffentliche Methoden einer 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 verwendet, sollte die Anzahl der Parameter in der .NET Framework-Implementierung mit der Anzahl der Parameter in der Transact-SQL-Deklaration der gespeicherten Prozedur übereinstimmen.

Bei den an eine gespeicherte CLR-Prozedur weitergegebenen Parametern kann es sich um einen beliebigen der systemeigenen SQL Server-Typen handeln, die über eine Entsprechung im verwalteten Code verfügen. Damit die Transact-SQL-Syntax die Prozedur erstellen kann, sollten diese Typen mit dem am besten passenden systemeigenen SQL Server-Typ 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 verfügen über eine ähnliche Funktionalität wie Parameterarrays, bieten aber größere Flexibilität und engere Integration mit Transact-SQL. Sie besitzen auch ein besseres Leistungspotenzial. TVPs helfen auch, die Anzahl von Roundtrips zum Server zu reduzieren. Anstatt mehrere Anfragen 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, oder von einer solchen Prozedur oder Funktion zurückgegeben werden. Weitere Informationen zu TVPs finden Sie unter Tabellenwertparameter (Datenbankmodul).

Zurückgeben von Ergebnissen von gespeicherten CLR-Prozeduren

Informationen werden möglicherweise auf mehrere Weisen in gespeicherten .NET Framework-Prozeduren zurückgegeben. Dies schließt Ausgabeparameter, Tabellenergebnisse und Meldungen ein.

OUTPUT-Parameter und gespeicherte CLR-Prozeduren

Wie bei gespeicherten Transact-SQL-Prozeduren werden Informationen möglicherweise mit OUTPUT-Parametern in gespeicherten .NET Framework-Prozeduren zurückgegeben. Die Transact-SQL-DML-Syntax, die zum Erstellen von gespeicherten .NET Framework-Prozeduren verwendet wird, ist mit der Syntax identisch, die zum Erstellen gespeicherter Prozeduren, die in Transact-SQL geschrieben werden, verwendet werden. Der entsprechende Parameter im Implementierungscode der .NET Framework-Klasse sollte einen als Verweis zu übergebenden Parameter als Argument verwenden. Beachten Sie, dass Visual Basic Ausgabeparameter nicht auf die gleiche Weise unterstützt wie Visual C#. Sie müssen den Parameter als Verweis angeben und das <Out()>-Attribut zur Darstellung eines OUTPUT-Parameters anwenden, wie im Folgenden gezeigt wird:

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);
            }
         }         
      }
   }
}

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

Sobald die Assembly mit der oben genannten gespeicherten CLR-Prozedur auf dem Server erstellt wurde, wird folgende Transact-SQL verwendet, um die Prozedur in der Datenbank zu erstellen. Außerdem wird sum als OUTPUT-Parameter angegeben.

CREATE PROCEDURE PriceSum (@sum int OUTPUT)
AS EXTERNAL NAME TestStoredProc.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 aufrufendes Programm die gespeicherte CLR-Prozedur ausführt, konvertiert SQL Server automatisch den SqlInt32-CLR-Datentyp in einen intSQL Server-Datentyp. Weitere Informationen dazu, welche CLR-Datentypen konvertiert werden können, finden Sie unter Zuordnen 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:

[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

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);
      }
   }
}

[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

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);
   }
}

[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

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);
      }
   }
}

[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

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 berechnungsintensive Anwendungen geeigneter als einfache Transact-SQL-Anweisungen. Eine fast identische gespeicherte Transact-SQL-Prozedur zum vorherigen Beispiel ist:

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

Hinweis
Meldungen und Resultsets werden in der Clientanwendung anders abgerufen. Zum Beispiel werden SQL Server Management Studio-Resultsets in der Ansicht Ergebnis angezeigt, und Meldungen werden im Bereich Meldungen angezeigt.

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

Hinweis
Ab SQL Server 2005 werden Visual C++-Datenbankobjekte (z. B. gespeicherte Prozeduren), die mit /clr:pure kompiliert werden, nicht für die Ausführung unterstützt.

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

Hinweis
Ab SQL Server 2005 können Sie in einer SQL Server-Datenbank mit einem Kompatibilitätsgrad von "80" keine verwalteten benutzerdefinierten Typen, gespeicherten Prozeduren, Funktionen, Aggregate oder Trigger erstellen. Um diese CLR-Integrationsfeatures von SQL Server nutzen zu können, müssen Sie mit der gespeicherten Prozedur sp_dbcmptlevel den Kompatibilitätsgrad der Datenbank auf "100" festlegen.

Ab SQL Server 2005 können Sie in einer SQL Server-Datenbank mit einem Kompatibilitätsgrad von "80" keine verwalteten benutzerdefinierten Typen, gespeicherten Prozeduren, Funktionen, Aggregate oder Trigger erstellen. Um diese CLR-Integrationsfeatures von SQL Server nutzen zu können, müssen Sie mit der gespeicherten Prozedur sp_dbcmptlevel den Kompatibilitätsgrad der Datenbank auf "100" festlegen.