Хранимые процедуры 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.

Параметры, возвращающие табличные значения

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

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

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

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

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

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

Следующий пример представляет хранимую процедуру с входным и выходным параметрами:

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 для создания процедуры в базе данных и задает sum в качестве параметра 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  

Обратите внимание, что sum объявляется как int SQL Server тип данных, а параметр value, определенный в хранимой 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;  

Примечание

Сообщения и результирующие наборы в клиентском приложении получаются по-разному. Например, SQL Server Management Studio результирующие наборы отображаются в представлении Результаты, а сообщения — на панели Сообщения.

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

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

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

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

Примечание

Начиная с SQL Server 2005, скомпилированные с /clr:pure помощью объекты базы данных Visual C++ (например, хранимые процедуры) не поддерживаются для выполнения.

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

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

См. также:

Определяемые пользователем функции среды CLR
Определяемые пользователем типы CLR
Триггеры CLR