Image

這週忙著打黑悟空，簡單記一下前陣子同事遇到的一個場景：

由於曆法的關係，SQL Server 的 datetime 範圍只能從 1753-01-01 開始。
如果我們要寫入 0001-01-01 進去的話就會報錯

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

補充：關於曆法的部份，有興趣的朋友可以參考這篇：
淺談：消失的日期，以關聯式資料庫的日期資料類型為例 - 德瑞克：SQL Server 學習筆記

這時候通常去把資料表欄位改成香香的 datetime2 就可以搞定，爽爽寫入 0001-01-01。

但如果我們正在使用 Dapper，預設會把 C# 的 DateTime 映射到 SQL Server 的 datetime，所以還是會寫入失敗。

這時候我們就需要明確地請 Dapper 幫我們使用 datetime2 來進行處理。

如果傳遞的只是某個參數，使用 DbType 就行了：

var parameters = new DynamicParameters();
parameters.Add("@Birthday", birthday, DbType.DateTime2);

但如果傳遞的是某個 Model 裡的特定欄位呢？例如：

public class HistoricalEvent
{
    public Guid EventId { get; set; }       // 歷史事件的唯一識別碼
    public string Description { get; set; } // 歷史事件的描述
    public DateTime EventDate { get; set; } // 歷史事件發生的日期, 可能早於 1753 年！
}

這種時候就可以使用 AddTypeMap 來調整一下 Dapper 的映射型別。

首先確認 DB 的欄位已經修改為 datetime2，接著我們就可以使用 SqlMapper.AddTypeMap 來幫 Dapper 加上一些小小的型別轉換：

// 粗暴地添加 TypeMap，讓 Dapper 知道 DateTime 現在對應到 DB 的 datetime2 了
// 等等 Dapper 才會乖乖地把 0001/1/1 寫進去= =
SqlMapper.AddTypeMap(typeof(DateTime), System.Data.DbType.DateTime2);

// 示範用資料
var historicalEvent = new HistoricalEvent
{
    EventId = Guid.NewGuid(),
    Description = "公曆紀元開始",
    EventDate = DateTime.Parse("0001-01-01")
};

var sql = 
@"
    insert into HistoricalEvents (
        EventId, 
        Description, 
        EventDate)
    values(
        @EventId, 
        @Description, 
        @EventDate); 
";

using (var conn = this.Connection)
{
    var result = await conn.ExecuteAsync(sql, historicalEvent);
    result.Dump();
}

備註：因為我們改的是靜態方法裡的全域設定，所以確定要調整型別映射的話，可以把 AddTypeMap 移動到註冊的地方，例如 Program。

方便後續維護的時候好管理，也避免其他同事不知道你偷改，減少被扁的醫療費用。

此外，雖然微軟把拔也建議改用 dateTime2，但如果你會想先了解這兩者的不同（例如精度）再進行處理，可以參考以下的文章：

大多數時候，我們不太需要寫入 0001-01-01，甚至 1753 年之前的日期
（像是日期尚未決定之類的場合，用 Null 表示會貼切）

但夜路走多了，總會遇到 0001-01-01。像是處理古老的遺留代碼、業務邏輯必須區分「還沒設定」和「沒有資料」的狀況，又或是像上面的例子，西元的開始真的就在那一天，這種時候就沒辦法了。

因此順手筆記下來，以後再回來抄，阿彌陀佛。

延伸閱讀

參考資料