通过拼接SQL字符串实现多条件查询

<summary> /// 按条件查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void BtSearch_Click_1(object sender, EventArgs e) { List<string> wherelist = new List<string>(); List<SqlParameter> parametlist = new List<SqlParameter>(); if (CbMzh.Checked) { wherelist.Add("mzh=@mzh"); parametlist.Add(new SqlParameter("@mzh", TbMzh.Text)); } if (CbXm.Checked) { wherelist.Add("xm like ‘%‘+@xm+‘%‘"); parametlist.Add(new SqlParameter("@xm", TbXm.Text)); } if (CbJbmc.Checked) { wherelist.Add("jbzd like ‘%‘+@jbzd+‘%‘"); parametlist.Add(new SqlParameter("@jbzd", TbJbmc.Text)); } if (CbJzlb.Checked) { wherelist.Add("jzlb=@jzlb"); parametlist.Add(new SqlParameter("@jzlb", ComBoxJzlb.Text)); } if (CbJzks.Checked) { wherelist.Add("jzks=@jzks"); parametlist.Add(new SqlParameter("@jzks", TbJzks.Text)); } if (CbJzys.Checked) { wherelist.Add("jzys=@jzys"); parametlist.Add(new SqlParameter("@jzys", TbJzys.Text)); } if (CbJzrq.Checked) { wherelist.Add("jzrq between @start and @end"); string start = DpStart.Value.ToString("yyyy-MM-dd") + " 00:00:00"; string end = DpEnd.Value.ToString("yyyy-MM-dd") + " 23:59:59"; parametlist.Add(new SqlParameter("@start", start)); parametlist.Add(new SqlParameter("@end", end)); } if (CbJbmcjqcx.Checked) { wherelist.Add("jbzd = @jbzd"); parametlist.Add(new SqlParameter("@jbzd", TbJbmc1.Text)); } string whereSql = string.Join(" and ", wherelist); string sql = "select mzh,xm,xb,csny,age,pcid,jzks,jzys,jbzd,zddm,gzdw,jtzz,lxfs,jzlb,jzrq,xy from his_mzjzrz"; if (wherelist.Count > 0) { sql = sql + " where " + whereSql + " order by jzrq desc"; } else { MessageBox.Show("请选择检索条件！"); return; } DataTable dt = MsSqlHelper.ExecuteDataTable(sql, parametlist.ToArray()); MzrzGridView.DataSource = dt; toolStripStatusLabel2.Text = string.Format("共检索到{0}条记录", dt.Rows.Count.ToString()); toolStripProgressBar1.Minimum = 0; toolStripProgressBar1.Maximum = dt.Rows.Count; for (int i = 0; i <= dt.Rows.Count; i++) { toolStripProgressBar1.Value = i; } }

 

通过拼接SQL字符串实现多条件查询

标签：idv   bsp   progress   拼接   data   where   oid   好处   src