执行sql搜索查询时忽略空文本框 [英] Ignore empty textboxes when executing sql search query

问题描述

我正在使用大约16个文本框的密集搜索功能来搜索数据库的16列

问题是，当我尝试搜索超过1个文本框时，它通常显示数据库中的所有行



我想要实现的目标：

简单地忽略所有搜索查询本身中的空字段\文本框

没有为每个空文本框本身分配单独的命令

或

在其他单词，我想过滤我的结果，只显示带有搜索数据的文本框的搜索结果，并忽略其他人



我的代码到现在为止：

I'm using an intensive search feature with around 16 text boxes to search in 16 columns of the database
the problem is that when I try to search through more than 1 text box, it usually shows all the rows in the database

what I want to achieve :
is to simply ignore all the empty fields \ text boxes in the search query itself
without dedicating a separate command for each empty text box per se
OR
In other words, I want to filter my results to show only search results of the text boxes with search data in them and ignore the others

My code till now :

sqlcmd = new SqlCeCommand("Select * from Cases WHERE CaseNum LIKE @S1 "
+ "AND DisposalProsecutor LIKE @S10 AND CaseYear LIKE @S2 AND CaseRegNum LIKE @S3 AND CaseRegYear LIKE @S4 "
+ "AND CaseSubject LIKE @S5 AND AccusedNames LIKE @S6 AND ArrestOfficer LIKE @S7 AND InvestigationsOfficer LIKE @S8 "
+ "AND InterrogatorProsecutor LIKE @S9 AND InterrogationDate LIKE @S11 AND RemainingReasons LIKE @S13 AND MandateDate LIKE @S16", sqlcon);
if (S1.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S1", "%" + S1.Text + "%");}

if (S2.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S2", "%" + S2.Text + "%");}

if (S3.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S3", "%" + S3.Text + "%");}

if (S4.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S4", "%" + S4.Text + "%");}

if (S5.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S5", "%" + S5.Text + "%");}

if (S6.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S6", "%" + S6.Text + "%");}

if (S7.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S7", "%" + S7.Text + "%");}

if (S8.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S8", "%" + S8.Text + "%");}

if (S9.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S9", "%" + S9.Text + "%");}

if (S10.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S10", "%" + S10.Text + "%");}

if (S11.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S11", "%" + S11.Text + "%");}

if (S13.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S13", "%" + S13.Text + "%");}

if (S16.Text != string.Empty)
{sqlcmd.Parameters.AddWithValue("@S16", "%" + S16.Text + "%");}

    listView1.Items.Clear();

    sqldr = sqlcmd.ExecuteReader();

推荐答案

请尝试以下方式....



使用stringbuilder格式化sql查询并根据需要添加where子句...



var sqlcmd = new SqlCommand（）;



StringBuilder sb = new StringBuilder（）;

sb.Append（Select * from Cases WHERE）;

if（S1.Text ！= string.Empty）

{

sb.Append（CaseNum LIKE @ S1 AND）;

sqlcmd.Parameters.AddWithValue（ @ S1，％+ S1.Text +％）;

}

if（S2.Text！= string.Empty）

{

sb.Append（CaseYear LIKE @ S2 AND）;

sqlcmd.Parameters.AddWithValue（@ S2，％+ S2 .Text +％）;

}



sqlcmd.Connection = connectionString;

sqlcmd.CommandT ext = sb.ToString（）;

sqlcmd.CommandType = CommandType.Text;



listView1.Items.Clear（）;



sqldr = sqlcmd.ExecuteReader（）;

Please try the below way....

Format the sql query with stringbuilder and add where clauses as required...

var sqlcmd = new SqlCommand();

StringBuilder sb = new StringBuilder();
sb.Append("Select * from Cases WHERE ");
if (S1.Text != string.Empty)
{
sb.Append("CaseNum LIKE @S1 AND");
sqlcmd.Parameters.AddWithValue("@S1", "%" + S1.Text + "%");
}
if (S2.Text != string.Empty)
{
sb.Append("CaseYear LIKE @S2 AND");
sqlcmd.Parameters.AddWithValue("@S2", "%" + S2.Text + "%");
}

sqlcmd.Connection = connectionString;
sqlcmd.CommandText = sb.ToString();
sqlcmd.CommandType = CommandType.Text;

listView1.Items.Clear();

sqldr = sqlcmd.ExecuteReader();

我建​​议你创建一个数据结构来保存你使用的TextBoxes的实例： br $>


列表< textbox> ParameterTextBoxes = new List< textbox>;



代码中的某处将TextBox实例添加到此列表中。



然后，当您只想在TextBox有活动内容时添加SQL参数时，可以使用Linq过滤该列表：

I suggest you create a data structure to hold the instances of TextBoxes you use:

List<textbox> ParameterTextBoxes = new List<textbox>;

Somewhere in your code add the TextBox instances to this list.

Then, when you want to only add SQL Parameters when the TextBoxes have active content you can use Linq to filter that list:

// required
using System.Linq;

foreach(TextBox tbx in ParameterTextBoxes.Where(tb => ! string.IsNullOrEmpty(tb.Text)))
{
    sqlcmd.Parameters.AddWithValue(tbx.Name, "%{0}%", tbx.Text);
}

注意：我已经测试了一个模拟以确认它编译并按预期工作。



我假设（希望）你正在做一些事情来验证用户在TextBoxes中输入的文本。

Note: I have tested a simulation to confirm it compiles and works as expected.

I assume (hope) you are doing something to validate the Text the user enters in the TextBoxes.

我添加了一个bool开关来处理....



压缩代码

I have added a bool switch to take care of that....

compacted the code

static void Main()
        {
            var S1 = new System.Web.UI.WebControls.TextBox();
            var S2 = new System.Web.UI.WebControls.TextBox();
            var S3 = new System.Web.UI.WebControls.TextBox();
            var S4 = new System.Web.UI.WebControls.TextBox();

            S1.Text = "abcd";
            S2.Text = "abcd";
            S3.Text = "abcd";
            S4.Text = "abcd";

            //this bool switch will take care that
            bool isSetAnd = false;

            var sqlcmd = new SqlCommand();

            StringBuilder sb = new StringBuilder();
            sb.Append("Select * from Cases WHERE ");
            generateQueryText(S1.Text, "S1", ref sb, ref isSetAnd, ref sqlcmd);
            generateQueryText(S2.Text, "S2", ref sb, ref isSetAnd, ref sqlcmd);
            generateQueryText(S3.Text, "S3", ref sb, ref isSetAnd, ref sqlcmd);
            generateQueryText(S4.Text, "S4", ref sb, ref isSetAnd, ref sqlcmd);
            
            Console.WriteLine(sb.ToString());
            sqlcmd.CommandText = sb.ToString();
            sqlcmd.CommandType = CommandType.Text;

            //listView1.Items.Clear();

            //sqldr = sqlcmd.ExecuteReader();
        }

        private static void generateQueryText(string textBoxText, string textBoxID, ref StringBuilder sb, ref bool isSetAnd, ref SqlCommand sqlcmd)
        {
            if (textBoxText != string.Empty)
            {
                if (isSetAnd)
                {
                    sb.Append(" AND ");
                }
                isSetAnd = true;
                sb.Append("CaseRegYear LIKE @" + textBoxID);
                sqlcmd.Parameters.AddWithValue("@" + textBoxID, "%" + textBoxText + "%");
            }
        }

这篇关于执行sql搜索查询时忽略空文本框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！