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

Если реализация требуемого функционала в хранимых процедурах с помощью «обычного» Transact SQL невозможна или не эффективна, её можно выполнить с помощью SQLCLR.

Для этого в SQLCLR сборке необходимо создать обычный класс C#, а в нём открытый статический метод типа void, который помечен атрибутом [Microsoft.SqlServer.Server.SqlProcedure].

Внутри этого метода нужно подключиться к базе данных, в которую загружена сборка, и выполнить необходимые действия с данными.

Хранимую процедуру SQLCLR можно условно разделить на несколько обязательных частей:

Создание подключения к базе данных;
Формирование SQL запроса;
Выполнение запроса.

Пункты 2 и 3 могут выполнять неоднократно, если требуется предварительно получить данные из одного или нескольких дополнительных источников и выполнить их дополнительную обработку.

Для наглядности рассмотрим простейший пример хранимой процедуры, которая добавляет в таблицу строки, содержащие email адреса (будем считать, что проверка правильности адреса на момент его передачи в процедуру уже выполнена).

Чтобы упростить процесс разработки можно создать «заготовку» хранимой процедуры воспользовавшись стандартным диалоговым окном Visual Studio для добавления нового элемента в проект (пункт «Хранимая процедура CSQL CLR C#» в разделе «SQL CLR C#»).

Далее внутри заготовки создаём подключение к базе данных, в которую будет загружена сборка.

[code lang=»c-sharp»]using (SqlConnection conn = new SqlConnection(«context connection=true»))[/code]

Важно отметить, что подключение не привязывается к конкретному имени базы данных. Однако создающееся впечатление «независимости» на самом деле ложное (почему, пояснено в конце статьи).

Формируем SQL запрос на добавление.

SqlCommand com = conn.CreateCommand();
com.CommandText = @"INSERT INTO dbo.Data VALUES (@mail)";
com.Parameters.Add("@mail", s);

SqlCommand com = conn.CreateCommand();

com.CommandText = @"INSERT INTO dbo.Data VALUES (@mail)";

com.Parameters.Add("@mail", s);

После этого выполняем сформированный запрос.

conn.Open();
SqlContext.Pipe.ExecuteAndSend(com);

1 2	conn.Open(); SqlContext.Pipe.ExecuteAndSend(com);

Полный исходный код класса и процедуры представлен ниже.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
     [Microsoft.SqlServer.Server.SqlProcedure]
     public static void SqlStoredProcedure1(SqlString s)
     {
         using (SqlConnection conn = new SqlConnection("context connection=true"))
         {
             SqlCommand com = conn.CreateCommand();
             com.CommandText = @"INSERT INTO dbo.Data VALUES (@mail)";
            com.Parameters.Add("@mail", s);
            conn.Open();
            SqlContext.Pipe.ExecuteAndSend(com);
        }
    }
}

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void SqlStoredProcedure1(SqlString s)

{

using (SqlConnection conn = new SqlConnection("context connection=true"))

{

SqlCommand com = conn.CreateCommand();

com.CommandText = @"INSERT INTO dbo.Data VALUES (@mail)";

com.Parameters.Add("@mail", s);

conn.Open();

SqlContext.Pipe.ExecuteAndSend(com);

}

Однако на этом процесс разработки хранимой процедуры ещё не закончен.

Необходимо загрузить сборку в базу данных (как это сделать, было подробно описано в статье «Расширение возможностей баз данных Microsoft SQL Server c помощью функций определяемых пользователем. Часть 2. Практическое применение в базе данных»). А, после её загрузки, импортировать из неё хранимую процедуру, чтобы она была доступна для использования средствами Transact-SQL.

CREATE PROCEDURE AddEmail
@EmailString nvarchar(50)
AS
EXTERNAL NAME [Database1].[StoredProcedures].AddEmail
GO

CREATE PROCEDURE AddEmail

@EmailString nvarchar(50)

EXTERNAL NAME [Database1].[StoredProcedures].AddEmail

После загрузки сборки и импорта хранимой процедуры можно проверить её в действие, добавив в таблицу пару вымышленных email адресов.

exec AddEmail 'test@example.com';
exec AddEmail 'test2@example.com';

1 2	exec AddEmail 'test@example.com'; exec AddEmail 'test2@example.com';

Результат показан на скриншоте ниже.

Как видно, создание и применение хранимых процедур SQLCLR мало отличается от разработки и использования функций, определяемых пользователем (подробнее в статьях «Расширение возможностей баз данных Microsoft SQL Server c помощью функций определяемых пользователем» часть 1 и часть 2).

Однако хранимые процедуры SQLCLR более тесно связаны с SQL и, следовательно, физической структурой базы данных. Поэтому работа с ними очень сильно напоминает привычное взаимодействие с базами данных MS SQL Server посредством ADO.NET и значительно проще для понимания, по сравнению с другими SQLCLR.

В тоже время столь тесная связь со структурой базы данных имеет и свои негативные последствия.

Сборки, в которых применяются хранимые процедуры SQLCLR, зачастую менее универсальны. При несоответствующей структуре базы данных, как минимум, часть функционала может оказаться неработоспособной.

Таким образом, если в проектируемой сборке или безе данных планируется использовать хранимые процедуры SQLCLR, требуется более тщательная проработка проекта. В особенности, физической структуры и её возможных изменений в ходе его развития и модернизации.

Добавить комментарий Отменить ответ