WINFORM 多条件动态查询 通用代码的设计与实现

  

          经常碰到多条件联合查询的问题，以前的习惯认为很简单总会从头开始设计布局代码，往往一个查询面要费上老半天的功夫，而效果也不咋地。

　　　    前段时间做了个相对通用的多条件动态查询面，复用起来还是挺方便的，放上来共参考指导　。
                                                                         供下载的源文件链接  ：   多条件动态查询通用模板下载

         主要的运行后布局：

           

　  
　　　　主要的通用功能和要求:

           
        主要的方法体：

 　　　　　动态的显示查询条件：

              

 1     // 初始化联合查询的页面显示
 2
 3          private   void  ConditionBind()
 4          {
 5            /**///查询条件邦定
 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");
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        }

 

添加条件 

 1
 2          private   void  AddFilter()
 3          {
 4            try
 5            {
 6                for (int i = 0; i < this.fpl.Controls.Count; i++)
 7                {
 8                    可替换代码#region 可替换代码
 9                    //if (((FlowLayoutPanel)this.fpl.Controls[i]).Visible == false)
10                    //{
11                    //    ((FlowLayoutPanel)this.fpl.Controls[i]).Visible = true;
12                    //    break;
13                    //}
14                    #endregion
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语句#region   生成sql语句
14                    if (this.Controls.Find("fpl" + i, true)[0].Visible == true)
15                    {
16
17                        if (this.Controls.Find("cbSelect" + i, true)[0].Text != "所有")
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                            {//为本类型
23                                isFirst++;
24                                if (isFirst == 1)//如果是第一次进入的话  isfirst应该为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                            {//下拉框类型
40                                isFirst++;
41                                if (isFirst == 1)//如果是第一次进入的话  isfirst应该为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                            {//时间类型
57                                isFirst++;
58                                if (isFirst == 1)//如果是第一次进入的话  isfirst应该为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            }
84        }

 

       注: 在设计过程中觉得最烦乱得是布局的设计 ,也许是不太熟练,浪费了很多的时间,好在通用或之际copy就ok了

　　    供下载的源文件链接  ：   多条件动态查询通用模板下载

转载于:https://www.cnblogs.com/huomm/archive/2008/03/22/1117552.html