Хранимые процедуры CLR

Хранимые процедуры — это подпрограммы, которые нельзя использовать в скалярных выражениях. В отличие от скалярных функций, они могут возвращать табличные результаты и сообщения клиенту, вызывать язык определения данных (DDL) и операторы языка обработки данных (DML) и возвращать выходные параметры. Сведения о преимуществах интеграции СРЕДЫ CLR и выборе между управляемым кодом и Transact-SQL см. в разделе "Обзор интеграции СРЕДЫ CLR".

Требования к хранимым процедурам CLR

В среде CLR хранимые процедуры реализуются как общедоступные статические методы класса в сборке Microsoft.NET Framework. Статический метод может быть объявлен как void или возвращать целочисленное значение. Если он возвращает целочисленное значение, возвращаемое целое число обрабатывается как возвращаемый код из процедуры. Рассмотрим пример.

EXECUTE @return_status = procedure_name

Переменная @return_status будет содержать значение, возвращаемое методом. Если метод объявлен void, возвращается значение 0.

Если метод принимает параметры, число параметров в реализации .NET Framework должно совпадать с количеством параметров, используемых в объявлении Transact-SQL хранимой процедуры.

Параметры, передаваемые хранимой процедуре CLR, могут быть любым из собственных типов SQL Server, которые имеют эквивалент в управляемом коде. Чтобы синтаксис Transact-SQL для создания процедуры, эти типы должны быть указаны с наиболее подходящим эквивалентом собственного типа SQL Server. Дополнительные сведения о преобразованиях типов см. в разделе "Сопоставление данных параметров CLR".

параметры Table-Valued

Возвращающие табличное значение параметры — это определяемые пользователем табличные типы, которые передаются в процедуру или функцию, предоставляя эффективный способ передачи на сервер нескольких строк данных. TvPs предоставляют аналогичные функциональные возможности для массивов параметров, но обеспечивают большую гибкость и более тесную интеграцию с Transact-SQL. Они также обеспечивают возможность повышения производительности. Кроме того, возвращающие табличное значение параметры способствуют сокращению циклов приема-передачи данных с сервера и на сервер. Вместо того чтобы отправлять на сервер несколько запросов (как в случае списка скалярных параметров), данные можно отправить в виде возвращающего табличное значение параметра. Определяемый пользователем тип таблицы не может передаваться в качестве табличного параметра или возвращаться из управляемой хранимой процедуры или функции, выполняемой в процессе SQL Server. Дополнительные сведения о tvPs см. в разделе "Использование параметров Table-Valued (ядро СУБД)".

Возврат результатов из хранимых процедур CLR

Сведения могут быть возвращены из хранимых процедур .NET Framework несколькими способами. Сюда входят выходные параметры, табличные результаты и сообщения.

Выходные параметры и хранимые процедуры CLR

Как и в случае с хранимыми процедурами Transact-SQL, данные могут быть возвращены из хранимых процедур .NET Framework с помощью параметров OUTPUT. Синтаксис DML Transact-SQL, используемый для создания хранимых процедур .NET Framework, совпадает с тем, что используется для создания хранимых процедур, написанных в Transact-SQL. Соответствующий параметр в коде реализации в классе .NET Framework должен использовать параметр сквозной ссылки в качестве аргумента. Обратите внимание, что Visual Basic не поддерживает выходные параметры таким же образом, как и C#. Необходимо указать параметр по ссылке и применить <атрибут Out()> для представления параметра OUTPUT, как показано в следующем:

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

Ниже показана хранимая процедура, возвращающая сведения с помощью параметра OUTPUT:

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  

После создания и создания на сервере сборки, содержащей указанную выше хранимую процедуру CLR, следующая Transact-SQL используется для создания процедуры в базе данных и указывает сумму в качестве параметра OUTPUT.

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  

Обратите внимание, что сумма объявлена int как тип данных SQL Server, и что параметр значения , определенный в хранимой SqlInt32 процедуре CLR, указывается как тип данных CLR. При выполнении хранимой процедуры СРЕДЫ CLR SQL Server автоматически преобразует SqlInt32 тип данных CLR в intтип данных SQL Server. Дополнительные сведения о том, какие типы данных CLR могут быть преобразованы и не могут быть преобразованы, см. в разделе "Сопоставление данных параметров CLR".

Возврат табличных результатов и сообщений

Возврат табличных результатов и сообщений клиенту выполняется через SqlPipe объект, который получается с помощью Pipe свойства SqlContext класса. Объект SqlPipe имеет Send метод. Вызывая Send метод, можно передавать данные через канал в вызывающее приложение.

Это несколько перегрузок SqlPipe.Send метода, включая одну, которая отправляет и другую, которая просто отправляет SqlDataReader текстовую строку.

Возврат сообщений

Используется SqlPipe.Send(string) для отправки сообщений в клиентское приложение. Текст сообщения ограничен 8000 символами. Если сообщение превышает 8000 символов, оно будет усечено.

Возврат табличных результатов

Чтобы отправить результаты запроса непосредственно клиенту, используйте одну из перегрузок Execute метода в объекте SqlPipe . Это наиболее эффективный способ возврата результатов клиенту, так как данные передаются в сетевые буферы без копирования в управляемую память. Рассмотрим пример.

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  

Чтобы отправить результаты запроса, который был выполнен ранее через поставщика внутрипроцессного процесса (или предварительно обработать данные с помощью пользовательской реализацииSqlDataReader), используйте перегрузку Send метода, который принимает .SqlDataReader Этот метод немного медленнее, чем описанный ранее прямой метод, но обеспечивает большую гибкость для управления данными перед отправкой клиенту.

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  

Чтобы создать динамический результирующий набор, заполните его и отправьте его клиенту, вы можете создать записи из текущего подключения и отправить их с помощью 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);  
   }  
}  

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   

Ниже приведен пример отправки табличного результата и сообщения 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   

Send Первый отправляет клиенту сообщение, а второй отправляет табличный результат с помощьюSqlDataReader.

Обратите внимание, что эти примеры предназначены только для иллюстрирующих целей. Функции CLR более подходящи, чем простые операторы Transact-SQL для приложений с большим объемом вычислений. Практически эквивалентно Transact-SQL хранимой процедуре предыдущего примера:

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

Если приведенный выше код Visual C# сохраняется в файле MyFirstUdp.cs и компилируется следующим образом:

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

Или, если приведенный выше код Visual Basic сохраняется в файле MyFirstUdp.vb и компилируется следующим образом:

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

Полученную сборку можно зарегистрировать и вызвать точку входа со следующим DDL:

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

См. также

Функции CLR User-Defined
Типы User-Defined среды CLR
Триггеры СРЕДЫ CLR