C # implement a common data filtering form,

C # implement a common data filtering form,

I have been working on WINFORM projects recently, so I often have some new ideas or try to share them with you.Common form mask layer, universally available data-bound DataGridView, form fade-inToday, we will share a common feature in other software: Data Filtering and query.

Let's take a look at the overall effect of my implementation:

 

After filtering:

Let's talk about how to implement the above functions:

First, let's talk about the interface design.

1. create a form (called the filter form FrmFilter), place a DataGridView control in the upper part of the form, and a Panel in the lower part. Then, put two buttons in the Panel, as for how to better layout or whether to adapt to form changes, these are relatively simple and will not be introduced here;

2. design the DataGridView control and add four columns (field name, operator, value, and value 2) respectively. The field name and operator column must support drop-down, and the value and value columns must support input.

The interface design is very simple. Now let's talk about the code implementation. 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> // obtain the table with filter conditions (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 (maid 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 is the automatically generated form design code:

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 shocould 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. points (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 = "Confirm"; 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 = "maid"; 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 = "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. dataGridViewAutoSizeColumnMode. fill; this. value. dataPropertyName = "value"; this. value. headerText = "value"; this. value. name = "value"; // value2 // this. value2.AutoSizeMode = System. windows. forms. dataGridViewAutoSizeColumnMode. 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 = 1D; 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 datagridatagrifilter; 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 in the constructor indicates the operator data source. I define a 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, "Equal to" },{ NotEqual, "not Equal to" },{ LessThan, "less than" },{ GreaterThan, "greater than" },{ LessThanOrEqual, "less than or equal to" },{ GreaterThanOrEqual, "greater than or equal to" },{ Contains, "include" },{ StartsWith, "beginning with" },{ EndsWith, "ended with" },{ Between, "interval "}};}

The FilterTable attribute is used to obtain the table with filtering conditions. The table data is obtained by binding the DataGridView control. If you do not know why you can obtain data by binding the data source to an empty table, please refer to the relevant materials. I will not describe them here. Of course, you can also share your comments with me.

The above are all about the implementation of the filter form. Next I want to explain the most keyword and the most important part, that is, how to convert the obtained filter condition DataTable into a query statement, the query statements here include SQL or Expression Tree. Because the query forms are filtered, I put the query condition statements generated after calling the form, of course, if you only use one method (entity class or table), you can directly integrate it into the form class and directly return the generated query statement.

Because my project uses Entity classes for queries, I dynamically generate a Lambda Expression Tree and splice it with the PredicateBuilder class (this is a class developed by someone else, see my previous blog post, finally, the expression tree for query is generated. The implementation code is as follows:

/// <Summary> // obtain the query expression tree (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 ("the operator cannot be blank! "," OperatorName ");} dynamic convertedValue; if (! Value. TryChangeType (pType, out convertedValue) {throw new ArgumentException (string. Format ("[{0}] query value type is incorrect, it must be {1} type! ", 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}] the query value type 2 is incorrect. It must be of the {1} type! ", 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) {exp Body = 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 <Func <TEntity, bool> (expBody, expParameter); return lamExp ;}

TryChangeType is an extension that supports conversion of any type. This extension method references other custom extension methods. The specific method is defined as follows:

/// <Summary> /// determine whether it can be converted to the specified type. /// </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. change Type (str, type) ;}} else {returnValue = Convert. changeType (str, type);} return true;} catch {returnValue = type. defaultValue (); return false ;}} /// <summary> /// determines whether the data type is empty. /// </summary> /// <param name = "type"> </param> // <returns> </returns> public static bool IsNullableType (this Type type) {return (type. isGenericType & type. getGenericTypeDefinition (). equals (typeof (Nullable <> )));}/// <Summary> /// default value /// </summary> /// <param name = "targetType"> </param> /// <returns> </returns> public static dynamic DefaultValue (this Type targetType) {return targetType. isValueType? Activator. CreateInstance (targetType): null ;}

　　

The following are specific calls:

// Obtain the fields used for filtering (the corresponding columns of the Data List are used here, and 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 (maid. columns [I]);} return cols;} // click the filter query button event (zuowenjun.cn) private void ToolStrip_OnFilter (object sender, EventArgs e) {if (filterForm = null) on the toolbar) {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); // dynamically generate the query expression tree whereExpr = whereExpr. and (fieldExpr); // connection 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 there are many codes and some other well-written classes are used, if you do not understand them, you can comment on them in this article. I will help you solve the problem, hoping to help you, you are also welcome to testify against the shortcomings. Thank you!