/// <summary> /// 查询条件 /// </summary> /// <typeparam name="T">查询条件类</typeparam> /// <param name="obj">查询条件对象</param> /// <param name="parames">返回SQL参数集合</param> /// <returns></returns> public static string GetWhere <T>(T obj, out List <SqlParameter> parames, string append = "", EWhereRelation appendRelation = EWhereRelation.AND) where T : class, new() { parames = new List <SqlParameter>(); if (obj == null) { return(string.Empty); } //获取查询条件 parames = GetSqlParameters(obj); //获取WHERE SQL语句 var whereSql = GetWhereSql(obj); //附加查询条件 if (append.IsNullOrWhiteSpace()) { return(whereSql); } if (whereSql.IsNullOrWhiteSpace()) { return(append.GetString("")); } return($"{append.GetString("")} {appendRelation.ToString()} {whereSql}"); }
/// <summary> /// 拼接Where条件 /// </summary> /// <param name="obj">对象</param> /// <returns></returns> private static string GetWhereSql(object obj) { if (obj == null) { return(string.Empty); } var props = obj.GetType().GetProperties(); if (props == null || props.Count() <= 0) { return(string.Empty); } var joinIndex = 0; EWhereRelation lastRelation = EWhereRelation.AND; var whereSql = new StringBuilder(); foreach (var p in props) { var pType = p.PropertyType; var dbType = pType.ToDbType(); var value = p.GetValue(obj, null); if (value == null) { continue; } var isList = pType.IsGenericType && pType.GetGenericTypeDefinition() == typeof(List <>); var isNullable = pType.IsGenericType && pType.GetGenericTypeDefinition() == typeof(Nullable <>); //获取属性上的特性(Attribute) var attObj = p.GetCustomAttributes(typeof(SqlParameterAttribute), true).FirstOrDefault(); if (attObj == null) { continue; } var attr = (SqlParameterAttribute)attObj; if (attr == null) { continue; } if (pType.IsGenericType && !isList && !isNullable) { continue; } //拼接条件关系 AND/OR if (joinIndex >= 1) { //如果第一个查询条件是OR,那第二拼接无论是AND还是OR 都应该是OR if (joinIndex == 1 && lastRelation == EWhereRelation.OR) { attr.ERelation = EWhereRelation.OR; } whereSql.Append($" {attr.ERelation.ToString()} "); } var fileName = p.Name; if (!attr.FieldName.IsNullOrWhiteSpace()) { fileName = attr.FieldName; } //获取查询条件 Value 默认是变量@属性名,如果是List 是拼接字符串(1,32,66) 并且 运算符 会用 IN var val = $"@{p.Name}"; if (isList) { attr.EOperator = EWhereOperator.IN; var listItemType = pType.GetGenericArguments()[0]; if (listItemType == null) { return(string.Empty); } if (listItemType == typeof(int)) { val = $"({string.Join(",", value as List<int>)})"; } else if (listItemType == typeof(string)) { val = $"({string.Join(",", value as List<string>)})"; } else { continue; } } var tableAlias = attr.TableAlias.IsNullOrWhiteSpace() ? "" : $"{attr.TableAlias}."; //拼接条件 whereSql.Append($" {tableAlias}{fileName} {attr.EOperator.GetEnumAttr().Desc} {val}"); //后续 joinIndex++; } return(whereSql.ToString()); }