Udostępnij za pośrednictwem

  • Artykuł

Środowisko CLR przechowywanych procedur

Procedury przechowywane są procedur, które nie mogą być używane w wyrażeniach wartość skalarna.W przeciwieństwie do funkcji wartość skalarna one może zwrócić wyniki tabelarycznych i wiadomości do klient, wywołać języka definicja danych (DDL) i instrukcji języka (DML) manipulacji na danych i zwracają parametry wyjściowe.Aby uzyskać informacje na temat zalet integracja środowiska CLR i wybór między kod zarządzany i Transact-SQL, zobacz Omówienie integracja CLR.

Wymagania dotyczące środowiska CLR przechowywanych procedur

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 albo być zadeklarowana jako nieważny lub zwraca wartość całkowitą.Jeżeli zwraca wartość całkowitą, liczba całkowita, zwracana jest traktowana jako kod powrotny z procedury.Na przykład:

EXECUTE @return\_status = procedure_name

Zmienna @ return_status będzie zawierać wartość zwracana przez metoda.Jeśli metoda zadeklarowano void, kod zwrotny jest równa 0.

Jeśli metoda ma parametry, liczba parametrów w .NET Framework Implementacja powinna być taka sama jak liczba parametrów używanych w Transact-SQL deklaracja procedura przechowywana.

Parametry przekazane w celu składowana procedura CLR może być macierzystego SQL Server typy, dla których w kodzie zarządzanym równorzędnym. Dla Transact-SQL Składnia, aby utworzyć procedury, należy określić te typy z macierzystego najbardziej odpowiedni SQL Server jest to odpowiednik typu. Aby uzyskać więcej informacji na temat konwersji typu zobacz Mapowanie danych parametru CLR.

Tabela Valued parametry

Wycenione tabela parametrów (TVPs), typy zdefiniowane przez użytkownika tabela, które są przekazywane do procedury lub funkcja, zapewnia skuteczny sposób na przekazywanie wielu wierszy danych na serwerze.TVPs funkcjonalność podobnymi do tablic parametrów, ale oferują większą elastyczność i ściślejszej integracja z Transact-SQL. Zapewniają także możliwości, aby zapewnić lepszą wydajność.TVPs pomóc w ograniczeniu liczby round podróży do serwera.Zamiast wysyłać wiele żądań do serwera, na przykład z listą parametrów wartość skalarna, dane mogą być wysyłane do serwera jako TVP.Typ zdefiniowany przez użytkownika tabela nie mogą być przekazywane jako Wycenione tabela parametr, zwracane z zarządzanych procedura przechowywana lub funkcja w SQL Server proces. Aby uzyskać więcej informacji na temat TVPs zobacz Tabela Valued parametry (aparat bazy danych).

Zwracania wyniki z CLR przechowywanych procedur

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

Parametry Wyjście i CLR przechowywanych procedur

W z Transact-SQL procedury przechowywane mogą być zwracane informacje z .NET Framework procedury przechowywane przy użyciu parametrów OUTPUT. The Transact-SQL DML syntax used for creating .NET Framework stored procedures is the same as that used for creating stored procedures written in Transact-SQL.Odpowiadającego mu parametru w kodzie implementacji w .NET Framework Klasa parametr przekazywany przez odwołanie jako argument powinien być używany. Należy zauważyć, że program Visual Basic nie obsługuje parametrów wyjściowych w taki sam sposób, w jaki program Visual C# jest.Należy określić parametr w odniesieniu i zastosować <Out()> atrybut do reprezentowania parametru WYJŚCIOWEGO, zgodnie z następujących czynności:

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

Poniżej przedstawiono procedura przechowywana zwracania informacji za pomocą parametru WYJŚCIOWEGO:

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ący powyższe składowana procedura CLR wbudowane i utworzone na serwerze, następujące Transact-SQL Służy do tworzenia procedury w bazie danych i określa sum jako parametru WYJŚCIOWEGO.

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

Należy zauważyć, że sum jest zadeklarowany jako int Typ danych programu SQL Server oraz że value określony jako parametr określone w procedurze CLR przechowywane SqlInt32 Typ danych środowiska CLR. When a calling program executes the CLR stored procedure, SQL Server automatically converts the SqlInt32 CLR data type to an intSQL Server data type.Aby uzyskać więcej informacji na temat których CLR typów danych może i nie mogą być konwertowane zobacz Mapowanie danych parametru CLR.

Zwracanie wyniki tabelarycznych i wiadomości

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

Oto kilka przeciążenia z SqlPipe.Send Metoda, włączając w to taki, który wysyła SqlDataReader a druga po prostu wysyła ciąg tekstowy.

Zwracanie wiadomości

Użycie SqlPipe.Send(string) do wysyłania komunikatów do aplikacji klient. Tekst komunikatu jest ograniczony do 8 000 znaków.Jeśli wiadomość przekracza 8000 znaków, zostaną obcięte.

Zwracanie wyniki tabelaryczny

Aby wysłać wyniki kwerendy bezpośrednio do klient, należy użyć jednej z przeciążenia z Execute Metoda SqlPipe obiekt. Jest to najbardziej efektywny sposób na zwrócenie wyniki do klient, ponieważ dane jest przenoszona 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 poprzednio wykonywane za pośrednictwem dostawca w procesie (lub preprocesorem dane przy użyciu niestandardowych wdrożenia SqlDataReader), za pomocą przeciążenie z Send Metoda, która zajmuje SqlDataReader. Ta metoda jest nieco wolniej niż metoda bezpośrednia opisano wcześniej, ale zapewnia większą elastyczność do manipulowania danymi, zanim zostaną wysłane 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 wysyłać je przy użyciu 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

Poniżej przedstawiono przykład wysyłania tabelarycznych wynik i komunikatów 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 za pomocą tabeli wyników SqlDataReader.

Należy zauważyć, że te przykłady ilustrujące wyłącznie do celów.Funkcje środowiska CLR są bardziej odpowiednie niż proste Transact-SQL instrukcje dla aplikacji wymagających dużej ilości obliczeń. Prawie równoważne Transact-SQL procedura przechowywana, aby w poprzednim przykładzie jest:

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

Uwaga

Wiadomości i zestawy wyników są pobierane inaczej w aplikacji klient.Na przykład SQL Server Management Studio zestawy wyników są wyświetlane w Wyniki widoku, a wiadomości są wyświetlane w Wiadomości okienko.

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 w powyższym kodzie języka Visual Basic jest zapisywane w pliku MyFirstUdp.vb i skompilowany z:

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

Uwaga

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

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

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

Uwaga

Zaczyna się od SQL Server 2005, na SQL ServerBaza danych o poziom zgodności „ 80"nie można tworzyć zarządzane typów zdefiniowanych przez użytkownika, procedura przechowywana, funkcje, zagregowanych lub wyzwalaczy. Aby korzystać z tych CLR funkcje integracja SQL Server, należy użyć sp_dbcmptlevel przechowywane procedury, aby ustawić poziom zgodności bazy danych "100".