用存储过程好些.定义变量时赋空值,如: @condition1 int=null, @condition2 char(8)=null, @condition3 vchar(8)int=null在SQL语句中如下写: select * from table1 where (@condition1=value1 or @condition1 is null) and (@condition2=value2 or @condition2 is null) ...
string subSql=""; // wehre后面条件语句 string nd=snd.SelectedItem.ToString(); string yf=syf.SelectedItem.ToString(); string sxmjlb=sxmjlb_list.SelectedItem.Text.ToString(); string sxmmc=sxmmc_list.SelectedItem.Text.ToString(); if(sxmmc!="全部") { subSql+="a.xmmc ="+sxmmc_list.SelectedItem.Value+" and ";} if(nd!="全部") { subSql+="year(a.insert_time) ="+snd.SelectedItem.Value+" and ";} if(yf!="全部") { subSql+="month(a.insert_time) ="+syf.SelectedItem.Value+" and ";} if(sxmjlb!="全部") { subSql+="a.xmjlb ="+sxmjlb_list.SelectedItem.Value+" and ";} if(sszhang!="全部") { subSql+="a.zhang ="+szhang.SelectedItem.Value+" and ";} string SQL=""; if(subSql.Length>0) { subSql=subSql.Substring(0,subSql.Length-4); // 去掉最后一个and SQL="select a.xmmc,a.dj as 单价 from htqd_a1 a where "+subSql+ "order by 编号"; } else { SQL="select a.xmmc,a.dj as 单价 from htqd_a1 a order by 编号"; }
这样很麻烦; 可以试试; string sql="select * from table where "; int flag=1; if(key1!="") { if(flag=="1") { sql+=" filedname=key1 " flag=1; } else { flag=0; } } 在存储过程或输出Sql语句都可以用!
@condition1 int=null,
@condition2 char(8)=null,
@condition3 vchar(8)int=null在SQL语句中如下写:
select * from table1 where (@condition1=value1 or @condition1 is null) and (@condition2=value2 or @condition2 is null) ...
string nd=snd.SelectedItem.ToString();
string yf=syf.SelectedItem.ToString();
string sxmjlb=sxmjlb_list.SelectedItem.Text.ToString();
string sxmmc=sxmmc_list.SelectedItem.Text.ToString();
if(sxmmc!="全部")
{ subSql+="a.xmmc ="+sxmmc_list.SelectedItem.Value+" and ";}
if(nd!="全部")
{ subSql+="year(a.insert_time) ="+snd.SelectedItem.Value+" and ";}
if(yf!="全部")
{ subSql+="month(a.insert_time) ="+syf.SelectedItem.Value+" and ";}
if(sxmjlb!="全部")
{ subSql+="a.xmjlb ="+sxmjlb_list.SelectedItem.Value+" and ";}
if(sszhang!="全部")
{ subSql+="a.zhang ="+szhang.SelectedItem.Value+" and ";}
string SQL="";
if(subSql.Length>0)
{
subSql=subSql.Substring(0,subSql.Length-4); // 去掉最后一个and
SQL="select a.xmmc,a.dj as 单价 from htqd_a1 a where "+subSql+ "order by 编号";
}
else
{
SQL="select a.xmmc,a.dj as 单价 from htqd_a1 a order by 编号";
}
可以试试;
string sql="select * from table where ";
int flag=1;
if(key1!="")
{
if(flag=="1")
{
sql+=" filedname=key1 "
flag=1;
}
else
{
flag=0;
}
}
在存储过程或输出Sql语句都可以用!
我上一个项目中有4个可以指定不同检索项目的ComboBox,最多有8种检索项目可以选择;
还有3个可以指定范围的ComboBox,检索项目最少2个,最多n个。
从数据检查到拼SQL文加最后查询就写了2000多行,真tm的bt!不发牢骚了,给你讲下我的方法吧:主要是用hashMap来保存检索条件
key:检索项目
value:保存了多个检索条件的ArrayList循环控件,根据选择的检索项目到HashMap中找,不存在就新建一个ArrayList,然后把新的条件加入到ArrayList中。拼SQL文就一个大循环套小循环,大循环遍历HashMap,因为是不同检索项目用AND连接,小循环就是同一检索项目的不同条件,用OR用IN随心情啦~~
//先查出全部数据
string sql = "SELECT * FROM tablename WHERE 1=1";//判断条件,设TextBox1中内容对应数据库中的数据类型为字符串
if(TextBox1.Text.Trim() != "")
sql += " AND 字段名 Like '%" + TextBox1.Text.Trim() + "%'";//设TextBox2中内容对应数据库中的数据类型为数字
if(TextBox2.Text.Trim() != "")
sql += " AND 字段名=" + TextBox2.Text.Trim();依次类推,可以用StringBuild来追加,比较节约。