對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.有空的各位就可以試試了