• 使用DevExpress 控件开发通用查询控件(Winform)


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

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

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

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

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

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

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

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

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

    效果0效果1(下拉列表)

    效果2(日期)效果3(GridLookUpEdit)

    效果4(PopEdit)

    环境配置

            开发工具: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

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

  • 相关阅读:
    保存宏的表-TRMAC
    问题解决]Personnel master record xxxx not found (check entry)
    检查员工号是否存在函数[RP_CHECK_PERNR]
    ◆◆0SAP IDOC 开发入门
    IDoc测试工具WE19使用教程
    ◆◆0如何从其他系统导入测试IDOC文件-WE19
    通过message type查找inbound函数-WE57,WE42,
    使用IDOC创建会计凭证[ACC_GL_POSTING01]
    ◆◆0[REUSE_ALV_GRID_DISPLAY_LVC]ALV中字段显示前导零(leading zero)
    不同网段_Pycharm访问服务器
  • 原文地址:https://www.cnblogs.com/fengmazi/p/2254760.html
Copyright © 2020-2023  润新知