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

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

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

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

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

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

Пункты 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 запрос на добавление.

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

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

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

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

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

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

Результат выполнения процедуры

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

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

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

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

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

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *