public static class DemoExtension
{
public static IEnumerable<dynamic> Query(this IDbConnection cnn, string sql)
{
using (var command = cnn.CreateCommand())
{
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
yield return reader.CastToDynamic();
}
}
}
}
private static dynamic CastToDynamic(this IDataReader reader)
{
dynamic e = new ExpandoObject();
var d = e as IDictionary<string,object>;
for (int i = 0; i < reader.FieldCount; i++)
d.Add(reader.GetName(i),reader[i]);
return e;
}
}
using (var cn = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=SSPI;Initial Catalog=master;"))
{
var result = cn.Query("select N'暐翰' Name,26 Age").First();
Console.WriteLine(result.Name);
}
private class DapperTable{/*略*/}
private class DapperRow :IDictionary<string, object>, IReadOnlyDictionary<string, object>,System.Dynamic.IDynamicMetaObjectProvider{/*略*/}
class Program
{
static void Main(string[] args)
{
using (var cn = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=SSPI;Initial Catalog=master;"))
{
var result = cn.Query<User>("select N'暐翰' Name , 25 Age").First();
Console.WriteLine(result.Name);
Console.WriteLine(result.Age);
}
}
}
public class User
{
public string Name { get; set; }
public int Age { get; set; }
}
要了解這段IL之前需要先了解ADO.NET DataReader快速讀取資料方式會使用GetItem By Index方式,如以下代碼
public static class DemoExtension
{
private static User CastToUser(this IDataReader reader)
{
var user = new User();
var value = reader[0];
if(!(value is System.DBNull))
user.Name = (string)value;
var value = reader[1];
if(!(value is System.DBNull))
user.Age = (int)value;
return user;
}
public static IEnumerable<User> Query<T>(this IDbConnection cnn, string sql)
{
if (cnn.State == ConnectionState.Closed) cnn.Open();
using (var command = cnn.CreateCommand())
{
command.CommandText = sql;
using (var reader = command.ExecuteReader())
while (reader.Read())
yield return reader.CastToUser();
}
}
}
public static class DemoExtension
{
public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql) where T : new()
{
using (var command = cnn.CreateCommand())
{
command.CommandText = sql;
using (var reader = command.ExecuteReader())
while (reader.Read())
yield return reader.CastToType<T>();
}
}
//1.使用泛型傳遞動態類別
private static T CastToType<T>(this IDataReader reader) where T : new()
{
//2.使用泛型的條件約束new()達到動態建立物件
var instance = new T();
//3.DataReader需要使用屬性字串名稱當Key,可以使用Reflection取得動態類別的屬性名稱,在藉由DataReader this[string parameter]取得數據庫資料
var type = typeof(T);
var props = type.GetProperties();
foreach (var p in props)
{
var val = reader[p.Name];
//4.使用PropertyInfo.SetValue方式動態將數據庫資料賦予物件
if( !(val is System.DBNull) )
p.SetValue(instance, val);
}
return instance;
}
}
User Class的Name屬性對應Reader Index 0 、類別是String 、 預設值是null
User Class的Age屬性對應Reader Index 1 、類別是int 、 預設值是0
void Main()
{
using (var cn = Connection)
{
var result = cn.Query<User>("select N'暐翰' Name,26 Age").First();
}
}
class User
{
public string Name { get; set; }
public int Age { get; set; }
}
假如系統能幫忙生成以下邏輯方法,那麼效率會是最好的
User 動態方法(IDataReader reader)
{
var user = new User();
var value = reader[0];
if( !(value is System.DBNull) )
user.Name = (string)value;
value = reader[1];
if( !(value is System.DBNull) )
user.Age = (int)value;
return user;
}
User 動態方法(IDataReader reader)
{
var user = new User();
var value = reader[0];
if( !(value is System.DBNull) )
user.Name = (string)value;
value = reader[1];
if( !(value is System.DBNull) )
user.Age = (int)value;
return user;
}
最後得出以下Exprssion版本代碼
public static class DemoExtension
{
public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql) where T : new()
{
using (var command = cnn.CreateCommand())
{
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
var func = CreateMappingFunction(reader, typeof(T));
while (reader.Read())
{
var result = func(reader as DbDataReader);
yield return result is T ? (T)result : default(T);
}
}
}
}
private static Func<DbDataReader, object> CreateMappingFunction(IDataReader reader, Type type)
{
//1. 取得sql select所有欄位名稱
var names = Enumerable.Range(0, reader.FieldCount).Select(index => reader.GetName(index)).ToArray();
//2. 取得mapping類別的屬性資料 > 將index,sql欄位,class屬性資料做好對應封裝在一個變量內方便後面使用
var props = type.GetProperties().ToList();
var members = names.Select((columnName, index) =>
{
var property = props.Find(p => string.Equals(p.Name, columnName, StringComparison.Ordinal))
?? props.Find(p => string.Equals(p.Name, columnName, StringComparison.OrdinalIgnoreCase));
return new
{
index,
columnName,
property
};
});
//3. 動態建立方法 : 從數據庫Reader按照順序讀取我們要的資料
/*方法邏輯 :
User 動態方法(IDataReader reader)
{
var user = new User();
var value = reader[0];
if( !(value is System.DBNull) )
user.Name = (string)value;
value = reader[1];
if( !(value is System.DBNull) )
user.Age = (int)value;
return user;
}
*/
var exBodys = new List<Expression>();
{
// 方法(IDataReader reader)
var exParam = Expression.Parameter(typeof(DbDataReader), "reader");
// Mapping類別 物件 = new Mapping類別();
var exVar = Expression.Variable(type, "mappingObj");
var exNew = Expression.New(type);
{
exBodys.Add(Expression.Assign(exVar, exNew));
}
// var value = defalut(object);
var exValueVar = Expression.Variable(typeof(object), "value");
{
exBodys.Add(Expression.Assign(exValueVar, Expression.Constant(null)));
}
var getItemMethod = typeof(DbDataReader).GetMethods().Where(w => w.Name == "get_Item")
.First(w => w.GetParameters().First().ParameterType == typeof(int));
foreach (var m in members)
{
//reader[0]
var exCall = Expression.Call(
exParam, getItemMethod,
Expression.Constant(m.index)
);
// value = reader[0];
exBodys.Add(Expression.Assign(exValueVar, exCall));
//user.Name = (string)value;
var exProp = Expression.Property(exVar, m.property.Name);
var exConvert = Expression.Convert(exValueVar, m.property.PropertyType); //(string)value
var exPropAssign = Expression.Assign(exProp, exConvert);
//if ( !(value is System.DBNull))
// (string)value
var exIfThenElse = Expression.IfThen(
Expression.Not(Expression.TypeIs(exValueVar, typeof(System.DBNull)))
, exPropAssign
);
exBodys.Add(exIfThenElse);
}
// return user;
exBodys.Add(exVar);
// Compiler Expression
var lambda = Expression.Lambda<Func<DbDataReader, object>>(
Expression.Block(
new[] { exVar, exValueVar },
exBodys
), exParam
);
return lambda.Compile();
}
}
}
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];
if (!(obj is DBNull))
{
user2.Name = (string)obj;
}
index = 1;
object obj2 = value = P_0[1];
if (!(obj2 is DBNull))
{
user2.Age = (int)obj2;
}
user = user2;
return user;
}
catch (Exception ex)
{
SqlMapper.ThrowDataException(ex, index, P_0, value);
return user;
}
}
有了C#代碼後再來了解Emit邏輯會快很多,接著就可以進到Emit版本Query實作部分。
9.Strongly Typed Mapping 原理 Part6 : Emit版本
以下代碼是Emit版本,我把C#對應IL部分都寫在註解。
public static class DemoExtension
{
public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql) where T : new()
{
using (var command = cnn.CreateCommand())
{
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
var func = GetTypeDeserializerImpl(typeof(T), reader);
while (reader.Read())
{
var result = func(reader as DbDataReader);
yield return result is T ? (T)result : default(T);
}
}
}
}
private static Func<DbDataReader, object> GetTypeDeserializerImpl(Type type, IDataReader reader, int startBound = 0, int length = -1, bool returnNullIfFirstMissing = false)
{
var returnType = type.IsValueType ? typeof(object) : type;
var dm = new DynamicMethod("Deserialize" + Guid.NewGuid().ToString(), returnType, new[] { typeof(IDataReader) }, type, true);
var il = dm.GetILGenerator();
//C# : User user = new User();
//IL :
//IL_0001: newobj
//IL_0006: stloc.0
var constructor = returnType.GetConstructors(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic)[0]; //這邊簡化成只會有預設constructor
il.Emit(OpCodes.Newobj, constructor);
var returnValueLocal = il.DeclareLocal(type);
il.Emit(OpCodes.Stloc, returnValueLocal); //User user = new User();
// C# :
//object value = default(object);
// IL :
//IL_0007: ldnull
//IL_0008: stloc.1 // value
var valueLoacl = il.DeclareLocal(typeof(object));
il.Emit(OpCodes.Ldnull);
il.Emit(OpCodes.Stloc, valueLoacl);
int index = startBound;
var getItem = typeof(IDataRecord).GetProperties(BindingFlags.Instance | BindingFlags.Public)
.Where(p => p.GetIndexParameters().Length > 0 && p.GetIndexParameters()[0].ParameterType == typeof(int))
.Select(p => p.GetGetMethod()).First();
foreach (var p in type.GetProperties())
{
//C# : value = P_0[0];
//IL:
//IL_0009: ldarg.0
//IL_000A: ldc.i4.0
//IL_000B: callvirt System.Data.IDataRecord.get_Item
//IL_0010: stloc.1 // value
il.Emit(OpCodes.Ldarg_0); //取得reader參數
EmitInt32(il, index);
il.Emit(OpCodes.Callvirt, getItem);
il.Emit(OpCodes.Stloc, valueLoacl);
//C#: if (!(value is DBNull)) user.Name = (string)value;
//IL:
// IL_0011: ldloc.1 // value
// IL_0012: isinst System.DBNull
// IL_0017: ldnull
// IL_0018: cgt.un
// IL_001A: ldc.i4.0
// IL_001B: ceq
// IL_001D: stloc.2
// IL_001E: ldloc.2
// IL_001F: brfalse.s IL_002E
// IL_0021: ldloc.0 // user
// IL_0022: ldloc.1 // value
// IL_0023: castclass System.String
// IL_0028: callvirt UserQuery+User.set_Name
il.Emit(OpCodes.Ldloc, valueLoacl);
il.Emit(OpCodes.Isinst, typeof(System.DBNull));
il.Emit(OpCodes.Ldnull);
var tmpLoacl = il.DeclareLocal(typeof(int));
il.Emit(OpCodes.Cgt_Un);
il.Emit(OpCodes.Ldc_I4_0);
il.Emit(OpCodes.Ceq);
il.Emit(OpCodes.Stloc,tmpLoacl);
il.Emit(OpCodes.Ldloc,tmpLoacl);
var labelFalse = il.DefineLabel();
il.Emit(OpCodes.Brfalse_S,labelFalse);
il.Emit(OpCodes.Ldloc, returnValueLocal);
il.Emit(OpCodes.Ldloc, valueLoacl);
if (p.PropertyType.IsValueType)
il.Emit(OpCodes.Unbox_Any, p.PropertyType);
else
il.Emit(OpCodes.Castclass, p.PropertyType);
il.Emit(OpCodes.Callvirt, p.SetMethod);
il.MarkLabel(labelFalse);
index++;
}
// IL_0053: ldloc.0 // user
// IL_0054: stloc.s 04 //不需要
// IL_0056: br.s IL_0058
// IL_0058: ldloc.s 04 //不需要
// IL_005A: ret
il.Emit(OpCodes.Ldloc, returnValueLocal);
il.Emit(OpCodes.Ret);
var funcType = System.Linq.Expressions.Expression.GetFuncType(typeof(IDataReader), returnType);
return (Func<IDataReader, object>)dm.CreateDelegate(funcType);
}
private static void EmitInt32(ILGenerator il, int value)
{
switch (value)
{
case -1: il.Emit(OpCodes.Ldc_I4_M1); break;
case 0: il.Emit(OpCodes.Ldc_I4_0); break;
case 1: il.Emit(OpCodes.Ldc_I4_1); break;
case 2: il.Emit(OpCodes.Ldc_I4_2); break;
case 3: il.Emit(OpCodes.Ldc_I4_3); break;
case 4: il.Emit(OpCodes.Ldc_I4_4); break;
case 5: il.Emit(OpCodes.Ldc_I4_5); break;
case 6: il.Emit(OpCodes.Ldc_I4_6); break;
case 7: il.Emit(OpCodes.Ldc_I4_7); break;
case 8: il.Emit(OpCodes.Ldc_I4_8); break;
default:
if (value >= -128 && value <= 127)
{
il.Emit(OpCodes.Ldc_I4_S, (sbyte)value);
}
else
{
il.Emit(OpCodes.Ldc_I4, value);
}
break;
}
}
}
using (var cn = new SqlConnection(@"connectionString"))
{
for (int i = 0; i < 999999; i++)
{
var guid = Guid.NewGuid();
for (int i2 = 0; i2 < 2; i2++)
{
var result = cn.Query<User>($"select '{guid}' ").First();
}
}
}
using (var cn = new SqlConnection(@"connectionString"))
{
for (int i = 0; i < 999999; i++)
{
var guid = Guid.NewGuid();
for (int i2 = 0; i2 < 2; i2++)
{
var result = cn.Query<User>($"select @guid ",new { guid}).First();
}
}
}
public static class DbExtension
{
public static IEnumerable<User> Query(this DbConnection cnn, string sql, User parameter)
{
using (var command = cnn.CreateCommand())
{
command.CommandText = sql;
CommandLiteralReplace(command, parameter);
using (var reader = command.ExecuteReader())
while (reader.Read())
yield return Mapping(reader);
}
}
private static void CommandLiteralReplace(IDbCommand cmd, User parameter)
{
cmd.CommandText = cmd.CommandText.Replace("{=VipLevel}", parameter.VipLevel.ToString(System.Globalization.CultureInfo.InvariantCulture));
}
private static User Mapping(IDataReader reader)
{
var user = new User();
var value = default(object);
value = reader[0];
if(!(value is System.DBNull))
user.Name = (string)value;
value = reader[1];
if (!(value is System.DBNull))
user.Age = (int)value;
value = reader[2];
if (!(value is System.DBNull))
user.VipLevel = (int)value;
return user;
}
}
void Main()
{
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]([ID] int, [Name] nvarchar(10));
INSERT INTO [User]([ID], [Name])VALUES(1, N'大雄'),(2, N'小明');
CREATE TABLE [Order]([ID] int, [OrderNo] varchar(13), [UserID] int);
INSERT INTO [Order]([ID], [OrderNo], [UserID])VALUES(1, 'SO20190900001', 1),(2, 'SO20190900002', 1),(3, 'SO20190900003', 2),(4, 'SO20190900004', 2);
");
var result = cn.Query<Order,User,Order>(@"
select * from [order] T1
left join [User] T2 on T1.UserId = T2.ID
", (order, user) => {
order.User = user;
return order;
}
);
ts.Dispose();
}
}
public class Order
{
public int ID { get; set; }
public string OrderNo { get; set; }
public User User { get; set; }
}
public class User
{
public int ID { get; set; }
public string Name { get; set; }
}
支援dynamic Multi Mapping
在初期常變動表格結構或是一次性功能不想宣告Class,Dapper Multi Mapping也支援dynamic方式
void Main()
{
using (var ts = new TransactionScope())
using (var connection = Connection)
{
const string createSql = @"
create table Users (Id int, Name nvarchar(20))
create table Posts (Id int, OwnerId int, Content nvarchar(20))
insert Users values(1, N'小明')
insert Users values(2, N'小智')
insert Posts values(101, 1, N'小明第1天日記')
insert Posts values(102, 1, N'小明第2天日記')
insert Posts values(103, 2, N'小智第1天日記')
";
connection.Execute(createSql);
const string sql =
@"select * from Posts p
left join Users u on u.Id = p.OwnerId
Order by p.Id
";
var data = connection.Query<dynamic, dynamic, dynamic>(sql, (post, user) => { post.Owner = user; return post; }).ToList();
}
}
SplitOn區分類別Mapping組別
var result = cn.Query<Order,User,Order>(@"
select * from [order] T1
left join [User] T2 on T1.UserId = T2.ID
", (order, user) => {
order.User = user;
return order;
}
);
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; }
}
if (handler != null)
{
il.Emit(OpCodes.Call, typeof(TypeHandlerCache<>).MakeGenericType(prop.PropertyType).GetMethod(nameof(TypeHandlerCache<int>.SetValue))); // stack is now [parameters] [[parameters]] [parameter]
}
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;
}
}
var result = cn.Query(@"select * from sys.objects where type_desc In @type_descs and name like @name"
, new { type_descs = new[] { "USER_TABLE", "VIEW" }, @name = "order%" });
using (var cn = Connection)
{
var paramters = new DynamicParameters();
paramters.Add("Name","John",DbType.AnsiString,size:4);
paramters.Add("Age",25,DbType.Int32);
var result = cn.Query("select @Name Name,@Age Age", paramters).First();
}
using (var cn = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=SSPI;Initial Catalog=Northwind;"))
{
cn.Open();
using (var tx = cn.BeginTransaction())
{
cn.Execute("create table #T (V int);", transaction: tx);
cn.Execute("insert into #T (V) values (@V)", Enumerable.Range(1, 10).Select(val => new { V = val }).ToArray() , transaction:tx);
var result = cn.Query("select * from #T", transaction: tx);
Console.WriteLine(result);
}
}
