Dapper小技巧:以資料表保存集合物件JSON

專案常遇到的需求:為指定資料保留修改歷程,以備稽核檢查或追查責任之用,使用機率不高且無統計或隨興查詢需求,不值得另開資料表。此時我偏好的做法是定義成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>>());
歡迎推文分享:
Published 18 July 2016 12:57 AM 由 Jeffrey
Filed under:
Views: 4,063



意見

沒有意見

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<July 2016>
SunMonTueWedThuFriSat
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456
 
RSS
創用 CC 授權條款
【廣告】
twMVC

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication