Using multiple condition query with order by-CodePudding

I have multiple if conditions for my query, so that the user can search from the database based on drop down list selection, for example I want to get the users that have pc or not and if they have laptop or not,

using a drop down list which has 3 options all, yes and no.

everything is working fine but I have one issue with sorting, as seen below on my code in the command I can't add (order by) at the end of the query because I will have an error if I selected one of the drop down lists,

is there any idea how to use order by in this scenario,

any help will be appreciated.

<asp:DropDownList ID="DropDownList_pc" runat="server">
<asp:ListItem Text="all" Value="1"></asp:ListItem>
<asp:ListItem Text="yes" Value="2"></asp:ListItem>
<asp:ListItem Text="no" Value="3"></asp:ListItem>
</asp:DropDownList>


<asp:DropDownList ID="DropDownList_laptop" runat="server">
<asp:ListItem Text="all" Value="1"></asp:ListItem>
<asp:ListItem Text="yes" Value="2"></asp:ListItem>
<asp:ListItem Text="no" Value="3"></asp:ListItem>
</asp:DropDownList>

string command = "SELECT user_name, user_full_name from users_table where user_name > 0";

if (Int32.Parse(DropDownList_pc.SelectedItem.Value) == 2)
{
    command  = " and user_pc >= 1";
}
else if (Int32.Parse(DropDownList_pc.SelectedItem.Value) == 3)
{
    command  = " and user_pc = 0";
}
   
if (Int32.Parse(DropDownList_laptop.SelectedItem.Value) == 2)
{
    command  = " and user_laptop >= 1";
}
else if (Int32.Parse(DropDownList_laptop.SelectedItem.Value) == 3)
{
    command  = " and user_laptop = 0";
}

CodePudding user response：

How about:

var pc = "";
if (DropDownList_pc.SelectedValue == "2")
  pc = " and user_pc >= 1";
else if (DropDownList_pc.SelectedValue == "3")
  pc = " and user_pc = 0";

var lap = "";
if (DropDownList_laptop.SelectedValue) == "2")
  lap = " and user_laptop >= 1";
else if (DropDownList_laptop.SelectedValue) == "3")
  lap = " and user_laptop = 0";

string command = $@"
SELECT user_name, user_full_name 
FROM users_table 
WHERE user_name > 0 {pc} {lap}
ORDER BY user_name";

It's conceptually exactly the same as Serg's suggestion, but keeping the SQL building together makes it a bit clearer to see it as a whole and extend it, in my opinion

One thing I would say; take care that you don't stray into adding user supplied data this way. It's fine to inspect some page value supplied by the user and concat SQL together in a fixed form from hard coded fragments based on the value, but don't be tempted to add a TextBox that searches the description of X..

If you do want to add a TextBox, concatenate a parameter into your SQL:

var txt = "";
if(TextBox.Text != "")
  txt = " and somecol LIKE @p1";

And then add a parameter with that name and the TextBox.Text as the parameter value, when you build the command ...

if(TextBox.Text != "")
  command.Parameters.Add(new SqlParameter("@p1", SqlDbType.Varchar, 100){ Value = TextBox.Text });