C#实现通用数据过滤窗体

最近一直在做WINFORM项目，所以经常有些新的想法或尝试与大家分享，之前与大家分享了通用窗体遮罩层、通用可附加数据绑定的DataGridView、窗体渐显，今天来分享一个大家在其它软件中常见的功能：数据过滤查询。

先看一下我实现的的整体效果：

过滤之后：

说一下实现上述功能的思路：

首先说一下界面的设计》

1.创建一个窗体（在此称作：过滤窗体FrmFilter），然后在窗体上部放一个DataGridView控件、下面放一个Panel，然后Panel中放两个按钮，至于如何更好的布局或是否需要适应窗体变化，这些都比较简单，在此就不介绍了；

2.设计DataGridView控件，分别加入4列（字段名、运算符、值、值2），其中字段名、运算符列需支持下拉，值、值2列需支持输入

界面设计很简单，现在说一下代码的实现，完整代如下：

using System;

using System.Data;

using System.Windows.Forms;

using TEMS.Service;

namespace TEMS.Forms

{

    public partial class FrmFilter : FormBase

    {

        private DataTable filterTable = null;

        /// <summary>

        /// 获取过滤条件的表格(zuowenjun.cn)

        /// </summary>

        public DataTable FilterTable

        {

            get

            {

                return filterTable;

            }

        }

        public FrmFilter(object source, string text, string value)

        {

            InitializeComponent();

            dataGridFilter.AutoGenerateColumns = false;

            var col0 = dataGridFilter.Columns[0] as DataGridViewComboBoxColumn;

            col0.DataSource = source;

            col0.DisplayMember = text;

            col0.ValueMember = value;

            var col1 = dataGridFilter.Columns[1] as DataGridViewComboBoxColumn;

            col1.DataSource = FilterOperators.Operators;

            col1.DisplayMember = "Value";

            col1.ValueMember = "Key";

            InitFilterDataTable();

        }

        private void InitFilterDataTable()

        {

            filterTable = new DataTable();

            foreach (DataGridViewColumn col in dataGridFilter.Columns)

            {

                filterTable.Columns.Add(col.DataPropertyName,typeof(string));

            }

            dataGridFilter.DataSource = filterTable;

        }

        private void btnOk_Click(object sender, EventArgs e)

        {

            this.Close();

        }

        private void btnReset_Click(object sender, EventArgs e)

        {

            InitFilterDataTable();

            this.Close();

        }

    }

}

以下是系统自动生成的窗体设计代码：

namespace TEMS.Forms

{

    partial class FrmFilter

    {

        /// <summary>

        /// Required designer variable.

        /// </summary>

        private System.ComponentModel.IContainer components = null;

        /// <summary>

        /// Clean up any resources being used.

        /// </summary>

        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>

        protected override void Dispose(bool disposing)

        {

            if (disposing && (components != null))

            {

                components.Dispose();

            }

            base.Dispose(disposing);

        }

        #region Windows Form Designer generated code

        /// <summary>

        /// Required method for Designer support - do not modify

        /// the contents of this method with the code editor.

        /// </summary>

        private void InitializeComponent()

        {

            this.tableLayoutPanel1 = new System.Windows.Forms.TableLayoutPanel();

            this.panel1 = new System.Windows.Forms.Panel();

            this.btnReset = new System.Windows.Forms.Button();

            this.btnOk = new System.Windows.Forms.Button();

            this.dataGridFilter = new System.Windows.Forms.DataGridView();

            this.name = new System.Windows.Forms.DataGridViewComboBoxColumn();

            this.operators = new System.Windows.Forms.DataGridViewComboBoxColumn();

            this.value = new System.Windows.Forms.DataGridViewTextBoxColumn();

            this.value2 = new System.Windows.Forms.DataGridViewTextBoxColumn();

            this.tableLayoutPanel1.SuspendLayout();

            this.panel1.SuspendLayout();

            ((System.ComponentModel.ISupportInitialize)(this.dataGridFilter)).BeginInit();

            this.SuspendLayout();

            //

            // tableLayoutPanel1

            //

            this.tableLayoutPanel1.ColumnCount = 1;

            this.tableLayoutPanel1.ColumnStyles.Add(new System.Windows.Forms.ColumnStyle());

            this.tableLayoutPanel1.Controls.Add(this.panel1, 0, 1);

            this.tableLayoutPanel1.Controls.Add(this.dataGridFilter, 0, 0);

            this.tableLayoutPanel1.Dock = System.Windows.Forms.DockStyle.Fill;

            this.tableLayoutPanel1.Location = new System.Drawing.Point(0, 0);

            this.tableLayoutPanel1.Name = "tableLayoutPanel1";

            this.tableLayoutPanel1.RowCount = 2;

            this.tableLayoutPanel1.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Percent, 90F));

            this.tableLayoutPanel1.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Percent, 10F));

            this.tableLayoutPanel1.Size = new System.Drawing.Size(598, 436);

            this.tableLayoutPanel1.TabIndex = 0;

            //

            // panel1

            //

            this.panel1.Controls.Add(this.btnReset);

            this.panel1.Controls.Add(this.btnOk);

            this.panel1.Dock = System.Windows.Forms.DockStyle.Fill;

            this.panel1.Location = new System.Drawing.Point(3, 395);

            this.panel1.Name = "panel1";

            this.panel1.Size = new System.Drawing.Size(592, 38);

            this.panel1.TabIndex = 0;

            //

            // btnReset

            //

            this.btnReset.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)

                        | System.Windows.Forms.AnchorStyles.Right)));

            this.btnReset.Location = new System.Drawing.Point(508, 6);

            this.btnReset.Name = "btnReset";

            this.btnReset.Size = new System.Drawing.Size(75, 23);

            this.btnReset.TabIndex = 0;

            this.btnReset.Text = "重 置";

            this.btnReset.UseVisualStyleBackColor = true;

            this.btnReset.Click += new System.EventHandler(this.btnReset_Click);

            //

            // btnOk

            //

            this.btnOk.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)

                        | System.Windows.Forms.AnchorStyles.Right)));

            this.btnOk.Location = new System.Drawing.Point(427, 6);

            this.btnOk.Name = "btnOk";

            this.btnOk.Size = new System.Drawing.Size(75, 23);

            this.btnOk.TabIndex = 0;

            this.btnOk.Text = "确 定";

            this.btnOk.UseVisualStyleBackColor = true;

            this.btnOk.Click += new System.EventHandler(this.btnOk_Click);

            //

            // dataGridFilter

            //

            this.dataGridFilter.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;

            this.dataGridFilter.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {

            this.name,

            this.operators,

            this.value,

            this.value2});

            this.dataGridFilter.Dock = System.Windows.Forms.DockStyle.Fill;

            this.dataGridFilter.Location = new System.Drawing.Point(3, 3);

            this.dataGridFilter.Name = "dataGridFilter";

            this.dataGridFilter.RowTemplate.Height = 23;

            this.dataGridFilter.Size = new System.Drawing.Size(592, 386);

            this.dataGridFilter.TabIndex = 1;

            //

            // name

            //

            this.name.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.Fill;

            this.name.DataPropertyName = "name";

            this.name.FillWeight = 80F;

            this.name.HeaderText = "字段名";

            this.name.Name = "name";

            //

            // operators

            //

            this.operators.DataPropertyName = "operators";

            this.operators.HeaderText = "运算符";

            this.operators.Name = "operators";

            //

            // value

            //

            this.value.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.Fill;

            this.value.DataPropertyName = "value";

            this.value.HeaderText = "值";

            this.value.Name = "value";

            //

            // value2

            //

            this.value2.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.Fill;

            this.value2.DataPropertyName = "value2";

            this.value2.HeaderText = "值2";

            this.value2.Name = "value2";

            //

            // FrmFilter

            //

            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);

            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;

            this.ClientSize = new System.Drawing.Size(598, 436);

            this.Controls.Add(this.tableLayoutPanel1);

            this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog;

            this.MaximizeBox = false;

            this.Name = "FrmFilter";

            this.Opacity = 1D;

            this.ShowInTaskbar = false;

            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterParent;

            this.Text = "筛选查询";

            this.tableLayoutPanel1.ResumeLayout(false);

            this.panel1.ResumeLayout(false);

            ((System.ComponentModel.ISupportInitialize)(this.dataGridFilter)).EndInit();

            this.ResumeLayout(false);

        }

        #endregion

        private System.Windows.Forms.TableLayoutPanel tableLayoutPanel1;

        private System.Windows.Forms.Panel panel1;

        private System.Windows.Forms.Button btnReset;

        private System.Windows.Forms.Button btnOk;

        private System.Windows.Forms.DataGridView dataGridFilter;

        private System.Windows.Forms.DataGridViewComboBoxColumn name;

        private System.Windows.Forms.DataGridViewComboBoxColumn operators;

        private System.Windows.Forms.DataGridViewTextBoxColumn value;

        private System.Windows.Forms.DataGridViewTextBoxColumn value2;

    }

}

构造函数中的的 FilterOperators.Operators表示的是运算符的数据源，我是定义的一个struct，如下：

    public struct FilterOperators

    {

        public const string Equal = "Equal";

        public const string NotEqual = "NotEqual";

        public const string LessThan = "LessThan";

        public const string GreaterThan = "GreaterThan";

        public const string LessThanOrEqual = "LessThanOrEqual";

        public const string GreaterThanOrEqual = "GreaterThanOrEqual";

        public const string Contains = "Contains";

        public const string StartsWith = "StartsWith";

        public const string EndsWith = "EndsWith";

        public const string Between = "Between";

        public static KeyValueList<string, string> Operators = new KeyValueList<string, string>()

        {

            {Equal,"等于"},{NotEqual,"不等于"},

            {LessThan,"小于"},{GreaterThan,"大于"},

            {LessThanOrEqual,"小于或等于"},{GreaterThanOrEqual,"大于或等于"},

            {Contains,"包含"},{StartsWith,"开头包含"},{EndsWith,"结尾包含"},

            {Between,"区间"}

        };

    }

FilterTable属性是用来获取过滤条件的表格，表格的数据是通过绑定DataGridView控件来获得的，如果不知道为什么通过数据源绑定到一个空表格就能获取数据，请查找相关资料，这里不作说明，当然也可以通过评论与我交流。

以上都是关于过滤窗体的实现，下面我要讲解最关键字部份，也是最重要的部份，就是：如何将获得的过滤条件DataTable转换成查询语句，这里的查询语句包括SQL或表达式树，由于是通过的过滤窗体，所以关于生成查询条件语句我放在了调用完该窗体后来实现，当然如果大家只用一种方式（实体类或表），可以直接集成在窗体类中，直接返回生成的查询语句即可。

因为我项目中用的是实体类来查询，所以我采用动态生成Lambda表达式树，然后借助PredicateBuilder类（这是别人开发的类，详见我之前的博文）进行拼接，最后生成用于查询的表达式树，实现代码如下：

        /// <summary>

        /// 获取查询表达式树 (zuowenjun.cn)

        /// </summary>

        /// <typeparam name="TEntity"></typeparam>

        /// <param name="fieldName"></param>

        /// <param name="operatorName"></param>

        /// <param name="value"></param>

        /// <param name="value2"></param>

        /// <returns></returns>

        public static Expression<Func<TEntity, bool>> GetQueryExpression<TEntity>(string fieldName, string operatorName, string value, string value2) where TEntity : class

        {

            PropertyInfo fieldInfo = typeof(TEntity).GetProperty(fieldName, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);

            Type pType = fieldInfo.PropertyType;

            if (string.IsNullOrEmpty(operatorName))

            {

                throw new ArgumentException("运算符不能为空！", "operatorName");

            }

            dynamic convertedValue;

            if (!value.TryChangeType(pType, out convertedValue))

            {

                throw new ArgumentException(string.Format("【{0}】的查询值类型不正确,必须为{1}类型！", General.GetDisplayName(fieldInfo), pType.FullName), "value");

            }

            ParameterExpression expParameter = Expression.Parameter(typeof(TEntity), "f");

            MemberExpression expl = Expression.Property(expParameter, fieldInfo);

            ConstantExpression expr = Expression.Constant(convertedValue, pType);

            Expression expBody = null;

            Type expType = typeof(Expression);

            var expMethod = expType.GetMethod(operatorName, new[] { expType, expType });

            if (expMethod != null)

            {

                expBody = (Expression)expMethod.Invoke(null, new object[] { expl, expr });

            }

            else if (FilterOperators.Between == operatorName)

            {

                dynamic convertedValue2;

                if (!value2.TryChangeType(pType, out convertedValue2))

                {

                    throw new ArgumentException(string.Format("【{0}】的查询值2类型不正确,必须为{1}类型！", General.GetDisplayName(fieldInfo), pType.FullName), "value");

                }

                ConstantExpression expr2 = Expression.Constant(convertedValue2, pType);

                expBody = Expression.GreaterThanOrEqual(expl, expr);

                expBody = Expression.AndAlso(expBody, Expression.LessThanOrEqual(expl, expr2));

            }

            else if (new[] { FilterOperators.Contains, FilterOperators.StartsWith, FilterOperators.EndsWith }.Contains(operatorName))

            {

                expBody = Expression.Call(expl, typeof(string).GetMethod(operatorName, new Type[] { typeof(string) }), expr);

            }

            else

            {

                throw new ArgumentException("无效的运算符！", "operatorName");

            }

            Expression<Func<TEntity, bool>> lamExp = Expression.Lambda<Func<TEntity, bool>>(expBody, expParameter);

            return lamExp;

        }

其中TryChangeType是我扩展的支持任意类型的转换，该扩展方法又引用了其它的自定义扩展方法，具体方法定义如下：

        /// <summary>

        /// 判断是否可为转换为指定的类型

        /// </summary>

        /// <param name="str"></param>

        /// <param name="type"></param>

        /// <returns></returns>

        public static bool TryChangeType(this object str, Type type,out dynamic returnValue)

        {

            try

            {

                if (type.IsNullableType())

                {

                    if (str == null || str.ToString().Length == 0)

                    {

                        returnValue = null;

                    }

                    else

                    {

                        type = type.GetGenericArguments()[0];

                        returnValue = Convert.ChangeType(str, type);

                    }

                }

                else

                {

                    returnValue = Convert.ChangeType(str, type);

                }

                return true;

            }

            catch

            {

                returnValue = type.DefaultValue();

                return false;

            }

        }

        /// <summary>

        /// 判断是否为可空类型

        /// </summary>

        /// <param name="type"></param>

        /// <returns></returns>

        public static bool IsNullableType(this Type type)

        {

            return (type.IsGenericType &&

              type.GetGenericTypeDefinition().Equals

              (typeof(Nullable<>)));

        }

        /// <summary>

        /// 默认值

        /// </summary>

        /// <param name="targetType"></param>

        /// <returns></returns>

        public static dynamic DefaultValue(this Type targetType)

        {

            return targetType.IsValueType ? Activator.CreateInstance(targetType) : null;

        }

以下是具体的调用：

//获取用于过滤的字段（这里直接采用数据列表的相应列，大家可以生成相应的List数据源） (zuowenjun.cn)

private List<DataGridViewColumn> GetQueryGridColumnInfos()

        {

            List<DataGridViewColumn> cols = new List<DataGridViewColumn>();

            for (int i = 0; i < dataGridBase.Columns.Count - 3; i++)

            {

                cols.Add(dataGridBase.Columns[i]);

            }

            return cols;

        }

//工具栏点击过滤查询按钮事件 (zuowenjun.cn)

private void ToolStrip_OnFilter(object sender, EventArgs e)

        {

            if (filterForm == null)

            {

                filterForm = new FrmFilter(GetQueryGridColumnInfos(), "HeaderText", "DataPropertyName");

            }

            filterForm.ShowDialog(Common.MainForm);

            whereExpr = PredicateBuilder.True<Category>();

            var p = whereExpr.Parameters[0];

            foreach (DataRow row in filterForm.FilterTable.Rows)

            {

                string fieldName = row[0].ToString();

                string opt = row[1].ToString();

                string value = row[2].ToString();

                string value2 = row[3].ToString();

                var fieldExpr = Common.GetQueryExpression<Category>(fieldName, opt, value, value2);//动态生成查询表达式树

                whereExpr = whereExpr.And(fieldExpr);//连接表达式树

            }

            FirstLoadList();//加载数据并显示

        }

///加载数据 (zuowenjun.cn)

 private void FirstLoadList()

        {

            int recordCount = 0;

            base.PageInfo = new PageInfo();

            base.PageInfo.PageSize = 10;

            base.PageInfo.CurrentPageNo = 1;

            var resultList = QueryBusiness<Category>.GetListByPage(whereExpr, t => t, t => t.ID, 1, base.PageInfo.PageSize, base.PageInfo.CurrentPageNo, out recordCount);

            base.PageInfo.RecordCount = recordCount;

            base.AppendDataToGrid(resultList,false);

        }

由于代码较多，且使用了一些其它的编写好的类，若有不明白的地方，可以在此文下评论，我会帮忙解答，希望能帮到大家，不足之处也欢迎大家指证，谢谢！

巴特西

C#实现通用数据过滤窗体

最新文章

热门文章