Udostępnij za pośrednictwem

Tworzenie typów zdefiniowanych przez użytkownika za pomocą ADO.NET

  • Artykuł

Dotyczy:programu SQL Server

Podczas kodowania definicji typu zdefiniowanego przez użytkownika (UDT) należy zaimplementować różne funkcje w zależności od tego, czy implementujesz funkcję UDT jako klasę, czy strukturę, oraz opcje formatowania i serializacji.

W przykładzie w tej sekcji przedstawiono implementację Point UDT jako struct (lub Structure w Visual Basic). Point UDT składa się ze współrzędnych X i Y implementowanych jako procedury właściwości.

Podczas definiowania funkcji UDT wymagane są następujące przestrzenie nazw:

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

Przestrzeń nazw Microsoft.SqlServer.Server zawiera obiekty wymagane dla różnych atrybutów udT, a System.Data.SqlTypes przestrzeni nazw zawiera klasy reprezentujące natywne typy danych programu SQL Server dostępne dla zestawu. Mogą istnieć inne przestrzenie nazw wymagane przez zestaw w celu poprawnego działania. Point udT używa również System.Text przestrzeni nazw do pracy z ciągami.

Nuta

Obiekty bazy danych Visual C++, takie jak UTS, skompilowane przy użyciu /clr:pure nie są obsługiwane do wykonywania.

Określanie atrybutów

Atrybuty określają sposób użycia serializacji do konstruowania reprezentacji magazynu tras ZDEFINIOWANYch przez użytkownika i przesyłania tras zdefiniowanych przez wartość do klienta.

Wymagany jest Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute. Atrybut Serializable jest opcjonalny. Można również określić Microsoft.SqlServer.Server.SqlFacetAttribute, aby podać informacje o zwracanym typie udT. Aby uzyskać więcej informacji, zobacz integracja środowiska CLR: atrybuty niestandardowe dla procedur CLR.

Atrybuty udT punktu

Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute ustawia format magazynu dla Point UDT na Native. IsByteOrdered jest ustawiona na true, co gwarantuje, że wyniki porównań są takie same w programie SQL Server, jak w przypadku tego samego porównania w kodzie zarządzanym. Funkcja UDT implementuje interfejs System.Data.SqlTypes.INullable, aby umożliwić rozpoznawanie wartości null przez funkcję UDT.

Poniższy fragment kodu przedstawia atrybuty Point UDT.

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

Implementowanie wartości null

Oprócz poprawnego określania atrybutów zestawów funkcja UDT musi również obsługiwać wartość null. Trasy zdefiniowane przez użytkownika załadowane do programu SQL Server mają wartość null, ale aby funkcja UDT rozpoznawała wartość null, funkcja UDT musi zaimplementować interfejs System.Data.SqlTypes.INullable.

Należy utworzyć właściwość o nazwie IsNull, która jest wymagana do określenia, czy wartość ma wartość null z poziomu kodu CLR. Gdy program SQL Server znajdzie wystąpienie funkcji UDT o wartości null, funkcja UDT jest utrwalana przy użyciu normalnych metod obsługi wartości null. Serwer nie traci czasu serializacji ani deserializacji udT, jeśli nie musi, i nie traci miejsca do przechowywania null UDT. Ta kontrola wartości null jest wykonywana za każdym razem, gdy funkcja UDT zostanie przeniesiona ze środowiska CLR, co oznacza, że użycie konstrukcji Transact-SQL IS NULL w celu sprawdzenia wartości null zdefiniowanych przez użytkownika powinno zawsze działać. Właściwość IsNull jest również używana przez serwer do testowania, czy wystąpienie ma wartość null. Gdy serwer ustali, że funkcja UDT ma wartość null, może użyć natywnej obsługi wartości null.

Metoda get()IsNull nie jest w żaden sposób specjalna. Jeśli zmienna Point@p@p jest Null, @p.IsNull domyślnie będzie oceniać NULL, a nie 1. Dzieje się tak, ponieważ atrybut SqlMethod(OnNullCall) metody IsNull get() domyślnie ma wartość false. Ponieważ obiekt jest Null, gdy właściwość jest żądana, obiekt nie jest deserializowany, metoda nie jest wywoływana, a zwracana jest domyślna wartość "NULL".

Przykład

W poniższym przykładzie zmienna is_Null jest prywatna i przechowuje stan null dla wystąpienia funkcji UDT. Kod musi zachować odpowiednią wartość dla is_Null. Funkcja UDT musi również mieć właściwość statyczną o nazwie Null, która zwraca wystąpienie wartości null udT. Dzięki temu funkcja UDT zwraca wartość null, jeśli wystąpienie rzeczywiście ma wartość null w bazie danych.

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 a IsNull

Rozważmy tabelę zawierającą Points(id int, location Point)schematu, gdzie Point jest clR UDT i następującymi zapytaniami:

  • Kwerenda 1:

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

  • Zapytanie 2:

    SELECT ID FROM Points
WHERE location.IsNull = 0;

Oba zapytania zwracają identyfikatory punktów z lokalizacjami innych niż null. W zapytaniu 1 używana jest normalna obsługa wartości null i nie jest wymagana deserializacja tras ZDEFINIOWANYch przez użytkownika. Zapytanie 2, z drugiej strony, musi wykonać deserializacji każdego obiektu innego niż null i wywołać do CLR, aby uzyskać wartość właściwości IsNull. Oczywiście użycie IS NULL wykazuje lepszą wydajność i nigdy nie powinno być powodem do odczytania właściwości IsNull udT z kodu Transact-SQL.

Co to jest użycie właściwości IsNull? Najpierw należy określić, czy wartość ma wartość null z poziomu kodu CLR. Po drugie serwer musi sprawdzić, czy wystąpienie ma wartość null, więc ta właściwość jest używana przez serwer. Po ustaleniu, że ma wartość null, może ona obsługiwać ją przy użyciu natywnej obsługi wartości null.

Implementowanie metody analizy

Metody Parse i ToString umożliwiają konwersje na i z reprezentacji ciągów udT. Metoda Parse umożliwia konwertowanie ciągu na udT. Musi być zadeklarowany jako static (lub Shared w Visual Basic) i mieć parametr typu System.Data.SqlTypes.SqlString.

Poniższy kod implementuje metodę Parse dla Point UDT, która oddziela współrzędne X i Y. Metoda Parse ma jeden argument typu System.Data.SqlTypes.SqlStringi zakłada, że wartości X i Y są dostarczane jako ciąg rozdzielany przecinkami. Ustawienie atrybutu Microsoft.SqlServer.Server.SqlMethodAttribute.OnNullCall na wartość false uniemożliwia wywoływanie metody Parse z wystąpienia punktu o wartości 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;
}

Implementowanie metody ToString

Metoda ToString konwertuje Point UDT na wartość ciągu. W tym przypadku ciąg "NULL" jest zwracany dla wystąpienia wartości Null typu Point. Metoda ToString odwraca metodę Parse przy użyciu System.Text.StringBuilder, aby zwrócić rozdzielane przecinkami System.String składające się z wartości współrzędnych X i Y. Ponieważ InvokeIfReceiverIsNull wartością domyślną jest fałsz, sprawdzanie wystąpienia wartości null Point jest niepotrzebne.

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();
    }
}

Uwidacznianie właściwości udT

Point UDT uwidacznia współrzędne X i Y, które są implementowane jako publiczne właściwości odczytu i zapisu typu System.Int32.

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

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

Weryfikowanie wartości UDT

Podczas pracy z danymi UDT aparat bazy danych programu SQL Server automatycznie konwertuje wartości binarne na wartości UDT. Ten proces konwersji obejmuje sprawdzenie, czy wartości są odpowiednie dla formatu serializacji typu i upewnienia się, że wartość może być poprawnie deserializowana. Dzięki temu można przekonwertować wartość z powrotem na formę binarną. W przypadku bajtów uporządkowanych przez użytkownika, zapewnia to również, że wynikowa wartość binarna jest zgodna z oryginalną wartością binarną. Zapobiega to utrwalaniu nieprawidłowych wartości w bazie danych. W niektórych przypadkach ten poziom kontroli może być niewystarczający. Dodatkowa weryfikacja może być wymagana, gdy wartości UDT muszą znajdować się w oczekiwanej domenie lub zakresie. Na przykład funkcja UDT, która implementuje datę, może wymagać, aby wartość dnia mogła być liczbą dodatnią, która mieści się w określonym zakresie prawidłowych wartości.

Właściwość Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute.ValidationMethodNameMicrosoft.SqlServer.Server.SqlUserDefinedTypeAttribute umożliwia podanie nazwy metody weryfikacji, która jest uruchamiana przez serwer, gdy dane są przypisane do funkcji UDT lub konwertowane na udT. jest również wywoływana podczas uruchamiania narzędzia bcp, , , , , zapytania rozproszonego i tabelarycznego strumienia danych (TDS) zdalnego wywołania procedury (RPC). Wartość domyślna dla ValidationMethodName ma wartość null, co oznacza, że nie ma metody sprawdzania poprawności.

Przykład

Poniższy fragment kodu przedstawia deklarację klasy Point, która określa ValidationMethodNameValidatePoint.

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

Jeśli określono metodę weryfikacji, musi ona mieć podpis podobny do poniższego fragmentu kodu.

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

Metoda walidacji może mieć dowolny zakres i powinna zwrócić true, jeśli wartość jest prawidłowa, a false w przeciwnym razie. Jeśli metoda zwraca false lub zgłasza wyjątek, wartość jest traktowana jako nieprawidłowa i zgłaszany jest błąd.

W poniższym przykładzie kod zezwala tylko na wartości zerowe lub większe współrzędnych X i Y.

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

Ograniczenia metody weryfikacji

Serwer wywołuje metodę weryfikacji, gdy serwer wykonuje konwersje, a nie podczas wstawiania danych przez ustawienie poszczególnych właściwości lub wstawienie danych przy użyciu instrukcji Transact-SQL INSERT.

Należy jawnie wywołać metodę weryfikacji z metod ustawiających właściwości i metodę Parse, jeśli chcesz, aby metoda walidacji wykonywana we wszystkich sytuacjach. Nie jest to wymagane, a w niektórych przypadkach nawet nie jest pożądane.

Przykład weryfikacji analizy

Aby upewnić się, że metoda ValidatePoint jest wywoływana w klasie Point, należy wywołać ją z metody Parse i z procedur właściwości, które ustawiają wartości współrzędnych X i Y. Poniższy fragment kodu pokazuje, jak wywołać metodę weryfikacji ValidatePoint z funkcji 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;
}

Przykład weryfikacji właściwości

Poniższy fragment kodu pokazuje, jak wywołać metodę weryfikacji ValidatePoint z procedur właściwości, które ustawiają współrzędne X i 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.");
        }
    }
}

Metody UDT kodu

Podczas kodowania metod UDT należy rozważyć, czy używany algorytm może ulec zmianie w czasie. Jeśli tak, warto rozważyć utworzenie oddzielnej klasy dla metod używanych przez użytkownika. Jeśli algorytm ulegnie zmianie, możesz ponownie skompilować klasę przy użyciu nowego kodu i załadować zestaw do programu SQL Server bez wpływu na udT. W wielu przypadkach można ponownie załadować trasy zdefiniowane przez użytkownika przy użyciu instrukcji Transact-SQL ALTER ASSEMBLY, ale może to spowodować problemy z istniejącymi danymi. Na przykład funkcja Currency UDT dołączona do przykładowej bazy danych AdventureWorks2022 używa funkcji ConvertCurrency do konwertowania wartości walutowych, które są implementowane w oddzielnej klasie. Możliwe, że algorytmy konwersji mogą ulec zmianie w nieprzewidywalny sposób w przyszłości lub że mogą być wymagane nowe funkcje. Oddzielenie funkcji ConvertCurrency od implementacji Currency UDT zapewnia większą elastyczność podczas planowania przyszłych zmian.

Przykład

Klasa Point zawiera trzy proste metody obliczania odległości: Distance, DistanceFromi DistanceFromXY. Każdy zwraca double oblicza odległość od Point do zera, odległość od określonego punktu do Pointoraz odległość od określonych współrzędnych X i Y do Point. Distance i DistanceFrom każde wywołanie DistanceFromXY, i pokazuje, jak używać różnych argumentów dla każdej metody.

// 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));
}

Używanie atrybutów SqlMethod

Klasa Microsoft.SqlServer.Server.SqlMethodAttribute udostępnia atrybuty niestandardowe, których można użyć do oznaczania definicji metod w celu określenia determinizmu, zachowania wywołania o wartości null i określenia, czy metoda jest mutatorem. Przyjmuje się wartości domyślne dla tych właściwości, a atrybut niestandardowy jest używany tylko wtedy, gdy wymagana jest wartość nie domyślna.

Nuta

Klasa SqlMethodAttribute dziedziczy z klasy SqlFunctionAttribute, dlatego SqlMethodAttribute dziedziczy pola FillRowMethodName i TableDefinition z SqlFunctionAttribute. Oznacza to, że można napisać metodę o wartości tabeli, która nie jest taka sama. Metoda kompiluje i wdraża zestaw, ale w czasie wykonywania jest zgłaszany błąd dotyczący IEnumerable zwracanego typu z następującym komunikatem: "Metoda, właściwość lub pole <name> w klasie <class> w <assembly> zestawu ma nieprawidłowy typ zwracany".

W poniższej tabeli opisano niektóre z odpowiednich właściwości Microsoft.SqlServer.Server.SqlMethodAttribute, które mogą być używane w metodach UDT, i wymieniono ich wartości domyślne.

Własność Opis
DataAccess Wskazuje, czy funkcja obejmuje dostęp do danych użytkownika przechowywanych w lokalnym wystąpieniu programu SQL Server. Wartość domyślna to DataAccessKind.None.
IsDeterministic Wskazuje, czy funkcja generuje te same wartości wyjściowe, biorąc pod uwagę te same wartości wejściowe i ten sam stan bazy danych. Wartość domyślna to false.
IsMutator Wskazuje, czy metoda powoduje zmianę stanu w wystąpieniu UDT. Wartość domyślna to false.
IsPrecise Wskazuje, czy funkcja obejmuje nieprecyzyjne obliczenia, takie jak operacje zmiennoprzecinkowe. Wartość domyślna to false.
OnNullCall Wskazuje, czy metoda jest wywoływana, gdy określono argumenty wejściowe odwołania o wartości null. Wartość domyślna to true.

Przykład

Właściwość Microsoft.SqlServer.Server.SqlMethodAttribute.IsMutator umożliwia oznaczenie metody, która umożliwia zmianę stanu wystąpienia funkcji UDT. Transact-SQL nie zezwala na ustawienie dwóch właściwości UDT w klauzuli SET jednej instrukcji UPDATE. Można jednak mieć metodę oznaczoną jako mutator, który zmienia dwa elementy członkowskie.

Nuta

Metody Mutatora nie są dozwolone w zapytaniach. Mogą być wywoływane tylko w instrukcjach przypisania lub instrukcjach modyfikacji danych. Jeśli metoda oznaczona jako mutator nie zwraca void (lub nie jest Sub w Visual Basic), CREATE TYPE kończy się niepowodzeniem z powodu błędu.

W poniższej instrukcji przyjęto założenie istnienia Triangles UDT, która ma metodę Rotate. Następująca instrukcja Transact-SQL update wywołuje metodę Rotate:

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

Metoda Rotate jest ozdobiona ustawieniem atrybutu SqlMethodIsMutator w celu true, aby program SQL Server mógł oznaczyć metodę jako metodę mutatora. Kod ustawia również OnNullCall na false, który wskazuje serwer, że metoda zwraca odwołanie o wartości null (Nothing w Visual Basic), jeśli którykolwiek z parametrów wejściowych są odwołaniami o wartości null.

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

Implementowanie funkcji UDT przy użyciu formatu zdefiniowanego przez użytkownika

Podczas implementowania funkcji UDT przy użyciu formatu zdefiniowanego przez użytkownika należy zaimplementować metody Read i Write, które implementują interfejs Microsoft.SqlServer.Server.IBinarySerialize w celu obsługi serializacji i deserializacji danych UDT. Należy również określić właściwość MaxByteSizeMicrosoft.SqlServer.Server.SqlUserDefinedTypeAttribute.

Funkcja UDT waluty

Zestaw Currency UDT jest dołączony do przykładów CLR, które można zainstalować za pomocą programu SQL Server.

Currency UDT obsługuje obsługę kwot pieniędzy w systemie pieniężnym określonej kultury. Musisz zdefiniować dwa pola: string dla CultureInfo, który określa, kto wystawił walutę (na przykładen-us, i decimal dla CurrencyValue, kwotę pieniędzy.

Mimo że nie jest używany przez serwer do przeprowadzania porównań, Currency UDT implementuje interfejs System.IComparable, który uwidacznia jedną metodę, System.IComparable.CompareTo. Jest to używane po stronie klienta w sytuacjach, w których pożądane jest dokładne porównywanie lub zamawianie wartości waluty w kulturach.

Kod uruchomiony w środowisku CLR porównuje kulturę oddzielnie od wartości waluty. W przypadku kodu Transact-SQL następujące akcje określają porównanie:

  1. Ustaw atrybut IsByteOrdered na wartość true, który informuje program SQL Server o użyciu utrwalonej reprezentacji binarnej na dysku na potrzeby porównań.

  2. Użyj metody Write dla Currency UDT, aby określić, jak udT jest utrwalone na dysku i w jaki sposób wartości UDT są porównywane i uporządkowane dla operacji Transact-SQL.

  3. Zapisz Currency udT przy użyciu następującego formatu binarnego:

    1. Zapisz kulturę jako ciąg zakodowany w formacie UTF-16 dla bajtów 0–19 z dopełnianiem po prawej stronie z znakami null.

    2. Użyj bajtów 20 i nowszych, aby zawierać wartość dziesiętną waluty.

Celem wypełnienia jest zapewnienie, że kultura jest całkowicie oddzielona od wartości waluty, tak aby w przypadku porównania jednego z nich w kodzie Transact-SQL, bajty kulturowe są porównywane z bajtami kultury, a wartości bajtów waluty są porównywane z wartościami bajtów waluty.

Atrybuty waluty

Currency udT jest definiowany z następującymi atrybutami.

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

Tworzenie metod odczytu i zapisu za pomocą metody ibinaryserialize

Po wybraniu formatu serializacji UserDefined należy również zaimplementować interfejs IBinarySerialize i utworzyć własne metody Read i Write. Poniższe procedury z Currency UDT używają System.IO.BinaryReader i System.IO.BinaryWriter do odczytu i zapisu do udT.

// 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();
}

Powiązana zawartość