首先感谢我以前的组长－－志远，他教会了我很多很多东西，他的责任心，他的为人处事让我深深敬佩，
在我们所有项目中，有一个功能是必不可少的，那就是查询，不同的需求，需要不同的查询条件，查询条件的设计也是千奇百怪，有时我就想，有没有一种通用的查询控件呢，这样，我们就不需要千篇一律写相同的代码，充分发挥代码的可重用性。
一个查询功能有三个必要条件，一个标题，一个输入控件，一个查询按钮，复杂的我就不在列举了，只有想不到的，没有做不到的。
如果要实现一个通用的查询功能，有几个前提必须明白
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
主要方法在这了，并附上源码,里面包含测试用例，下载源码