Пользовательские типы данных в Microsoft SQL Server

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

В качестве примера создадим тип данных для хранения email адреса.

Сразу отметим, что для решения задачи хранения в базе данных email адресов вполне достаточно и «обычных» типов данных. В тоже время, в силу своей простоты, эта задача позволяет лучше понять и более наглядно продемонстрировать архитектуру и работу пользовательских типов данных в SQL Server.

Для этого добавим в проект базы данных SQL Server пользовательский тип как показано на скриншоте ниже.

Добавление типа данных

После этого Visual Studio создаст шаблон пользовательского типа данных, с которым мы будем в дальнейшем работать.

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

Из всего того, что создаётся в рамках стандартного шаблона, обязательны только закрытый член _null, свойства Null и IsNull, а также методы Parse и ToString. Остальное можно спокойно удалить.

Null, IsNull и _null, служат для корректной реализации интерфейса INullable, который необходим на случай, если поле с данным конкретным типом данных может быть не заполнено (имеет значение NULL).

Метод Parse предназначен для преобразования данных из SQL запроса в данные пользовательского типа и выполнения необходимых проверок.

В частности, так как email адрес имеет строгий формат, необходимо ввести проверку его корректности в методе Parse.

Метод ToString предназначен для преобразования пользовательского типа данных в строку.

По умолчанию, пользовательский тип помечен атрибутом для «собственной» (Native) сериализации, но этот вид сериализации поддерживает только следующие типы данных: bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble, SqlMoney, SqlBoolean.

Так как email будет храниться в формате string, необходимо сразу изменить вид сериализации с «собственной» на «определяемую пользователем» (UserDefined). Например:

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

Ниже приведён код уже готового пользовательского типа данных для хранения email адреса.

 

После того как тип данных создан необходимо загрузить сборку в базу данных (процесс загрузки подробно описан в одной из предыдущих статей) и импортировать созданный тип данных с помощью следующего запроса:

AssemblyName – имя сборки.

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

Пусть таблица состоит из двух полей (int и Email)

Запрос на добавление данных будет выглядеть примерно следующим образом:

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

Результат заполнения таблицы

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

Пользовательский тип данных в двоичном формате

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

Как ни странно, но проще всего эта проблема решается именно путём обычного приведения типов.

Если такой запрос вызовет ошибку.

То запрос с приведением типов отработает полностью корректно.

Также можно добавить в пользовательский тип специальное свойство, которое позволяет напрямую прочитать и модифицировать хранимые данные. В данном случае это свойство просто читает и записывает (с предварительной проверкой) закрытый член _email, в котором хранится email адрес.

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

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

Результат удаления записи

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

Строго регламентируются только те члены, которые являются минимально необходимыми для реализации пользовательского типа (см. выше). Остальные ограничены только тем, что открытые члены (свойства и методы) обязательно должны работать с типами данных SQL Server (имеют в названиях характерный префикс Sql).

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

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

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