从不同的条件中选择 [英] selecting from different condition

问题描述

大家好，



i有6列

如col1，col2，col3，...... col6



现在我需要根据条件获取记录

hi All,

i have 6 columns
like col1,col2,col3,......col6

now i need to fetch records based on conditions

if (txt1.text!="" &&txt2.text!="" && txt3.text!="" && txt4.text!="" &&txt5.text!="" && txt6.text!="")
{
query="select * from table1 where col1='" + txt1.text + " '  and col2='" + txt2.text + " '  and col3='" + txt3.text + " ' and  col4='" + txt4.text + " ' and col5=' "+ txt5.text + "' and col6='" + txt6.text + "'";
}
else if (txt1.text!="" &&txt2.text!="" &&txt3.text!="" && txt4.text!="" &&txt5.text!="" && txt6.text="")

{
 query="select * from table1 where col1='" + txt1.text + "' and col2='" + txt2.text + ' " and col3='" + txt3.text +"' and  col4=' "+txt4.text+"' and col5='"+txt5.text+"'" ;
}

else if (txt1.text!="" &&txt2.text!="" &&txt3.text!="" && txt4.text!="" &&txt5.text="" && txt6.text="")
{
query="select * from table1 where col1=" ' + txt1.text + '" and col2='" +  txt2.text + "'  and col3=' " +txt3.text + "' and  col4=' " +txt4.text+ "' ";

}
else if (txt1.text="" &&txt2.text!="" &&txt3.text!="" && txt4.text!="" &&txt5.text="" && txt6.text!="")
{
query="select * from table1 where  col2='" + txt2.text + "'  and col3=' " +txt3.text + "' and  col4=' "+ txt4.text + "'  and col6='"+txt6.text +"'";

}
else if.........
:
:
:
:
:
:
:

else
{
}

继续全部条件





这意味着它有大约6个！ = 720条件

在这种情况下是否有任何快捷方式



谢谢。

continues all the condtions


this means it has about 6! =720 conditions
is there any shortcut to implement in this type of situations

Thank You.

推荐答案

还有另一种方法可以做到这一点。这里是我给你的简单例子



for Instance，你想用4种不同的条件选择数据

There is a another way to do this. here is the Simple example i give you

for Instance ,you want to select data by 4 different Conditions

select Col1,Col2,COl3,Col4 from table where col1=1 and col2=2 and col3=3 and col4=4

Query="Select  Col1,Col2,COl3,Col4 from table where 1=1 ";
if(col1!='')
Query=Query+ " and Col1=1";

if(Col2!='')
Query=Query+" and Col2=2";

if(Col3!="")
Query=Query+" and Col3=3";

if(Col4!="")
Query=Query+" and Col4=4";

//运行您的查询

我建议你创建这个作为存储过程



编辑：



在存储过程中构建动态SQL [ ^ ]

哦亲爱的！无需编写720 if else 条件。只有一行代码可以完成相同的工作。使用逻辑OR运算符实现的小技巧。

Oh dear ! No need to write 720 if else conditions. Only a single line of code can do the same job. A small trick to be implemented using logical OR operator.

query="select * from table1 where ('"+txt1.Text+"'='' OR col1='"+txt1.Text+"') and ('"+txt2.Text+"'='' OR col2='"+txt2.Text+"') and ('"+txt3.Text+"'='' OR col3='"+txt3.Text+"') and ('"+txt4.Text+"'='' OR col4='"+txt4.Text+"') and ('"+txt5.Text+"'='' OR col5='"+txt5.Text+"') and ('"+txt6.Text+"'='' OR col6='"+txt6.Text+"')";

不要在此周围放置任何 if else 条件。



希望，我已经节省了你的写作时间浪费 if else 条件。

如果你想在存储过程中使用它，你可以查看我的博客文章 SQL Server：创建具有多个可选参数的过程的最佳方法 [ ^ ]



如果没有请帮忙，请告诉我:)



更新（防止SQL注入）

正如@ CHill60建议的那样，您应该考虑改进查询方法以防止SQL注入。将其转换为存储过程或者参数化查询可能会有所帮助。

Don't put any if else condition around this.

Hopefully, I have saved your time wasting in writing if else conditions.
In case you want to use this in a stored procedure, you can check my blog post on SQL Server: An optimal way to create procedure with multiple optional parameters[^]

In case this doesn't help, please let me know :)

Update (Preventing SQL Injection)
As @CHill60 suggested, you should consider improving your querying method to prevent SQL Injection. Either convert it to a stored procedure or following parametrized query may help.

query="select * from table1 where (@txt1='' OR col1=@txt1) and (@txt2='' OR col2=@txt2) and (@txt3='' OR col3=@txt3) and (@txt4='' OR col4=@txt4) and (@txt5='' OR col5=@txt5) and (@txt6='' OR col6=@txt6)";
//SqlCommand cmd = new SqlCommand(Query);
cmd.Parameters.AddWithValue("@txt1", txt1.Text.Trim());
cmd.Parameters.AddWithValue("@txt2", txt2.Text.Trim());
cmd.Parameters.AddWithValue("@txt3", txt3.Text.Trim());
cmd.Parameters.AddWithValue("@txt4", txt4.Text.Trim());
cmd.Parameters.AddWithValue("@txt5", txt5.Text.Trim());
cmd.Parameters.AddWithValue("@txt6", txt6.Text.Trim());
//execute the command

：）

:)

你可以把你的查询写成

You can write your Query as

Select * from table1 where col1=ISNULL(@Col1,col1) and Col2=ISNULL(@Col2,Col2) and Col3=ISNULL(@Col3,Col3)

以后你可以写如下：

and later on you can write as below:

cmd.Parameters.AddWithValue("@txt1", txt1.Text.Trim()== string.Empty ? null :txt1.Text.Trim());

In任何情况下它都会给你正确的记录数，而不需要写任何其他代码。

In any case it will give you correct number of Records and no need to write any other code.

这篇关于从不同的条件中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！