Question

C#多条件查询语句

要实现按条件查询的代码是什么 就是点条件查询按钮的时候需要的代码

private void button1_Click(object sender, EventArgs e)
{
// sql = "SELECT * FROM Book WHERE BookId,BookName,Author,DateTime,Publisher ";
sql = " select BookId,BookName,Author,DateTime,Publisher,Note from Book where ";
if (txtBookId.Text != "")
sql = sql + "BookId=" + "'" + txtBookId.Text + "'";
else if (txtBookName.Text != "")
sql = sql + "BookName like" + "'%" + txtBookName.Text + "%'";
else if (txtAuthor.Text != "")
sql = sql + "Author like" + "'%" + txtAuthor.Text + "%'";
//else if (DateTime.Text != "")
// strSql = strSql + "Ldate >" + "'" + DateTime.Text + "'";
else if (cbbPublisher.Text != "")
sql = sql + "Publisher=" + "'" + cbbPublisher.Text + "'";
// strSql = strSql+"CustomerName like"+"'%"+textName.Text+"%'";
else
{
MessageBox.Show("请选择查询条件！", "提示");
return;
}
这段代码运行后点条件查询是出错的

Answer 1

可以写个关于where 条件发生变化的函数,给你个参考的看看，你也可以只传一个参数

  public void GetWhere(out string where, out string order)
    {
        where = "where 1=1";
        int cataId = int.Parse(ddlChildren.SelectedValue);  
        if (cataId > 0)
            where += string.Format(" and CataId={0}", cataId);
        if (ddlUnitPrice.SelectedIndex > 0)
            where += string.Format(" and UnitPrice {0}", ddlUnitPrice.SelectedValue);       
        if (ddlDiscountPrice.SelectedIndex > 0)
            where += string.Format("and DiscountPrice {0}", ddlDiscountPrice.SelectedValue);
        if (!string.IsNullOrEmpty(txtName.Text))
            where += string.Format("and Name like '%{0}%'", txtName.Text);
        if (!string.IsNullOrEmpty(txtBrand.Text))
            where += string.Format("and Brand like '%{0}%'", txtBrand.Text);         
        if (ddlSort.SelectedIndex > 0)
            order = " order by " + ddlSort.SelectedValue + " " + rbtSort.SelectedValue;
        else
            order = "order by Id ";
    }

Answer 2

#region 多条件搜索时，使用List集合来拼接条件（拼接Sql）

StringBuilder sql = new StringBuilder("select * from PhoneNum");
List<string> wheres = new List<string>();
if (cboGroup.SelectedIndex != 0)
{
wheres.Add(" ptypeid=" + cboGroup.Text.Split('|')[0]);
}
if (txtSearchName.Text.Trim().Length > 0)
{
wheres.Add(" pname like '%" + txtSearchName.Text.Trim() + "%'");
}
if (txtSearchCellPhone.Text.Trim().Length > 0)
{
wheres.Add(" pcellphone like '%" + txtSearchCellPhone.Text.Trim() + "%'");
}

//判断用户是否选择了条件
if (wheres.Count > 0)
{
string wh = string.Join(" and ", wheres.ToArray());
sql.Append(" where " + wh);
}
#endregion

#region 多条件搜索使用带参数的sql语句
StringBuilder sql = new StringBuilder("select * from PhoneNum");
List<string> wheres = new List<string>();
List<SqlParameter> listParameter = new List<SqlParameter>();
if (cboGroup.SelectedIndex != 0)
{
wheres.Add(" ptypeid=@typeid ");
listParameter.Add(new SqlParameter("@typeid", cboGroup.Text.Split('|')[0]));
}
if (txtSearchName.Text.Trim().Length > 0)
{
wheres.Add(" pname like @pname ");
//pname like '%乔%'
//pname liek '%'+@pname+'%'
listParameter.Add(new SqlParameter("@pname", "%" + txtSearchName.Text.Trim() + "%"));
}
if (txtSearchCellPhone.Text.Trim().Length > 0)
{
wheres.Add(" pcellphone like @cellphone ");
listParameter.Add(new SqlParameter("@cellphone", "%" + txtSearchCellPhone.Text.Trim() + "%"));
}

//判断用户是否选择了条件
if (wheres.Count > 0)
{
string wh = string.Join(" and ", wheres.ToArray());
sql.Append(" where " + wh);
}

SqlHelper.ExecuteDataTable(sql.ToString(), listParameter.ToArray());
#endregion

Answer 3

将sql = " select BookId,BookName,Author,DateTime,Publisher,Note from Book where ";改为
sql = " select BookId,BookName,Author,DateTime,Publisher,Note from Book where 1=1";，然后在判断不为空时加上“and”，如
if (txtBookId.Text != "")
sql = sql + "BookId=" + "'" + txtBookId.Text + "'";
修改为：
if (txtBookId.Text != "")
sql = sql + "and BookId=" + "'" + txtBookId.Text + "'";
其他几个条件也一样计改就好了

追问

提示BookId附近有语法错误

Answer 4

因为你的 and 和 not 分支里，只是设置了相应的 str 的值，并没有调用
ds = db.GetDataFromDB(str);
所以ds保持null。

在两个 str = ... 的下一行，添加 ds = db.GetDataFromDB(str);
即可。

NOT的语法可以是 SELECT * FROM [Table] WHERE [Field] NOT LIKE '%string%'
或者SELECT * FROM [Table] WHERE NOT [Field] LIKE '%string%'

看样子你的语法是正确的，具体出什么错误？

Answer 5

主要就是拼SQL语句
sql = " select BookId,BookName,Author,DateTime,Publisher,Note from Book ";
str=“ where 1=1 ”；
if (txtBookId.Text != "")
sql = sql +str+“ and ”+ "BookId=" + "'" + txtBookId.Text + "'";（下面全部一样）
else if (txtBookName.Text != "")

追问

提示BookId附近有语法错误

追答

有些小问题，你假设有输入内容，把语句放到sqlserver里看还有什么小问题，修改就行。
sql = sql +str+“ and ”+ " BookId=" + “’ + txtBookId.Text + ‘”;