SQL Server CLR 整合：在預存程序中呼叫 C# 組件實現 FileTime 轉換

場景痛點

資料庫儲存 DateTime.ToFileTime() 產生的 long 值（1601 年起始的 100ns 刻度），T-SQL 原生無 FromFileTime 對應函數，導致在預存程序中難以進行年份、季度等時間維度分析。

解決方案：SQL CLR 整合

編寫 C# 組件，註冊至 SQL Server，再透過預存程序封裝呼叫，實現無縫轉換。

1. C# 組件實作

using System;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;

[ComVisible(true)]
[Guid("729BA6AF-3EFF-4B75-B43B-D951A190DBE6")]
public class FileTimeConverter
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static string ConvertFileTime(long fileTime)
    {
        if (fileTime <= 0) return string.Empty;
        try
        {
            DateTime dt = DateTime.FromFileTime(fileTime);
            return dt.ToString("yyyy-MM-dd HH:mm:ss"); // 統一格式
        }
        catch
        {
            return string.Empty;
        }
    }
}

2. 組件註冊與部署

-- 開啟 CLR 功能
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;

-- 建立組件 (需 TRUSTWORTHY 或簽名)
CREATE ASSEMBLY FileTimeUtils
FROM 'C:\Deploy\FileTimeUtils.dll'
WITH PERMISSION_SET = SAFE;

-- 建立標量函數對應
CREATE FUNCTION dbo.ufn_ConvertFileTime(@fileTime BIGINT)
RETURNS NVARCHAR(30)
AS EXTERNAL NAME FileTimeUtils.[FileTimeConverter].ConvertFileTime;

3. 直接在 T-SQL 中使用

-- 單筆轉換
SELECT dbo.ufn_ConvertFileTime(133123456789012345);

-- 批次查詢
SELECT CreateTime, dbo.ufn_ConvertFileTime(CreateTime) AS NormalTime
FROM AuditLog
WHERE CreateTime > 0;

為何優於舊式 sp_OA* 方案？

注意事項

• 目標框架建議 .NET Framework 4.0/4.5/4.8（SQL Server 2012+ 內建）
• 組件標記 PERMISSION_SET = SAFE 限制僅純運算、無外部存取
• 資料庫屬性 TRUSTWORTHY ON 或簽名非對稱金鑰/憑證授權 UNSAFE 組件
• 避免在轉換函式中進行資料庫存取，保持純函式特性