Dapper小技巧:以資料表保存集合物件JSON
2 |
專案常遇到的需求:為指定資料保留修改歷程,以備稽核檢查或追查責任之用,使用機率不高且無統計或隨興查詢需求,不值得另開資料表。此時我偏好的做法是定義成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