Question

C#程序——多条件查询问题。

这是一个web程序，页面上有3个textbox可以输入查询条件，对数据库中某表进行查询操作。可以输入1个条件查询，也可以输入2个条件查询，也可以3个条件全输入进行查询。如果条件都不输，就是查询出整个表。这是我自己写的程序，虽然执行出来结果正确，但是感觉太蠢了一些，这是3条件查询我写了9句判断，要是10条件查询呢？我得写100句判断啊！累死我非得！请教有没有高手编的好方法能解决这个多条件插叙的问题呢？
string sql;
if (TextBox1.Text.Trim() != "" && TextBox2.Text.Trim() == "" && TextBox3.Text.Trim() == "")
sql = "select * from Individual_Grade_Base where name='" + TextBox1.Text + "'";
else if (TextBox2.Text.Trim() != "" && TextBox1.Text.Trim() == "" && TextBox3.Text.Trim() == "")
sql = "select * from Individual_Grade_Base where cardid='" + TextBox2.Text + "'";
else if (TextBox3.Text.Trim() != "" && TextBox1.Text.Trim() == "" && TextBox2.Text.Trim() == "")
sql = "select * from Individual_Grade_Base where score>='" + TextBox3.Text + "'";
else if (TextBox1.Text.Trim() != "" && TextBox2.Text.Trim() != "" && TextBox3.Text.Trim() == "")
sql = "select * from Individual_Grade_Base where name='" + TextBox1.Text + "' and cardid='" + TextBox2.Text + "'";
else if (TextBox1.Text.Trim() != "" && TextBox3.Text.Trim() != "" && TextBox2.Text.Trim() == "")
sql = "select * from Individual_Grade_Base where name='" + TextBox1.Text + "' and score>='" + TextBox3.Text + "'";
else if (TextBox2.Text.Trim() != "" && TextBox3.Text.Trim() != "" && TextBox1.Text.Trim() == "")
sql = "select * from Individual_Grade_Base where cardid='" + TextBox2.Text + "' and score>='" + TextBox3.Text + "'";
else if (TextBox1.Text.Trim() != "" && TextBox2.Text.Trim() != "" && TextBox3.Text.Trim() != "")
sql = "select * from Individual_Grade_Base where name='" + TextBox1.Text + "' and cardid='" + TextBox2.Text + "' and score>='" + TextBox3.Text + "'";
else
sql = "select * from Individual_Grade_Base";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();

Answer 1

string sql = "select * from Individual_Grade_Base where 1=1";
if(TextBox1.Text.Trim() != "")
{
sql = sql + " and name = '" +TextBox1.Text + "' ";
}
if(TextBox2.Text.Trim() != "")
{
sql = sql + " and name = '" +TextBox2.Text + "' ";
}
这样sql就拼出来了

Answer 2

假如所有的TextBox都放在pannel控件中:

string sql="select * from Individual_Grade_Base where 1=1 ";

void AddSqlStrInPnl(Control pnl) //历遍容器 {
foreach(Control ctrl in pnl.Controls)
{
if(ctrl is TextBox)
{
if(ctrl.Text.Trim() != "" )
{sql = sql + " and name = '" +ctrl.Text + "' "; }

}
if(ctrl.HasChildren)
AddSqlStrInPnl(ctrl);
}
}

俺没测试，只是给个思路！当然，如果不是嵌套关系，大可不必递归。

Answer 3

你应该动态生成查询条件，比如：
string sql = "select * from Individual_Grade_Base";
string sql_where = "";
string w_1 = TextBox1.Text.Trim();
string w_2 = TextBox2.Text.Trim();
string w_3 = TextBox3.Text.Trim();
if(w_1!="")
{
if(sql_where == "")
{
sql_where = " name='" + w_1 + "'";
}
else
{
sql_where = " and name='" + w_1 + "'";
}
}
if(w_2!="")
{
if(sql_where == "")
{
sql_where = " cardid='" + w_2 + "'";
}
else
{
sql_where = " and cardid='" + w_2 + "'";
}
}
if(w_3!="")
{
if(sql_where == "")
{
sql_where = " score>='" + w_3 + "'";
}
else
{
sql_where = " and score>='" + w_3 + "'";
}
}
if(sql_where!="")
{
sql += " where "+sql_where
}
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();

Answer 4

string sql = "select * from tableName where 1=1 "
if(!String.Isnullorempty(this.textbox1.text))
{
sql += " and 条件1"
}
if(!String.isnullorempty(this.textbox2.text))
{
sql +=" and 条件2";
}
if(!String.isnullorempty(this.textbox3.text))
{
sql+=" and 条件3";
}

Answer 5

string sql="select * from Individual_Grade_Base where 1=1 ";

if (TextBox1.Text.Trim() != "" )
{
sql+="and name='"+ TextBox1.Text.Trim() +"' "
}

if (TextBox2.Text.Trim() != "" )
{
.....}

if (TextBox3.Text.Trim() != "" )
{
.....}