Procedury przechowywane CLR

Procedury przechowywane są procedury, których nie można używać w wyrażeniach wartość skalarna.W odróżnieniu od funkcji wartość skalarna one może zwracać wyniki tabelarycznych i wiadomości do klient, należy wywołać języka definicja danych (DDL) i język edycji danych (DML) sprawozdania i zwracają parametry wyjściowe.Informacje dotyczące zalet integracja CLR i wybór między kod zarządzany i Transact-SQL, zobacz Omówienie integracji CLR.

Wymagania dotyczące CLR procedur przechowywanych.

In the common language runtime (CLR), stored procedures are implemented as public static methods on a class in a Microsoft .NET Framework assembly.Statyczna metoda może być zadeklarowana jako nieważny lub zwraca wartość całkowitą.Jeżeli zwraca wartość całkowitą, zwrócona liczba całkowita jest traktowana jako kod zwrotny z procedury.Na przykład:

EXECUTE @return_status = procedure_name

Zmienna @ return_status będzie zawierać wartość zwracana przez metoda.Jeśli metoda jest uznana za nieważną, kod zwrotny jest 0.

Jeśli metoda pobiera parametry, liczba parametrów w .NET Framework wykonania powinna być taka sama jak liczba parametrów używanych w Transact-SQL deklaracja procedura składowana.

Parametry przekazywane do procedura składowana CLR może być dowolny macierzystego SQL Server typów, które mają odpowiedniki w kod zarządzany.Dla Transact-SQL składni, aby utworzyć procedury, te typy powinny być określone z macierzystego najbardziej odpowiednie SQL Server typu równoważnej.Aby uzyskać więcej informacji o konwersje typów, zobacz Mapowanie danych parametru CLR.

Parametry oródwierszową

Oródwierszową parametry (TVPs), typy zdefiniowane przez użytkownika tabela, które są przekazywane do procedury lub funkcja, zapewniają skuteczny sposób przekazywania wielu wierszy danych do serwera.TVPs oferują podobne funkcje tablic parametrów, ale oferują większą elastyczność i ściślejszej integracja z Transact-SQL.Zapewniają także potencjału dla zwiększenia wydajności.TVPs także zmniejszyć liczbę przesłania do serwera.Zamiast wysyłać wiele żądań do serwera, takie jak lista parametrów wartość skalarna danych mogą być wysyłane do serwera jako TVP.Typ zdefiniowany przez użytkownika tabela nie przekazane jako parametr oródwierszową do zwracane z zarządzanych procedura składowana lub funkcja wykonywania w SQL Server procesu.Aby uzyskać więcej informacji o TVPs, zobacz Parametry oródwierszową (aparat bazy danych).

Zwracanie wyników z CLR procedur przechowywanych

Informacje mogą być zwracane z .NET Framework przechowywane procedury na kilka sposobów.Obejmuje to parametry wyjściowe, tabelarycznych wyniki i wiadomości.

Parametry wyjściowe i CLR procedur przechowywanych

Tak jak w przypadku Transact-SQL procedur przechowywanych informacji mogą być zwracane z .NET Framework przechowywane procedury za pomocą parametrów wyjściowych. Transact-SQLDML Składnia służąca do tworzenia .NET Framework procedur przechowywanych jest taka sama, jak dla tworzenia procedur przechowywanych w Transact-SQL.Odpowiedniego parametru w kodzie wykonania w .NET Framework klasy należy użyć parametru przekazywany przez odwołanie jako argumentu.Należy zauważyć, że Visual Basic nie obsługuje parametrów wyjściowych w taki sam sposób, że Visual C#.Należy określić parametr odniesienie i zastosować <Out()> atrybut do reprezentowania parametru WYJŚCIOWEGO, jak w następujących:

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

Poniżej przedstawiono procedura składowana zwróceniem informacji poprzez parametr wyjściowy:

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

Raz wirtualny plik dziennika zawierającego powyższego procedura składowana CLR zbudowany i utworzone na serwerze następujące Transact-SQL jest używany do tworzenia procedury w bazie danych i określa sum jako parametr wyjściowy.

CREATE PROCEDURE PriceSum (@sum int OUTPUT)
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum

Należy zauważyć, że sum jest zadeklarowana jako int Typ danych programu SQL Server, a value parametru zdefiniowanego w procedura składowana CLR jest określony jako SqlInt32 danych typu CLR.When a calling program executes the CLR stored procedure, SQL Server automatically converts the SqlInt32 CLR data type to an int SQL Server data type.Aby uzyskać więcej informacji o które CLR może typów danych i nie mogą być konwertowane, zobacz Mapowanie danych parametru CLR.

Zwracanie tabelarycznych wyników i wiadomości

Zwracanie tabelarycznych wyniki i wiadomości do klient jest wykonywana za pomocą SqlPipe obiekt, który jest uzyskiwany przy użyciu Pipe Właściwość SqlContext klasySqlPipe Obiekt ma Send metoda.Wywołując Send metoda mogą przesyłać dane poprzez potoku do aplikacji wywołującej.

Są to kilka przeciążenia SqlPipe.Send metoda, łącznie z jedną, która wysyła SqlDataReader i innej, po prostu wysyła ciąg tekstowy.

Zwracania wiadomości

Użyj SqlPipe.Send(string) do wysyłania komunikatów do aplikacji klient.Tekst wiadomości jest maksymalnie 8000 znaków.Wiadomość przekroczy 8000 znaków, zostanie obcięty.

Zwracanie tabelarycznych wyników

Aby wysłać wyniki kwerendy bezpośrednio do klient, użyj jednej z overloads z Execute metoda na SqlPipe obiektu.Jest to najbardziej skuteczny sposób zwracanie wyniki do klient, ponieważ dane są przesyłane do buforów sieciowych bez kopiowane do zarządzanych pamięci.Na przykład:

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

[Język 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

Aby wysłać wyniki kwerendy, która została wcześniej wykonywane za pośrednictwem dostawca w trakcie (lub do wstępnego przetwarzania danych przy użyciu niestandardowej implementacji SqlDataReader), użyj przeciążenie z Send metoda, która ma SqlDataReader.Ta metoda jest nieco wolniej niż metoda bezpośrednia opisano wcześniej, ale zapewnia większą elastyczność do manipulowania danymi, przed wysłaniem do klient.

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

[Język 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

Aby utworzyć zestaw wyników dynamiczne, go wypełnić i wysłać go do klient, można utworzyć rekordów z bieżącego połączenia i wysłać je za pomocą 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);
   }
}

[Język 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 

Oto przykład wysyłania tabelarycznych wyników i wiadomości za pośrednictwem 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);
      }
   }
}

[Język 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 

Pierwszy Send wysyła wiadomość do klient, podczas gdy drugi wysyła tabelarycznych wyników za pomocą SqlDataReader.

Należy zauważyć, że te przykłady są wyłącznie w celach ilustracyjnych.Funkcje CLR są bardziej odpowiednie niż proste Transact-SQL instrukcji dla aplikacji intensywnie wykorzystujących obliczeń.Prawie równoważne Transact-SQL jest procedura składowana z poprzedniego przykładu:

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

Ostrzeżenie

Wiadomości i zestawy wyników są pobierane w sposób klient aplikacji.Dla wystąpienie, SQL Server Management Studio zestawy wyników są wyświetlane w wyniki widok i wiadomości są wyświetlane w wiadomości okienka.

Jeśli powyższe kodu Visual C# jest zapisywane w pliku MyFirstUdp.cs i skompilowany z:

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

Lub Jeśli powyższy kod Visual Basic jest zapisywane w pliku MyFirstUdp.vb i skompilowane przy użyciu:

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

Ostrzeżenie

Począwszy od SQL Server 2005, Visual C++ obiektów bazy danych (takie jak procedury przechowywane) skompilowany z /clr:pure nie są obsługiwane przez wykonanie.

wirtualny plik dziennika wynikowy może zostać zarejestrowana i punkt wejścia, wywoływane z następującego DDL:

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

Ostrzeżenie

Począwszy od SQL Server 2005, na SQL Server bazy danych z poziom zgodności "80" nie można utworzyć typy zarządzane przez użytkownika, procedura składowana, funkcje, agregatów ani wyzwalaczy. Aby skorzystać z tych funkcji integracja CLR z SQL Server, należy użyć sp_dbcmptlevel przechowywane procedury zestaw poziom zgodności bazy danych "100".

Zobacz także

Koncepcje

CLR typów zdefiniowanych przez użytkownika

Wyzwalacze CLR

Opcja poziom zgodności bazy danych

Inne zasoby

CLR funkcje zdefiniowane przez użytkownika