Membuat jenis yang ditentukan pengguna dengan ADO.NET

Berlaku untuk:SQL Server

Saat mengodekan definisi jenis yang ditentukan pengguna (UDT), Anda harus menerapkan berbagai fitur, tergantung pada apakah Anda menerapkan UDT sebagai kelas atau struktur, dan pada opsi format dan serialisasi yang Anda pilih.

Contoh di bagian ini menggambarkan penerapan UDT Point sebagai struct (atau Structure di Visual Basic). UDT Point terdiri dari koordinat X dan Y yang diimplementasikan sebagai prosedur properti.

Namespace berikut diperlukan saat menentukan UDT:

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

Namespace Microsoft.SqlServer.Server berisi objek yang diperlukan untuk berbagai atribut UDT Anda, dan namespace System.Data.SqlTypes berisi kelas yang mewakili jenis data asli SQL Server yang tersedia untuk perakitan. Mungkin ada namespace lain yang diperlukan assembly Anda untuk berfungsi dengan benar. UDT Point juga menggunakan namespace System.Text untuk bekerja dengan string.

Catatan

Objek database Visual C++, seperti UDT, yang dikompilasi dengan /clr:pure tidak didukung untuk eksekusi.

Tentukan atribut

Atribut menentukan bagaimana serialisasi digunakan untuk membangun representasi penyimpanan UDT dan untuk mengirimkan UDT berdasarkan nilai ke klien.

Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute diperlukan. Atribut Serializable bersifat opsional. Anda juga dapat menentukan Microsoft.SqlServer.Server.SqlFacetAttribute untuk memberikan informasi tentang jenis pengembalian UDT. Untuk informasi selengkapnya, lihat integrasi CLR : atribut kustom untuk rutinitas CLR.

Atribut Point UDT

Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute mengatur format penyimpanan untuk UDT Point ke Native. IsByteOrdered diatur ke true, yang menjamin bahwa hasil perbandingan sama di SQL Server seolah-olah perbandingan yang sama terjadi dalam kode terkelola. UDT mengimplementasikan antarmuka System.Data.SqlTypes.INullable untuk membuat UDT sadar null.

Fragmen kode berikut menunjukkan atribut untuk UDT Point.

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

Menerapkan nullability

Selain menentukan atribut untuk rakitan Anda dengan benar, UDT Anda juga harus mendukung nullability. UDT yang dimuat ke SQL Server sadar null, tetapi agar UDT mengenali nilai null, UDT harus mengimplementasikan antarmuka System.Data.SqlTypes.INullable.

Anda harus membuat properti bernama IsNull, yang diperlukan untuk menentukan apakah nilai null dari dalam kode CLR. Ketika SQL Server menemukan instans null UDT, UDT tetap menggunakan metode penanganan null normal. Server tidak membuang waktu untuk membuat serialisasi atau deserialisasi UDT jika tidak perlu, dan tidak membuang-buang ruang untuk menyimpan UDT null. Pemeriksaan null ini dilakukan setiap kali UDT dibawa dari CLR, yang berarti bahwa menggunakan konstruksi Transact-SQL IS NULL untuk memeriksa UDT null harus selalu berfungsi. Properti IsNull juga digunakan oleh server untuk menguji apakah instans null. Setelah server menentukan bahwa UDT null, server dapat menggunakan penanganan null aslinya.

Metode get()IsNull tidak ditangani secara khusus dengan cara apa pun. Jika Point variabel @pNull, maka @p.IsNull akan, secara default, mengevaluasi ke NULL, bukan 1. Ini karena atribut SqlMethod(OnNullCall) metode IsNull get() default ke false. Karena objek Null, ketika properti diminta, objek tidak dideserialisasi, metode tidak dipanggil, dan nilai default "NULL" dikembalikan.

Contoh

Dalam contoh berikut, is_Null variabel bersifat privat dan memegang status null untuk instans UDT. Kode Anda harus mempertahankan nilai yang sesuai untuk is_Null. UDT juga harus memiliki properti statis bernama Null yang mengembalikan instans nilai null UDT. Ini memungkinkan UDT untuk mengembalikan nilai null jika instans memang null dalam database.

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 vs. IsNull

Pertimbangkan tabel yang berisi skema Points(id int, location Point), di mana Point adalah UDT CLR, dan kueri berikut:

  • Kueri 1:

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

  • Kueri 2:

    SELECT ID FROM Points
WHERE location.IsNull = 0;

Kedua kueri mengembalikan ID titik dengan lokasi non-null. Di Kueri 1, penanganan null normal digunakan, dan tidak diperlukan deserialisasi UDT. Kueri 2, di sisi lain, harus mendeserialisasi setiap objek non-null dan memanggil ke CLR untuk mendapatkan nilai properti IsNull. Dengan jelas, menggunakan IS NULL menunjukkan performa yang lebih baik dan seharusnya tidak pernah ada alasan untuk membaca properti IsNull UDT dari kode Transact-SQL.

Jadi, apa penggunaan properti IsNull? Pertama, diperlukan untuk menentukan apakah nilai null dari dalam kode CLR. Kedua, server memerlukan cara untuk menguji apakah instans null, sehingga properti ini digunakan oleh server. Setelah ditentukan null, maka dapat menggunakan penanganan null aslinya untuk menanganinya.

Menerapkan metode penguraian

Metode Parse dan ToString memungkinkan konversi ke dan dari representasi string UDT. Metode Parse memungkinkan string dikonversi menjadi UDT. Ini harus dinyatakan sebagai static (atau Shared di Visual Basic), dan mengambil parameter jenis System.Data.SqlTypes.SqlString.

Kode berikut mengimplementasikan metode Parse untuk UDT Point, yang memisahkan koordinat X dan Y. Metode Parse memiliki satu argumen jenis System.Data.SqlTypes.SqlString, dan mengasumsikan bahwa nilai X dan Y disediakan sebagai string yang dibatasi koma. Mengatur atribut Microsoft.SqlServer.Server.SqlMethodAttribute.OnNullCall ke false mencegah metode Parse dipanggil dari instans Null Point.

[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;
}

Menerapkan metode ToString

Metode ToString mengonversi UDT Point menjadi nilai string. Dalam hal ini, string "NULL" dikembalikan untuk instans Null dari jenis Point. Metode ToString membalikkan metode Parse dengan menggunakan System.Text.StringBuilder untuk mengembalikan System.String yang dibatasi koma yang terdiri dari nilai koordinat X dan Y. Karena InvokeIfReceiverIsNull default ke false, pemeriksaan instans null Point tidak perlu.

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

Mengekspos properti UDT

UDT Point mengekspos koordinat X dan Y yang diimplementasikan sebagai properti baca-tulis publik dari jenis System.Int32.

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

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

Memvalidasi nilai UDT

Saat bekerja dengan data UDT, Mesin Database SQL Server secara otomatis mengonversi nilai biner menjadi nilai UDT. Proses konversi ini melibatkan pemeriksaan bahwa nilai sesuai untuk format serialisasi jenis dan memastikan bahwa nilai dapat dideserialisasi dengan benar. Ini memastikan bahwa nilai dapat dikonversi kembali ke formulir biner. Dalam kasus UDT yang diurutkan byte, ini juga memastikan bahwa nilai biner yang dihasilkan cocok dengan nilai biner asli. Hal ini mencegah nilai yang tidak valid dipertahankan dalam database. Dalam beberapa kasus, tingkat pemeriksaan ini mungkin tidak memadai. Validasi tambahan mungkin diperlukan ketika nilai UDT diperlukan untuk berada di domain atau rentang yang diharapkan. Misalnya, UDT yang menerapkan tanggal mungkin mengharuskan nilai hari menjadi angka positif yang termasuk dalam rentang nilai tertentu yang valid.

Properti Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute.ValidationMethodName dari Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute memungkinkan Anda untuk memberikan nama metode validasi yang dijalankan server saat data ditetapkan ke UDT atau dikonversi ke UDT. juga dipanggil selama menjalankan utilitas bcp, , , , , kueri terdistribusi, dan operasi panggilan prosedur jarak jauh (RPC) aliran data tabular (TDS). Nilai default untuk ValidationMethodName null, menunjukkan bahwa tidak ada metode validasi.

Contoh

Fragmen kode berikut menunjukkan deklarasi untuk kelas Point, yang menentukan ValidationMethodNameValidatePoint.

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

Jika metode validasi ditentukan, metode tersebut harus memiliki tanda tangan yang terlihat seperti fragmen kode berikut.

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

Metode validasi dapat memiliki cakupan apa pun dan harus mengembalikan true jika nilainya valid, dan false sebaliknya. Jika metode mengembalikan false atau melempar pengecualian, nilai diperlakukan sebagai tidak valid dan kesalahan dimunculkan.

Dalam contoh berikut, kode hanya mengizinkan nilai nol atau lebih besar koordinat X dan Y.

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

Batasan metode validasi

Server memanggil metode validasi ketika server melakukan konversi, bukan ketika data disisipkan dengan mengatur properti individual atau ketika data disisipkan menggunakan pernyataan Transact-SQL INSERT.

Anda harus secara eksplisit memanggil metode validasi dari setter properti dan metode Parse jika Anda ingin metode validasi dijalankan dalam semua situasi. Ini bukan persyaratan, dan dalam beberapa kasus bahkan mungkin tidak diinginkan.

Contoh validasi penguraian

Untuk memastikan bahwa metode ValidatePoint dipanggil di kelas Point, Anda harus memanggilnya dari metode Parse dan dari prosedur properti yang mengatur nilai koordinat X dan Y. Fragmen kode berikut menunjukkan cara memanggil metode validasi ValidatePoint dari fungsi 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;
}

Contoh validasi properti

Fragmen kode berikut menunjukkan cara memanggil metode validasi ValidatePoint dari prosedur properti yang mengatur koordinat X dan 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.");
        }
    }
}

Metode UDT kode

Saat mengodekan metode UDT, pertimbangkan apakah algoritma yang digunakan mungkin dapat berubah dari waktu ke waktu. Jika demikian, Anda mungkin ingin mempertimbangkan untuk membuat kelas terpisah untuk metode yang digunakan UDT Anda. Jika algoritma berubah, Anda dapat mengkompilasi ulang kelas dengan kode baru, dan memuat rakitan ke SQL Server tanpa memengaruhi UDT. Dalam banyak kasus, UDT dapat dimuat ulang menggunakan pernyataan Transact-SQL ALTER ASSEMBLY, tetapi itu berpotensi menyebabkan masalah dengan data yang ada. Misalnya, Currency UDT yang disertakan dengan database sampel AdventureWorks2025 menggunakan fungsi ConvertCurrency untuk mengonversi nilai mata uang, yang diimplementasikan dalam kelas terpisah. Ada kemungkinan bahwa algoritma konversi mungkin berubah dengan cara yang tidak dapat diprediksi di masa depan, atau fungsionalitas baru mungkin diperlukan. Memisahkan fungsi ConvertCurrency dari implementasi UDT Currency memberikan fleksibilitas yang lebih besar saat merencanakan perubahan di masa mendatang.

Contoh

Kelas Point berisi tiga metode sederhana untuk menghitung jarak: Distance, DistanceFrom, dan DistanceFromXY. Setiap mengembalikan double yang menghitung jarak dari Point ke nol, jarak dari titik tertentu ke Point, dan jarak dari koordinat X dan Y yang ditentukan ke Point. Distance dan DistanceFrom setiap panggilan DistanceFromXY, dan menunjukkan cara menggunakan argumen yang berbeda untuk setiap metode.

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

Menggunakan atribut SqlMethod

Kelas Microsoft.SqlServer.Server.SqlMethodAttribute menyediakan atribut kustom yang dapat digunakan untuk menandai definisi metode untuk menentukan determinisme, pada perilaku panggilan null, dan untuk menentukan apakah metode adalah mutator. Nilai default untuk properti ini diasumsikan, dan atribut kustom hanya digunakan saat nilai non-default diperlukan.

Catatan

Kelas SqlMethodAttribute mewarisi dari kelas SqlFunctionAttribute, jadi SqlMethodAttribute mewarisi bidang FillRowMethodName dan TableDefinition dari SqlFunctionAttribute. Ini menyiratkan bahwa dimungkinkan untuk menulis metode bernilai tabel, yang bukan masalahnya. Metode ini mengkompilasi dan rakitan disebarkan, tetapi kesalahan tentang jenis pengembalian IEnumerable dimunculkan pada runtime dengan pesan berikut: "Metode, properti, atau bidang <name> di kelas <class> dalam rakitan <assembly> memiliki jenis pengembalian yang tidak valid."

Tabel berikut ini menjelaskan beberapa properti Microsoft.SqlServer.Server.SqlMethodAttribute relevan yang dapat digunakan dalam metode UDT, dan mencantumkan nilai defaultnya.

Harta benda Deskripsi
DataAccess Menunjukkan apakah fungsi melibatkan akses ke data pengguna yang disimpan dalam instans lokal SQL Server. Defaultnya adalah DataAccessKind.None.
IsDeterministic Menunjukkan apakah fungsi menghasilkan nilai output yang sama dengan nilai input yang sama dan status database yang sama. Defaultnya adalah false.
IsMutator Menunjukkan apakah metode menyebabkan perubahan status dalam instans UDT. Defaultnya adalah false.
IsPrecise Menunjukkan apakah fungsi melibatkan komputasi yang tidak tepat, seperti operasi floating point. Defaultnya adalah false.
OnNullCall Menunjukkan apakah metode dipanggil ketika argumen input referensi null ditentukan. Defaultnya adalah true.

Contoh

Properti Microsoft.SqlServer.Server.SqlMethodAttribute.IsMutator memungkinkan Anda menandai metode yang memungkinkan perubahan status instans UDT. Transact-SQL tidak memungkinkan Anda mengatur dua properti UDT dalam klausa SET dari satu pernyataan UPDATE. Namun, Anda dapat memiliki metode yang ditandai sebagai mutator yang mengubah dua anggota.

Catatan

Metode mutator tidak diizinkan dalam kueri. Mereka hanya dapat dipanggil dalam pernyataan penugasan atau pernyataan modifikasi data. Jika metode yang ditandai sebagai mutator tidak mengembalikan void (atau bukan Sub di Visual Basic), CREATE TYPE gagal dengan kesalahan.

Pernyataan berikut mengasumsikan keberadaan UDT Triangles yang memiliki metode Rotate. Pernyataan pembaruan Transact-SQL berikut memanggil metode Rotate:

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

Metode Rotate dihiasi dengan pengaturan atribut SqlMethodIsMutator ke true sehingga SQL Server dapat menandai metode sebagai metode mutator. Kode juga mengatur OnNullCall ke false, yang menunjukkan ke server bahwa metode mengembalikan referensi null (Nothing di Visual Basic) jika salah satu parameter input adalah referensi null.

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

Menerapkan UDT dengan format yang ditentukan pengguna

Saat menerapkan UDT dengan format yang ditentukan pengguna, Anda harus menerapkan metode Read dan Write yang mengimplementasikan antarmuka Microsoft.SqlServer.Server.IBinarySerialize untuk menangani data UDT serialisasi dan deserialisasi. Anda juga harus menentukan properti MaxByteSize dari Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute.

Mata uang UDT

UDT Currency disertakan dengan sampel CLR yang dapat diinstal dengan SQL Server.

UDT Currency mendukung penanganan jumlah uang dalam sistem moneter budaya tertentu. Anda harus menentukan dua bidang: string untuk CultureInfo, yang menentukan siapa yang mengeluarkan mata uang (en-us, misalnya) dan decimal untuk CurrencyValue, jumlah uang.

Meskipun tidak digunakan oleh server untuk melakukan perbandingan, Currency UDT mengimplementasikan antarmuka System.IComparable, yang mengekspos satu metode, System.IComparable.CompareTo. Ini digunakan di sisi klien dalam situasi di mana diinginkan untuk secara akurat membandingkan atau memesan nilai mata uang dalam budaya.

Kode yang berjalan di CLR membandingkan budaya secara terpisah dari nilai mata uang. Untuk kode Transact-SQL, tindakan berikut menentukan perbandingan:

  1. Atur atribut IsByteOrdered ke true, yang memberi tahu SQL Server untuk menggunakan representasi biner yang dipertahankan pada disk untuk perbandingan.

  2. Gunakan metode Write untuk UDT Currency untuk menentukan bagaimana UDT dipertahankan pada disk dan oleh karena itu bagaimana nilai UDT dibandingkan dan diurutkan untuk operasi Transact-SQL.

  3. Simpan UDT Currency menggunakan format biner berikut:

    1. Simpan budaya sebagai string yang dikodekan UTF-16 untuk byte 0-19 dengan padding ke kanan dengan karakter null.

    2. Gunakan byte 20 ke atas untuk berisi nilai desimal mata uang.

Tujuan dari padding adalah untuk memastikan bahwa budaya benar-benar terpisah dari nilai mata uang, sehingga ketika satu UDT dibandingkan dengan yang lain dalam kode Transact-SQL, byte budaya dibandingkan dengan byte budaya, dan nilai byte mata uang dibandingkan dengan nilai byte mata uang.

Atribut mata uang

UDT Currency didefinisikan dengan atribut berikut.

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

Membuat metode baca dan tulis dengan ibinaryserialize

Saat Anda memilih format serialisasi UserDefined, Anda juga harus menerapkan antarmuka IBinarySerialize dan membuat metode Read dan Write Anda sendiri. Prosedur berikut dari UDT Currency menggunakan System.IO.BinaryReader dan System.IO.BinaryWriter untuk membaca dari dan menulis ke 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();
}

Konten terkait

  • Last updated on