C # Implementing a common data filtering form

Recently has been doing WinForm project, so often some new ideas or try to share with you, before sharing with you the general form mask layer, universal attachable data binding DataGridView, form fade , Today to share a common feature in other software: Data filtering queries.

Let's look at the overall effect I've achieved:

After filtering:

Let's talk about the idea of realizing the above functions:

First of all, the design of the interface

1. Create a form (this is called Filter form Frmfilter), then place a DataGridView control in the upper part of the form, put a panel underneath it, and then put two buttons in the panel, as for how to better layout or whether it is necessary to adapt to the form changes, these are relatively simple, It is not introduced here;

2. Design the DataGridView control, add 4 columns (field name, operator, value, value 2), where field name, operator column support drop-down, value, value 2 column to support input

Interface design is very simple, now say the implementation of the code, the complete generation is as follows:

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>///Get the filter form (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 (); }    }}

The following are the form design codes that are automatically generated by the system:

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 & amp;& (components = null)) {components.            Dispose (); } base.        Dispose (disposing); } #region Windows Form Designer generated code///<summary>//Required method for Designer s        Upport-do not modify//the contents of this method with the Code editor. </summary> private void InitializeComponent () {this.tablelayoutpanel1 = new System.win DoWs.            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 = "Reset";            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 = "OK";            This.btnOk.UseVisualStyleBackColor = true;            This.btnOk.Click + = new System.EventHandler (This.btnok_click); Datagridfilter//This.dataGridFilter.ColumnHeadersHeightSizeMode = System.window            S.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.DataGridViewAutoSizeColumnM Ode.            Fill;            This.name.DataPropertyName = "name";            This.name.FillWeight = 80F;            This.name.HeaderText = "field name";            This.name.Name = "name";            Operators//This.operators.DataPropertyName = "operators";            This.operators.HeaderText = "operator";            This.operators.Name = "Operators"; Value//This.value.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColum            Nmode.fill; This.value.DataPropertyName = "value";            This.value.HeaderText = "value";            This.value.Name = "value"; value2//This.value2.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeCol            Umnmode.fill;            This.value2.DataPropertyName = "value2";            This.value2.HeaderText = "Value 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 = 1 D; This.            ShowInTaskbar = false; This. StartPosition = System.Windows.Forms.FormStartPosition.CenterParent; This.            Text = "Filter Query";            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; }}

represents the data source of the operator, I am a defined struct, as follows:

 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, "equals"},{notequal, "Not Equal to"}, {LessThan, "less than"},{greaterthan, "Greater than"}, {lessthanorequal, "less than or equal"},{greate        Rthanorequal, "greater than or equal to"}, {Contains, "contains"},{startswith, "contains"},{endswith, "End contains"}, {between, "interval"}    }; }
 

The Filtertable property is a table used to get the filter, the data of the table is obtained by binding the DataGridView control, and if you do not know why you can get the data by binding it to an empty table through the data source, please look for the relevant information, here is no explanation, Of course, you can also communicate with me through comments.

The above is about filtering the implementation of the form, I would like to explain the most important part, but also the most significant part, is: How to get the filter of the DataTable into a query statement, where the query statement includes SQL or expression tree, because it is through the filter form, So, about generating a query condition statement I put it to the completion of the call, of course, if you use only one way (entity class or table), you can directly integrate in the form class, directly return the generated query statement.

Because my project uses entity classes to query, I use the dynamic generation of lambda expression trees, and then using the Predicatebuilder class (which is what others have developed, see my previous post) for stitching, and finally generating an expression tree for the query, the implementation code is as follows:

        <summary>///Get Query expression tree (core code, this method generates an expression tree such as: t.id==1) (zuowenjun.cn)///</summary> <typeparam name= "TEntity" ></typeparam>//<param name= "FieldName" ></param>/ <param name= "Operatorname" ></param>///<param Name= "value" ></param>//<para M name= "value2" ></param>///<returns></returns> public static Expression<func<te Ntity, 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 ("operator cannot be empty!            "," Operatorname "); } if (!value. Canconvert (PType)) {throw new ArgumentException (string. The query value type for Format ("" {0} "is not correct and must be of type {1}!)            ", General.getdisplayname (FieldInfo), ptype.fullname)," value "); } if (Operatorname = = Filteroperators.between &&!value2. Canconvert (PType)) {throw new ArgumentException (string. The query value 2 of Format ("" {0} "is not of the correct type and must be of type {1}!)            ", General.getdisplayname (FieldInfo), ptype.fullname)," value ");            } Dynamic Convertedvalue = Convert.changetype (value, PType);            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 = Conve Rt.                ChangeType (value2, PType);                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 ("Invalid operator!            "," Operatorname "); } expression<func<tentity, bool>> lamexp = expression.lambda&Lt            Func<tentity, Bool>> (Expbody, Expparameter);        return lamexp; }

The following is a specific invocation:

Get the field for filtering (the corresponding column of the data list is directly used here, you can generate the corresponding list data source) (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; }//Toolbar Click Filter Query button event (zuowenjun.cn) private void Toolstrip_onfilter (object sender, EventArgs e) {if (filterf ORM = = null) {Filterform = new Frmfilter (Getquerygridcolumninfos (), "HeaderText", "Dataproperty            Name ");            } 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);//dynamically generate query expression tree where Expr = Whereexpr.and (fieldexpr);//Connect expression tree} firstloadlist ()//Load data and display}///load data (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); }

Because the code is more, and use some other well-written class, if there is not understand the place, you can comment in this article, I will help the answer, I hope to help everyone, the shortcomings are also welcome to testify, thank you!

C # Implementing a common data filtering form