/// <summary>
/// 处理数组
/// </summary>
/// <param name="SP"></param>
/// <param name="Result"></param>
/// <returns></returns>
private static string DealArrValue(SQLAndParameter SP, object Result)
{
if (Result is Array)
{
StringBuilder tmpstr = new StringBuilder();
Array Arr = (Array)Result;
if (Arr == null || Arr.Length == 0)
{
Arr = new int?[] { 0 }
}
;
if (Arr.Length == 1 && Arr.GetValue(0) == null)
{
Arr = new int?[] { 0 }
}
;
foreach (object o in Arr)
{
if (o == null || o.ToString2() == "null")
{
continue;
}
tmpstr.Append(GetValue(SP, o));
tmpstr.Append(",");
}
string str = tmpstr.ToString();
return(str.Length >= 1 ? str.Substring(0, str.Length - 1) : str);
}
return(GetValue(SP, Result));
}
/// <summary>
/// 获取和处理参数值
/// </summary>
/// <param name="SP"></param>
/// <param name="Value"></param>
/// <returns></returns>
private static string GetValue(SQLAndParameter SP, object Value)
{
int LastIndex = SP.Parameter.CanShu.ChanShuObjectArrSql.Count - 1;
if (Value == null)
{
SP.Parameter.CanShu.ChanShuObjectArrSql.RemoveAt(LastIndex);
return(null);
}
//else if (ce.Value is ValueType)
// return ce.Value.ToString();
//else if (ce.Value is string || ce.Value is DateTime || ce.Value is char)
// return string.Format("'{0}'", ce.Value.ToString());
SqlParameter _SqlParameter = (SqlParameter)SP.Parameter.CanShu.ChanShuObjectArrSql[LastIndex];
if (_SqlParameter.TypeName == "-1")
{
_SqlParameter.TypeName = "";
_SqlParameter.Value = Value;
SP.Parameter.CanShu.ChanShuObjectArrSql[LastIndex] = _SqlParameter;
SP.sign++;
return(_SqlParameter.ParameterName);
}
else
{
string ParameterName = _SqlParameter.ParameterName.Split('7')[0] + SP.sign;
SP.Parameter.CanShu.Add(ParameterName, _SqlParameter.SqlDbType, Value);
SP.sign++;
return(ParameterName);
}
}
private static string BinarExpressionProvider(SQLAndParameter SP, Expression left, Expression right, ExpressionType type)
{
string sb = "(";
//先处理左边
sb += ExpressionRouter(SP, left, true);
sb += ExpressionTypeCast(type);
//再处理右边
string tmpStr = ExpressionRouter(SP, right, false);
if (tmpStr == null)
{
sb = sb.Trim();
if (sb.EndsWith("="))
{
sb = sb.Substring(0, sb.Length - 1).Trim() + " is null";
}
else if (sb.EndsWith("<>"))
{
sb = sb.Substring(0, sb.Length - 2).Trim() + " is not null";
}
}
else
{
sb += tmpStr;
}
return(sb += ")");
}
/// <summary>
/// 获取字段名
/// </summary>
/// <returns></returns>
private static string GetColumeName(SQLAndParameter SP, Expression exp, bool NeedParameter)
{
MemberExpression me = ((MemberExpression)exp);
string ColumnName = me.Member.Name;
ColumnAttribute[] cas = me.Member.ReflectedType.GetProperty(me.Member.Name).GetCustomAttributes(typeof(ColumnAttribute), false) as ColumnAttribute[];
if (cas.Length > 0)
{
if (NeedParameter)
{
SP.Parameter.CanShu.Add("@" + ColumnName + SP.sign, cas[0].Type, null);
((SqlParameter)SP.Parameter.CanShu.ChanShuObjectArrSql[SP.Parameter.CanShu.ChanShuObjectArrSql.Count - 1]).TypeName = "-1";
}
}
else
{
throw new Exception("MemberExpression: LambdaToSQL " + ColumnName + " 数据库类型(SqlDbType)获取失败");
}
return(ColumnName);
}
/// <summary>
/// 返回SQL和参数
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="func">Lambda表达式</param>
/// <returns></returns>
public static SQLAndParameter GetWhere <T>(Expression <Func <T, bool> > func) where T : EntityBase <T>, new()
{
SQLAndParameter SP = new SQLAndParameter();
string SQL = string.Empty;
if (func != null)
{
if (func.Body is BinaryExpression)
{
BinaryExpression be = ((BinaryExpression)func.Body);
SP.SQL = BinarExpressionProvider(SP, be.Left, be.Right, be.NodeType);
}
else if (func.Body is MethodCallExpression)
{
SP.SQL = ExpressionRouter(SP, ((MethodCallExpression)func.Body), true);
}
}
else
{
SP.SQL = "";
}
return(SP);
}
private static string ExpressionRouter(SQLAndParameter SP, Expression exp, bool IsLeft)
{//IsLeft是否是表达式左边
string sb = string.Empty;
if (exp is BinaryExpression)
{
BinaryExpression be = ((BinaryExpression)exp);
return(BinarExpressionProvider(SP, be.Left, be.Right, be.NodeType));
}
else if (exp is MemberExpression)
{
#region MyRegion
if (!IsLeft)
{
try
{
//获取值
object Result = Expression.Lambda(exp).Compile().DynamicInvoke();
return(DealArrValue(SP, Result));
}
catch { throw new Exception("MemberExpression: Lambda表达式表 值必须放右边"); }
}
//获取字段
return(GetColumeName(SP, exp, true));
#endregion
}
else if (exp is NewArrayExpression)
{
NewArrayExpression ae = ((NewArrayExpression)exp);
StringBuilder tmpstr = new StringBuilder();
foreach (Expression ex in ae.Expressions)
{
if (ex == null || ex.ToString2() == "null")
{
continue;
}
tmpstr.Append(ExpressionRouter(SP, ex, false));
tmpstr.Append(",");
}
if (ae.Expressions == null || ae.Expressions.Count == 0)
{
tmpstr.Append(GetValue(SP, "0"));
tmpstr.Append(",");
}
string str = tmpstr.ToString();
return(str.Length >= 1 ? str.Substring(0, str.Length - 1) : str);
}
else if (exp is MethodCallExpression)
{
#region MyRegion
MethodCallExpression mce = (MethodCallExpression)exp;
#region 条件
/*条件*/
if (mce.Method.Name == "lb_Like")
{
return(string.Format("({0} like '%'+{1}+'%')", ExpressionRouter(SP, mce.Arguments[0], true), ExpressionRouter(SP, mce.Arguments[1], false)));
}
if (mce.Method.Name == "lb_LikeR")
{
return(string.Format("({0} like {1}+'%')", ExpressionRouter(SP, mce.Arguments[0], true), ExpressionRouter(SP, mce.Arguments[1], false)));
}
if (mce.Method.Name == "lb_LikeL")
{
return(string.Format("({0} like '%'+{1})", ExpressionRouter(SP, mce.Arguments[0], true), ExpressionRouter(SP, mce.Arguments[1], false)));
}
else if (mce.Method.Name == "lb_NotLike")
{
return(string.Format("({0} Not like '%'+{1}+'%')", ExpressionRouter(SP, mce.Arguments[0], true), ExpressionRouter(SP, mce.Arguments[1], false)));
}
else if (mce.Method.Name == "lb_In")
{
return(string.Format("({0} In ({1}))", ExpressionRouter(SP, mce.Arguments[0], true), ExpressionRouter(SP, mce.Arguments[1], false)));
}
else if (mce.Method.Name == "lb_NotIn")
{
return(string.Format("({0} Not In ({1}))", ExpressionRouter(SP, mce.Arguments[0], true), ExpressionRouter(SP, mce.Arguments[1], false)));
}
else if (mce.Method.Name == "lb_IsNotNullAndEqual")
{
string ColumeName = ExpressionRouter(SP, mce.Arguments[0], true);
object Value = ExpressionRouter(SP, mce.Arguments[1], false);
if (Value == null)
{
return(string.Format("( 1=1 )"));
}
else
{
return(string.Format("({0} = {1})", ColumeName, Value));
}
}
else if (mce.Method.Name == "lb_IsNotNullAndDo")
{
string ColumeName = ExpressionRouter(SP, mce.Arguments[0], true);
object Value = ExpressionRouter(SP, mce.Arguments[1], false);
string FuHao = Expression.Lambda(mce.Arguments[2]).Compile().DynamicInvoke().ToString2().Replace("\"", "");// mce.Arguments[2].ToString2().Replace("\"", "");
if (Value == null)
{
return(string.Format("( 1=1 )"));
}
else
{
return(string.Format("({0} {2} {1})", ColumeName, Value, FuHao));
}
}
else if (mce.Method.Name == "lb_IsNotNullAndEmptyAndDo")
{
string ColumeName = ExpressionRouter(SP, mce.Arguments[0], true);
object Value = ExpressionRouter(SP, mce.Arguments[1], false);
string FuHao = Expression.Lambda(mce.Arguments[2]).Compile().DynamicInvoke().ToString2().Replace("\"", "");// mce.Arguments[2].ToString2().Replace("\"", "");
if (Value == null)
{
return(string.Format("( 1=1 )"));
}
else
{
int LastIndex = SP.Parameter.CanShu.ChanShuObjectArrSql.Count - 1;
if (LastIndex >= 0)
{
SqlParameter _SqlParameter = (SqlParameter)SP.Parameter.CanShu.ChanShuObjectArrSql[LastIndex];
string val = _SqlParameter.Value.ToString2();
if (string.IsNullOrEmpty(val))
{
SP.Parameter.CanShu.ChanShuObjectArrSql.RemoveAt(LastIndex);
return(string.Format("( 1=1 )"));
}
}
return(string.Format("({0} {2} {1})", ColumeName, Value, FuHao));
}
}
else if (mce.Method.Name == "lb_IsNotFalseAndEqual")
{
string ColumeName = ExpressionRouter(SP, mce.Arguments[0], true);
object Value = ExpressionRouter(SP, mce.Arguments[1], false);
if (Value == null)
{
return(string.Format("( 1=1 )"));
}
else
{
int LastIndex = SP.Parameter.CanShu.ChanShuObjectArrSql.Count - 1;
if (LastIndex >= 0)
{
SqlParameter _SqlParameter = (SqlParameter)SP.Parameter.CanShu.ChanShuObjectArrSql[LastIndex];
bool val = (bool)_SqlParameter.Value;
if (val == false)
{
SP.Parameter.CanShu.ChanShuObjectArrSql.RemoveAt(LastIndex);
return(string.Format("( 1=1 )"));
}
}
return(string.Format("({0} = {1})", ColumeName, Value));
}
}
#endregion
#region 排序
/*排序*/
else if (mce.Method.Name == "lb_Desc")
{
SP.SQLSort = SQLSort.SQLOrder;
return(string.Format(" {0} Desc ", GetColumeName(SP, mce.Arguments[0], false)));
}
else if (mce.Method.Name == "lb_Asc")
{
SP.SQLSort = SQLSort.SQLOrder;
return(string.Format(" {0} Asc ", GetColumeName(SP, mce.Arguments[0], false)));
}
else if (mce.Method.Name == "lb_OrderByArr")
{
SP.SQLSort = SQLSort.SQLOrder;
string ColumeName = GetColumeName(SP, mce.Arguments[0], true);
object Value = ExpressionRouter(SP, mce.Arguments[1], false);
return(string.Format(" charindex（',' + ltrim（{0}） + ',' , ','+{1}+',' ） ", ColumeName, Value.ToString2().Replace(",", "+','+")));
}
#endregion
#region 字段
/*字段*/
else if (mce.Method.Name == "lb_ColumeName")
{
SP.SQLSort = SQLSort.SQLFields;
return(string.Format(" {0} ", ExpressionRouter(SP, mce.Arguments[0], true)));
}
else if (mce.Method.Name == "lb_Sum")
{
SP.SQLSort = SQLSort.SQLFields;
return(string.Format(" Sum({0}) ", ExpressionRouter(SP, mce.Arguments[0], true)));
}
else if (mce.Method.Name == "lb_Avg")
{
SP.SQLSort = SQLSort.SQLFields;
return(string.Format(" Avg({0}) ", ExpressionRouter(SP, mce.Arguments[0], true)));
}
else if (mce.Method.Name == "lb_Distinct")
{
SP.SQLSort = SQLSort.SQLFields;
return(string.Format(" Distinct {0} ) ", ExpressionRouter(SP, mce.Arguments[0], true)));
}
#endregion
#endregion
#region MyRegion
try
{
object Result = Expression.Lambda(exp).Compile().DynamicInvoke();
return(DealArrValue(SP, Result));
}
catch { throw new Exception("MethodCallExpression: 获取值出错"); }
#endregion
}
else if (exp is ConstantExpression)
{
ConstantExpression ce = ((ConstantExpression)exp);
return(GetValue(SP, ce.Value));
}
else if (exp is UnaryExpression)
{
//UnaryExpression ue = ((UnaryExpression)exp);
//return ExpressionRouter(SP, ue.Operand);
object Result = Expression.Lambda(exp).Compile().DynamicInvoke();
return(GetValue(SP, Result));
}
return(null);
}
