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


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

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

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

    主要的运行后布局:

        主要的通用功能和要求:

    主要的方法体:

      动态的显示查询条件:

    按查询条件设置显示模式:

    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");
    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 //用copy解决联动问题
    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 == "时间")
    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;
    35
    36                        }
    37
    38
    39
    40                    }
    41                }
    42            }
    43 catch (Exception ex)
    44 {
    45                MessageBox.Show(ex.Message);
    46            }
    47        }

    添加条件
    提取sql语句

    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
    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 //需要的时候修改表明 得到通用
    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            }
    83
    84        }

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

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

  • 相关阅读:
    (Java) LeetCode 44. Wildcard Matching —— 通配符匹配
    (Java) LeetCode 30. Substring with Concatenation of All Words —— 与所有单词相关联的字串
    (Java) LeetCode 515. Find Largest Value in Each Tree Row —— 在每个树行中找最大值
    (Java) LeetCode 433. Minimum Genetic Mutation —— 最小基因变化
    (Java) LeetCode 413. Arithmetic Slices —— 等差数列划分
    (Java) LeetCode 289. Game of Life —— 生命游戏
    (Java) LeetCode 337. House Robber III —— 打家劫舍 III
    (Java) LeetCode 213. House Robber II —— 打家劫舍 II
    (Java) LeetCode 198. House Robber —— 打家劫舍
    (Java) LeetCode 152. Maximum Product Subarray —— 乘积最大子序列
  • 原文地址:https://www.cnblogs.com/blsong/p/1810171.html
Copyright © 2020-2023  润新知