/// <summary> /// 对gridView数据进行筛选 返回RowFilter 字符，如果有PY列，则会一并处理 注意 返回是 and (xxx like xxx) 有And 进行连接 /// </summary> /// <param name="view">要筛选的GridView 仅处理显示列</param> /// <param name="_inputTxt">要筛选的文本，中间可以有空格，具体空格用途见第三个参数，注意不要对用户录入的txt做特殊字符过滤处理</param> /// <param name="comperType">如果要筛选的文本，中间可以有空格，则要如何处理，默认不处理 类似如 用户录入 湖南 长沙，可以筛选出包含湖南和长沙的内容 </param> /// <returns>注意 返回是 and (xxx like xxx) 有And 进行连接</returns> public static string GetDataTable_LikeString(DevExpress.XtraGrid.Views.Grid.GridView view, string _inputTxt, EnComperType comperType = EnComperType.None) { if (view == null || view.GridControl == null || string.IsNullOrEmpty(_inputTxt)) return ""; DataTable dt = view.GridControl.DataSource as DataTable; if (dt == null) return ""; List<string> visableCol = new List<string>(); foreach (DevExpress.XtraGrid.Columns.GridColumn col in view.VisibleColumns) { visableCol.Add(col.FieldName); } //处理所有以PY列开头或结尾的 foreach (DataColumn item in dt.Columns) { if (item.ColumnName.ToUpper().StartsWith("PY") || item.ColumnName.ToUpper().EndsWith("PY")) visableCol.Add("PY"); } string strFilter = ""; string[] spinput = _inputTxt.Trim().Split(' ', ' '); List<string> spinput2 = new List<string>(); foreach (string item in spinput) { if (item.Trim() == "") continue; if (!spinput2.Contains(item.Trim())) spinput2.Add(item.Trim()); } if (spinput2.Count > 1) { if (comperType == EnComperType.None) { strFilter = GetDataTable_LikeString_Base(dt, _inputTxt, string.Join(",", visableCol.ToArray())); } else { string tempstr = ""; for (int i = 0; i < spinput2.Count; i++) { tempstr = GetDataTable_LikeString_Base(dt, spinput2[i], string.Join(",", visableCol.ToArray())); if (i == 0) { strFilter = tempstr; } else { strFilter = strFilter + (comperType == EnComperType.And ? " And " : " Or ") + tempstr; } } } } else { strFilter = GetDataTable_LikeString_Base(dt, _inputTxt, string.Join(",", visableCol.ToArray())); } if (strFilter.Length > 0) { strFilter = " AND ( " + strFilter + ") "; } return strFilter; } /// <summary> /// 对DataTable数据进行筛选 返回RowFilter 字符，如果有PY列，则会一并处理 注意 返回是 (xxx like xxx) 没有And 进行连接 /// </summary> /// <param name="sourceDT">目标表</param> /// <param name="_inputTxt">需要匹配的字符串</param> /// <param name="isRepRowFilterStr">是否需要做ToRowFilterStr 特殊字符过滤 处理，</param> /// <returns>返回 ( filedName like '%ZS%')</returns> public static string GetDataTable_LikeString_Base(DataTable sourceDT, string _inputTxt, string filterColumsStr, bool isRepRowFilterStr = true) { if (string.IsNullOrEmpty(_inputTxt)) return ""; StringBuilder strB = new StringBuilder(); string likeStr = isRepRowFilterStr ? _inputTxt.ToRowFilterStr() : _inputTxt; if (_inputTxt.Contains("%")) likeStr = _inputTxt; else likeStr = $"%{likeStr}%"; //判断目标表是否为空,是否没有列 if (sourceDT == null || sourceDT.Columns.Count == 0) return ""; //循环所有的列 foreach (DataColumn columnCollection in sourceDT.Columns) { //columnCollection.DataType.FullName.Dump(); string rowfilterFiled = "";// Convert(AddTime, 'System.String') string columnName = columnCollection.ColumnName.ToStringNull(); if (columnName == "") continue; if (filterColumsStr != null) { var filterColums = filterColumsStr.Split(',', '，'); if (filterColums.Length > 0) if (!Array.Exists(filterColums, (t) => { return t.Trim().ToUpper() == columnName.Trim().ToUpper(); })) { continue; } } switch (columnCollection.DataType.FullName) { case "System.Int32": case "System.Int64": case "System.Decimal": case "System.Single": case "System.Double": case "System.DateTime": rowfilterFiled = "Convert([" + columnName + "], 'System.String')"; break; case "System.String": rowfilterFiled = "[" + columnName + "]"; break; } if (string.IsNullOrEmpty(rowfilterFiled)) continue; strB.Append($"{rowfilterFiled} like '{likeStr}' OR "); } string filterStr = strB.ToString(); if (filterStr.Length > 0) //有数据 { //去掉最后的“ OR ” filterStr = "(" + filterStr.Substring(0, filterStr.Length - 3) + ")"; } return filterStr; } /// <summary> /// 当录入内容有空格时，要怎么处理 /// </summary> public enum EnComperType { /// <summary> /// 不处理录入的筛选内容是否有空格 /// </summary> None, /// <summary> ///如果用户录入有空格，则进行空格前 后使用And拼接 /// </summary> And, /// <summary> /// 如果用户录入有空格，则进行空格前 后使用Or拼接 /// </summary> Or, }