对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.
public static class LambdaToSql
{
#region 基础方法
#region 获取条件语句方法
public static string Where<T>(Expression<Func<T, bool>> func) where T : class
{
string res = "";
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 = " 1=1";
}
var a1 = func.GetType().FullName;
var a2 = a1.Split(new string[] { "[[" }, StringSplitOptions.RemoveEmptyEntries);
var a3 = a2[2].Split(',');
var a4 = a3[0].Split('.')[1];
res = "select * from " + a4 + " where" + res;
return res;
}
#endregion 获取条件语句方法
#region 生成Dapper插入语法
public static string Insert<T>(T model)
{
var sql = "insert into "+ model.GetType().Name+ " values (";
var dic= GetProperties(model);
//var data = dic["mu"];
//List<string> test = new List<string>();
//test.AddRange(dic.Keys);
//for (int i = 0; i < list.Count; i++)
//{
// Console.WriteLine(test[i] + list[test[i]]);
//}
for (int i = 1; i < dic.Count; i++)
{
//跳过主键
if (dic.Count - 1 > i)
{
sql += "@" + dic[i] + ",";
}
if (dic.Count-1==i)
{
sql += "@" + dic[i] + ")";
}
}
return sql;
}
#endregion
#region 获取排序语句 order by
private static string GetOrderSql<T>(Expression<Func<T, object>> exp) where T : class
{
var res = "";
if (exp.Body is UnaryExpression)
{
UnaryExpression ue = ((UnaryExpression)exp.Body);
res = "order by `" + ExpressionRouter(ue.Operand).ToLower() + "`";
}
else
{
MemberExpression order = ((MemberExpression)exp.Body);
res = "order by `" + order.Member.Name.ToLower() + "`";
}
return res;
}
#endregion 获取排序语句 order by
#endregion 基础方法
#region 底层
//internal static bool In<T>(this T obj, T[] array)
//{
// return true;
//}
//internal static bool NotIn<T>(this T obj, T[] array)
//{
// return true;
//}
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(0, sb.Length - 2) + " is null";
}
else if (sb.EndsWith("<>"))
{
sb = sb.Substring(0, sb.Length - 2) + " 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 == 1)
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 == 1)
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(0, tmpstr.Length - 1);
}
else if (exp is MethodCallExpression)
{
MethodCallExpression mce = (MethodCallExpression)exp;
string value = ExpressionRouter(mce.Arguments[0]).Replace("'", "");
string[] exps = mce.ToString().Split(new char[] { '.' });
string fieldName = exps[1];
if (mce.Method.Name == "In")
return string.Format("{0} In ({1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
else if (mce.Method.Name == "NotIn")
return string.Format("{0} Not In ({1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
else if (mce.Method.Name == "Contains")
return fieldName + " like '%" + 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;
}
}
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 <= 0) { return null; }
int i = 0;
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;
}
#endregion 底层
}
以上是将Lambda转换为Sql的帮助类
//查询
Expression<Func<mm, bool>> exp = item => item.mu != null;
var sql = LambdaToSql.Where(exp);
var list = DBHelper.Query<mm>(sql).ToList();
//单条插入
mm model = new mm();
model.mu = "2017";
model.mu1 = "2018";
model.mu2 = "2019";
var insertsql= LambdaToSql.Insert(model);
var l2 = DBHelper.Execute(insertsql, model, null, null, CommandType.Text);
以上调用案例就和EF很像了.但是底层还是保持着Dapper的模式.至于性能比之原生Dapper或者EF6.0.有空的各位就可以试试了