写这篇短文是因为要做一个多条件用户搜索(类似于QQ查找里的高级查找,可选择条件,并且存在一个不限的条件),发现如果写为存储过程,也是逃不过根据条件自动生成正确的sql语句这一关的,所以就写了下面的代码。如果谁还有更好的方法,欢迎赐教
以下代码在.net2005 XP系统下测试通过
///这是一个生成sql语句where子句的逻辑
///用于多条件搜索的sql语句
///这里是三个条件
///如果要用5个条件,则只需修改temp1和temp2数组还有for的循环次数
string sql = "select userName,sex,user_age,user_city from [user]";
string clause = " where ";
//string temp = "user_age between=",
string tempValue1 = "all";
string tempValue2 = "all";
string tempValue3 = "3' and '4";//在前面先给这个字符串符值,用于拼接betwwen后面的字符串
string[] temp1 = { "sex=", "user_city=", "user_age between=" };//条件的变量名
string[] temp2 = { tempValue1, tempValue2, tempValue3 };//条件的变量值,对应temp1数组
int n = 0;
for (int i = 0; i < 3; i++)
{
//temp = Console.ReadLine();
if (temp2[n] == "all")
{
n++;
continue;
}
else
{
if (clause == " where ")
{
//clause += " and ";
clause += temp1[n];
clause += "'";
clause += temp2[n];
clause += "'";
n++;
}
else
{
clause += " and ";
clause += temp1[n];
clause += "'";
clause += temp2[n];
clause += "'";
n++;
}
}
}
if (temp2[0] != "all" || temp2[1] != "all" || temp2[2] != "all")
{
sql += clause;
Console.WriteLine(sql.ToString());
}
else
{
Console.WriteLine(sql.ToString());
}
sql += clause;
Console.WriteLine(clause.ToString());
string userName = "user&&area &sex&online&camera&key";
string[] strGroup = userName.Split('&');
foreach (string a in strGroup)
{
if (a != "")
Console.WriteLine(a.ToString());
}
///用于多条件搜索的sql语句
///这里是三个条件
///如果要用5个条件,则只需修改temp1和temp2数组还有for的循环次数
string sql = "select userName,sex,user_age,user_city from [user]";
string clause = " where ";
//string temp = "user_age between=",
string tempValue1 = "all";
string tempValue2 = "all";
string tempValue3 = "3' and '4";//在前面先给这个字符串符值,用于拼接betwwen后面的字符串
string[] temp1 = { "sex=", "user_city=", "user_age between=" };//条件的变量名
string[] temp2 = { tempValue1, tempValue2, tempValue3 };//条件的变量值,对应temp1数组
int n = 0;
for (int i = 0; i < 3; i++)
{
//temp = Console.ReadLine();
if (temp2[n] == "all")
{
n++;
continue;
}
else
{
if (clause == " where ")
{
//clause += " and ";
clause += temp1[n];
clause += "'";
clause += temp2[n];
clause += "'";
n++;
}
else
{
clause += " and ";
clause += temp1[n];
clause += "'";
clause += temp2[n];
clause += "'";
n++;
}
}
}
if (temp2[0] != "all" || temp2[1] != "all" || temp2[2] != "all")
{
sql += clause;
Console.WriteLine(sql.ToString());
}
else
{
Console.WriteLine(sql.ToString());
}
sql += clause;
Console.WriteLine(clause.ToString());
string userName = "user&&area &sex&online&camera&key";
string[] strGroup = userName.Split('&');
foreach (string a in strGroup)
{
if (a != "")
Console.WriteLine(a.ToString());
}