ASP.NET MVC自动完成切换EmployeeId时无法显示EmployeeName

问题描述 #

开发ASP.NET MVC自动完成项目时遇到异常：在ID为txtLineManagerId的员工ID输入框（对应代码@Html.EditorFor(model => model.LineManager, new { htmlAttributes = new { @class = "form-control", id = "txtLineManagerId" } })）中选择员工ID后，ID为LineManagerName的输入框（对应代码<input type="text" id="LineManagerName" class="form-control" />）无法自动填充对应的员工姓名。

问题根源 #

1. 后端SQL查询虽然选中了EmployeeName字段，但返回的对象仅包含EmployeeID，未携带姓名数据；
2. 前端自动完成组件未监听选项选中事件，也未利用返回的姓名数据填充目标输入框。

解决方案 #

1. 修改后端代码，返回完整的员工ID和姓名数据

public ActionResult GetAllEmployeeBasedSearchText(string searchText)
{
    JDEUtility jde = new JDEUtility();
    List<object> employeeListCriteria = jde.GetAllEmployeeBasedSearchText(searchText);
    return Json(employeeListCriteria, JsonRequestBehavior.AllowGet);
}

public static List<object> GetAllEmployeeBasedSearchText(string searchText)
{
    OleDbConnection con = new OleDbConnection(connectionString);
    string query = "SELECT cast(EMP.YAAN8 as varchar(20)) as EmployeeID,EMP.YAALPH AS EmployeeName FROM CRPDTA.F060116 EMP WHERE cast(EMP.YAAN8 as varchar(20)) LIKE '%" + searchText + "%' WITH UR";

    List<object> ApplicationsDataValues = new List<object>();
    try
    {
        using (var command = con.CreateCommand())
        {
            command.CommandText = query;
            command.CommandType = CommandType.Text;

            command.Connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    ApplicationsDataValues.Add(new
                    {
                        EmployeeID = reader.GetFieldValue<string>(0),
                        // 新增：将查询到的员工姓名加入返回对象
                        EmployeeName = reader.GetFieldValue<string>(1)
                    });
                }
            }
        }
        con.Close();
        return ApplicationsDataValues;
    }
    catch (Exception e)
    {
        return new List<object>();
    }
}

2. 修改前端代码，添加选中事件监听并填充姓名

$(document).ready(function () {
    $("#txtLineManagerId").autocomplete({
        source: function (request, response) {
            // 使用request.term获取自动组件的当前输入值，更符合组件规范
            var searchText = request.term;
            console.log("search text: " + searchText);
            $.ajax({
                url: '@Url.Action("GetAllEmployeeBasedSearchText", "Resignation")',
                data: { searchText: searchText },
                method: "GET",
                dataType: "json",
                success: function (data) {
                    response($.map(data, function (item) {
                        console.log("employee data: " + item.EmployeeID + " - " + item.EmployeeName);
                        // 返回包含ID、姓名的对象，label显示ID+姓名便于用户识别，value存ID，额外携带name字段
                        return { 
                            label: item.EmployeeID + " - " + item.EmployeeName, 
                            value: item.EmployeeID,
                            name: item.EmployeeName 
                        };
                    }));
                }
            });
        },
        // 新增：选中自动完成选项时触发的事件
        select: function(event, ui) {
            // 将选中的员工姓名填充到LineManagerName输入框
            $("#LineManagerName").val(ui.item.name);
        }
    });
});

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