当前位置: 动力学知识库 > 问答 > 编程问答 >

c# - Ignore empty textboxes while searching in database

问题描述:

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".

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"];

}

网友答案:
  • 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,

please take a look:

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);                                     
    }
}
网友答案:

If you don't want your SQL command to include the clause AND Revision Like '' you will need to stop hardcoding your SQL command as a single string and instead build that string depending on your input boxes.

StringBuilder sqlCommandText = new StringBuilder();

sqlCommandText.Append("Select DocumentNo , Revision, DocumentTitle, DocumentType FROM DocumentLog WHERE IsDeleted = 0");

if(!string.IsNullOrEmpty(tbxRevision.Text))
{
    sqlCommandText.Append(" AND Revision Like @revision");
    command.Parameters.Add("@revision", tbxRevision.Text);
}

// do this for all fields

command.CommandText = sqlCommandText.ToString();   
网友答案:

Hi You can use some query logic for this, for example the query could be:

Select DocumentNo , Revision, DocumentTitle, DocumentType 
FROM DocumentLog 
WHERE (DocumentNo Like '%"+tbxDocumentNo.Text+"%' OR tbxDocumentNo.Text = '') AND
(Revision Like '%"+tbxRevision.Text+"%' OR Revision = '') AND 
(DocumentTitle Like '%"+tbxDocumentTitle.Text+"%' OR DocumentTitle = '') AND
DocumentType '%"+tbxDocumentType.Text+"%'";AND IsDeleted='0'
分享给朋友:
您可能感兴趣的文章:
随机阅读: