在C#编程中调用如下语句:
public void CheckInformation(int stuID,string stuName ,int stuAge)
{
string str="select * from Sometable where (ID="+stuID+") and (Name ='" + stuName + "') and (Age="+stuAge+")";
using(OracleDataAdapter thisAdapter = new OracleDataAdapter(str, thisConnection))
{
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "Sometable"); ...//输出数据
}
}
其中ID,Name ,Age的值为函数参数赋值传入,当键入相应的值时,能够正确查询出相应的数据;
请问:ID,Name为空,Age不为空时,如何忽略查询语句中的ID和Name值,而直接查询出相应Age的数据?如何忽略其中某一项或则某几项输入无效的情况下,判断出有效约束条件并查询出数据?
public void CheckInformation(int stuID,string stuName ,int stuAge)
{
string str="select * from Sometable where (ID="+stuID+") and (Name ='" + stuName + "') and (Age="+stuAge+")";
using(OracleDataAdapter thisAdapter = new OracleDataAdapter(str, thisConnection))
{
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "Sometable"); ...//输出数据
}
}
其中ID,Name ,Age的值为函数参数赋值传入,当键入相应的值时,能够正确查询出相应的数据;
请问:ID,Name为空,Age不为空时,如何忽略查询语句中的ID和Name值,而直接查询出相应Age的数据?如何忽略其中某一项或则某几项输入无效的情况下,判断出有效约束条件并查询出数据?
string str="select * from Sometable where 1=1 "
string strWhere = "";
if (stuID != "")
{
strWhere = strWhere + " and (ID="+stuID+") ";
}
if (stuName != "")
{
strWhere = strWhere + " and (Name ='" + stuName + "') ";
}
if (Age != "")
{
strWhere = strWhere + " and (Age="+stuAge+") ";
}
str = str + strWhere;
select * from Sometable where (ID="+stuID+" or "+stuID+" is null) and (Name ='" + stuName + "' or '" + stuName + "' is null) and (Age="+stuAge+" or "+stuAge+" is null)";
id="+stuID+" or id is null在数据允许的情况还可以先把stuID做处理,若为Null,则赋予'%',否则赋予原值
然后SQL用id like ="+stuID+"
由于你的where条件传入为空时出问题,改为:string str="select * from Sometable where 1=1";
if(stuID != null && !stuID.trim().equals("")){
str = str+" and ID = "+stuID;
}
if(stuName != null && !stuName.trim().equals("")){
str = str+" and Name = "+stuName;
}
if(stuAge!= null && !stuAge.trim().equals("")){
str = str+" and Age= "+stuAge;
}