如何从多个列表框选定值构建SQL查询？ [英] How Do I Build A Sql Query From Multiple Listbox Selected Values?

问题描述

嗨



我正忙于一个显示活动用户ListView的Intranet应用程序。 ListView上方有5个列表框，允许过滤ListView（按国家，状态，职业等）。每个ListBox允许选择多个条件，我使用sql WHERE子句通过循环列表框中的选定项并将它们添加到IN运算符（例如WHERE Country IN（'Country1'，'Country2'））进行过滤。现在，对于那些让我听起来像真正的新闻的部分..如何在构建查询的各部分之间添加AND关键字？它应该很简单，但如果一个人按照第1和第3标准过滤并离开2,4和5怎么办。



循环列表框的代码如下：

Hi

I am busy with an intranet application that shows a ListView of active users. Above the ListView are 5 Listboxes that allow the ListView to be filtered (by Country, Status, Occupation etc). Each ListBox allows multiple criteria to be selected and i am filtering using a sql WHERE clause by looping through the selected items in the Listbox and adding them to an IN operator eg WHERE Country IN ('Country1', 'Country2'). Now for the part that's gonna make me sound like a real newb.. How do i add the AND keyword between the portions of the built up query? It should be simple but what if a person filters by the 1st and 3rd criteria and leaves 2, 4 and 5.

My code for looping through the listboxes is as follows:

if (lbOccupation.GetSelectedIndices().Count() != 0)
                {
                    strFilter.Append("OccupationID IN (");
                    foreach (ListItem item in lbOccupation.Items)
                    {
                        if (item.Selected)
                        {
                            strFilter.Append("'" + item.Value + "',");
                        }
                    }
                    strFilter.Remove(strFilter.Length - 1, 1);
                    strFilter.Append(")");
                }

任何建议真的很值得赞赏。谢谢

Any suggestions would really be appreciated. Thanks

推荐答案

不要这样做 - 你的代码容易受到 SQL注入 [ ^ ]。您需要使用参数化查询。

Don't do it like that - you're code is vulnerable to SQL Injection[^]. You need to use a parameterized query instead.

// The base query:
strFilter.Append("SELECT .....");

// Add an "always true" condition at the start;
// that way, you *always* add an "AND" before each condition.
strFilter.Append("WHERE 1 = 1");

ListItem[] selectedItems = lbOccupation.Items.Where(item => item.Selected).ToArray();
if (selectedItems.Length != 0)
{
    strFilter.Append("AND OccupationID IN (");
    
    for (int index = 0; index < selectedItems.Length; index++)
    {
        if (index != 0) strFilter.Append(',');
        
        // Create a parameter and add it to the command:
        string name = "@Occupation" + index;
        command.Parameters.AddWithValue(name, item.Value);
        
        // Use the parameter in the query:
        strFilter.Append(name);
    }
    
    strFilter.Append(")");
}

我最终找到了答案。通过检查strFilter.Length属性和Listbox.GetSelectedIndices（）。Count（）来确定是否有所选项目。

I found the answer eventually. By checking the strFilter.Length property and the Listbox.GetSelectedIndices().Count() to determine if there are selected items.

if (strFilter.Length != 0 && lbOccupation.GetSelectedIndices().Count() != 0)
     {
        strFilter.Append(" AND ");
     }

谢谢

Thanks

这篇关于如何从多个列表框选定值构建SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！