使用DevExpress 控件开发通用查询控件（Winform）

来自：http://www.cnblogs.com/fengmazi/archive/2011/11/18/2254760.html

首先感谢我以前的组长－－志远，他教会了我很多很多东西，他的责任心，他的为人处事让我深深敬佩，

       在我们所有项目中，有一个功能是必不可少的，那就是查询，不同的需求，需要不同的查询条件，查询条件的设计也是千奇百怪，有时我就想，有没有一种通用的查询控件呢，这样，我们就不需要千篇一律写相同的代码，充分发挥代码的可重用性。

        一个查询功能有三个必要条件，一个标题，一个输入控件，一个查询按钮，复杂的我就不在列举了，只有想不到的，没有做不到的。

        如果要实现一个通用的查询功能，有几个前提必须明白

        1）什么是通用，在我的理解是，可以适合不同的需求。

        2）输入控件得包含基本的数据类型，如整形，浮点类型，日期，复制框，下拉列表。。。。。当然也有人说，他只输入一个文本框就行了，什么东西都可以输入，如果你这样想，下面的内容无需再看，这种牛人地球人都无法阻止他了，我更伤不起。

        3）多语言的支持，此文不会涉及到，在本人项目中已实现些了功能，我想大家会有很好的办法实现

        4）安全，而无需自己添加安全机制，如防SQL注入。

先上效果图，如果有不足之处，还需大家提点，小人不才，不是做得非常完美。

 

环境配置

        开发工具：VS2010，.NET Framework4.0,DXperience-10.2.5

        1.先从创建项目开始：创建解决方案(Com),创建类库项目(QueryLibrary),接着创建Window窗口应用程序（ControlQueryText）此项目用于测试

        2.在库类Control添加用户控件ControlQuery.cs，界面布局如下图

        3.下面开始编写代码了，定义输入框所支持的数据类型，使用枚举类型

?

1 2 3 4 5 6 7 8 9 10 11

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等），右括号

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

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脚本

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196

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.定义自定义查询事件,在调用控件方触发

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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拼接查询字符串

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166

#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

 

主要方法在这了，并附上源码,里面包含测试用例，下载源码