在数据库中搜索时忽略空文本框

问题描述

此代码能够根据搜索表单文本框中提供的值搜索数据并将其加载到 DataGridView.如果我将任何文本框留空，则没有搜索结果，因为 SQL 查询与AND"组合在一起.

This code is able to search and load data to DataGridView based on values provided in search form text boxes. If I left any text box blank, there is no search result since the SQL query is combined with "AND".

如何在搜索时忽略空文本框(从 SQL 查询或 C# 代码)?

How can I ignore the empty textboxes while searching (from SQL query or C# code)?

private void btnSearch_Click(object sender, EventArgs e)
{
    DataSet ds = new DataSet();

    String select = "SELECT DocumentNo, Revision, DocumentTitle, DocumentType
                     FROM DocumentLog
                     WHERE DocumentNo Like '%" + tbxDocumentNo.Text + "%'
                       AND Revision Like '%" + tbxRevision.Text + "%'
                       AND DocumentTitle Like '%" + tbxDocumentTitle.Text + "%'
                       AND DocumentType '%" + tbxDocumentType.Text + "%'"
                       AND IsDeleted = '0';

    SqlConnection conn = DBConnection.openConnection();
    SqlCommand command = new SqlCommand(select, conn);

    SqlDataAdapter da = new SqlDataAdapter(command);
    da.Fill(ds, "DocumentLog");

    dgvTracking.AutoGenerateColumns = false;
    dgvTracking.DataSource = ds.Tables["DocumentLog"];
}

推荐答案

• 首先，您必须用参数化查询替换连接的字符串查询以避免注入，
• 其次，您可以使用 String.IsNullOrEmpty 在将它们添加到 where 子句之前检查该值是 null 还是空.
• 你需要注意的第三件事是AND的多条件分隔符，如果你在第二个条件(tbxRevision)的开头添加 then 则查询变成如果 tbxDocumentNo 为 null 或为空，则出错.与 last 类似，如果最后一个条件为假，那么查询将以 AND 结束，这也是一个错误，为了避免这些，我们可以采用 IsDeleted='0' 作为第一个条件，就像我在下面的代码中所做的那样，
• First of all you have to replace the concatenated string queries with parameterized queries to avoid injection,
• Secondly you can use String.IsNullOrEmpty to check whether the value is null or empty before add them to the where clause.
• Third thing you need to notice is the multiple condition separator that is AND, if you add then In the beginning of the second condition (tbxRevision) then the query became error if tbxDocumentNo is null or empty. similar in the case of last if the last condition is false then query will ends up with an AND which is also an error, To avoid these we can take IsDeleted='0' as first condition in where as like i did in the following code,

请看:

string querySQL = "Select DocumentNo , Revision, DocumentTitle, DocumentType FROM DocumentLog WHERE IsDeleted='0'";
using(SqlConnection conSQL = DBConnection.openConnection())
{
    using(SqlCommand cmdSQL = new SqlCommand())
    {
        if(!string.IsNullOrEmpty(tbxDocumentNo.Text))
        {
            querySQL += "AND  DocumentNo Like @DocumentNo";
            cmdSQL.Parameters.Add("@DocumentNo", SqlDbType.VarChar).Value = "%" + tbxDocumentNo.Text + "%";
        }

        // Add rest of conditions here like this

        cmdSQL.CommandText=querySQL;
        cmdSQL.Connection = conSQL;
    SqlDataAdapter da = new SqlDataAdapter(cmdSQL);
    }
}

这篇关于在数据库中搜索时忽略空文本框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！