ASP.NET MVC中下拉列表筛选数据的分页实现求助

我是ASP.NET MVC新手，当前项目默认首页无数据，需通过两个联动下拉列表筛选数据库中的数据，希望当筛选出的数据超过10条时实现分页展示。目前已实现下拉列表联动及筛选数据展示功能，但不知如何添加分页逻辑，尤其是不清楚如何修改后端GetShopmap方法及前端JS代码来完成需求，急求帮助。

JavaScript代码 #

<script type="text/javascript">
    $(document).ready(function () {

        $("#CITY").change(function () { ChangeCity(); });
        $("#AREA").change(function () { ChangeArea(); });
        $(document).on('submit', '#ButtonSubmit', function () {
            return false;
        });


    })
    function SetareaEmpty() {
        $('#CITY').empty();
        $('#CITY').append($('<option></option>').val('').text('select'));
    }
    function ChangeCity() {
        var selectedCity = $.trim($('#CITY option:selected').val());
        if ($.trim(selectedCity.length) > 0) {
            ChangeArea(selectedCity);
        }
        else {
            SetareaEmpty()
        }
    }
    function ChangeArea(selectedCity) {
        $.ajax({
            type: "POST",
            url: '@Url.Action("GetSecondDDL", "Getarea")',
            dataType: "json",
            data: { cityName: selectedCity },
            success: function (mems) {
                if (mems.length > 0) {
                    $('#AREA').empty();
                    $('#AREA').append($('<option></option>').val('').text('select'));
                }
                $.each(mems, function (i, member) {
                    $("#AREA").append($('<option></option>').val(member).text(member));

                });
            }
        });

    }
    function SerchallData(selectedCity) {
        var selectedCity = $('#CITY option:selected').val();
        var selectedValue = $('#AREA option:selected').val();
        if ($.trim(selectedValue).length > 0) {
            $.ajax({
                url: '@Url.Action("Getmap", "Getarea")',
                data: { cityName: selectedCity, areaName: selectedValue },
                type: 'POST',
                dataType: 'json',
                success: function (data) {
                    $('#Mytable>tbody').empty();
                    for (var i = 0; i < data.length; i++) {
                        var row = $('<tr><td></td><td>' + data[i].ID + '</td><td>' + data[i].Name + '</td><td>' + data[i].Address + '</td><td>' +  data[i].Phone + '</td><td></td></tr>');
                        $('#Mytable>tbody').append(row);
                        
                    }
                    $('#Mytable').show(); //show filter data


                },
                error: function (jqXHR, textStatus, errorThrown) {
                    alert('Error: ' + textStatus + ' - ' + errorThrown);
                }
            });

        }
    }
</script>

HTML代码 #

<form>
    <div class="row well">
        <div class="col-lg-3">
            <div class="input-group">
                <span class="input-group-addon">City</span>
                <p>
                    @Html.DropDownList("City", (IEnumerable<SelectListItem>)ViewBag.Allcity, "Please select", new { id = "CITY" })
                </p>
            </div>
        </div>
        <div class="col-lg-3">
            <div class="input-group">
                <span class="input-group-addon">Area</span>
                <p> <select id="AREA" name="AREA"><option>Please select</option></select></p>

            </div>
        </div>

        <div class="col-lg-2">
            <button type="button" onclick="SearchallData()" id="ButtonSubmit" class="btn btn-primary">
                <span class="glyphicon glyphicon-search"></span>search
            </button>

        </div>


    </div>

<table id="Mytable" class="table table-bordered table-hover" style="display:none">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Address</th>
                <th>Phone</th>
             </tr>
        </thead>

<tbody>
    //first page table will not show any data so is null
</tbody>

</form>

Controller代码 #

public async Task<ActionResult> Index()
{
    var b = new GetCollection();
    var areasource = await b.GetInserchdata();
    ViewBag.AllCity = areasource.Select(s => s.City).Distinct().Select(item => new SelectListItem()
    {
        Text = item,
        Value = item
    });

    DataTable dt = new DataTable();
    using (SqlConnection con = new SqlConnection(connstr))
    {
        con.Open();
        string q = "Select * from Shopmap";
        SqlDataAdapter da = new SqlDataAdapter(q, con);
        da.Fill(dt);
    }
    return View(dt);
}

//js for first selected dropdownlist
[HttpPost]
public async Task<JsonResult> GetSecondDDL(string cityName)
{
    var CitySlectList = this.GetSelectList(await GetCity(), cityName);

    if (string.IsNullOrEmpty(cityName))
    {
        return Json(new EmptyResult());
    }
    var AreaSlectList = await this.GetArea(cityName);

    return Json(AreaSlectList);
}

//js for selected second dropdownlist then search data
[HttpPost]
public JsonResult GetShopmap(string cityName, string areaName)
{
    var b = new GetCollection();
    List<Inserch> shop = b.GetDBdata();
    var a = shop.FindAll(x => x.Address.Contains(cityName));
    var Alldata = a.FindAll(x => x.Address.Contains(areaName)).AsEnumerable();
    if (string.IsNullOrEmpty(areaName))
    {
        return Json(new EmptyResult());
    }
   
    return Json(Alldata);
}

解决方案 #

1. 后端GetShopmap方法修改 #

第一步：定义分页结果类

在项目Models文件夹下新增一个类，用于封装分页数据和总条数：

public class PagedResult<T>
{
    public List<T> Data { get; set; }
    public int TotalCount { get; set; }
    public int PageIndex { get; set; }
    public int PageSize { get; set; }
}

第二步：修改GetShopmap方法

添加分页参数，实现内存分页逻辑（数据量大时建议直接在SQL层面分页）：

[HttpPost]
public JsonResult GetShopmap(string cityName, string areaName, int pageIndex = 1, int pageSize = 10)
{
    var b = new GetCollection();
    List<Inserch> shop = b.GetDBdata();
    
    // 筛选符合条件的数据
    var filteredData = shop.Where(x => x.Address.Contains(cityName) && x.Address.Contains(areaName)).ToList();

    if (string.IsNullOrEmpty(areaName))
    {
        return Json(new PagedResult<Inserch> { Data = new List<Inserch>(), TotalCount = 0 });
    }

    // 计算总条数和分页数据
    int totalCount = filteredData.Count;
    var pagedData = filteredData.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();

    // 返回分页结果
    return Json(new PagedResult<Inserch>
    {
        Data = pagedData,
        TotalCount = totalCount,
        PageIndex = pageIndex,
        PageSize = pageSize
    });
}

2. 前端代码修改 #

第一步：添加分页控件和隐藏域

在HTML表格下方添加分页容器和保存总条数的隐藏域：

<input type="hidden" id="totalCount" value="0" />
<div id="paginationContainer" style="display:none; margin-top:10px; text-align:center;">
    <button id="prevPage" class="btn btn-default">上一页</button>
    <span id="pageInfo"></span>
    <button id="nextPage" class="btn btn-default">下一页</button>
</div>

第二步：修改JavaScript逻辑

添加全局变量保存筛选条件和分页状态，重构数据加载逻辑：

<script type="text/javascript">
    // 全局变量保存当前筛选条件和分页信息
    var currentCity = "";
    var currentArea = "";
    var currentPage = 1;
    var pageSize = 10;

    $(document).ready(function () {
        $("#CITY").change(function () { ChangeCity(); });
        $("#AREA").change(function () { ChangeArea(); });
        $(document).on('submit', '#ButtonSubmit', function () {
            return false;
        });

        // 分页按钮点击事件
        $("#prevPage").click(function () {
            if (currentPage > 1) {
                currentPage--;
                loadShopData();
            }
        });
        $("#nextPage").click(function () {
            var totalPages = Math.ceil($("#totalCount").val() / pageSize);
            if (currentPage < totalPages) {
                currentPage++;
                loadShopData();
            }
        });
    })

    function SetareaEmpty() {
        $('#CITY').empty();
        $('#CITY').append($('<option></option>').val('').text('select'));
    }

    function ChangeCity() {
        var selectedCity = $.trim($('#CITY option:selected').val());
        if ($.trim(selectedCity.length) > 0) {
            ChangeArea(selectedCity);
        } else {
            SetareaEmpty()
        }
    }

    function ChangeArea(selectedCity) {
        $.ajax({
            type: "POST",
            url: '@Url.Action("GetSecondDDL", "Getarea")',
            dataType: "json",
            data: { cityName: selectedCity },
            success: function (mems) {
                if (mems.length > 0) {
                    $('#AREA').empty();
                    $('#AREA').append($('<option></option>').val('').text('select'));
                }
                $.each(mems, function (i, member) {
                    $("#AREA").append($('<option></option>').val(member).text(member));
                });
            }
        });
    }

    // 搜索方法：初始化分页参数，加载第一页数据
    function SearchallData() {
        currentCity = $('#CITY option:selected').val();
        currentArea = $('#AREA option:selected').val();
        currentPage = 1;
        if ($.trim(currentArea).length > 0) {
            loadShopData();
        }
    }

    // 通用数据加载方法，支持分页
    function loadShopData() {
        $.ajax({
            url: '@Url.Action("GetShopmap", "Getarea")',
            data: { 
                cityName: currentCity, 
                areaName: currentArea,
                pageIndex: currentPage,
                pageSize: pageSize
            },
            type: 'POST',
            dataType: 'json',
            success: function (result) {
                $('#Mytable>tbody').empty();
                // 渲染表格数据
                for (var i = 0; i < result.Data.length; i++) {
                    var row = $('<tr><td>' + result.Data[i].ID + '</td><td>' + result.Data[i].Name + '</td><td>' + result.Data[i].Address + '</td><td>' + result.Data[i].Phone + '</td></tr>');
                    $('#Mytable>tbody').append(row);
                }
                $('#Mytable').show();

                // 控制分页控件显示
                if (result.TotalCount > pageSize) {
                    $("#paginationContainer").show();
                    $("#totalCount").val(result.TotalCount);
                    // 更新页码信息
                    var totalPages = Math.ceil(result.TotalCount / pageSize);
                    $("#pageInfo").text('第 ' + currentPage + ' 页 / 共 ' + totalPages + ' 页');
                    // 禁用/启用分页按钮
                    $("#prevPage").prop('disabled', currentPage === 1);
                    $("#nextPage").prop('disabled', currentPage === totalPages);
                } else {
                    $("#paginationContainer").hide();
                }
            },
            error: function (jqXHR, textStatus, errorThrown) {
                alert('Error: ' + textStatus + ' - ' + errorThrown);
            }
        });
    }
</script>

性能优化提示 #

如果数据库中数据量较大，建议直接在GetDBdata方法中编写带分页的SQL语句（使用OFFSET ... FETCH NEXT或ROW_NUMBER()），避免将所有数据加载到内存后再筛选分页，提升系统性能。

内容的提问来源于stack exchange，提问作者鍾佩妤