ASP.NET MVC中根据下拉框选择值更新表格数据的技术问询

问题核心 #

下拉框选择值后，表格自动加载对应数据，需解决：参数传递到控制器、动态更新SQL查询、合理设计控制器方法这三个问题。

解决方案步骤 #

1. 修正控制器代码 #

将数据获取逻辑拆分，支持筛选参数，同时避免SQL注入，修复原代码中的线程不安全问题：

public class TestController : Controller
{
    // 建议将连接字符串放在appsettings.json中，这里直接写是为了示例
    private readonly string _connectionString = "bbbb";

    // 主页面方法，支持传入筛选参数
    public IActionResult Index(string pr = null)
    {
        var dropdownData = GetDropdownOptions();
        var tableData = GetFilteredTableData(pr);

        ViewData["dropdown"] = dropdownData;
        ViewData["table"] = tableData;

        return View();
    }

    // 单独获取下拉框数据
    private List<TestModel> GetDropdownOptions()
    {
        var list = new List<TestModel>();
        try
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                string sql = "SELECT * FROM db WHERE detail = 1";
                using (var cmd = new SqlCommand(sql, conn))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(new TestModel
                            {
                                id = Convert.ToInt32(reader[0]),
                                name = reader[1].ToString(),
                                serial = reader[3].ToString(),
                                pr = reader[4].ToString()
                            });
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.ToString()}");
        }
        return list;
    }

    // 获取表格数据，支持筛选
    private List<TableModel> GetFilteredTableData(string prFilter)
    {
        var list = new List<TableModel>();
        try
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                // 构造带参数的SQL，避免注入
                string sql = "SELECT * FROM bb1";
                if (!string.IsNullOrEmpty(prFilter))
                {
                    sql += " WHERE pr = @Pr"; // 这里的pr要和你的表字段对应
                }

                using (var cmd = new SqlCommand(sql, conn))
                {
                    if (!string.IsNullOrEmpty(prFilter))
                    {
                        cmd.Parameters.AddWithValue("@Pr", prFilter);
                    }

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(new TableModel
                            {
                                id = Convert.ToInt32(reader[0]),
                                tr = reader[2].ToString(),
                                pr = reader[4].ToString(),
                                req = reader[5].ToString(),
                                test = reader[6].ToString(),
                                method = reader[7].ToString(),
                                sport = reader[8].ToString(),
                                timer = reader[9].ToString(),
                                ra = reader[10].ToString(),
                                calib = reader[12].ToString()
                            });
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.ToString()}");
        }
        return list;
    }

    // 用于AJAX异步加载数据的接口
    public IActionResult LoadTableData(string pr)
    {
        var data = GetFilteredTableData(pr);
        return Json(data);
    }
}

2. 修正视图代码 #

修复原视图中的冲突问题，优化下拉框参数传递，实现无刷新表格更新：

<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-table/1.18.3/bootstrap-table.min.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/js/bootstrap.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-table/1.18.3/bootstrap-table.min.js"></script>
</head>

<body>
    <div class="container">
        <div class="row mt-3">
            <div class="col-md-4">
                <select id="UserId" class="form-control">
                    <option value="">请选择筛选条件</option>
                    @foreach (var item in ViewData["dropdown"] as IEnumerable<TestModel>)
                    {
                        <!-- 用data属性存储额外参数，避免分割字符串 -->
                        <option value="@item.pr" data-serial="@item.serial">@item.name</option>
                    }
                </select>
            </div>
            <div class="col-md-4">
                <input id="serial" type="text" class="form-control" placeholder="Serial">
            </div>
            <div class="col-md-4">
                <input id="pr" type="text" class="form-control" placeholder="PR">
            </div>
        </div>

        <div class="row mt-3">
            <table id="fresh-table" class="table table-hover table-striped">
                <thead>
                    <th data-field="tr" data-sortable="true">TR</th>
                    <th data-field="pr" data-sortable="true">Priority</th>
                    <th data-field="req" data-sortable="true">Requestor</th>
                    <th data-field="test" data-sortable="true">Test Name</th>
                    <th data-field="method" data-sortable="true">Description</th>
                    <th data-field="sport" data-sortable="true">Failed Part</th>
                    <th data-field="timer" data-sortable="true">Emission</th>
                    <th data-field="ra" data-sortable="true">Rating</th>
                    <th data-field="calib" data-sortable="true">Calibration</th>
                </thead>
                <tbody>
                    @foreach (var item in ViewData["table"] as IEnumerable<TableModel>)
                    {
                        <tr>
                            <td>@item.tr</td>
                            <td>@item.pr</td>
                            <td>@item.req</td>
                            <td>@item.test</td>
                            <td>@item.method</td>
                            <td>@item.sport</td>
                            <td>@item.timer</td>
                            <td>@item.ra</td>
                            <td>@item.calib</td>
                        </tr>
                    }
                </tbody>
            </table>
        </div>
    </div>

    <script type="text/javascript">
        $(function () {
            $('#fresh-table').bootstrapTable({
                classes: 'table table-hover table-striped',
                search: true,
                showRefresh: true,
                showToggle: true,
                showColumns: true,
                pagination: true,
                height: $(window).height() - 120,
                striped: true,
                sortable: true,
                pageSize: 10,
                pageList: [8, 10, 25, 50, 100],
                formatShowingRows: () => '',
                formatRecordsPerPage: (num) => `${num} rows visible`
            });

            $(window).resize(() => {
                $('#fresh-table').bootstrapTable('resetView', { height: $(window).height() - 120 });
            });
        });

        // 下拉框选择事件
        $("#UserId").change(function () {
            const selectedPr = $(this).val();
            const selectedOption = $(this).find('option:selected');
            const serial = selectedOption.data('serial');

            // 更新输入框内容
            $('#serial').val(serial);
            $('#pr').val(selectedPr);

            // 异步加载表格数据
            $.ajax({
                url: '/Test/LoadTableData',
                type: 'GET',
                data: { pr: selectedPr },
                success: (data) => {
                    $('#fresh-table').bootstrapTable('load', data);
                },
                error: () => {
                    alert('数据加载失败');
                }
            });
        });
    </script>
</body>
</html>

3. 关键细节说明 #

• 参数传递：通过下拉框的change事件，将选中的pr值传递给控制器，可选两种方式：
  • 页面重载：通过window.location.href = '/Test/Index?pr=' + selectedPr直接跳转，逻辑简单但有刷新
  • AJAX异步：调用LoadTableData接口返回JSON，无刷新更新表格（推荐）
• SQL安全：使用参数化查询@Pr，绝对禁止直接拼接字符串，避免SQL注入风险
• 控制器方法设计：可以复用Index方法，通过可选参数pr实现筛选；也可以单独写接口用于AJAX，两种方式都能满足需求
• 视图优化：用data-*属性存储额外参数（如serial），比拼接字符串更优雅、易维护

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