• 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了

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

  • 相关阅读:
    Microsoft Visual Studio 2005中使用水晶报表
    net的辅助工具列表
    基于.net开发平台项目案例集锦
    一个蛮不错的图形绘制控件 dotnetCHARTING
    统计图控件dotnetCharting和ZedGraph比较
    c#获取真实IP和代理IP
    虚拟主机比较安全设置
    正则表达式 大杂烩
    【转载】说说大型高并发高负载网站的系统架构
    使用.NET实现断点续传
  • 原文地址:https://www.cnblogs.com/blsong/p/1810171.html
Copyright © 2020-2023  润新知