Поделиться через

Создание определяемых пользователем типов с помощью ADO.NET

Область применения:SQL Server

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

В этом разделе показано, как реализовать Point определяемый пользователем тип в виде struct (или Structure в Visual Basic). Point UDT состоит из координат X и Y, реализованных как процедуры свойств.

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

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

Пространство имен Microsoft.SqlServer.Server содержит объекты, необходимые для различных атрибутов UDT, а пространство имен System.Data.SqlTypes содержит классы, представляющие собственные типы данных SQL Server, доступные для сборки. Для правильной работы сборки могут потребоваться другие пространства имен. UDT Point также использует пространство имен System.Text для работы со строками.

Примечание.

Объекты базы данных Visual C++, такие как определяемые пользователем объекты, скомпилированные с /clr:pure, не поддерживаются для выполнения.

Указание атрибутов

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

Требуется Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute. Атрибут Serializable необязателен. Вы также можете указать Microsoft.SqlServer.Server.SqlFacetAttribute для предоставления сведений о типе возврата определяемого пользователем типа. Дополнительные сведения см. в разделе интеграции СРЕДЫ CLR: настраиваемые атрибуты для подпрограмм CLR.

Атрибуты определяемого пользователем точки

Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute задает формат хранилища для Point определяемого пользователем определяемого пользователем Native. IsByteOrdered задано значение true, что гарантирует, что результаты сравнения совпадают в SQL Server, как если бы в управляемом коде произошло то же сравнение. Определяемый пользователем интерфейс реализует интерфейс System.Data.SqlTypes.INullable, чтобы UDT знал null.

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

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
  IsByteOrdered=true)]
public struct Point : INullable { ... }

Реализация nullability

Помимо задания необходимых атрибутов для сборок определяемый пользователем тип должен также поддерживать допустимость значений NULL. Определяемые пользователем UDT, загруженные в SQL Server, имеют значение NULL, но для распознавания значения NULL определяемый пользователем интерфейс должен реализовать интерфейс System.Data.SqlTypes.INullable.

Необходимо создать свойство с именем IsNull, которое необходимо, чтобы определить, является ли значение null из кода CLR. Когда SQL Server находит пустой экземпляр определяемого пользователем типа, UDT сохраняется с помощью обычных методов обработки значений NULL. Сервер не тратит время на сериализацию или десериализацию определяемого пользователем типа UDT, если он не требуется, и он не тратит свободное место для хранения UDT null. Эта проверка значений NULL выполняется каждый раз, когда UDT выполняется из среды CLR, что означает, что при использовании конструкции Transact-SQL IS NULL для проверки наличия UDT null всегда должна работать. Свойство IsNull также используется сервером для проверки того, имеет ли экземпляр значение NULL. Если сервер определил, что определяемый пользователем тип равен NULL, то может использовать собственные методы работы со значениями NULL.

Метод get()IsNull не является особым регистром. Если Point переменная @pNull, то по умолчанию @p.IsNull будет оцениваться NULL, а не 1. Это связано с тем, что атрибут SqlMethod(OnNullCall) метода IsNull get() по умолчанию имеет значение false. Так как объект Null, когда свойство запрашивается, объект не десериализирован, метод не вызывается, а возвращается значение по умолчанию null.

Пример

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

private bool is_Null;

public bool IsNull
{
    get
    {
        return (is_Null);
    }
}

public static Point Null
{
    get
    {
        Point pt = new Point();
        pt.is_Null = true;
        return pt;
    }
}

IS NULL и IsNull

Рассмотрим таблицу, содержащую схему Points(id int, location Point), где Point является определяемой пользователем средой CLR и следующими запросами:

  • Запрос 1.

    SELECT ID FROM Points
WHERE NOT (location IS NULL); -- Or, WHERE location IS NOT NULL;

  • Запрос 2.

    SELECT ID FROM Points
WHERE location.IsNull = 0;

Оба запроса возвращают идентификаторы точек с ненулевыми расположениями. В запросе 1 используется нормальная обработка значений NULL, а десериализация определяемых пользователем типов не требуется. Запрос 2, с другой стороны, должен десериализировать каждый ненулевой объект и вызвать clR, чтобы получить значение свойства IsNull. Очевидно, что использование IS NULL демонстрирует лучшую производительность, и никогда не должно быть причины считывать свойство IsNull определяемого пользователем кода из Transact-SQL кода.

Итак, что такое использование свойства IsNull? Во-первых, необходимо определить, является ли значение null из кода СРЕДЫ CLR. Во-вторых, серверу требуется способ проверить, имеет ли экземпляр значение NULL, поэтому это свойство используется сервером. После определения значения NULL он может использовать собственную обработку NULL для обработки.

Реализация метода синтаксического анализа

Методы Parse и ToString позволяют преобразовать в строковые представления определяемого пользователем типа и из нее. Метод Parse позволяет преобразовать строку в определяемую пользователем строку. Он должен быть объявлен как static (или Shared в Visual Basic) и принимать параметр типа System.Data.SqlTypes.SqlString.

Следующий код реализует метод Parse для Point определяемого пользователем типа, который отделяет координаты X и Y. Метод Parse имеет один аргумент типа System.Data.SqlTypes.SqlString, и предполагает, что значения X и Y предоставляются в виде строки с разделителями-запятыми. Установка атрибута Microsoft.SqlServer.Server.SqlMethodAttribute.OnNullCall на false запрещает вызов метода Parse из экземпляра Point null.

[SqlMethod(OnNullCall = false)]
public static Point Parse(SqlString s)
{
    if (s.IsNull)
        return Null;

    // Parse input string to separate out points.
    Point pt = new Point();
    string[] xy = s.Value.Split(",".ToCharArray());
    pt.X = Int32.Parse(xy[0]);
    pt.Y = Int32.Parse(xy[1]);
    return pt;
}

Реализация метода ToString

Метод ToString преобразует Point UDT в строковое значение. В этом случае строка NULL возвращается для экземпляра NULL типа Point. Метод ToString изменяет метод Parse с помощью System.Text.StringBuilder для возврата System.String с разделителями-запятыми, состоящих из значений координат X и Y. Так как InvokeIfReceiverIsNull по умолчанию имеет значение false, проверка на наличие экземпляра null Point не требуется.

private Int32 _x;
private Int32 _y;

public override string ToString()
{
    if (this.IsNull)
        return "NULL";
    else
    {
        StringBuilder builder = new StringBuilder();
        builder.Append(_x);
        builder.Append(",");
        builder.Append(_y);
        return builder.ToString();
    }
}

Предоставление свойств определяемого пользователем объекта

Point UDT предоставляет координаты X и Y, реализованные как общедоступные свойства чтения и записи типа System.Int32.

public Int32 X
{
    get
    {
        return this._x;
    }
    set
    {
        _x = value;
    }
}

public Int32 Y
{
    get
    {
        return this._y;
    }
    set
    {
        _y = value;
    }
}

Проверка значений определяемого пользователем типа

При работе с данными UDT SQL Server ядро СУБД автоматически преобразует двоичные значения в значения UDT. Этот процесс преобразования включает в себя проверку соответствия значений формату сериализации и проверку того, что значения могут быть десериализованы правильно. Это гарантирует, что значение можно преобразовать обратно в двоичную форму. В случае определяемого пользователем типа с заданным порядком байтов это также гарантирует, что результирующее двоичное значение совпадет с исходным. Благодаря этому предотвращается сохранение недопустимых значений в базе данных. В некоторых случаях этот уровень проверки может оказаться недостаточным. Дополнительная проверка может потребоваться, если значения определяемых пользователем значений должны находиться в ожидаемом домене или диапазоне. Например, для определяемого пользователем типа, реализующего дату, может потребоваться, чтобы день был положительным числом, попадающим в определенный диапазон допустимых значений.

Свойство Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute.ValidationMethodNameMicrosoft.SqlServer.Server.SqlUserDefinedTypeAttribute позволяет указать имя метода проверки, запускаемого сервером при назначении данных определяемого пользователем типа или преобразованном в определяемый пользователем тип. ValidationMethodName также вызывается во время выполнения программы bcp, BULK INSERT, DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE, распределенных запросов и потоков табличных данных (TDS) удаленного вызова процедур (RPC). Значение по умолчанию для ValidationMethodName равно NULL, указывающее, что метод проверки отсутствует.

Пример

В следующем фрагменте кода показано объявление для класса Point, указывающего ValidationMethodNameValidatePoint.

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
  IsByteOrdered=true,
  ValidationMethodName = "ValidatePoint")]
public struct Point : INullable { ... }

Если метод проверки задан, то должен иметь подпись, которая выглядит, как показано в следующем фрагменте кода:

private bool ValidationFunction()
{
    if (validation logic here)
    {
        return true;
    }
    else
    {
        return false;
    }
}

Метод проверки может иметь любую область и должен возвращать true, если значение допустимо, и false в противном случае. Если метод возвращает false или вызывает исключение, значение обрабатывается как недопустимое и возникает ошибка.

В следующем примере код разрешает только значения нулевых или больше координат X и Y.

private bool ValidatePoint()
{
    if ((_x >= 0) && (_y >= 0))
    {
        return true;
    }
    else
    {
        return false;
    }
}

Ограничения метода проверки

Сервер вызывает метод проверки, если сервер выполняет преобразования, а не при вставке данных путем задания отдельных свойств или вставки данных с помощью инструкции Transact-SQL INSERT.

Необходимо явно вызвать метод проверки из наборов свойств и метод Parse, если требуется, чтобы метод проверки выполнялся во всех ситуациях. Это не является требованием, и в некоторых случаях может даже не быть желательным.

Пример проверки синтаксического анализа

Чтобы убедиться, что метод ValidatePoint вызывается в классе Point, необходимо вызвать его из метода Parse и из процедур свойств, которые задают значения координат X и Y. В следующем фрагменте кода показано, как вызвать метод проверки ValidatePoint из функции Parse.

[SqlMethod(OnNullCall = false)]
public static Point Parse(SqlString s)
{
    if (s.IsNull)
        return Null;

    // Parse input string to separate out points.
    Point pt = new Point();
    string[] xy = s.Value.Split(",".ToCharArray());
    pt.X = Int32.Parse(xy[0]);
    pt.Y = Int32.Parse(xy[1]);

    // Call ValidatePoint to enforce validation
    // for string conversions.
    if (!pt.ValidatePoint())
        throw new ArgumentException("Invalid XY coordinate values.");
    return pt;
}

Пример проверки свойств

В следующем фрагменте кода показано, как вызвать метод проверки ValidatePoint из процедур свойств, которые задают координаты X и Y.

public Int32 X
{
    get
    {
        return this._x;
    }
    // Call ValidatePoint to ensure valid range of Point values.
    set
    {
        Int32 temp = _x;
        _x = value;
        if (!ValidatePoint())
        {
            _x = temp;
            throw new ArgumentException("Invalid X coordinate value.");
        }
    }
}

public Int32 Y
{
    get
    {
        return this._y;
    }
    set
    {
        Int32 temp = _y;
        _y = value;
        if (!ValidatePoint())
        {
            _y = temp;
            throw new ArgumentException("Invalid Y coordinate value.");
        }
    }
}

Методы определяемого пользователем кода

При реализации методов определяемого пользователем типа следует учитывать возможность изменения алгоритма в будущем. В этом случае может потребоваться создать отдельный класс для методов, которые использует UDT. Если алгоритм изменяется, можно перекомпилировать класс с новым кодом и загрузить сборку в SQL Server, не влияя на определяемый пользователем код. Во многих случаях UDT можно перезагрузить с помощью инструкции Transact-SQL ALTER ASSEMBLY, но это может привести к проблемам с существующими данными. Например, Currency UDT, включенный в пример базы данных AdventureWorks2022, использует функцию ConvertCurrency для преобразования значений валют, реализуемой в отдельном классе. Возможно, что алгоритмы преобразования могут изменяться непредсказуемыми способами в будущем или что новые функции могут потребоваться. Разделение функции ConvertCurrency от реализации UDT Currency обеспечивает большую гибкость при планировании будущих изменений.

Пример

Класс Point содержит три простых метода вычисления расстояния: Distance, DistanceFromи DistanceFromXY. Каждый возвращает double вычисление расстояния от Point до нуля, расстояние от указанной точки до Point, а также расстояние от указанных координат X и Y до Point. Distance и DistanceFrom каждый вызов DistanceFromXY, а также показано, как использовать разные аргументы для каждого метода.

// Distance from 0 to Point.
[SqlMethod(OnNullCall = false)]
public Double Distance()
{
    return DistanceFromXY(0, 0);
}

// Distance from Point to the specified point.
[SqlMethod(OnNullCall = false)]
public Double DistanceFrom(Point pFrom)
{
    return DistanceFromXY(pFrom.X, pFrom.Y);
}

// Distance from Point to the specified x and y values.
[SqlMethod(OnNullCall = false)]
public Double DistanceFromXY(Int32 iX, Int32 iY)
{
    return Math.Sqrt(Math.Pow(iX - _x, 2.0) + Math.Pow(iY - _y, 2.0));
}

Использование атрибутов SqlMethod

Класс Microsoft.SqlServer.Server.SqlMethodAttribute предоставляет настраиваемые атрибуты, которые можно использовать для пометки определений методов, чтобы указать детерминизм, поведение вызова NULL и указать, является ли метод мутатором. Предполагается, что эти свойства имеют определенные значения по умолчанию, а настраиваемый атрибут используется только тогда, когда необходимо задать другое значение.

Примечание.

Класс SqlMethodAttribute наследует от класса SqlFunctionAttribute, поэтому SqlMethodAttribute наследует поля FillRowMethodName и TableDefinition от SqlFunctionAttribute. Это означает, что можно написать табличный метод, который не так. Метод компилируется и развертывается сборка, но ошибка о типе возврата IEnumerable возникает во время выполнения со следующим сообщением: "Метод, свойство или поле <name> в классе <class> в сборке <assembly> имеет недопустимый тип возврата".

В следующей таблице описаны некоторые из соответствующих свойств Microsoft.SqlServer.Server.SqlMethodAttribute, которые можно использовать в методах UDT и перечисляют значения по умолчанию.

Свойство Описание
DataAccess Указывает, включает ли функция доступ к пользовательским данным, хранящимся в локальном экземпляре SQL Server. По умолчанию используется DataAccessKind.None.
IsDeterministic Указывает, производит ли функция одни и те же выходные значения при одинаковых наборах входных значений и одинаковых состояниях базы данных. По умолчанию используется false.
IsMutator Указывает, вызывает ли метод изменение состояния экземпляра определяемого пользователем типа. По умолчанию используется false.
IsPrecise Указывает, содержит ли функция вычисления с потерей точности (например, операции с плавающей запятой). По умолчанию используется false.
OnNullCall Указывает, вызывается ли метод, если в качестве ссылки на входные аргументы заданы значения NULL. По умолчанию используется true.

Пример

Свойство Microsoft.SqlServer.Server.SqlMethodAttribute.IsMutator позволяет пометить метод, который позволяет изменять состояние экземпляра определяемого пользователем типа. Transact-SQL не позволяет задать два свойства UDT в предложении SET одной инструкции UPDATE. Однако можно создать метод-мутатор, изменяющий два члена определяемого пользователем типа сразу.

Примечание.

Методы мутатора не допускаются в запросах. Их можно вызывать только в инструкциях присваивания или изменения данных. Если метод, помеченный как мутатор, не возвращает void (или не является Sub в Visual Basic), CREATE TYPE завершается ошибкой.

В следующей инструкции предполагается существование Triangles определяемого пользователем типа, имеющего метод Rotate. Следующая инструкция обновления Transact-SQL вызывает метод Rotate:

UPDATE Triangles
SET t.RotateY(0.6)
WHERE id = 5;

Метод Rotate украшен параметром атрибута SqlMethodIsMutator для true, чтобы SQL Server помечал метод как метод мутатора. Код также задает для OnNullCall значение false, указывающее на сервер, что метод возвращает пустую ссылку (Nothing в Visual Basic), если какие-либо входные параметры являются пустыми ссылками.

[SqlMethod(IsMutator = true, OnNullCall = false)]
public void Rotate(double anglex, double angley, double anglez)
{
   RotateX(anglex);
   RotateY(angley);
   RotateZ(anglez);
}

Реализация определяемого пользователем формата

При реализации определяемого пользователем формата необходимо реализовать методы Read и Write, реализующие интерфейс Microsoft.SqlServer.Server.IBinarySerialize для обработки сериализации и десериализации данных UDT. Необходимо также указать свойство MaxByteSizeMicrosoft.SqlServer.Server.SqlUserDefinedTypeAttribute.

UDT валюты

Currency UDT входит в состав примеров среды CLR, которые можно установить с ПОМОЩЬЮ SQL Server.

Currency UDT поддерживает обработку сумм денег в денежной системе определенной культуры. Необходимо определить два поля: string для CultureInfo, которая указывает, кто выдал валюту (например,en-us), а также decimal для CurrencyValue, сумму денег.

Хотя он не используется сервером для сравнения, Currency определяемый пользователем интерфейс реализует интерфейс System.IComparable, который предоставляет один метод, System.IComparable.CompareTo. Это используется на стороне клиента в ситуациях, когда желательно точно сравнивать или упорядочивать валютные значения в языках и региональных параметров.

Код, работающий в среде CLR, сравнивает культуры отдельно от значений суммы. Для кода Transact-SQL следующие действия определяют сравнение:

  1. Задайте для атрибута IsByteOrdered значение true, которое сообщает SQL Server использовать сохраненное двоичное представление на диске для сравнения.

  2. Используйте метод Write для определяемого пользователем типа Currency, чтобы определить, как UDT сохраняется на диске, и, следовательно, как сравниваются и упорядочены значения определяемого пользователем типа для операций Transact-SQL.

  3. Сохраните Currency UDT с помощью следующего двоичного формата:

    1. Культура сохраняется в виде строки в кодировке UTF-16 для байтов 0-19 с дополнением нулевыми символами справа.

    2. Байты с 20 и выше используются для сохранения десятичного значения денежной суммы.

Цель заполнения заключается в том, чтобы язык и региональные параметры полностью отделялись от значения валюты, чтобы при сравнении одного определяемого пользователем значения с другим в коде Transact-SQL байты и региональных байтов сравнивались с значениями байтов в валюте.

Атрибуты валюты

Currency определяемого пользователем типа "UDT" со следующими атрибутами.

[Serializable]
[SqlUserDefinedType(Format.UserDefined,
    IsByteOrdered = true, MaxByteSize = 32)]
    [CLSCompliant(false)]
public struct Currency : INullable, IComparable, IBinarySerialize
{ ... }

Создание методов чтения и записи с помощью ibinaryserialize

При выборе формата сериализации UserDefined необходимо также реализовать интерфейс IBinarySerialize и создать собственные Read и методы Write. Следующие процедуры из определяемого пользователем Currency используют System.IO.BinaryReader и System.IO.BinaryWriter для чтения и записи в определяемый пользователем тип.

// IBinarySerialize methods
// The binary layout is as follow:
//    Bytes 0 - 19:Culture name, padded to the right
//    with null characters, UTF-16 encoded
//    Bytes 20+:Decimal value of money
// If the culture name is empty, the currency is null.
public void Write(System.IO.BinaryWriter w)
{
    if (this.IsNull)
    {
        w.Write(nullMarker);
        w.Write((decimal)0);
        return;
    }

    if (cultureName.Length > cultureNameMaxSize)
    {
        throw new ApplicationException(string.Format(
            CultureInfo.InvariantCulture,
            "{0} is an invalid culture name for currency as it is too long.",
            cultureNameMaxSize));
    }

    String paddedName = cultureName.PadRight(cultureNameMaxSize, '\0');
    for (int i = 0; i < cultureNameMaxSize; i++)
    {
        w.Write(paddedName[i]);
    }

    // Normalize decimal value to two places
    currencyValue = Decimal.Floor(currencyValue * 100) / 100;
    w.Write(currencyValue);
}
public void Read(System.IO.BinaryReader r)
{
    char[] name = r.ReadChars(cultureNameMaxSize);
    int stringEnd = Array.IndexOf(name, '\0');

    if (stringEnd == 0)
    {
        cultureName = null;
        return;
    }

    cultureName = new String(name, 0, stringEnd);
    currencyValue = r.ReadDecimal();
}

Связанное содержимое