遇到想要客製某些屬性Mapping邏輯時,在Dapper可以使用TypeHandler
使用方式 :
SqlMapper.TypeHandler
泛型
,e.g : JsonTypeHandler<客製類別> : SqlMapper.TypeHandler<客製類別>
查詢
的邏輯使用override實作Parse
方法,增刪改
邏輯實作SetValue
方法泛型
方式,客製類別在AddTypeHandler
時指定就可以,可以避免建立一堆類別,e.g : JsonTypeHandler<T> : SqlMapper.TypeHandler<T> where T : class
舉例 :
想要特定屬性成員在資料庫保存Json,在AP端自動轉成對應Class類別,這時候可以使用SqlMapper.AddTypeHandler<繼承實作TypeHandler的類別>
。
以下例子是User資料變更時會自動在Log欄位紀錄變更動作。
public class JsonTypeHandler<T> : SqlMapper.TypeHandler<T>
where T : class
{
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);
}
}
public void Main()
{
SqlMapper.AddTypeHandler(new JsonTypeHandler<List<Log>>());
using (var ts = new TransactionScope())
using (var cn = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=SSPI;Initial Catalog=master;"))
{
cn.Execute("create table [User] (Name nvarchar(200),Age int,Level int,Logs nvarchar(max))");
var user = new User()
{
Name = "暐翰",
Age = 26,
Level = 1,
Logs = new List<Log>() {
new Log(){Time=DateTime.Now,Remark="CreateUser"}
}
};
//新增資料
{
cn.Execute("insert into [User] (Name,Age,Level,Logs) values (@Name,@Age,@Level,@Logs);", user);
var result = cn.Query("select * from [User]");
Console.WriteLine(result);
}
//升級Level動作
{
user.Level = 9;
user.Logs.Add(new Log() {Remark="UpdateLevel"});
cn.Execute("update [User] set Level = @Level,Logs = @Logs where Name = @Name", user);
var result = cn.Query("select * from [User]");
Console.WriteLine(result);
}
ts.Dispose();
}
}
public class User
{
public string Name { get; set; }
public int Age { get; set; }
public int Level { get; set; }
public List<Log> Logs { get; set; }
}
public class Log
{
public DateTime Time { get; set; } = DateTime.Now;
public string Remark { get; set; }
}
效果圖 :
接著追蹤TypeHandler源碼邏輯,需要分兩個部份來追蹤 : SetValue,Parse
if (handler != null)
{
il.Emit(OpCodes.Call, typeof(TypeHandlerCache<>).MakeGenericType(prop.PropertyType).GetMethod(nameof(TypeHandlerCache<int>.SetValue))); // stack is now [parameters] [[parameters]] [parameter]
}
最後查看IL轉成的C#代碼
public static void TestMeThod(IDbCommand P_0, object P_1)
{
User user = (User)P_1;
IDataParameterCollection parameters = P_0.Parameters;
//略...
IDbDataParameter dbDataParameter3 = P_0.CreateParameter();
dbDataParameter3.ParameterName = "Logs";
dbDataParameter3.Direction = ParameterDirection.Input;
SqlMapper.TypeHandlerCache<List<Log>>.SetValue(dbDataParameter3, ((object)user.Logs) ?? ((object)DBNull.Value));
parameters.Add(dbDataParameter3);
//略...
}
可以發現生成的Emit IL會去從TypeHandlerCache取得我們實作的TypeHandler,接著呼叫實作SetValue方法
運行設定的邏輯,並且TypeHandlerCache特別使用泛型類別
依照不同泛型以Singleton
方式保存不同handler,這樣有以下優點 :
避免重複建立物件
提升效率
主要邏輯是在GenerateDeserializerFromMap方法Emit建立動態Mapping方法時,假如判斷TypeHandler緩存有資料,以Parse方法取代原本的Set屬性動作。
查看動態Mapping方法生成的IL代碼 :
IL_0000: ldc.i4.0
IL_0001: stloc.0
IL_0002: newobj Void .ctor()/Demo.User
IL_0007: stloc.1
IL_0008: ldloc.1
IL_0009: dup
IL_000a: ldc.i4.0
IL_000b: stloc.0
IL_000c: ldarg.0
IL_000d: ldc.i4.0
IL_000e: callvirt System.Object get_Item(Int32)/System.Data.IDataRecord
IL_0013: dup
IL_0014: stloc.2
IL_0015: dup
IL_0016: isinst System.DBNull
IL_001b: brtrue.s IL_0029
IL_001d: unbox.any System.String
IL_0022: callvirt Void set_Name(System.String)/Demo.User
IL_0027: br.s IL_002b
IL_0029: pop
IL_002a: pop
IL_002b: dup
IL_002c: ldc.i4.1
IL_002d: stloc.0
IL_002e: ldarg.0
IL_002f: ldc.i4.1
IL_0030: callvirt System.Object get_Item(Int32)/System.Data.IDataRecord
IL_0035: dup
IL_0036: stloc.2
IL_0037: dup
IL_0038: isinst System.DBNull
IL_003d: brtrue.s IL_004b
IL_003f: unbox.any System.Int32
IL_0044: callvirt Void set_Age(Int32)/Demo.User
IL_0049: br.s IL_004d
IL_004b: pop
IL_004c: pop
IL_004d: dup
IL_004e: ldc.i4.2
IL_004f: stloc.0
IL_0050: ldarg.0
IL_0051: ldc.i4.2
IL_0052: callvirt System.Object get_Item(Int32)/System.Data.IDataRecord
IL_0057: dup
IL_0058: stloc.2
IL_0059: dup
IL_005a: isinst System.DBNull
IL_005f: brtrue.s IL_006d
IL_0061: unbox.any System.Int32
IL_0066: callvirt Void set_Level(Int32)/Demo.User
IL_006b: br.s IL_006f
IL_006d: pop
IL_006e: pop
IL_006f: dup
IL_0070: ldc.i4.3
IL_0071: stloc.0
IL_0072: ldarg.0
IL_0073: ldc.i4.3
IL_0074: callvirt System.Object get_Item(Int32)/System.Data.IDataRecord
IL_0079: dup
IL_007a: stloc.2
IL_007b: dup
IL_007c: isinst System.DBNull
IL_0081: brtrue.s IL_008f
IL_0083: call System.Collections.Generic.List`1[Demo.Log] Parse(System.Object)/Dapper.SqlMapper+TypeHandlerCache`1[System.Collections.Generic.List`1[Demo.Log]]
IL_0088: callvirt Void set_Logs(System.Collections.Generic.List`1[Demo.Log])/Demo.User
IL_008d: br.s IL_0091
IL_008f: pop
IL_0090: pop
IL_0091: stloc.1
IL_0092: leave IL_00a4
IL_0097: ldloc.0
IL_0098: ldarg.0
IL_0099: ldloc.2
IL_009a: call Void ThrowDataException(System.Exception, Int32, System.Data.IDataReader, System.Object)/Dapper.SqlMapper
IL_009f: leave IL_00a4
IL_00a4: ldloc.1
IL_00a5: ret
轉成C#代碼來驗證 :
public static User TestMeThod(IDataReader P_0)
{
int index = 0;
User user = new User();
object value = default(object);
try
{
User user2 = user;
index = 0;
object obj = value = P_0[0];
//..略
index = 3;
object obj4 = value = P_0[3];
if (!(obj4 is DBNull))
{
user2.Logs = SqlMapper.TypeHandlerCache<List<Log>>.Parse(obj4);
}
user = user2;
return user;
}
catch (Exception ex)
{
SqlMapper.ThrowDataException(ex, index, P_0, value);
return user;
}
}