c# - 使用多个复选框过滤 Gridview 结果(如何使 And/Or 语句起作用)

Question

因此，我有一个 GridView、“CustomerGridView”和一个 SqlDataSource、“SqlDataSource1”，它们从我的 sql 表中提取数据。现在我想添加一些过滤，所以我创建了一个带有各种选项的侧面板。

我的问题是，对于我当前的代码，如果我选择，比如每月付款的客户、新客户以及每半年付款的客户。它向我展示了所有这些类型，包括符合标准的活跃(非新)客户，而不是只向我展示每半年或每月支付一次的新客户。

我该如何解决这个问题？

我试过括号之类的，但都无济于事；我是否只需要为每种情况创建一个 if 并手动编写每个过滤器表达式？

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    SqlDataSource1.FilterExpression = string.Empty;
    if (MonthlyCheckBox.Checked)
    {

            SqlDataSource1.FilterExpression = "[CustomerSubscriptionType]='Monthly'";
    }
    if (SemiAnnuallyCheckBox.Checked)
    {
        if (!string.IsNullOrEmpty(SqlDataSource1.FilterExpression))
        {
            SqlDataSource1.FilterExpression += "OR ([CustomerSubscriptionType]='Semi-Annually')";
        }
        else
        {
            SqlDataSource1.FilterExpression = "[CustomerSubscriptionType]='Semi-Annually'";
        }

    }
    if (AnnuallyCheckBox.Checked)
    {
        if (!string.IsNullOrEmpty(SqlDataSource1.FilterExpression))
        {
            SqlDataSource1.FilterExpression += "OR ([CustomerSubscriptionType]='Annually')";
        }
        else
        {
            SqlDataSource1.FilterExpression = "[CustomerSubscriptionType]='Annually'";
        }
    }
    if (NewCheckBox.Checked)
    {
        if (!string.IsNullOrEmpty(SqlDataSource1.FilterExpression))
        {
            SqlDataSource1.FilterExpression += " ( OR [CustomerStatus]='Active')";
        }
        else
        {
            SqlDataSource1.FilterExpression = "[CustomerStatus]='New'";
        }

    }
    if (ActiveCheckBox.Checked)
    {
        if (!string.IsNullOrEmpty(SqlDataSource1.FilterExpression))
        {
            SqlDataSource1.FilterExpression += " OR [CustomerStatus]='Active'";
        }
        else
        {
            SqlDataSource1.FilterExpression = "[CustomerStatus]='Active'";
        }
    }
    if (SuspendedCheckBox.Checked)
    {
        if (!string.IsNullOrEmpty(SqlDataSource1.FilterExpression))
        {
            SqlDataSource1.FilterExpression += " OR [CustomerStatus]='Suspended'";
        }
        else
        {
            SqlDataSource1.FilterExpression = "[CustomerStatus]='Suspended'";
        }
    }
    if (ResidentialCheckBox.Checked)
    {
        if (!string.IsNullOrEmpty(SqlDataSource1.FilterExpression))
        {
                SqlDataSource1.FilterExpression += " AND [CustomerType]='Residential' ";
        }
        else
        {
            SqlDataSource1.FilterExpression = "[CustomerType]='Residential'";
        } 
    }
    if (CommercialCheckBox.Checked)
    {
        if (!string.IsNullOrEmpty(SqlDataSource1.FilterExpression))
        {
            SqlDataSource1.FilterExpression += " AND [CustomerType]='Commercial' ";
        }
        else
        {
            SqlDataSource1.FilterExpression = "[CustomerType]='Commercial'";
        } 

    }
    if (NotInGroupCheckBox.Checked)
    {
        if (!string.IsNullOrEmpty(SqlDataSource1.FilterExpression))
        {
            SqlDataSource1.FilterExpression += " AND [CustomerGroup]=''";
        }
        else
        {
            SqlDataSource1.FilterExpression = "[CustomerGroup]=''";
        }
    }
    if (InGroupCheckBox.Checked)
    {
        if (!string.IsNullOrEmpty(SqlDataSource1.FilterExpression))
        {
            SqlDataSource1.FilterExpression += " AND [CustomerGroup]<>''";
        }
        else
        {
            SqlDataSource1.FilterExpression = "[CustomerGroup]<>''";
        }
    }
    if (GroupDropDownFilter.Text != "Select...")
    {
        if (!string.IsNullOrEmpty(SqlDataSource1.FilterExpression))
        {
            SqlDataSource1.FilterExpression += " AND [CustomerGroup]='{0}'";
            SqlDataSource1.FilterParameters.Add("@CustomerGroup", GroupDropDownFilter.Text);
        }
        else
        {
            SqlDataSource1.FilterExpression = "[CustomerGroup]='{0}'";
            SqlDataSource1.FilterParameters.Add("@CustomerGroup", GroupDropDownFilter.Text);
        }
    }
}

Answer 1

您正在使用 OR 运算符。

if(@CustomerSubscriptionType = 'Monthly' OR @CustomerStatus = 'New') 将为您提供所有按月付费的客户(活跃客户和新客户)和所有新客户(无论订阅类型如何)。

如果您使用 AND 运算符，您只会让新客户按月付费。

您可能希望结合使用这两个运算符，因此我会在组内使用 OR 运算符，在组之间使用 AND 运算符。

if(
    (@CustomerSubscriptionType = 'Monthly' OR @CustomerSubscriptionType = 'Semi-Monthly')
AND (@CustomerStatus = 'New')
AND (@SomeOtherCriteria = 'Other' OR @SomeOtherCriteria = 'Other2')
AND etc...

为了修复您的代码，我将为每个“组”使用单独的字符串:

SqlDataSource1.FilterExpression = string.Empty;

List<string> subscriptionTypeFilter = new List<string>();
if (MonthlyCheckBox.Checked)
{
    subscriptionTypeFilter.Add("[CustomerSubscriptionType]='Monthly'");
}

if (SemiAnnuallyCheckBox.Checked)
{
    subscriptionTypeFilter.Add("[CustomerSubscriptionType]='Semi-Annually'");
}

if (AnnuallyCheckBox.Checked)
{
subscriptionTypeFilter.Add("[CustomerSubscriptionType]='Annually'");
}

List<string> customerStatusFilter = new List<string>();
if (NewCheckBox.Checked)
{
subscriptionTypeFilter.Add("[CustomerStatus]='New'");             
}

if (ActiveCheckBox.Checked)
{
subscriptionTypeFilter.Add("[CustomerStatus]='Active'");
}

if (SuspendedCheckBox.Checked)
{
subscriptionTypeFilter.Add("[CustomerStatus]='Suspended'");
}


List<string> filters = new List<string>();

filters.Add("(" + string.Join(" OR ", subscriptionTypeFilter) + ")");
filters.Add("(" + string.Join(" OR ", customerStatusFilter) + ")");

SqlDataSource1.FilterExpression = string.Join(" AND ", filters);      

EDIT: Yeah, this is what I meant by having "to create an if for every situation and manually write each filterexpression." I was hoping I wouldn't have to do that and could make it work some other way

要动态执行您想要的操作，您可以将字段名称和值指定为元素上的自定义属性:

<asp:CheckBox id="SuspendedCheckBox" runat="server" fieldName="CustomerStatus" fieldValue="Suspended" />

一旦正确定义了所有复选框，就只需要循环控件即可。

foreach(Control c in this.Controls)
{
    if(c is CheckBox)
    {
        CheckBox cb = (CheckBox)c;

        if(cb.IsChecked)
        {
            string fieldName = cb.Attributes["fieldName"];
            string fieldValue = cb.Attributes["fieldValue"];

            someDictionaryMaybe.Add(string.Format("[{0}] = '{1}'", fieldName, fieldValue), fieldName);
        }
    }
}

// loop through the dictionary, joining the different expressions with ORs and ANDs
// or use LINQ to put it all together, it's up to you.  The fieldName was added to 
// the dictionary, which can be used to separate with ANDs and ORs.