/// <summary> /// 使用SearchParamerters 自动追加 sql 的 where 条件, 并用占位符处理value 例: `ORDER_ID`=@OrderId /// 并根据PageInfo信息生成sql分页内容 /// </summary> /// <param name="sql">sqlkey</param> /// <param name="searchParameters">查询条件</param> /// <returns>返回一个组织好的sql语句</returns> private string GetSqlBySearchParameter(string sql, SearchParameters searchParameters) { StringBuilder sb = new StringBuilder(sql); if (searchParameters.QueryModel.Items.Count > 0) { sb.Append(" where " + ConvertToSqlWhere(searchParameters, DbType.MySql)); } if (string.IsNullOrEmpty(searchParameters.PageInfo.SortDirection)) { if (!string.IsNullOrEmpty(searchParameters.PageInfo.SortField)) { sb.Append(" Order by "); //如果是多字段排序,则会把排序字段和排序方式记录到sort上,dir为空 char[] delimiters = { ',' }; string[] sorts = searchParameters.PageInfo.SortField.Split(delimiters, StringSplitOptions.RemoveEmptyEntries); var mapDic = DbColumnMaps.GetDbContextFiledMaps(); for (int i = 0; i < sorts.Length; i++) { string[] values = sorts[i].Trim().Split(' '); string sortName = values[0]; if (mapDic.ContainsKey(sortName)) { sortName = mapDic[sortName]; } string sortDir = values.Length == 2 ? values[1] : "ASC"; if (i == sorts.Length - 1) { sb.Append(" " + sortName + " " + sortDir + " "); } else { sb.Append(" " + sortName + " " + sortDir + ", "); } } } } else { var mapDic = DbColumnMaps.GetDbContextFiledMaps(); var sortName = searchParameters.PageInfo.SortField; if (mapDic.ContainsKey(sortName)) { sortName = mapDic[sortName]; } sb.Append(" Order by " + sortName + " " + searchParameters.PageInfo.SortDirection + " "); } return(sb.ToString()); }
/// <summary> /// 根据QueryModel组织sqlWhere语句,如果有字段前缀的话,需要提前增加进来 /// 如果是Dapper 需要转换成 /// </summary> /// <returns>Where 语句</returns> public string ConvertToSqlWhere(SearchParameters searchParameters, DbType dbType) { if (searchParameters == null) { return(""); } //处理sql查询时,查询null值问题 searchParameters.BuildEmptySearch(); // 复制一个,避免修改的时候影响外部数据 var copyCondition = JsonEx.JsonConvert.CopyObject(searchParameters.QueryModel.Items); copyCondition.Sort((a, b) => { return(a.Field.CompareTo(b.Field)); }); var sb = new StringBuilder(); List <string> groups = new List <string>(); // 如果是Dapper 则把Condition 的 Field 和 Value 替换掉 var mapDic = DbColumnMaps.GetDbContextFiledMaps(); for (int i = 0; i < copyCondition.Count; i++) { var item = copyCondition[i]; if (dbType == DbType.Oracle) { // Oracle 字段需要前后双引号 item.Value = "\"" + Prefix + item.Field + i + "\""; } else if (item.Value != null) { item.Value = Prefix + item.Field + i; } if (mapDic.ContainsKey(item.Field)) { item.Field = mapDic[item.Field]; } } foreach (var conditionItem in copyCondition) { if (!string.IsNullOrEmpty(conditionItem.OrGroup)) { if (!groups.Contains(conditionItem.OrGroup)) { var sbChild = new StringBuilder(); foreach (var senItem in copyCondition) { if (senItem.OrGroup == conditionItem.OrGroup) { if (sbChild.Length > 0) { sbChild.Append(" or "); } sbChild.Append(GetQueryCloumn(senItem) + " " + ConvertMethodToSql(senItem.Method, senItem.Value)); } } if (sb.Length > 0) { sb.Append(" and "); } sb.Append("(" + sbChild.ToString() + ")"); groups.Add(conditionItem.OrGroup); } } else { if (sb.Length > 0) { sb.Append(" and "); } sb.Append((string.IsNullOrEmpty(conditionItem.Prefix) ? "" : (conditionItem.Prefix + ".")) + conditionItem.Field + " " + ConvertMethodToSql(conditionItem.Method, conditionItem.Value)); } } return(sb.ToString()); }