javascript - 如何在mvc 5中基于静态下拉列表过滤数据库记录

Question

我想使用静态html dropdwonlist根据数据库列QtyRecieved、QtyRecievedand Void过滤数据库记录

QtyRecieved,QtyRecieved 为十进制，void 为 bool 值

这是我尝试过的

@Html.DropDownList("Filter", new List<SelectListItem>

           {
              new SelectListItem{ Text="Open", Value = "0" },
              new SelectListItem{ Text="Partial", Value = "1" },
              new SelectListItem{ Text="All", Value = "2" }
           })

我使用ajax将请求发送到 Controller

$("#Filter").change(function () {
    var listval = $("select option:selected").text();
        $.ajax({
            type: "GET",
            url: "@Url.Action("Index", "MaterialRequest")",
            data: { id: listval }
        });
    });

在我的 Controller 中，我放置了 else if 条件来显示记录我想要，当选择打开时，它会显示QtyRecieved == QtyRequested

的记录

当选择部分时，它会显示QtyRecieved < QtyRequested且Void = True的记录

和

当选择全部时，它将显示所有记录

请帮助我进行查询以过滤记录或显示任何替代方法

public ActionResult Index(string listval)
        {
            if (listval == "Open")
            {
                ViewBag.Items = db.Query<MaterialDeptItemVw>("Select mt.MaterialRequestId, mt.TDate, d.DepartmentName, it.ItemName, mt.QtyRequested, mt.Comment, mt.RecievedDateTime , u.UnitName from MaterialRequest mt INNER JOIN Department d ON mt.DepartmentId = d.DepartmentId INNER JOIN Items it ON mt.ItemId = it.ItemId INNER JOIN Units u ON it.UnitId = u.UnitId where QtyRecieved = QtyRequested");
            }
            else if (listval == "Partial")
            {
                ViewBag.Items = db.Query<MaterialDeptItemVw>("Select mt.MaterialRequestId, mt.TDate, d.DepartmentName, it.ItemName, mt.QtyRequested, mt.Comment, mt.RecievedDateTime , u.UnitName from MaterialRequest mt INNER JOIN Department d ON mt.DepartmentId = d.DepartmentId INNER JOIN Items it ON mt.ItemId = it.ItemId INNER JOIN Units u ON it.UnitId = u.UnitId where QtyRecieved < QtyRequested and Void = 0");
            }
            else if (listval == "All")
            {
                ViewBag.Items = db.Query<MaterialDeptItemVw>("Select mt.MaterialRequestId, mt.TDate, d.DepartmentName, it.ItemName, mt.QtyRequested, mt.Comment, mt.RecievedDateTime , u.UnitName from MaterialRequest mt INNER JOIN Department d ON mt.DepartmentId = d.DepartmentId INNER JOIN Items it ON mt.ItemId = it.ItemId INNER JOIN Units u ON it.UnitId = u.UnitId");
            }

            return View();
        }

Answer 1

您的代码中有几个问题:

1) 操作方法声明为 public ActionResult Index(string listval)，而在 AJAX 回调中，您有 data: { id: listval } 参数，因此 AJAX 调用从未到达 Controller 操作，因为它使用不同的参数名称进行调用。

2) return View() 在使用 AJAX 时不适用，需要返回 JSON 数据或部分 View 并从 AJAX 结果更新目标 DOM 元素。

因此，您应该将参数名称更改为与 AJAX data 参数完全匹配，并使用 return Json():

public ActionResult Index(string id)
{
    // using DRY principle, just write the same part of the query in a string variable
    // and add another part depending on case value inside switch block below
    string baseQuery = @"Select mt.MaterialRequestId, mt.TDate, 
                         d.DepartmentName, it.ItemName, 
                         mt.QtyRequested, mt.Comment, 
                         mt.RecievedDateTime , u.UnitName from MaterialRequest mt 
                         INNER JOIN Department d ON mt.DepartmentId = d.DepartmentId 
                         INNER JOIN Items it ON mt.ItemId = it.ItemId 
                         INNER JOIN Units u ON it.UnitId = u.UnitId";

    switch (id)
    {
        case "Open":
            baseQuery += " where QtyRecieved = QtyRequested";
            break;

        case "Partial":
            baseQuery += " where QtyRecieved < QtyRequested and Void = 0";
            break;

        case "All":
            break; // not doing anything

        default: goto case "All";
    }

    // create a list of object from query results
    var items = db.Query<MaterialDeptItemVw>(baseQuery).ToList();

    // return JSON data to populate target element
    return Json(items, JsonRequestBehavior.AllowGet);
}

然后修改 AJAX 调用以根据返回的数据更新目标 DOM 元素:

$("#Filter").change(function () {
    var listval = $("select option:selected").text();
    $.ajax({
        type: "GET",
        url: '@Url.Action("Index", "MaterialRequest")',
        data: { id: listval },
        success: function (result) {
           // an example to update target element
           $('#targetElementID').html(result);
        },
        error: function (xhr, status, err) {
           // error handling
        }
    });
});