对Dapper的一点改造

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

微软推出的ORM，

EF在我开发的项目中给我的感觉一直都是慢.优点是高度封装的底层.便于开发.

Dapper在多篇性能比较的网站中.都是名列前三.缺点是手写SQL,不便于开发.

如果能结合EF的优点和Dapper的优点.

那么此次改造相比原生Dapper是脱胎换骨的.

上图是Dapper最简单的语法.

相比ADO.NET.只增加了输出结果的序列化(LIst<T>)和输入结果的封装(List<T>).

上图是EF6.0最简单的查询.

完全封装了底层.

只要写写语法简单的lamade表达式就能完成大部分开发.

在此我将结合EF的一部分优点(lambda)和Dapper的一部分优点(性能).

做一点升级

public class DBHelper
{
//private volatile static SqlConnection _instance = null;
//private static readonly object lockHelper = new object();
//private DBHelper() { }
public static SqlConnection CreateConnection()
{
//if (_instance == null)
//{
// lock (lockHelper)
// {
// if (_instance == null)
// {
string connStr = ConfigurationManager.AppSettings["SqlConnStr"];
SqlConnection conn = new SqlConnection(connStr);
//_instance = conn;
// }
// }
//}
//string connStr = ConfigurationManager.AppSettings["SqlConnStr"];
//SqlConnection conn = new SqlConnection(connStr);
return conn;
}
/// <summary>
/// 单个数据集查询
/// </summary>
/// <param name="sql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static List<TEntity> Query<TEntity>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
{
using (IDbConnection conn = CreateConnection())
{
return conn.Query<TEntity>(sql, param, transaction, buffered, commandTimeout, commandType).Distinct().ToList();
}
}
/// <summary>
/// 执行增、删、改方法
/// </summary>
/// <param name="sql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static int Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
{
using (IDbConnection conn = CreateConnection())
{
return conn.Execute(sql, param, transaction, commandTimeout, commandType);
}
}
}

以上是底层加入了垃圾回收的Dapper.

using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;namespace Microsofot.Core
{
/// <summary>
/// Lambda帮助
/// !item.IsDeleted ===> item.IsDeleted == false
/// menuIds.Contains(item.Id) + new { @Id = menuIds }
/// 请不要传递IEnumerable<TSource>因为不认识
/// </summary>
public static class LambdaHelper
{/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="func"></param>
/// <returns></returns>
public static string Query<T>(Expression<Func<T, bool>> func) where T : class
{
string res = string.Empty;
if (func.Body is BinaryExpression)
{
BinaryExpression be = ((BinaryExpression)func.Body);
res = BinarExpressionProvider(be.Left, be.Right, be.NodeType);
}
else if (func.Body is MethodCallExpression)
{
MethodCallExpression be = ((MethodCallExpression)func.Body);
res = ExpressionRouter(func.Body);
}
else
{
res = string.Empty;
}
var a1 = func.GetType().FullName;
var a2 = a1.Split('.')[];
var a3 = a2.Split(',')[];
res = "select * from " + a3 + " where" + res;
return res;
}/// <summary>
/// 插入(支持单条和批量)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public static string Insert<T>(T model)
{
var sql = "insert into " + model.GetType().Name + " values (";
var dic = GetProperties(model);for (int i = ; i < dic.Count; i++)
{
if (i == )
{
//跳过主键
continue;
}if (dic.Count - > i)
{
sql += "@" + dic[i] + ",";
}if (dic.Count - == i)
{
sql += "@" + dic[i] + ")";
}
}
return sql;
}/// <summary>
/// 修改(支持单条和批量)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public static string Update<T>(T model)
{
var sql = "update " + model.GetType().Name + " set ";var dic = new Dictionary<object, object>();
if (model == null) { return null; }
PropertyInfo[] properties = model.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
if (properties.Length <= ) { return null; }
int a = ;
foreach (PropertyInfo item in properties)
{
string name = item.Name;
object value = item.GetValue(model, null);
if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
{
dic.Add(a, name+","+ value);
a++;
}
}for (int i = ; i < dic.Count; i++)
{
var split = dic[i].ToString().Split(',');
if (i == )
{
//跳过主键
continue;
}if (dic.Count - > i)
{
//非最后一个字段拼接sql加逗号
if (!string.IsNullOrWhiteSpace(split[]))
{
sql += split[] + "=@" + split[] + ",";
}}if (dic.Count - == i)
{
var id = dic[].ToString().Split(',');
if (!string.IsNullOrWhiteSpace(split[]))
{
sql += split[] + "=@" + split[];
}sql += " where "+ id[] + "=@" + id[];
}
}
return sql;
}/// <summary>
/// 逻辑删除(支持单条和批量)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public static string Delete<T>(T model)
{
var sql = "update " + model.GetType().Name + " set ";var dic = new Dictionary<object, object>();
if (model == null) { return null; }
PropertyInfo[] properties = model.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
if (properties.Length <= ) { return null; }
int a = ;
foreach (PropertyInfo item in properties)
{
string name = item.Name;
object value = item.GetValue(model, null);
if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
{
dic.Add(a, name + "," + value);
a++;
}
}
var id = dic[].ToString().Split(',');sql += " IsDeleted='true'";
sql += " where " + id[] + "=@" + id[];return sql;
}/// <summary>
/// 排序
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="exp"></param>
/// <param name="desc">true顺序false倒序</param>
/// <returns></returns>
public static string Order<T>(Expression<Func<T, object>> exp,bool desc) where T : class
{
string orderby = " order by";
var res = string.Empty;
if (exp.Body is UnaryExpression)
{
UnaryExpression ue = ((UnaryExpression)exp.Body);
res = ExpressionRouter(ue.Operand).ToLower() ;
}
else
{
MemberExpression order = ((MemberExpression)exp.Body);
res = order.Member.Name.ToLower() ;
}
if (desc)
{
res = $"{orderby} '{res}'";
}
else
{
res = $"{orderby} '{res}' desc";
}return res;
}private static string GetValueStringByType(object oj)
{
if (oj == null)
{
return "null";
}
else if (oj is ValueType)
{
return oj.ToString();
}
else if (oj is string || oj is DateTime || oj is char)
{
return string.Format("'{0}'", oj.ToString());
}
else
{
return string.Format("'{0}'", oj.ToString());
}
}private static string BinarExpressionProvider(Expression left, Expression right, ExpressionType type)
{
string sb = "(";
//先处理左边
string reLeftStr = ExpressionRouter(left);
sb += reLeftStr;sb += ExpressionTypeCast(type);//再处理右边
string tmpStr = ExpressionRouter(right);
if (tmpStr == "null")
{
if (sb.EndsWith(" ="))
{
sb = sb.Substring(, sb.Length - ) + " is null";
}
else if (sb.EndsWith("<>"))
{
sb = sb.Substring(, sb.Length - ) + " is not null";
}
}
else
{
//添加参数
sb += tmpStr;
}return sb += ")";
}private static string ExpressionRouter(Expression exp)
{
string sb = string.Empty;if (exp is BinaryExpression)
{
BinaryExpression be = ((BinaryExpression)exp);
return BinarExpressionProvider(be.Left, be.Right, be.NodeType);
}
else if (exp is MemberExpression)
{
MemberExpression me = ((MemberExpression)exp);
if (!exp.ToString().StartsWith("value"))
{
return me.Member.Name;
}
else
{
var result = Expression.Lambda(exp).Compile().DynamicInvoke();
if (result == null)
{
return "null";
}
else if (result is ValueType)
{
//return Convert.ToBoolean(result) ? "1" : "0";
return Convert.ToString(result);
}
else if (result is string || result is DateTime || result is char)
{
return string.Format("'{0}'", result.ToString());
}
else if (result is int[])
{
var rl = result as int[];
StringBuilder sbIntStr = new StringBuilder();
sbIntStr.Append("(");
foreach (var r in rl)
{
if (sbIntStr.Length == )
sbIntStr.Append(Convert.ToString(r));
else
sbIntStr.Append("," + Convert.ToString(r));
}
sbIntStr.Append(")");
return sbIntStr.ToString();
}
else if (result is string[])
{
var rl = result as string[];
StringBuilder sbIntStr = new StringBuilder();
sbIntStr.Append("(");
foreach (var r in rl)
{
if (sbIntStr.Length == )
sbIntStr.Append("'" + r + "'");
else
sbIntStr.Append(",'" + r + "'");
}
sbIntStr.Append(")");
return sbIntStr.ToString();
}
}
}
else if (exp is NewArrayExpression)
{
NewArrayExpression ae = ((NewArrayExpression)exp);
StringBuilder tmpstr = new StringBuilder();
foreach (Expression ex in ae.Expressions)
{
tmpstr.Append(ExpressionRouter(ex));
tmpstr.Append(",");
}
//添加参数return tmpstr.ToString(, tmpstr.Length - );
}
else if (exp is MethodCallExpression)
{
MethodCallExpression mce = (MethodCallExpression)exp;string value = ExpressionRouter(mce.Arguments[]).Replace("'", "");
string[] exps = mce.ToString().Split(new char[] { '.' });
string fieldName = exps[exps.Length-].Split(new char[] { ')' })[];if (mce.Method.Name == "In")
return string.Format("{0} In ({1})", ExpressionRouter(mce.Arguments[]), ExpressionRouter(mce.Arguments[]));
else if (mce.Method.Name == "NotIn")
return string.Format("{0} Not In ({1})", ExpressionRouter(mce.Arguments[]), ExpressionRouter(mce.Arguments[]));
else if (mce.Method.Name == "Contains")
return fieldName + " in @" + value;
else if (mce.Method.Name == "StartsWith")
return fieldName + " like '" + value + "%'";
else if (mce.Method.Name == "EndsWith")
return fieldName + " like '%" + value + "'";
}
else if (exp is ConstantExpression)
{
ConstantExpression ce = ((ConstantExpression)exp);
if (ce.Value == null)
{
return "null";
}
else if (ce.Value is ValueType)
{
//return Convert.ToBoolean(ce.Value) ? "1" : "0";
return Convert.ToString("'"+ce.Value+"'");
}
else if (ce.Value is string || ce.Value is DateTime || ce.Value is char)
{
return string.Format("'{0}'", Convert.ToString(ce.Value));
}//对数值进行参数附加
}
else if (exp is UnaryExpression)
{
UnaryExpression ue = ((UnaryExpression)exp);return ExpressionRouter(ue.Operand);
}
return null;
}private static string ExpressionTypeCast(ExpressionType type)
{
switch (type)
{
case ExpressionType.And:
case ExpressionType.AndAlso:
return " AND ";case ExpressionType.Equal:
return " =";case ExpressionType.GreaterThan:
return " >";case ExpressionType.GreaterThanOrEqual:
return ">=";case ExpressionType.LessThan:
return "<";case ExpressionType.LessThanOrEqual:
return "<=";case ExpressionType.NotEqual:
return "<>";case ExpressionType.Or:
case ExpressionType.OrElse:
return " Or ";case ExpressionType.Add:
case ExpressionType.AddChecked:
return "+";case ExpressionType.Subtract:
case ExpressionType.SubtractChecked:
return "-";case ExpressionType.Divide:
return "/";case ExpressionType.Multiply:
case ExpressionType.MultiplyChecked:
return "*";default:
return null;
}
}/// <summary>
/// 读取实体的属性字段和值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
private static Dictionary<object, object> GetProperties<T>(T t)
{
var ret = new Dictionary<object, object>();
if (t == null) { return null; }
PropertyInfo[] properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
if (properties.Length <= ) { return null; }
int i = ;
foreach (PropertyInfo item in properties)
{
string name = item.Name;
object value = item.GetValue(t, null);
if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
{
ret.Add(i, name);
i++;
}
}
return ret;
}}
}

以上是将Lambda转换为Sql的帮助类

using Microsofot.Core;
using Microsoft.Data;
using Microsoft.Entity;
using Microsoft.Service.Interface;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;namespace Microsoft.Service.Service
{
/// <summary>
/// 访问服务
/// </summary>
public class PageViewService: IPageViewService
{
/// <summary>
/// 查询
/// </summary>
/// <param name="exp"></param>
/// <returns></returns>
public List<PageView> Query(Expression<Func<PageView, bool>> exp, Expression<Func<PageView, object>> order, bool desc, object param = null)
{
List<PageView> result = null;
if (exp != null)
{
var sql = LambdaHelper.Query(exp) + LambdaHelper.Order(order, desc);
if (!string.IsNullOrWhiteSpace(sql))
{
result = DapperManager.Query<PageView>(sql,param).ToList();
LogHelper.Info("PageViewService.Query:" + result.Count);
}
}
return result;
} /// <summary>
/// 单条插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(PageView model)
{
int result = -;
if (model != null)
{
string sql = LambdaHelper.Insert(model);
result = DapperManager.Execute(sql, model, null, null, CommandType.Text);
LogHelper.Info("PageViewService.Insert:" + result);
}
return result;
} /// <summary>
/// 批量插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(List<PageView> model)
{
int result = -;
if (model != null)
{
string sql = LambdaHelper.Insert(model.Last());
result = DapperManager.Execute(sql, model, null, null, CommandType.Text);
LogHelper.Info("PageViewService.Insert:" + result);
}
return result;
} /// <summary>
/// 单条修改
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(PageView model)
{
int result = -;
if (model != null)
{
string sql = LambdaHelper.Update(model);
result = DapperManager.Execute(sql, model, null, null, CommandType.Text);
LogHelper.Info("PageViewService.Update:" + result);
}
return result;
} /// <summary>
/// 多条修改
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(List<PageView> model)
{
int result = -;
if (model != null)
{
string sql = LambdaHelper.Update(model.Last());
result = DapperManager.Execute(sql, model, null, null, CommandType.Text);
LogHelper.Info("PageViewService.Update:" + result);
}
return result;
} /// <summary>
/// 单条删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int Delete(PageView model)
{
int result = -;
if (model != null)
{
string sql = LambdaHelper.Delete(model);
result = DapperManager.Execute(sql, model, null, null, CommandType.Text);
LogHelper.Info("PageViewService.Delete:" + result);
}
return result;
} /// <summary>
/// 多条删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int Delete(List<PageView> model)
{
int result = -;
if (model != null)
{
string sql = LambdaHelper.Delete(model.Last());
result = DapperManager.Execute(sql, model, null, null, CommandType.Text);
LogHelper.Info("PageViewService.Delete:" + result);
}
return result;
} /// <summary>
/// 原生Dapper查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="buffered"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public List<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
{
return DapperManager.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).ToList();
}
/// <summary>
/// 原生Dapper增、删、改、存储过程方法
/// </summary>
/// <param name="sql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public int Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
{
return DapperManager.Execute(sql, param, transaction, commandTimeout, commandType);
}
}
}