SQL Server 2005 CLR 整合實作：使用 C# 建立自訂 Date 使用者定義型別 (UDT)

SQL Server 2005 引入 CLR 整合，允許使用 .NET 語言 (C#、VB.NET) 建立使用者定義型別 (UDT)，擴充資料庫型別系統。本文完整示範建立 Date 型別 (僅日期、無時間) 的全過程：從 Visual Studio 專案建立、C# 結構實作、部署到資料庫、T-SQL 測試驗證。

前置需求

• Visual Studio 2005/2008/2010+ (含 SQL Server 專案範本)
• SQL Server 2005+ 已啟用 CLR：EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
• 目標資料庫設定 TRUSTWORTHY ON 或簽署組件憑證

步驟 1：建立 SQL Server 專案

1. VS 中 新增專案 → C# → 資料庫 → SQL Server 專案
2. 指定專案名稱 (如 DateUDT)，選擇目標資料庫連線
3. 專案右鍵 → 新增項目 → 使用者定義型別 → 命名 Date.cs

步驟 2：C# 結構完整實作

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

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(
    Format.UserDefined, 
    IsByteOrdered = true, 
    MaxByteSize = 20, 
    ValidationMethodName = "ValidateDate")]
public struct Date : INullable, IBinarySerialize
{
    // 私有欄位
    private bool m_Null;
    private string m_date; // 格式：yyyy-MM-dd

    // INullable 介面
    public bool IsNull => m_Null;

    public static Date Null
    {
        get
        {
            Date d = new Date();
            d.m_Null = true;
            return d;
        }
    }

    // 字串轉換 (SQL Server 呼叫)
    public override string ToString()
    {
        return m_Null ? "NULL" : m_date;
    }

    // Parse: 字串 → UDT (CAST/CONVERT 時呼叫)
    public static Date Parse(SqlString s)
    {
        if (s.IsNull || s.Value == "") return Null;
        
        Date d = new Date();
        string[] parts = s.Value.Split(' ');
        d.m_date = parts[0]; // 取日期部分
        
        if (!d.ValidateDate())
            throw new ArgumentException("無效的日期格式");
        return d;
    }

    // 屬性存取器
    public string Value
    {
        get => m_date;
        set
        {
            m_Null = false;
            m_date = value;
            if (!ValidateDate()) throw new ArgumentException("無效的日期");
        }
    }

    // IBinarySerialize: 序列化 (儲存至資料庫)
    public void Write(System.IO.BinaryWriter w)
    {
        byte header = (byte)(m_Null ? 1 : 0);
        w.Write(header);
        if (header == 1) return;
        w.Write(m_date);
    }

    // 反序列化 (從資料庫讀取)
    public void Read(System.IO.BinaryReader r)
    {
        byte header = r.ReadByte();
        if (header == 1) { m_Null = true; return; }
        m_Null = false;
        m_date = r.ReadString();
    }

    // 驗證日期有效性
    private bool ValidateDate()
    {
        try { DateTime dt = Convert.ToDateTime(m_date); return true; }
        catch { return false; }
    }
}

步驟 3：部署 (F5)

• VS 會自動編譯組件、在資料庫建立組件 (CREATE ASSEMBLY)、建立型別 (CREATE TYPE)
• 輸出視窗確認部署成功

步驟 4：T-SQL 測試驗證

-- 建立測試表，使用 dbo.Date 做欄位型別，預設值為今天
CREATE TABLE tb (
    id INT,
    dt dbo.Date DEFAULT CONVERT(dbo.Date, CONVERT(VARCHAR(10), GETDATE(), 120))
);

-- 插入測試 (不指定 dt，使用預設值)
INSERT INTO tb (id) VALUES (1);

-- 查詢 (需呼叫 ToString() 取得字串)
SELECT id, dt = dt.ToString() FROM tb;
/* 結果：
id  dt
1   2009-08-14
*/

-- 直接 SELECT * 會顯示二進位資料，不可直接讀取
-- SELECT * FROM tb; -- 不要這樣查 dt 欄位

DROP TABLE tb;

重要限制與注意事項

• 無法直接 SELECT *：UDT 欄位直接查詢回傳二進位，必須呼叫 .ToString() 或屬性存取器
• 日期運算不支援：需 ToString() → CAST AS DATETIME → 運算 → 再轉回 UDT
• 索引支援：IsByteOrdered = true 允許建立索引，排序依二進位序 (ISO 日期格式 yyyy-MM-dd 符合字典序)
• 最大長度：MaxByteSize = 20 涵蓋 "yyyy-MM-dd" (10 字元) + Unicode 預留
• 驗證方法：ValidationMethodName 指定的方法會在 INSERT/UPDATE 時自動呼叫

擴充建議

• 加入 AddDays(int)、DiffDays(Date) 等方法 (需標記 [SqlMethod])
• 實作 IComparable 以支援排序比較
• 加入 Year、Month、Day 唯讀屬性
• 支援多種輸入格式解析 (yyyy/MM/dd、yyyy年M月d日 等)

適用版本與替代方案

SQL Server 2012 起內建 DATE、TIME、DATETIME2、DATETIMEOFFSET，新專案請直接使用原生型別，僅維護舊系統才需 CLR UDT。