ASP.NET如何使用<select>下拉菜单更新SQLDataSource的SELECT查询命令

实现步骤 #

1. 调整前端控件结构 #

你当前的两个下拉框放在<form runat="server">外部，且用的是原生HTML select标签，无法被后台.NET代码直接访问，需要先移到form内部，替换为ASP.NET服务端下拉控件：

<form id="form1" runat="server">
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="RunsData">
        <Columns>
            <asp:BoundField DataField="PlayerId" HeaderText="PlayerId" SortExpression="PlayerId" />
            <asp:BoundField DataField="Duration" HeaderText="Duration" SortExpression="Duration" />
            <asp:BoundField DataField="VersionId" HeaderText="VersionId" SortExpression="VersionId" />
            <asp:BoundField DataField="DateUploaded" HeaderText="DateUploaded" SortExpression="DateUploaded" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="RunsData" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [PlayerId], [Duration], [VersionId], [DateUploaded] FROM [Run] ORDER BY [Duration]"></asp:SqlDataSource>

    <div>
        <label class="label" for="version">Version: </label>
        <asp:DropDownList runat="server" ID="VersionSelect" AutoPostBack="true">
            <asp:ListItem Value="lev1" Text="Level 1"></asp:ListItem>
            <asp:ListItem Value="lev1to3" Text="Level 1-3"></asp:ListItem>
            <asp:ListItem Value="lev8" Text="Level 8"></asp:ListItem>
            <asp:ListItem Value="lev17" Text="Level 17"></asp:ListItem>
        </asp:DropDownList>
        <label class="label" for="sortby">Sort By: </label>
        <asp:DropDownList runat="server" ID="SortBySelect" AutoPostBack="true" OnSelectedIndexChanged="SortBySelect_SelectedIndexChanged">
            <asp:ListItem Value="duration" Text="Duration"></asp:ListItem>
            <asp:ListItem Value="date" Text="Date"></asp:ListItem>
            <asp:ListItem Value="player" Text="Player"></asp:ListItem>
        </asp:DropDownList>
    </div>
</form>

2. 编写后台逻辑 #

在对应页面的.cs后置代码里，添加排序下拉框的选中变更事件处理逻辑，动态修改SqlDataSource的查询语句，通过固定枚举匹配排序字段可以避免SQL注入风险：

protected void SortBySelect_SelectedIndexChanged(object sender, EventArgs e)
{
    // 基础查询语句
    string baseSql = "SELECT [PlayerId], [Duration], [VersionId], [DateUploaded] FROM [Run] ";
    string sortField = "Duration";

    // 匹配排序字段
    switch (SortBySelect.SelectedValue)
    {
        case "date":
            sortField = "DateUploaded";
            break;
        case "player":
            sortField = "PlayerId";
            break;
        default:
            sortField = "Duration";
            break;
    }

    // 拼接最终查询语句，需要版本筛选可在此处拼接WHERE条件
    string finalSql = baseSql + " ORDER BY [" + sortField + "]";

    // 更新查询命令，GridView自动刷新
    RunsData.SelectCommand = finalSql;
    GridView1.DataBind();
}

补充优化点 #

• 如果需要版本筛选，只需要给VersionSelect也加SelectedIndexChanged事件，拼接对应WHERE条件即可，推荐搭配SqlDataSource的SelectParameters传值，安全性更高
• 不需要整页刷新的话，可以把所有控件包裹在<asp:UpdatePanel>内，实现局部无刷新更新数据
• 所有逻辑都是ASP.NET Web Forms原生支持的功能，不需要引入额外组件

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