Если реализация требуемого функционала в хранимых процедурах с помощью «обычного» 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 запрос на добавление.
1 2 3 |
SqlCommand com = conn.CreateCommand(); com.CommandText = @"INSERT INTO dbo.Data VALUES (@mail)"; com.Parameters.Add("@mail", s); |
После этого выполняем сформированный запрос.
1 2 |
conn.Open(); SqlContext.Pipe.ExecuteAndSend(com); |
Полный исходный код класса и процедуры представлен ниже.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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.
1 2 3 4 5 |
CREATE PROCEDURE AddEmail @EmailString nvarchar(50) AS EXTERNAL NAME [Database1].[StoredProcedures].AddEmail GO |
После загрузки сборки и импорта хранимой процедуры можно проверить её в действие, добавив в таблицу пару вымышленных email адресов.
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, требуется более тщательная проработка проекта. В особенности, физической структуры и её возможных изменений в ходе его развития и модернизации.
Добавить комментарий