Design and Implementation of universal winform multi-condition dynamic query code

Http://blog.csdn.net/huomm/archive/2008/03/22/2205564.aspx

I often encounter the problem of multi-condition joint query. I used to think that it is very simple. I always design the layout from the beginning.CodeOften, it takes a long time to query the surface, and the effect is not bad.

Some time ago, I made a relatively general multi-condition dynamic query surface, which is quite convenient to reuse. I will provide a reference for you.
Source file for download link: Multi-condition dynamic query general template download

Main layout after running:

Main general functions and requirements:

Main method body:

Dynamic display query conditions:

Set the display mode according to the query conditions:

1 // display the page for initializing the joint Query
2
3 private void conditionbind ()
4 {
5/** // the query conditions are fixed
6 datatable dt = new datatable ();
7 datacolumncollection columns = DT. columns;
8 columns. Add ("name ");
9 columns. Add ("key ");
10 datarowcollection rows = DT. Rows;
11 rows. Add ("all", "all ");
12 rows. Add ("Document No.", "Code ");
13 rows. Add ("supplier name", "suppliername ");
14 rows. Add ("managers", "employeesname ");
15 rows. Add ("time", "Time ");
16
17
18 try
19 {
20 For (INT I = 0; I <this. FPL. Controls. Count; I ++)
21 {
22
23 if (this. Controls. Find ("FPL" + I, true). length> 0)
24 {
25 (ComboBox) This. Controls. Find ("cbselect" + I, true) [0]). displaymember = "name ";
26 (ComboBox) This. Controls. Find ("cbselect" + I, true) [0]). valuemember = "key ";
27 // use copy to solve the linkage problem
28 (ComboBox) This. Controls. Find ("cbselect" + I, true) [0]). datasource = DT. Copy ();
29}
30
31}
32
33
34}
35 catch (exception ex)
36 {
37 MessageBox. Show (ex. Message );
38}
39
40
41
42}

1 private void setfiltercondition (ref ComboBox conditionselect)
2 {
3 try
4 {
5 For (INT I = 0; I <this. FPL. Controls. Count; I ++)
6 {
7 if (conditionselect. Name = "cbselect" + I. tostring ())
8 {
9 If (conditionselect. Text = "time ")
10 {
11 if (this. Controls. Find ("fplfilter" + I, true). length> 0)
12 This. Controls. Find ("fplfilter" + I, true) [0]. Visible = true;
13 if (this. Controls. Find ("txtfilter" + I, true). length> 0)
14 This. Controls. Find ("txtfilter" + I, true) [0]. Visible = false;
15 if (this. Controls. Find ("cbfilter" + I, true). length> 0)
16 this. Controls. Find ("cbfilter" + I, true) [0]. Visible = false;
17}
18 else if (conditionselect. Text = "") // you need to modify or add
19 {
20 if (this. Controls. Find ("fplfilter" + I, true). length> 0)
21 This. Controls. Find ("fplfilter" + I, true) [0]. Visible = false;
22 if (this. Controls. Find ("txtfilter" + I, true). length> 0)
23 This. Controls. Find ("txtfilter" + I, true) [0]. Visible = false;
24 if (this. Controls. Find ("cbfilter" + I, true). length> 0)
25 this. Controls. Find ("cbfilter" + I, true) [0]. Visible = true;
26}
27 else
28 {
29 If (this. Controls. Find ("fplfilter" + I, true). length> 0)
30 this. Controls. Find ("fplfilter" + I, true) [0]. Visible = false;
31 if (this. Controls. Find ("txtfilter" + I, true). length> 0)
32 This. Controls. Find ("txtfilter" + I, true) [0]. Visible = true;
33 If (this. Controls. Find ("cbfilter" + I, true). length> 0)
34 this. Controls. Find ("cbfilter" + I, true) [0]. Visible = false;
35
36}
37
38
39
40}
41}
42}
43 catch (exception ex)
44 {
45 MessageBox. Show (ex. Message );
46}
47}

Add conditions
Extract SQL statements

1
2 Private void addfilter ()
3 {
4 try
5 {
6 For (INT I = 0; I <this. FPL. Controls. Count; I ++)
7 {
8 replaceable Code # region replaceable code
9 // If (flowlayoutpanel) This. FPL. controls [I]). Visible = false)
10 //{
11 // (flowlayoutpanel) This. FPL. controls [I]). Visible = true;
12 // break;
13 //}
14 # endregion
15
16 if (this. Controls. Find ("FPL" + I, true). length> 0)
17 {
18 if (this. Controls. Find ("FPL" + I, true) [0]. Visible = false)
19 {
20 This. Controls. Find ("FPL" + I, true) [0]. Visible = true;
21 break;
22}
23}
24
25}
26}
27 catch (exception ex)
28 {
29 MessageBox. Show (ex. Message );
30}
31}

1 private string buildsql ()
2 {
3 try
4 {
5
6 stringbuilder sb = new stringbuilder ();
7 // if necessary, the modification indicates that the modification is universal.
8 sb. append ("select * From instorebill_view ");
9 // used to determine whether the first data is used to add the WHERE clause
10 int isfirst = 0;
11 For (INT I = 0; I <this. FPL. Controls. Count; I ++)
12 {
13. generate an SQL statement # generate an SQL statement for Region
14 if (this. Controls. Find ("FPL" + I, true) [0]. Visible = true)
15 {
16
17 if (this. Controls. Find ("cbselect" + I, true) [0]. Text! = "All ")
18 {
19 ComboBox selectcondition = (ComboBox) This. Controls. Find ("cbselect" + I, true) [0];
20
21 if (this. Controls. Find ("txtfilter" + I, true) [0]. Visible = true)
22 {// This type
23 isfirst ++;
24 if (isfirst = 1) // if it is the first entry, isfirst should be 1
25 {
26 Sb. append ("where ");
27}
28 else if (isfirst> 1)
29 {
30 sb. append ("and ");
31}
32 else
33 {}
34
35 sb. append (string. format ("{0} like '% {1} %'", selectcondition. selectedvalue. tostring (). trim (), this. controls. find ("txtfilter" + I. tostring (), true) [0]. text. trim ()));
36
37}
38 else if (this. Controls. Find ("cbfilter" + I, true) [0]. Visible = true)
39 {// drop-down box type
40 isfirst ++;
41 if (isfirst = 1) // if it is the first entry, isfirst should be 1
42 {
43 sb. append ("where ");
44}
45 else if (isfirst> 1)
46 {
47 Sb. append ("and ");
48}
49 else
50 {}
51 sb. append (string. format ("{0} like '% {1} %'", selectcondition. selectedvalue. tostring (). trim (), this. controls. find ("cbfilter" + I. tostring (), true) [0]. text. trim ()));
52
53
54}
55 else
56 {// time type
57 isfirst ++;
58 If (isfirst = 1) // if it is the first entry, isfirst should be 1
59 {
60 sb. append ("where ");
61}
62 else if (isfirst> 1)
63 {
64 sb. append ("and ");
65}
66 else
67 {}
68 sb. append (string. format ("{0} between' {1} 'and' {2} '", selectcondition. selectedvalue. tostring (). trim (), (datetimepicker) This. controls. find ("DTP" + I. tostring () + "begin", true) [0]). value. toshortdatestring (), (datetimepicker) This. controls. find ("DTP" + I. tostring () + "end", true) [0]). value. toshortdatestring ()));
69
70}
71
72}
73}
74 # endregion
75}
76 return sb. tostring ();
77}
78 catch (exception ex)
79 {
80 MessageBox. Show (ex. Message );
81 return "";
82}
83
84}

Note: during the design process, I felt that the layout design was the most messy. Maybe it was not very skillful, and a lot of time was wasted. Fortunately, the general or on-demand copy was okay.

Source file for download link: Multi-condition dynamic query general template download