描述:
我的form中,查询功能求解。
两个text ,三个下拉列表框 ,组成查询条件集合,
可以任意选择查询条件。
提交后,得到每个控件的值,根据值定义where子句。问题:
我需要判断每一个控件的值是什么,是否为空。
那要写多少个if else呀!!!请求:
优秀的解决方案。
我的form中,查询功能求解。
两个text ,三个下拉列表框 ,组成查询条件集合,
可以任意选择查询条件。
提交后,得到每个控件的值,根据值定义where子句。问题:
我需要判断每一个控件的值是什么,是否为空。
那要写多少个if else呀!!!请求:
优秀的解决方案。
楼主【ldy214】截止到2008-06-27 15:00:18的历史汇总数据(不包括此帖):
发帖数:48 发帖分:1225
结贴数:48 结贴分:1225
未结数:0 未结分:0
结贴率:100.00% 结分率:100.00%
敬礼!
直接拿到sql的where里面判断
比如
where
? is null or 字段1 = ?
and ? is null or 字段2 = ?楼主说的是这意思吗?不是也别鄙视我。
主要是判断 是否 是全部查询 还是特定条件查。
并不是,只有null和条件。
不过还是谢谢,上楼。
然后你的form中部就是由五个控件吗,也就是加五个if语句吧,如果该控件空就不往 sql 中追加,否则追加。同时自定义个标志flag = false(是否加“AND”)
如if (!txtA1.equels("") || txtA1 !=null){
if(flag == tuue){
sql = sql + "AND tb.Name = '" +txtA1+"' ";}
else{
sql = sql + "tb.Name = '" +txtA1+"' ";
flag = true;
}
}
}
}
依次类推,Ok了
String sql = "SELECT * FROM tablename WHERE 1=1 ";
if(select!=null && !"".equals(select) && "0".equals(select))
{
sql += " AND condition = "+select;
}
public static readonly string[] express={
">",
">=",
"<",
"<=",
"=",
"<>",
"以开头",
"包含",
"不包含",
"并列包含",
"不并列包含"
};public static readonly string[] systemType= { //全类型
"Boolean",
"Byte",
"Char",
"DateTime",
"Decimal",
"Double",
"Int16",
"Int32",
........... };//number 型
public static readonly string[] systemTypeNum= {
"Decimal",
"Double",
"Int16",
"Int32",
"Int64",
"Sbyte",
"Single",
"Uint16",
"Uint32",
"Uint64"
};public static readonly string[] systemTypeString= {....} //字符型//Fname:string;Ftype:TFieldType;index:integer;str:string//字段名,条伯类型,条件索引,条伯内容
public static string GetSql(string Fname,string Ftype,int index,string str)
{
string Result=ComString.SqlEqual; //return "1=1"; if (str==String.Empty)
{
return Result;
}
if ((index==9) || (index==10))
{
while (str.Substring(0,1)==",")
{
str = str.Substring(2, str.Length);
} while (str.Substring(str.Length-1,1)==",")
{
str = str.Substring(0, str.Length - 1);
}
while (str.IndexOf(",,")!=0)
{
str.Replace(",,", ",");
}
int i = str.IndexOf(","); if (inArray(systemTypeNum,Ftype))
{ Result = "(" + Fname + " in " + str + "))";
}
if (inArray(systemTypeString,Ftype))
{
if (i==0)
{
Result = Fname + " like \'%" + str + "%\'";
} if (i!=0)
{
Result = "(1=2"; while (i!=0)
{
Result += " OR " + Fname + " like \'%" + str.Substring(1, i - 1) + "%\'";
str = str.Substring(i + 1, str.Length);
i = str.IndexOf(",");
}
if (str.Trim() !=String.Empty)
{
Result += " OR " + Fname + " like \'%" + str + "%\'";
} Result += ")";
}
} //(inArray(systemTypeString,Ftype)) if (inArray(systemTypeDateTime,Ftype))
{
if (i==0)
{
Result = Fname + "=\'" + str + "\'";
} if (i!=0)
{
Result = "(1=2";
while (i!=0)
{
Result += " OR " + Fname + "=\'" + str.Substring(1, i - 1) + "\'";
str = str.Substring(i + 1, str.Length);
i = str.IndexOf(",");
} if (str.Trim()!=String.Empty)
{
Result += " OR " + Fname + "=\'" + str + "\'";
}
Result += ")";
}
} //(inArray(systemTypeDateTime,Ftype))
return Result;
} // ((index==9) || (index==10)) if (index==10)
{
return "( NOT " + Result + ")";
} if (inArray(systemTypeNum,Ftype))
{
switch(index)
{
case 0:
Result = Fname + ">" + str;
break;
case 1:
Result = Fname + ">=" + str;
break;
case 2:
Result = Fname + "<" + str;
break;
case 3:
Result = Fname + "<=" + str;
break;
case 4:
Result = Fname + "=" + str;
break;
case 5:
Result = Fname + "<>" + str;
break;
default:
Result = "1=1";
break;
} return Result;
} //(inArray(systemTypeNum,Ftype))
int j = str.IndexOf(",");
if (j==-1)
{
j = 0;
}
string temp;
int icount = 0; if (inArray(systemTypeDateTime,Ftype)||inArray(systemTypeString,Ftype))
{
switch(index)
{
case 0:
Result = Fname + ">" + QuotedStr(str);
break;
case 1:
Result = Fname + ">=" + QuotedStr(str);
break;
case 2:
Result = Fname + "<" + QuotedStr(str);
break;
case 3:
Result = Fname + "<=" + QuotedStr(str);
break;
case 4:
Result = Fname + "=" + QuotedStr(str);
break;
case 5:
Result = Fname + "<>" + QuotedStr(str);
break;
case 6:
temp = str;
icount = 0;
while (j>0)
{
if (icount!=0)
{
Result+=" and ("+ Fname+" NOT LIKE \'%"+temp.Substring(0,j-1)+"%\'";
}
else
{
Result+="("+Fname+" NOT LIKE \'%"+temp.Substring(0,j-1)+"%\')"; }
}
temp.Remove(1, j); j = temp.IndexOf(",");
if (j==-1)
{
j = 0;
}
icount++;
if (icount>0)
{
Result += " AND (" + Fname + " NOT LIKE \'%" + temp.Substring(0, temp.Length) + "%\')";
}
else
{
Result += "(" + Fname + " NOT LIKE \'%" + temp.Substring(0, temp.Length) + "%\')";
}
break;
case 7:
temp = str;
icount = 0;
while (j>0)
{
if (icount!=0)
{
Result+=" and ("+Fname+" LIKE \'%"+temp.Substring(0,j-1)+"%\')";
}
else
{
Result+="("+Fname+" LIKE \'%"+temp.Substring(0,j-1)+"%\')"; }
}
temp.Remove(1, j);
j = temp.IndexOf(",");
if (j==-1)
{
j = 0;
}
icount++;
if (icount>0)
{
Result += " AND (" + Fname + " LIKE \'%" + temp.Substring(0, temp.Length) + "%\')";
}
else
{
Result += "(" + Fname + " LIKE \'%" + temp.Substring(0, temp.Length) + "%\')";
}
break;
case 8:
temp = str;
icount = 0;
while (j>0)
{
if (icount!=0)
{
Result+=" and ("+Fname+" NOT LIKE \'%"+temp.Substring(0,j-1)+"%\')";
}
else
{
Result+="("+Fname+" NOT LIKE \'%"+temp.Substring(0,j-1)+"%\')"; }
}
temp.Remove(1, j);
j = temp.IndexOf(","); if (j==-1)
{
j = 0;
}
icount++;
if (icount>0)
{
Result += " AND (" + Fname + " NOT LIKE \'%" + temp.Substring(0, temp.Length) + "%\')";
}
else
{
Result += "(" + Fname + " NOT LIKE \'%" + temp.Substring(0, temp.Length) + "%\')";
}
break;
default:
Result = "1=1";
break; }
return Result;
} // (inArray(systemTypeDateTime,Ftype)) return Result;
}------------------------------------
写GetSql 的公共方法,.可以实现任意条件的组合查询.if (cBoxFieldA.SelectedIndex!=-1)
{FindA =ComFun.GetSql(Fname[cBoxFieldA.SelectedIndex],
Ftype[cBoxFieldA.SelectedIndex],
cBoxCondA.SelectedIndex,
tBoxCondTxtA.Text);
}
else
{
FindA = ComString.SqlEqual;
}
if (cBoxFieldB.SelectedIndex!=-1)
{
FindB =
ComFun.GetSql(Fname[cBoxFieldB.SelectedIndex],
Ftype[cBoxFieldB.SelectedIndex],
cBoxCondB.SelectedIndex,
tBoxCondTxtB.Text);
}
else
{
FindB = ComString.SqlEqual;
}
sql+=arg1==null?"":" and cond1="+arg1;
sql+=arg2==null?"":" and cond2="+arg2;
不要鄙视我啊...