.NET Core Web API GetById接口返回JSON对象而非数组的实现

我正在开发员工管理的.NET Core Web API应用，通过控制器编写接口，希望GetById方法返回单个JSON对象而非数组。

当前代码 #

[HttpGet("{id}")]
public JsonResult Get(int id)
{
    string query = @"select EmployeeId,EmployeeFirstName,EmployeeLastName,EmployeeEmail,EmployeeGender,(select DepartmentName from Department where DepartmentId =Employee.Emp_DepartmentId) as DepartmentName,(select DesignationName from Designation where DesignationId=Employee.Emp_DesignationId) as DesignationName,EmployeeDob from Employee where EmployeeId = @EmployeeId ";
    DataTable table = new DataTable();
    string sqlDataSource = _configuration.GetConnectionString("Dbconn");
    SqlDataReader myReader;
    using (SqlConnection myCon = new SqlConnection(sqlDataSource))
    {
        myCon.Open();
        using (SqlCommand myCommand = new SqlCommand(query, myCon))
        {
            myCommand.Parameters.AddWithValue("@EmployeeId", id);
            myReader = myCommand.ExecuteReader();
            table.Load(myReader);
            myReader.Close();
            myCon.Close();
        }
    }
    return new JsonResult(table);
}

当前输出（JSON数组） #

[
    {
        "EmployeeId": 1,
        "EmployeeFirstName": "abcd",
        "EmployeeLastName": "abcd",
        "EmployeeEmail": "abcd@abcd.com",
        "EmployeeGender": "abcd",
        "EmployeeDoj": "1995-01-01T00:00:00",
        "DepartmentName": "abcd",
        "DesignationName": "abcd",
        "EmployeeDob": "1995-01-01T00:00:00"
    }
]

期望输出（单个JSON对象） #

{
    "EmployeeId": 1,
    "EmployeeFirstName": "abcd",
    "EmployeeLastName": "abcd",
    "EmployeeEmail": "abcd@abcd.com",
    "EmployeeGender": "abcd",
    "EmployeeDoj": "1995-01-01T00:00:00",
    "DepartmentName": "abcd",
    "DesignationName": "abcd",
    "EmployeeDob": "1995-01-01T00:00:00"
}

解决方案 #

问题根源是直接返回DataTable对象——DataTable本质是行集合，序列化后会自动转为JSON数组。只需判断表格是否有数据，返回第一行数据即可：

修改后的代码：

[HttpGet("{id}")]
public IActionResult Get(int id)
{
    string query = @"select EmployeeId,EmployeeFirstName,EmployeeLastName,EmployeeEmail,EmployeeGender,(select DepartmentName from Department where DepartmentId =Employee.Emp_DepartmentId) as DepartmentName,(select DesignationName from Designation where DesignationId=Employee.Emp_DesignationId) as DesignationName,EmployeeDob from Employee where EmployeeId = @EmployeeId ";
    DataTable table = new DataTable();
    string sqlDataSource = _configuration.GetConnectionString("Dbconn");
    
    using (SqlConnection myCon = new SqlConnection(sqlDataSource))
    {
        myCon.Open();
        using (SqlCommand myCommand = new SqlCommand(query, myCon))
        {
            myCommand.Parameters.AddWithValue("@EmployeeId", id);
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {
                table.Load(myReader);
            }
        }
    }

    if (table.Rows.Count > 0)
    {
        return new JsonResult(table.Rows[0]);
    }
    // 未找到对应员工时返回标准404状态码
    return NotFound();
}

关键调整说明 #

1. 把返回类型从JsonResult改为IActionResult，支持灵活返回JSON数据或404状态码，符合RESTful接口规范
2. 移除手动关闭myReader和myCon的代码，using语句会自动释放资源，避免资源泄漏
3. 判断DataTable是否存在数据，有数据则返回第一行（table.Rows[0]），序列化后即为单个JSON对象；无数据则返回404

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