如何基于单个或多个DropDownList筛选GridView数据

核心思路 #

采用参数化查询+动态条件拼接的方式，既避免硬编码大量if判断，又能保证SQL语句的安全性，防止注入攻击。同时利用DropDownList的AutoPostBack属性，实现选择选项时自动更新GridView。

具体实现步骤 #

1. 统一绑定下拉列表的选择事件 #

修改ASPX中的DropDownList，给四个控件绑定同一个SelectedIndexChanged事件处理方法，减少冗余代码：

<tr>
    <td>
        <asp:DropDownList ID="ddlSearchOCFamily" AutoPostBack="True" AppendDataBoundItems="True" 
            DataTextField="FamilyLong" DataValueField="FamilyLong" runat="server" 
            OnSelectedIndexChanged="ddlFilter_SelectedIndexChanged"></asp:DropDownList>
    </td>
    <td>
        <asp:DropDownList ID="ddlSearchOCRate" AutoPostBack="True" AppendDataBoundItems="True" 
            DataTextField="RateLong" DataValueField="RateLong" runat="server" 
            OnSelectedIndexChanged="ddlFilter_SelectedIndexChanged"></asp:DropDownList>
    </td>
    <td>
        <asp:DropDownList ID="ddlSearchOCTerm" AutoPostBack="True" AppendDataBoundItems="True" 
            DataTextField="TermLong" DataValueField="TermLong" runat="server" 
            OnSelectedIndexChanged="ddlFilter_SelectedIndexChanged"></asp:DropDownList>
    </td>
    <td>
        <asp:DropDownList ID="ddlSearchOCEnrollment" AutoPostBack="True" AppendDataBoundItems="True" 
            DataTextField="TypeDescription" DataValueField="TypeDescription" runat="server" 
            OnSelectedIndexChanged="ddlFilter_SelectedIndexChanged"></asp:DropDownList>
    </td>
</tr>

2. 重构Page_Load事件 #

仅在首次加载时初始化控件和全量数据，避免PostBack时重复执行：

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        pnlSearchOC.Visible = False
        PopSearchOCDDLs()
        ' 首次加载全量数据
        PopGVSearchOC(GetFilteredQuery())
    End If
End Sub

3. 实现动态筛选查询生成 #

新增方法动态拼接筛选条件，同时生成参数化SQL，确保安全：

Private Function GetFilteredQuery() As String
    ' 基础查询：需包含筛选所需字段（子查询中新增关联字段）
    Dim baseQuery As String = "
        SELECT dDown.OrderCode, dDown.[Description], dDown.[Net Price]
        FROM (
              SELECT SKU.OrderCode AS OrderCode, SKU.[Description] AS [Description],
              (SELECT FORMAT(SKU.ListPrice * EnrollmentType.Multiplier, 'C')) AS [Net Price],
              SKUBuild.FamilyLong, SKUBuild.RateLong, SKUBuild.TermLong, EnrollmentType.TypeDescription
              FROM Enrollment
              INNER JOIN EnrollmentType ON Enrollment.EnrollmentTypeID = EnrollmentType.RecID
              INNER JOIN SKU ON EnrollmentType.TypeID = SKU.EnrollmentTypeID
              INNER JOIN SKUBuild ON SKU.SKU = SKUBuild.SKU
              WHERE (Enrollment.AccountNumber = '12345')
              AND (Enrollment.Active = 'yes')
              AND (SKU.[Description] IS NOT NULL AND NOT (SKU.[Description] = ''))
        ) AS dDown
        WHERE 1=1 " ' 用1=1简化条件拼接逻辑

    Dim parameters As New List(Of SqlParameter)()

    ' 处理Family筛选（排除默认空选项）
    If Not String.IsNullOrEmpty(ddlSearchOCFamily.SelectedValue) AndAlso ddlSearchOCFamily.SelectedValue <> "请选择" Then
        baseQuery += " AND dDown.FamilyLong = @FamilyLong"
        parameters.Add(New SqlParameter("@FamilyLong", ddlSearchOCFamily.SelectedValue))
    End If

    ' 处理Rate筛选
    If Not String.IsNullOrEmpty(ddlSearchOCRate.SelectedValue) AndAlso ddlSearchOCRate.SelectedValue <> "请选择" Then
        baseQuery += " AND dDown.RateLong = @RateLong"
        parameters.Add(New SqlParameter("@RateLong", ddlSearchOCRate.SelectedValue))
    End If

    ' 处理Term筛选
    If Not String.IsNullOrEmpty(ddlSearchOCTerm.SelectedValue) AndAlso ddlSearchOCTerm.SelectedValue <> "请选择" Then
        baseQuery += " AND dDown.TermLong = @TermLong"
        parameters.Add(New SqlParameter("@TermLong", ddlSearchOCTerm.SelectedValue))
    End If

    ' 处理Enrollment筛选
    If Not String.IsNullOrEmpty(ddlSearchOCEnrollment.SelectedValue) AndAlso ddlSearchOCEnrollment.SelectedValue <> "请选择" Then
        baseQuery += " AND dDown.TypeDescription = @TypeDescription"
        parameters.Add(New SqlParameter("@TypeDescription", ddlSearchOCEnrollment.SelectedValue))
    End If

    ' 参数存入ViewState，供GridView绑定方法使用
    ViewState("FilterParameters") = parameters

    Return baseQuery
End Function

4. 修改GridView绑定方法，支持参数传递 #

调整PopGVSearchOC方法，接收参数化查询并执行：

Protected Sub PopGVSearchOC(ByVal query As String)
    Dim dt As New DataTable
    Dim parameters As List(Of SqlParameter) = TryCast(ViewState("FilterParameters"), List(Of SqlParameter))

    Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("WarrantyConnectionString").ToString)
        Using cmd As New SqlCommand(query, con)
            ' 添加筛选参数
            If parameters IsNot Nothing Then
                cmd.Parameters.AddRange(parameters.ToArray())
            End If

            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader
                If sdr.HasRows Then
                    dt.Load(sdr)
                End If
            End Using

            With gvReverseSearchOC
                .DataSource = dt
                .DataBind()
            End With
        End Using
    End Using
End Sub

5. 实现统一的下拉列表选择事件 #

触发筛选逻辑并更新GridView：

Protected Sub ddlFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    PopGVSearchOC(GetFilteredQuery())
End Sub

方案优势 #

• 代码简洁易维护：通过WHERE 1=1动态追加条件，避免大量嵌套if判断
• SQL安全：全程使用参数化查询，彻底杜绝SQL注入风险
• 减少冗余：四个下拉列表共用一个事件处理方法，降低代码重复率
• 自动触发筛选：借助AutoPostBack属性，选择选项时自动更新GridView数据

内容的提问来源于stack exchange，提问作者WhiteGlint