正文
WINFORM 多条件动态查询 通用代码的设计与实现
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
经常碰到多条件联合查询的问题,以前的习惯认为很简单总会从头开始设计布局代码,往往一个查询面要费上老半天的功夫,而效果也不咋地。
前段时间做了个相对通用的多条件动态查询面,复用起来还是挺方便的,放上来共参考指导。
供下载的源文件链接 : 多条件动态查询通用模板下载
主要的运行后布局:
主要的通用功能和要求:
主要的方法体:
动态的显示查询条件:
//初始化联合查询的页面显示
private void ConditionBind()
{
//查询条件邦定
DataTable dt = new DataTable();
DataColumnCollection columns = dt.Columns;
columns.Add("name");
columns.Add("key");
10 DataRowCollection rows = dt.Rows;
11 rows.Add("所有", "All");
12 rows.Add("单据号", "Code");
13 rows.Add("供应商名称", "SupplierName");
14 rows.Add("经办人", "EmployeesName");
15 rows.Add("时间", "time");
18 try
19 {
20 for (int i = 0; i < this.fpl.Controls.Count; i++)
21 {
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 //用copy解决联动问题
28 ((ComboBox)this.Controls.Find("cbSelect" + i, true)[0]).DataSource = dt.Copy();
29 }
31 }
34 }
35 catch (Exception ex)
36 {
37 MessageBox.Show(ex.Message);
38 }
42 }
private void ConditionBind()
{
//查询条件邦定
DataTable dt = new DataTable();
DataColumnCollection columns = dt.Columns;
columns.Add("name");
columns.Add("key");
10 DataRowCollection rows = dt.Rows;
11 rows.Add("所有", "All");
12 rows.Add("单据号", "Code");
13 rows.Add("供应商名称", "SupplierName");
14 rows.Add("经办人", "EmployeesName");
15 rows.Add("时间", "time");
18 try
19 {
20 for (int i = 0; i < this.fpl.Controls.Count; i++)
21 {
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 //用copy解决联动问题
28 ((ComboBox)this.Controls.Find("cbSelect" + i, true)[0]).DataSource = dt.Copy();
29 }
31 }
34 }
35 catch (Exception ex)
36 {
37 MessageBox.Show(ex.Message);
38 }
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 == "时间")
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 == "供应商名称")//在通用中需要修改或添加
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;
36 }
40 }
41 }
42 }
43 catch (Exception ex)
44 {
45 MessageBox.Show(ex.Message);
46 }
47 }
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 == "时间")
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 == "供应商名称")//在通用中需要修改或添加
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;
36 }
40 }
41 }
42 }
43 catch (Exception ex)
44 {
45 MessageBox.Show(ex.Message);
46 }
47 }
添加条件
1
2 private void AddFilter()
3 {
4 try
5 {
6 for (int i = 0; i < this.fpl.Controls.Count; i++)
7 {
8 可替换代码
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 }
25 }
26 }
27 catch (Exception ex)
28 {
29 MessageBox.Show(ex.Message);
30 }
31 }
2 private void AddFilter()
3 {
4 try
5 {
6 for (int i = 0; i < this.fpl.Controls.Count; i++)
7 {
8 可替换代码
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 }
25 }
26 }
27 catch (Exception ex)
28 {
29 MessageBox.Show(ex.Message);
30 }
31 }
提取sql语句
1 private string BuildSQL()
2 {
3 try
4 {
6 StringBuilder sb = new StringBuilder();
7 //需要的时候修改表明 得到通用
8 sb.Append("select * from InStoreBill_View ");
9 //用于判断是否是第一条数据 用于添加where的判断
10 int isFirst = 0;
11 for (int i = 0; i < this.fpl.Controls.Count; i++)
12 {
13 生成sql语句
75 }
76 return sb.ToString();
77 }
78 catch (Exception ex)
79 {
80 MessageBox.Show(ex.Message);
81 return "";
82 }
84 }
2 {
3 try
4 {
6 StringBuilder sb = new StringBuilder();
7 //需要的时候修改表明 得到通用
8 sb.Append("select * from InStoreBill_View ");
9 //用于判断是否是第一条数据 用于添加where的判断
10 int isFirst = 0;
11 for (int i = 0; i < this.fpl.Controls.Count; i++)
12 {
13 生成sql语句
75 }
76 return sb.ToString();
77 }
78 catch (Exception ex)
79 {
80 MessageBox.Show(ex.Message);
81 return "";
82 }
84 }
注: 在设计过程中觉得最烦乱得是布局的设计 ,也许是不太熟练,浪费了很多的时间,好在通用或之际copy就ok了