SQL Server 2005 自訂類型 (UDT) 實作格式化時間完整指南

前言

在 SQL Server 2005 中，使用者定義類型 (User-Defined Type, UDT) 讓開發人員能夠擴展資料庫的型別系統，將自訂的商業邏輯直接封裝在資料型別中。本文將詳細說明如何建立一個能夠自動格式化時間輸入的 UDT，涵蓋從介面實作到部署的完整流程。

UDT 核心需求解析

1. 實作 INullable 介面

所有 UDT 必須實作 System.Data.SqlTypes.INullable 介面，這包含兩個關鍵成員：

• static Null 屬性：回傳代表 Null 值的型別實例
• IsNull 屬性：判斷目前實例是否為 Null

SQL Server 會透過 IsNull 屬性快速識別 Null 值，避免不必要的序列化/反序列化開銷。在 CLR 程式碼中，這也是檢查 Null 值的標準方式。

2. Parse 與 ToString 方法

UDT 必須提供：

• static Parse 方法：從字串解析建立型別實例
• override ToString 方法：將實例轉換為字串表示

這兩個方法是 SQL Server 與 UDT 互動的橋樑，所有資料進出都會經過它們。

3. 序列化機制：IBinarySerialize

當 UDT 包含非實值型別欄位（如 string）時，必須實作 System.Data.IBinarySerialize 介面，自行實作 Read 和 Write 方法，告訴 SQL Server 如何將物件序列化為二進位格式並還原。

完整程式碼實作

以下是一個將輸入時間統一格式化為「短日期 + 短時間」的 UDT 範例：

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize = 4000)]
public struct TypeTime : INullable, IBinarySerialize
{
    #region IBinarySerialize 實作
    public string result;

    public void Read(System.IO.BinaryReader r)
    {
        this.result = r.ReadString();
    }

    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(this.result);
    }
    #endregion

    public override string ToString()
    {
        return result;
    }

    private bool m_Null;
    public bool IsNull
    {
        get { return m_Null; }
    }

    public static TypeTime Null
    {
        get
        {
            TypeTime h = new TypeTime();
            h.m_Null = true;
            return h;
        }
    }

    public static TypeTime Parse(SqlString s)
    {
        if (s.IsNull) return Null;
        TypeTime u = new TypeTime();
        u.result = OneTime(s);
        return u;
    }

    /// 

    /// 統一時間格式：短日期 + 短時間
    /// 
    public static string OneTime(SqlString str)
    {
        DateTime dt = Convert.ToDateTime(str.Value);
        return dt.ToShortDateString() + dt.ToShortTimeString();
    }

    private bool m_Null;
}

關鍵注意事項

1. 雙介面實作：必須同時實作 INullable 和 IBinarySerialize
2. 序列化屬性設定：將預設的 Format.Native 改為 Format.UserDefined，並指定 MaxByteSize
3. SqlString 使用：存取資料庫字串型別時，需使用 .Value 屬性取得 .NET 字串
4. 刪除預設方法：Visual Studio 產生的 Method1、Method2 可移除，換上自訂邏輯

部署與測試

建置專案後，在 SQL Server Management Studio 中執行：

CREATE ASSEMBLY TypeTimeAssembly FROM '路徑\TypeTime.dll' WITH PERMISSION_SET = SAFE;
CREATE TYPE TypeTime EXTERNAL NAME TypeTimeAssembly.[TypeTime];

即可在資料表中使用 TypeTime 型別，所有寫入的時間值都會自動格式化。

總結

UDT 雖然門檻較高，但能將資料驗證與格式化邏輯下沉到資料庫層，確保資料一致性。遇到問題時，冷靜分析需求、參考官方文件與社群經驗，總能找到解決之道。