你的位置:首页 > ASP.net教程

[ASP.net教程]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);    }

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