首先感谢我以前的组长--志远,他教会了我很多很多东西,他的责任心,他的为人处事让我深深敬佩,
在我们所有项目中,有一个功能是必不可少的,那就是查询,不同的需求,需要不同的查询条件,查询条件的设计也是千奇百怪,有时我就想,有没有一种通用的查询控件呢,这样,我们就不需要千篇一律写相同的代码,充分发挥代码的可重用性。
一个查询功能有三个必要条件,一个标题,一个输入控件,一个查询按钮,复杂的我就不在列举了,只有想不到的,没有做不到的。
如果要实现一个通用的查询功能,有几个前提必须明白
1)什么是通用,在我的理解是,可以适合不同的需求。
2)输入控件得包含基本的数据类型,如整形,浮点类型,日期,复制框,下拉列表。。。。。当然也有人说,他只输入一个文本框就行了,什么东西都可以输入,如果你这样想,下面的内容无需再看,这种牛人地球人都无法阻止他了,我更伤不起。
3)多语言的支持,此文不会涉及到,在本人项目中已实现些了功能,我想大家会有很好的办法实现
4)安全,而无需自己添加安全机制,如防SQL注入。
先上效果图,如果有不足之处,还需大家提点,小人不才,不是做得非常完美。
环境配置
开发工具:VS2010,.NET Framework4.0,DXperience-10.2.5
1.先从创建项目开始:创建解决方案(Com),创建类库项目(QueryLibrary),接着创建Window窗口应用程序(ControlQueryText)此项目用于测试
2.在库类Control添加用户控件ControlQuery.cs,界面布局如下图
3.下面开始编写代码了,定义输入框所支持的数据类型,使用枚举类型
public enum ControlType { TextEdit=0,//普通文本框 SpinEdit=1,//数字类型输入框 ComboBox=2,//下拉列表 DateTime=3,//日期类型 ButtonEdit=4,//XX类型,文本框边有个按钮,点击它弹出一个自定义窗口,在自定义窗口输入或选择要输入的条件 GridLookUpEdit= 5,//XX类型,点击弹出GridControl列表,在里面选择一行或多行数据作为输入条件 MemoExEdit= 6,//XX类型,是一个多行文本控件,可以输入多个值,以“,”,“;”,“空格”分界 PopEdit = 7//XX类型,输入自定义DataTable,GridControl,Control }
4.定义一个条件的数据结构DataSources,从左到右依次为:连接符(and 或者 or)左括号,提示符,判断符(=,<,>,<>,like,in等),右括号
public delegate object DelegateEditMethod(); public delegate void DelegateReturnControl(object control); public struct DataSources { public string description { get; set; }//条件显示名 public object value { get; set; }//条件值 public ControlType controlType { get; set; }//控件类型 public bool defaultSelect { get; set; }//是否默显示此条件 public string defaultOperator { get; set; }//默认连接符 public object defaultValue { get; set; }//默认值 public IList<KeyValuePair <string,object>> selectDataSource { get; set; }//选择项的数据源,如下拉列表之类的 public string[] group { get; set; }//分组 个人感觉这个没有多少意义 public bool fixedLine { get; set; }//指定不能删除的条件 public DelegateEditMethod editMethod;//委托方法 public DelegateReturnControl returnControl;//委托控件 }
4.定义查询结果集类FilterItem
public class FilterItem { /// <summary> /// 判斷符 And 或 Or /// </summary> public string Logical { get; set; } /// <summary> /// 左括號 /// </summary> public string LeftParentheses { get; set; } /// <summary> /// 字段 /// </summary> public string Column { get; set; } /// <summary> /// 連接符 如: = /// </summary> public string Operator { get; set; } /// <summary> /// 值 /// </summary> public string Value { get; set; } /// <summary> /// 右括號 /// </summary> public string RigthParentheses { get; set; } /// <summary> /// 所属组 /// </summary> public string[] group { get; set; } /// <summary> /// 得到参数名 /// </summary> public string ParameterName { get; set; } }
5,定义FilterItem集合,实现IEnumerable,IEnumerator ,并提供一般基础方法如 Add,RemoveAt,Clear,得到sql脚本,以Parameter形式的sql脚本
public class Filter:IEnumerable,IEnumerator { private int index=-1; private List<FilterItem> Filters; public int Length { get { if (Filters == null) { return 0; } return Filters.Count; } } /// <summary> /// 是否出错 false :出错 , true :未出错 /// </summary> public bool isError { get; set; } /// <summary> /// 出错描述语言 /// </summary> public string Error { get; set; } public FilterItem this[int index] { get { if (Filters==null|| index < 0 || index > Filters.Count) { return null; } else { return Filters[index]; } } set { if (!(index < 0 || index <= Filters.Count) && Filters!=null) { Filters[index] = value; } } } public FilterItem this[string columnsName] { get { if (Filters == null || columnsName == null || columnsName=="") { return null; } else { return Filters.Find(delegate(FilterItem filter) { return (filter.Column == columnsName); }); } } set { if (Filters != null && columnsName != null && columnsName != "") { FilterItem filteritem=Filters.Find(delegate(FilterItem filter) { return (filter.Column == columnsName); }); filteritem = value; } } } public IEnumerator GetEnumerator() { return (IEnumerator)this; } public object Current { get { return Filters[index]; } } public bool MoveNext() { index++; return index >= Filters.Count ? false : true; } public void Reset() { index = -1; } #region 一般方法 public void Add(FilterItem filterItem) { if (Filters == null) { Filters = new List<FilterItem>(); } Filters.Add(filterItem); } public void RemoveAt(int index) { if (Filters != null) { Filters.RemoveAt(index); } } public void RemoveAt(FilterItem filterItem) { if (Filters != null && filterItem!=null) { Filters.Remove(filterItem); } } public void Clear() { if (Filters != null) { Filters.Clear(); } } public List<FilterItem> FindGroup(string group) { List<FilterItem> groupFilterItem=Filters.FindAll(delegate(FilterItem filter) { for (int i = 0; i < filter.group.Length; i++) { if (filter.group[i] == group) return true; } return false; }); return groupFilterItem; } #endregion /// <summary> /// 得到查询字符串 /// </summary> /// <returns></returns> public override string ToString() { StringBuilder str = new StringBuilder(); if (Filters == null) return ""; foreach (FilterItem item in Filters) { str.Append(" " + item.Logical); str.Append(" " +item.LeftParentheses); str.Append(" " + item.Column); str.Append(" " + item.Operator); str.Append(" " + item.Value); str.Append(" " +item.RigthParentheses); } return str.ToString(); } /// <summary> /// 得到分组的查询字符串 /// </summary> /// <param name="group">组名</param> /// <returns></returns> public string ToGroupString(string group) { StringBuilder str = new StringBuilder(); if (Filters == null) return ""; List<FilterItem> groupFilterItem = FindGroup(group); foreach (FilterItem item in groupFilterItem) { str.Append(" " + item.Logical); str.Append(" " + item.LeftParentheses); str.Append(" " + item.Column); str.Append(" " + item.Operator); str.Append(" " + item.Value); str.Append(" " + item.RigthParentheses); } return str.ToString(); } /// <summary> /// 得到参数查询字符串 如 and Id=@Id 如果有 . 换为 _ /// </summary> /// <returns></returns> public string ToParameterString() { StringBuilder str = new StringBuilder(); if (Filters == null) return ""; foreach (FilterItem item in Filters) { str.Append(" " + item.Logical); str.Append(" " + item.LeftParentheses); str.Append(" " + item.Column); str.Append(" " + item.Operator); str.Append(" @" + item.ParameterName); str.Append(" " + item.RigthParentheses); } return str.ToString(); } }
6.定义自定义查询事件,在调用控件方触发
public event btnSearchHandle SearchClick; public delegate void btnSearchHandle(object sender, SearchEventArgs e); public class SearchEventArgs : EventArgs { private Filter filter; public SearchEventArgs(Filter _filter) { this.filter = _filter; } public Filter Filter { get { return filter; } } /// <summary> /// 此方法还需要改进 返回值应该是 Filter 类,而不是 List<FilterItem> 泛型 /// </summary> /// <param name="group"></param> /// <returns></returns> public List<FilterItem> GetGroup(string group) { return filter.FindGroup(group); } }
7拼接查询字符串
#region 拼接查询字符串 private Filter GetItemControl() { Control c = mainPanelControl; string strlogical = ""; string strleftParentheses=""; string strColumn = ""; string strOperator = ""; string strValue = ""; string strrigthParentheses = ""; Filter filter = new Filter(); filter.isError=true; filter.Error = ""; for (int i = c.Controls.Count - 1; i >-1; i--) { if (c.Controls[i] is DevExpress.XtraEditors.PanelControl) { if (c.Controls[i].BackColor == System.Drawing.Color.Red) c.Controls[i].BackColor = c.Controls[i].Parent.BackColor; Control.ControlCollection controlCollection = c.Controls[i].Controls; ComboBoxEdit txtLogical = controlCollection[2] as ComboBoxEdit; ComboBoxEdit txtLeftParentheses = controlCollection[3] as ComboBoxEdit; ComboBoxEdit txtRightParentheses = controlCollection[4] as ComboBoxEdit; ImageComboBoxEdit txtColumn0 = controlCollection[0] as ImageComboBoxEdit; ComboBoxEdit txtOperator = controlCollection[1] as ComboBoxEdit; TextEdit txtValue0 = controlCollection[6] as TextEdit; if (txtColumn0.EditValue == null || string.IsNullOrEmpty(txtColumn0.EditValue.ToString())) { continue; } if (txtLogical.EditValue == null || string.IsNullOrEmpty(txtLogical.EditValue.ToString()) || txtOperator.EditValue == null || string.IsNullOrEmpty(txtOperator.EditValue.ToString())) { //filter.Clear(); //filter.isError = false; //filter.Error += "Column \"" + ((DevExpress.XtraEditors.Controls.ImageComboBoxItem)txtColumn0.SelectedItem).Description + "\" no value," + System.Environment.NewLine; //c.Controls[i].BackColor = System.Drawing.Color.Red; //txtValue0.EditValue == null || string.IsNullOrEmpty(txtValue0.EditValue.ToString() continue; } if (txtValue0 == null) { if (controlCollection[6] is PopEditColumns) { PopEditColumns aa = controlCollection[6] as PopEditColumns; if (aa.GetSelectRow(false) == null) { continue; } } else { continue; } } else if (txtValue0.EditValue == null) { continue; } strlogical = txtLogical.EditValue.ToString(); strleftParentheses = txtLeftParentheses.EditValue.ToString(); strColumn = txtColumn0.EditValue.ToString(); strOperator = txtOperator.EditValue.ToString(); if(txtValue0!=null) strValue = txtValue0.EditValue.ToString().Replace("'","''"); strrigthParentheses = txtRightParentheses.EditValue.ToString(); FilterItem filterItem = new FilterItem(); filterItem.Logical = strlogical; filterItem.LeftParentheses = strleftParentheses; filterItem.Column = strColumn; filterItem.Operator = strOperator; filterItem.ParameterName = "ParameterName_" + i.ToString(); if (controlCollection[6] is PopEditColumns) { StringBuilder str = new StringBuilder(); PopEditColumns controlColumns = controlCollection[6] as PopEditColumns; List<DataRow> dr = controlColumns.GetSelectRow(false); if (dr == null || dr.Count == 0) continue; string[] keyname = filterItem.Column.Split('/'); foreach (DataRow item in dr) { str.Append(" or "); bool temp = true; for (int j = 0; j < keyname.Length; j++) { if (String.IsNullOrEmpty(keyname[j])) continue; string[] columns = keyname[j].Split(':'); if (item[columns[0]] == null) continue; if (temp) { str.Append(columns[1] + "='" + item[columns[0]].ToString() + "'"); temp = false; } else { str.Append(" and " + columns[1] + "='" + item[columns[0]].ToString() + "'"); } } } str = str.Remove(0, 4).Insert(0, " (").Insert(str.Length, ") "); filterItem.Column = ""; filterItem.Operator = ""; filterItem.Value = str.ToString(); } else { if (txtValue0 is DevExpress.XtraEditors.SpinEdit) { filterItem.Value = strValue; } else if (txtValue0 is DevExpress.XtraEditors.DateEdit) { DateTime valuedate = new DateTime(); DateTime.TryParse(strValue, out valuedate); filterItem.Value = strValue; if (filterItem.Operator == "<" || filterItem.Operator == "<=") { filterItem.Value = "convert(datetime,'" + valuedate.ToString("yyyy-MM-dd 23:59:59.999") + "')"; } else if (filterItem.Operator == ">" || filterItem.Operator == ">=") { filterItem.Value = "convert(datetime,'" + valuedate.ToString("yyyy-MM-dd 00:00:00.000") + "')"; } else if (filterItem.Operator == "=") { filterItem.Operator = " between "; filterItem.Value = "convert(datetime,'" + valuedate.ToString("yyyy-MM-dd 00:00:00.000") + "')" + " and " + "convert(datetime,'" + valuedate.ToString("yyyy-MM-dd 23:59:59.999") + "')"; } else { filterItem.Value = "convert(datetime,'" + valuedate + "')"; } } else if (txtValue0 is DevExpress.XtraEditors.MemoExEdit) { if (strOperator == "in") { //分隔符 ",",";" "换行" //string[] split = strValue.Split(new string[] { ",", ";", System.Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries); strValue = strValue.Replace(",", "','").Replace(";", "','").Replace(System.Environment.NewLine, "','"); filterItem.Value = "('" + strValue + "')"; } else filterItem.Value = " '" + strValue + "'"; } else { if (strOperator == "in") filterItem.Value = "(" + strValue + ")"; else filterItem.Value = " '" + strValue + "'"; } } filterItem.RigthParentheses = strrigthParentheses; filter.Add(filterItem); } } return filter; } #endregion
主要方法在这了,并附上源码,里面包含测试用例,下载源码