/// <summary>
/// 获取实体属性的参数和SQL
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private List <ParamSqlModel> GetParamSQLByEntityProp(TEntity entity)
{
List <ParamSqlModel> paramSqlModels = new List <ParamSqlModel>();
//获取公共属性
CommonProperty cmmonProperty = (CommonProperty)entity;
//如果没有变更则返回null
if (cmmonProperty.ChanageProperty.Count == 0)
{
return(null);
}
//循环遍历属性得到SQL脚本和参数
foreach (PropertyInfo prop in entity.GetType().GetProperties())
{
ParamSqlModel paramSqlModel = new ParamSqlModel();
//如果不包含在变更集里面则跳出
if (cmmonProperty.ChanageProperty.ContainsKey(prop.Name) == false)
{
continue;
}
//获取属性数据库信息
EntityPropColumnAttributes columnAttribute = columnAttrList.Where(w => w.propName.ToLower() == prop.Name.ToLower()).First();
//字段
paramSqlModel.Field = columnAttribute.fieldName;
paramSqlModel.IsDbGenerated = columnAttribute.isDbGenerated;
paramSqlModel.IsPrimaryKey = columnAttribute.isPrimaryKey;
//SQL字符
string propSql = columnAttribute.fieldName + "=@" + columnAttribute.fieldName;
paramSqlModel.SQL = propSql;
//参数添加
object val = prop.GetValue(entity, null);
SqlParameter param = new SqlParameter("@" + columnAttribute.fieldName, val ?? DBNull.Value);
DbType dbtype;
Enum.TryParse(prop.PropertyType.Name, out dbtype);
param.DbType = dbtype;
//参数赋值
paramSqlModel.Param = param;
paramSqlModels.Add(paramSqlModel);
}
return(paramSqlModels);
}
/// <summary>
/// 二元运算符表达式
/// </summary>
/// <param name="left"></param>
/// <param name="right"></param>
/// <param name="type"></param>
/// <returns></returns>
string BinarExpressionProvider(System.Linq.Expressions.Expression left, System.Linq.Expressions.Expression right, System.Linq.Expressions.ExpressionType type)
{
string where = "(";
//先处理左边
string leftStr = ExpressionRouter(left);
//获取实体列的特性
List <EntityPropColumnAttributes> columnAttrList = AttributeHelper.GetEntityColumnAtrributes <TEntity>();
var list = columnAttrList.Where(w => w.propName == leftStr);
if (list.Count() > 0)
{
EntityPropColumnAttributes columnAttribute = list.First();
leftStr = columnAttribute.fieldName;
}
//节点类型
string typeStr = ExpressionTypeCast(type);
//再处理右边
string rightStr = RightExpressionRouter(right).ToStr();
where += leftStr;
where += typeStr;
if (rightStr == "null")
{
if (where.EndsWith(" ="))
{
where = where.Substring(0, where.Length - 2) + " is null";
}
else if (where.EndsWith("<>"))
{
where = where.Substring(0, where.Length - 2) + " is not null";
}
}
else
{
//如果左侧包含（则代表左侧非字段
if (leftStr.Contains("("))
{
where += rightStr;
}
else
{
int num = random.Next(100, 999);
where += "@" + leftStr + num;
listPara.Add(new SqlParameter("@" + leftStr + num, rightStr));
}
}
return(where += ")");
}
/// <summary>
/// 修改
/// </summary>
/// <param name="id">主键值</param>
/// <returns></returns>
public int Update(TEntity entity)
{
EntityPropColumnAttributes columnAttribute = columnAttrList.Where(w => w.fieldName.ToLower() == PrimaryKey.ToLower()).First();
//获取属性及属性值
PropertyInfo prop = typeof(TEntity).GetProperty(columnAttribute.propName);
object value = prop.GetValue(entity, null);
//拼接条件
List <Expression> express = new List <Expression>();
express.Add(new Expression(columnAttribute.propName, ConditionEnum.Eq, value));
return(Update(entity, express));
}
/// <summary>
/// 获取实体的列特性
/// </summary>
/// <returns></returns>
public static List <EntityPropColumnAttributes> GetEntityColumnAtrributes <TEntity>() where TEntity : class, new()
{
List <EntityPropColumnAttributes> list = new List <EntityPropColumnAttributes>();
TEntity model = new TEntity();
foreach (PropertyInfo prop in model.GetType().GetProperties())
{
EntityPropColumnAttributes entity = new EntityPropColumnAttributes();
entity.propName = prop.Name;
entity.fieldName = prop.Name;
entity.isPrimaryKey = false;
entity.isDbGenerated = false;
Type type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
entity.typeName = type.FullName;
ColumnAttribute columnAttribute = prop.GetCustomAttribute <ColumnAttribute>();
if (columnAttribute != null)
{
entity.fieldName = columnAttribute.Name;
}
KeyAttribute keyAttribute = prop.GetCustomAttribute <KeyAttribute>();
if (keyAttribute != null)
{
entity.isPrimaryKey = true;
}
DatabaseGeneratedAttribute databaseGeneratedAttribute = prop.GetCustomAttribute <DatabaseGeneratedAttribute>();
if (databaseGeneratedAttribute != null)
{
entity.isDbGenerated = databaseGeneratedAttribute.DatabaseGeneratedOption == DatabaseGeneratedOption.Identity ? true : false;
}
list.Add(entity);
}
return(list);
}
string MethodExpression(System.Linq.Expressions.Expression exp)
{
System.Linq.Expressions.MethodCallExpression mce = (System.Linq.Expressions.MethodCallExpression)exp;
string key = ExpressionRouter(mce.Arguments[0]);
object obj = RightExpressionRouter(mce.Arguments[1]);
//对象属性
EntityPropColumnAttributes columnAttribute = columnAttrList.Where(w => w.propName == key).First();
key = columnAttribute.fieldName;
//参数名称
string paramName = "@" + key + random.Next(1000, 9999);
string values = "";
#region 拼接参数值
if (mce.Method.Name == "Like" || mce.Method.Name == "NotLike")
{
listPara.Add(new SqlParameter(paramName, "%" + obj.ToStr() + "%"));
}
if (mce.Method.Name == "LeftLike")
{
listPara.Add(new SqlParameter(paramName, obj.ToStr() + "%"));
}
if (mce.Method.Name == "RightLike")
{
listPara.Add(new SqlParameter(paramName, "%" + obj.ToStr()));
}
if (mce.Method.Name == "In" || mce.Method.Name == "NotIn")
{
string json = Newtonsoft.Json.JsonConvert.SerializeObject(obj);
List <object> list = Newtonsoft.Json.JsonConvert.DeserializeObject <List <object> >(json);
if (list.Count == 0)
{
return(" (1=2) ");
}
int index = 0;
foreach (var value in list)
{
index++;
listPara.Add(new SqlParameter(paramName + index, value.ToStr()));
values += paramName + index + ",";
}
}
values = values.TrimEnd(',');
#endregion
if (mce.Method.Name == "Like" || mce.Method.Name == "LeftLike" || mce.Method.Name == "RightLike")
{
return(string.Format("({0} like {1})", key, paramName));
}
else if (mce.Method.Name == "NotLike")
{
return(string.Format("({0} Not like {1})", key, paramName));
}
else if (mce.Method.Name == "In")
{
return(string.Format("{0} In ({1})", key, values));
}
else if (mce.Method.Name == "NotIn")
{
return(string.Format("{0} Not In ({1})", key, values));
}
return(" (1=2) ");
}
/// <summary>
/// 获取表达式列表对应的参数列表和条件
/// </summary>
/// <param name="express"></param>
/// <returns></returns>
public CoreFrameworkEntity GetParaListAndWhere(List <Expression> express)
{
//获取实体列的特性
List <EntityPropColumnAttributes> columnAttrList = AttributeHelper.GetEntityColumnAtrributes <TEntity>();
#region 获取参数和条件
//参数列表
List <SqlParameter> listPara = new List <SqlParameter>();
CoreFrameworkEntity entity = new CoreFrameworkEntity();
if (express == null || express.Count == 0)
{
entity.ParaList = listPara;
entity.Where = "1=1";
return(entity);
}
//条件
string where = "";
int i = 0;//运行的位置，从下标0开始
foreach (Expression exp in express)
{
//当两个条件没有使用连接符时，则默认用and拼接,从第一个参数后试用
if (i > 0)
{
//如果上一个参数不是连接符时，默认每个参数是以and的形式拼接
string join = string.Empty;
switch (express[i - 1].Join)
{
case JoinEnum.And:
join = "and";
break;
case JoinEnum.Or:
join = "or";
break;
}
where += " " + join;
}
if (string.IsNullOrEmpty(exp.FieldName) || exp.Value == null)
{
continue;
}
EntityPropColumnAttributes column = columnAttrList.Where(w => w.propName.ToLower() == exp.FieldName.ToLower()).FirstOrDefault();
string fieldName = column != null ? column.fieldName : exp.FieldName;
//判断个数是为了防止参数名相同
int fieldCount = express.Where(e => e.FieldName == exp.FieldName).Count();
string paraName = fieldCount == 1 ? exp.FieldName : exp.FieldName + "_p" + i.ToStr();
//加空格
where += " ";
#region 条件语句
switch (exp.Condition)
{
case ConditionEnum.Like:
listPara.Add(new SqlParameter("@" + paraName, "%" + exp.Value + "%"));
where += fieldName + " like @" + paraName;
break;
case ConditionEnum.LeftLike:
listPara.Add(new SqlParameter("@" + paraName, "%" + exp.Value));
where += fieldName + " like @" + paraName;
break;
case ConditionEnum.RightLike:
listPara.Add(new SqlParameter("@" + paraName, exp.Value + "%"));
where += fieldName + " like @" + paraName;
break;
case ConditionEnum.NotLike:
listPara.Add(new SqlParameter("@" + paraName, "%" + exp.Value + "%"));
where += fieldName + " not like @" + paraName;
break;
case ConditionEnum.In:
List <SqlParameter> paramConvertIn = GetParamList((List <object>)exp.Value, exp.FieldName);
listPara.AddRange(paramConvertIn);
where += fieldName + " in (" + string.Join(",", paramConvertIn.Select(s => s.ParameterName).ToArray()) + ")";
break;
case ConditionEnum.NotIn:
List <SqlParameter> paramConvertNotIn = GetParamList((List <object>)exp.Value, exp.FieldName);
listPara.AddRange(paramConvertNotIn);
where += fieldName + " not in (" + string.Join(",", paramConvertNotIn.Select(s => s.ParameterName).ToArray()) + ")";
break;
default:
string sign = "=";
switch (exp.Condition)
{
case ConditionEnum.Eq:
sign = "=";
break;
case ConditionEnum.Ne:
sign = "<>";
break;
case ConditionEnum.Gt:
sign = ">";
break;
case ConditionEnum.Ge:
sign = ">=";
break;
case ConditionEnum.Lt:
sign = "<";
break;
case ConditionEnum.Le:
sign = "<=";
break;
}
listPara.Add(new SqlParameter("@" + paraName, exp.Value));
where += fieldName + " " + sign + " @" + paraName;
break;
}
#endregion
i++;
}
#endregion
entity.ParaList = listPara;
entity.Where = where;
return(entity);
}
