date time comments 2 pageviews

專案常遇到的需求：為指定資料保留修改歷程，以備稽核檢查或追查責任之用，使用機率不高且無統計或隨興查詢需求，不值得另開資料表。此時我偏好的做法是定義成List<HistoryRecord>，在資料表開一個NVARCHAR(MAX)保存其JSON內容，調閱時讀取JSON反序列化還原內容，足以滿足規格所需。

用個實例說明，假設資料物件定義如下：

public class HistoryRecord
{
    public DateTime Time { get; set; }
    public string User { get; set; }
    public string Remark { get; set; }
}
 
public class ProjectItem
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<HistoryRecord> History { get; set; }
}

資料表設計如下：

CREATE TABLE [dbo].[ProjectItem] (
    [Id]      INT            NOT NULL,
    [Name]    NVARCHAR (64)  NULL,
    [History] NVARCHAR (MAX) NULL
);

資料庫存取部分我主要用Dapper實作，但問題來了，試著將List<HistoryRecord>當成History欄位的輸入參數：

var kernel = new ProjectItem()
{
    Id = 1,
    Name = "KernelModule",
    History = new List<HistoryRecord>()
    {
        new HistoryRecord()
        {
            Time = new DateTime(2016, 7, 1),
            User = "Jeffrey",
            Remark = "Initial version"
        },
        new HistoryRecord()
        {
            Time = new DateTime(2016,7,11),
            User = "Jeffrey",
            Remark = "Refactoring"
        }
    }
    
};
cn.Execute("INSERT INTO ProjectItem VALUES(@Id, @Name, @History)", kernel);

卻冒出錯誤：The member  of type DapperLab.Program+HistoryRecord cannot be used as a parameter value。Dapper不知道怎麼將List<HistoryRecord>轉成可以存入資料庫的內容。

針對這類需求，Dapper的解決方案是開放開發者自訂TypeHandler，指定型別該如何對應資料庫內容。實作方法很簡單，宣告一個類別繼承SqlMapper.TypeHandler<T>，提供兩個函式：Parse<T>()函式負責將資料庫內容轉成該型別，SetValue()函式將型別轉型後指定給IDbDataParameter.Value：

public class HistoryRecordListHandler : SqlMapper.TypeHandler<List<HistoryRecord>>
{
    public override List<HistoryRecord> Parse(object value)
    {
        return JsonConvert.DeserializeObject<List<HistoryRecord>>((string)value);
    }
 
    public override void SetValue(IDbDataParameter parameter, List<HistoryRecord> value)
    {
        parameter.Value = JsonConvert.SerializeObject(value);
    }
}

接著，在執行cn.Execute()之前先透過SqlMapper.AddTypeHandler()註冊，指定由HistoryRecordListHandler負責處理List<HistoryRecord>資料轉換：
SqlMapper.AddTypeHandler<List<HistoryRecord>>(new HistoryRecordListHandler());

如此，Dapper就會將Historyh屬性JSON後寫入資料表，讀取時也能正確由JSON還原回List<HistoryRecord>，大功告成！

以上寫法可以再改良，HistoryRecordListHandler的核心邏輯可抽取成泛型，適用於所有要JSON化存入資料庫的型別，省去為每個要轉JSON型別撰寫專屬TypeConverter的困擾。

public class JsonConvertHandler<T> : SqlMapper.TypeHandler<T>
{
    public override T Parse(object value)
    {
        return JsonConvert.DeserializeObject<T>((string)value);
    }
 
    public override void SetValue(IDbDataParameter parameter, T value)
    {
        parameter.Value = JsonConvert.SerializeObject(value);
    }
}
 
//...略...
 
SqlMapper.AddTypeHandler<List<HistoryRecord>>(new JsonConvertHandler<List<HistoryRecord>>());

Comments

# by 456

123456789

# by icejd

666

Post a comment