Пользовательские типы данных в 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). Например:

[SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)]

1	[SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)]

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

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

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
[Serializable]
[SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)]
public struct Email : INullable, IBinarySerialize
{
   //Закрытый член для хранения email адреса
   private string _email;
   //Закрытый член для
   private bool _null;
   public override string ToString()
   {//Преобразование типа Email в строку
       return _email;
   }
   public bool IsNull
   {
       get
       {
           return _null;
       }
   }
   public static Email Null
   {
       get
       {
           Email h = new Email();
           h._null = true;
           return h;
       }
   }
   public static Email Parse(SqlString s)
   {//Преобразование строки из SQL запроса в тип Email
       if (s.IsNull)
           return Null;
       Email u = new Email();
       string str = s.ToString();
       if (isValidEmail(str))
       {
           u._email = str;
       }
else
{
throw new Exception("Invalid data format");
};
       return u;
   }
   private static bool isValidEmail(string email)
   {
       //Проверка правильности email
       string pattern = "[.\\-_a-z0-9]+@([a-z0-9][\\-a-z0-9]+\\.)+[a-z]{2,6}";
       Match isMatch = Regex.Match(email, pattern, RegexOptions.IgnoreCase);
       return isMatch.Success;
   }
   void IBinarySerialize.Read(System.IO.BinaryReader r)
   {
       this._email = r.ReadString();
   }
   void IBinarySerialize.Write(System.IO.BinaryWriter w)
   {
       w.Write(this._email);
   }
}

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

[Serializable]

[SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)]

public struct Email : INullable, IBinarySerialize

{

//Закрытый член для хранения email адреса

private string _email;

//Закрытый член для

private bool _null;

public override string ToString()

{//Преобразование типа Email в строку

return _email;

}

public bool IsNull

{

get

{

return _null;

}

public static Email Null

{

get

{

Email h = new Email();

h._null = true;

return h;

}

public static Email Parse(SqlString s)

{//Преобразование строки из SQL запроса в тип Email

if (s.IsNull)

return Null;

Email u = new Email();

string str = s.ToString();

if (isValidEmail(str))

{

u._email = str;

}

else

{

throw new Exception("Invalid data format");

};

return u;

}

private static bool isValidEmail(string email)

{

//Проверка правильности email

string pattern = "[.\\-_a-z0-9]+@([a-z0-9][\\-a-z0-9]+\\.)+[a-z]{2,6}";

Match isMatch = Regex.Match(email, pattern, RegexOptions.IgnoreCase);

return isMatch.Success;

}

void IBinarySerialize.Read(System.IO.BinaryReader r)

{

this._email = r.ReadString();

}

void IBinarySerialize.Write(System.IO.BinaryWriter w)

{

w.Write(this._email);

}

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

CREATE TYPE dbo.Email EXTERNAL NAME AssemblyName."Email"

1	CREATE TYPE dbo.Email EXTERNAL NAME AssemblyName."Email"

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

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

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

CREATE TABLE dbo.TestTable(
                id int,
                mail dbo.Email
)

CREATE TABLE dbo.TestTable(

id int,

mail dbo.Email

)

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

INSERT INTO dbo.TestTable (id,mail) VALUES (5,'test1@example.com')

1	INSERT INTO dbo.TestTable (id,mail) VALUES (5,'test1@example.com')

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

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

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

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

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

UPDATE TestTable SET id=3 WHERE mail ='amp@list.ru'

1	UPDATE TestTable SET id=3 WHERE mail ='amp@list.ru'

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

UPDATE TestTable SET id=3 WHERE CAST(mail AS nvarchar(max)) ='amp@list.ru'

1	UPDATE TestTable SET id=3 WHERE CAST(mail AS nvarchar(max)) ='amp@list.ru'

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

public SqlString Value
{
   get
   {
      return _email;
   }
   set
    {
      if (isValid(value.ToString()))
      {
         _email = value.ToString();
      }
      else
     {
        throw new Exception("Invalid data format");
     };
    }
}

public SqlString Value

{

get

{

return _email;

}

set

{

if (isValid(value.ToString()))

{

_email = value.ToString();

}

else

{

throw new Exception("Invalid data format");

};

}

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

DELETE FROM TestTable WHERE mail.Value='test2@example.com'

1	DELETE FROM TestTable WHERE mail.Value='test2@example.com'

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

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

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

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

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