Использование SQL CLR в Microsoft SQL Server 2008

Microsoft SQL Server и язык T -SQL обладают достаточно широким набором команд, параметров и функций, которые позволяют разработчику выполнять практически любые запросы к базам данных. Однако, в некоторых ситуациях данной инфраструктуры оказывается недостаточно для удовлетворения самых изощренных потребностей разработчиков. Здесь на помощь приходит расширение SQL Server под названием SQL CLR.

SQL CLR – это технология, позволяющая значительно увеличить гибкость и расширить возможности разработчиков при работе с базами данных в SQL Server. При помощи SQL CLR возможно писать собственные функции, хранимые процедуры и триггеры на языках программирования C# или Visual Basic, а затем импортировать этот код в среду SQL Server и использовать его при написании запросов на языке T-SQL. Поскольку такой язык как C# предоставляет разработчику гораздо более широкий набор возможностей, чем T-SQL, импортирование C# функций в SQL Server будет отличным решением многих задач, требующих специфичных вычислений.

В данной статье мы рассмотрим пример создания хранимой процедуры на языке C# и процесс ее импортирования в среду SQL CLR.

Итак, допустим, у нас есть следующая таблица и небольшой набор данных в ней:

Напишем хранимую процедуру с запросом для выборки устройств по идентификатору магазина:

   1:  CREATE PROCEDURE dbo.sp_GetDevicesByShopId

   2:      @shopId int

   3:  AS

   4:  BEGIN

   5:      SELECT * FROM Devices WHERE ShopId = @shopId;

   6:  END

Теперь попробуем реализовать тоже, но при помощи SQL CLR. Первым делом необходимо активировать поддержку SQL CLR в экземпляре SQL Server. Выполним следующий запрос:

   1:  EXEC sp_configure 'clr enabled', 1;

   2:  RECONFIGURE;

Если команда выполнилась без ошибок, можем приступать к написанию кода на языке C#. Создадим в Visual Studio библиотеку классов под названием «SQL_CLR_TEST.Procedure». Добавим туда класс с именем «MyProcedure», содержащий один статический метод, как показано далее:

   1:  using System;

   2:  using System.Data;

   3:  using System.Data.SqlTypes;

   4:  using System.Data.SqlClient;

   5:  using Microsoft.SqlServer.Server;

   6:   

   7:  namespace SQL_CLR_TEST.Procedure

   8:  {

   9:      public class MyProcedure

  10:      {

  11:          public static void GetDevicesByShopIdProcedure(SqlInt32 shopId)

  12:          {

  13:              using (SqlConnection connection = new SqlConnection("context connection=true"))

  14:              {

  15:                  SqlCommand command = connection.CreateCommand();

  16:                  command.CommandText = "SELECT * FROM Devices WHERE ShopId = @shopId;";

  17:                  

  18:                  SqlParameter parameter = new SqlParameter("@shopId", SqlDbType.Int);

  19:                  parameter.Value = shopId;

  20:                  command.Parameters.Add(parameter);

  21:   

  22:                  connection.Open();

  23:                  SqlContext.Pipe.ExecuteAndSend(command);

  24:              }

  25:          }

  26:      }

  27:  }

Код выше очень похож на традиционное взаимодействие с базой данных через ADO.NET, за исключением нескольких отличий. Параметры метода могут содержать только типы данных из пространства имен System.Data.SqlTypes. Строка соединения с базой данных не указывается полностью – вместо этого строка "context connection=true" указывает среде выполнения, что необходимо использовать экземпляр SQL Server, в контексте которого, вызвана данная процедура.

Теперь необходимо собрать проект и скопировать ссылку на файловой системе к библиотеке DLL.

Следующий шаг – импортирование библиотеки, написанной на языке C#, в среду SQL Server. Для этого выполним следующий скрипт:

   1:  USE XMLTest

   2:  GO

   3:   

   4:  CREATE ASSEMBLY ClrProcTest

   5:      FROM 'C:\SQL_CLR_TEST.Procedure.dll';

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

Команда выше может завершиться с ошибкой в случае, если вы написали библиотеку на версии .NET Framework, которая не поддерживается SQL Server. Например, SQL Server 2008 сможет импортировать только сборки, версия которых не выше .NET Framework 3.5.

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

   1:  SELECT * FROM sys.assemblies WHERE name = 'ClrProcTest';

Теперь, когда наша сборка находится в SQL Server, попробуем вызвать написанный ранее метод. Напрямую это сделать не получится, поэтому нам понадобится обертка в виде хранимой процедуры на языке T-SQL.

   1:  CREATE PROCEDURE dbo.sp_GetDevicesByShopIdWithCLR

   2:      @shopId int

   3:  AS

   4:      EXTERNAL NAME ClrProcTest."SQL_CLR_TEST.Procedure.MyProcedure". GetDevicesByShopIdProcedure;

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

Нам осталось лишь вызвать созданную нами процедуру-оболочку и увидеть результаты ее работы. Код вызова приведен ниже:

   1:  USE XMLTest

   2:  GO

   3:   

   4:  EXEC dbo.sp_GetDevicesByShopIdWithCLR 2

Результаты будут аналогичны вызову обыкновенной хранимой процедуры:

Приведенный выше пример, является исключительно демонстрационным. В реальной ситуации использовать хранимую процедуру будет во много раз эффективней, чем каждый раз дергать среду CLR. Данный пример показывает образец процесса импортирования кода .NET в среду SQL Server.

Такое решение используется для решения весьма специфичных проблем, которые никак нельзя решить средствами T-SQL. Как правило, такие задачи попадаются редко, так как платить за такое решение приходится дорого – существенным снижением производительности запросов с участием CLR кода.

Также стоит отметить, что данный режим работы (внедрение CLR кода) не поддерживается в Windows Azure SQL (по крайней мере, на текущий момент). Это может создать некоторые трудности при миграции в облака баз данных, в которых применены решения на основе SQL CLR.

В данной статье приведен пример инъекции кода CLR в среду SQL Server. Описаны достоинства и недостатки таких решений, так что выбор остается за вами. Могу лишь добавить, что я бы рекомендовал использовать такой подход только в крайнем случае.