C# ASP.NET Core MVC应用中Show视图无法获取数据问题

我正在开发一个基于学校数据库的CMS系统，List视图页面可正常显示教师列表。当用户点击某一教师时，会跳转至Show视图页面，该页面应展示对应教师的详细信息。目前问题是List页面能正常获取并展示数据，但Show页面无法获取数据，且无任何报错，仅数据未显示。

相关代码 #

List.cshtml #

@model IEnumerable<Cumulative.Models.Teacher>

<h1>This page shows the list of all the teachers.</h1>

@foreach(Cumulative.Models.Teacher CurrentTeacher in Model)
{
    <div><a href="/TeacherPage/Show/@CurrentTeacher.TeacherId">@CurrentTeacher.TeacherFName @CurrentTeacher.TeacherLName</a></div>
}

Show.cshtml #

@model Cumulative.Models.Teacher
<h1>@Model.TeacherFName @Model.TeacherLName</h1>

TeacherPageController.cs #

using Microsoft.AspNetCore.Mvc;
using Cumulative.Models;
using Cumulative.Controllers;
using Google.Protobuf.WellKnownTypes;

namespace Cumulative.Controllers
{
    public class TeacherPageController : Controller
    {
        private readonly TeacherAPIController _api;

        public TeacherPageController(TeacherAPIController api)
        {
            _api = api;
        }

        // TeacherPage/List -> A webpage that shows all teachers in the database
        public IActionResult List()
        {
            // creating a variable for the list of teachers
            List<Teacher> Teachers = _api.ListTeachers();

            // direct us to the /Views/TeacherPage/List.cshtml
            return View(Teachers);
        }

        // GET : TeacherPage/Show/{id} -> A webpage that shows a particular teacher in the database matching the given id
        [HttpGet]
        public IActionResult Show(int TeacherId)
        {
            // Creating a Teacher instance for the teacher information received upon inserting the id
            Teacher SelectedTeacher = _api.GiveTeacherInfo(TeacherId);

            return View(SelectedTeacher);
        }
    }
}

TeacherAPIController.cs #

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Cumulative.Models;
using System;
using MySql.Data.MySqlClient;

namespace Cumulative.Controllers
{
    [Route("api/Teacher")]
    [ApiController]
    public class TeacherAPIController : ControllerBase
    {
        private readonly SchoolDbContext _context;
        // dependency injection of database context
        public TeacherAPIController(SchoolDbContext context)
        {
            _context = context;
        }

        /// <summary>
        /// Returns information about all the teachers
        /// </summary>
        /// <example>
        /// GET api/teacher/GiveTeachersList -> ["","","","",""]
        /// </example>
        /// <returns>
        /// A list of the teachers 
        /// </returns>
        /// 
        [HttpGet]
        [Route(template:"ListTeachers")]
        public List<Teacher> ListTeachers()
        {
            // Create a list of instances of Teachers
            List<Teacher> Teachers = new List<Teacher>();

            // 'using' will close the connection after the code executes
            using (MySqlConnection Connection = _context.AccessDatabase())
            {
                Connection.Open();
                // Establish a new command (query) for our database
                MySqlCommand Command = Connection.CreateCommand();

                //SQL query
                Command.CommandText = "SELECT * FROM teachers";

                // Gather Result Set of Query into a variable
                using (MySqlDataReader ResultSet = Command.ExecuteReader())
                {
                    //Loop Through Each Row of the Result Set
                    while (ResultSet.Read())
                    {
                        int Id = Convert.ToInt32(ResultSet["teacherid"]);
                        string LastName = ResultSet["teacherlname"].ToString();
                        string EmployeeNumber = ResultSet["employeenumber"].ToString();
                        DateTime HireDate = Convert.ToDateTime(ResultSet["hiredate"]);
                        Decimal Salary = Convert.ToDecimal(ResultSet["salary"]);

                        // Add the Teacher Data to the Teachers

                        Teacher CurrentTeacher = new Teacher()
                        {
                            TeacherId = Id,
                            TeacherFName = ResultSet["teacherfname"].ToString(),
                            TeacherLName = LastName,
                            EmployeeNumber = EmployeeNumber,
                            HireDate = HireDate,
                            Salary = Salary
                        };

                        Teachers.Add(CurrentTeacher);
                    }
                }
            }

            return Teachers;
        }

        /// <summary>
        /// Receives a teacher id and returns the associated teacher information
        /// </summary>
        /// <param name="TeacherId">The primary key of the teachers table.</param>
        /// <example>
        /// GET api/teacher/GiveTeacherInfo/5 -> {"":"","":"","":""} 
        /// </example>
        /// <returns>
        /// All the information about one teacher
        /// </returns>
      
        [HttpGet]
        [Route(template:"GiveTeacherInfo/{TeacherId}")]
        public Teacher GiveTeacherInfo(int TeacherId)
        {
            Teacher SelectedTeacher = new Teacher();

            // 'using' will close the connection after the code executes
            using (MySqlConnection Connection = _context.AccessDatabase())
            {
                Connection.Open();
                // Establish a new command (query) for our database
                MySqlCommand Command = Connection.CreateCommand();

                // SQL query
                Command.CommandText = $"SELECT * FROM teachers WHERE teacherid = {TeacherId}";

                // Gather Result Set of Query into the Teacher 
                using (MySqlDataReader ResultSet = Command.ExecuteReader())
                {
                    //Loop Through Each Row of the Result Set
                    while (ResultSet.Read())
                    {
                        SelectedTeacher.TeacherId = Convert.ToInt32(ResultSet["teacherid"]);
                        SelectedTeacher.TeacherFName = ResultSet["teacherfname"].ToString();
                        SelectedTeacher.TeacherLName = ResultSet["teacherlname"].ToString();
                        SelectedTeacher.EmployeeNumber = ResultSet["employeenumber"].ToString();
                        SelectedTeacher.HireDate = Convert.ToDateTime(ResultSet["hiredate"]);
                        SelectedTeacher.Salary = Convert.ToDecimal(ResultSet["salary"]);

                    }
                }
            }

            return SelectedTeacher;
        }
    }
}

Teacher.cs #

namespace Cumulative.Models
{
    public class Teacher
    {
        public int TeacherId { get; set; }

        public string TeacherFName { get; set; }
        public string TeacherLName { get; set; }
        public string EmployeeNumber { get; set; }

        public DateTime HireDate { get; set; }

        public Decimal Salary { get; set; }
    }
}

核心问题1：SQL注入风险与查询逻辑缺陷 #

TeacherAPIController的GiveTeacherInfo方法中，直接用字符串拼接构造SQL语句，不仅存在SQL注入风险，还可能因参数类型不匹配导致查询无结果。

修复方案：使用参数化查询替代字符串拼接：

// 替换原SQL拼接代码
Command.CommandText = "SELECT * FROM teachers WHERE teacherid = @TeacherId";
Command.Parameters.AddWithValue("@TeacherId", TeacherId);

核心问题2：空数据处理缺失 #

当查询不到对应ID的教师时，GiveTeacherInfo会返回一个默认初始化的Teacher对象（字符串属性为null、数值属性为0），视图直接访问这些属性时只会显示空白，无任何提示。

修复方案：

1. 在GiveTeacherInfo中检查查询结果，无匹配数据时返回null：

using (MySqlDataReader ResultSet = Command.ExecuteReader())
{
    if (ResultSet.Read())
    {
        SelectedTeacher.TeacherId = Convert.ToInt32(ResultSet["teacherid"]);
        SelectedTeacher.TeacherFName = ResultSet["teacherfname"].ToString();
        SelectedTeacher.TeacherLName = ResultSet["teacherlname"].ToString();
        SelectedTeacher.EmployeeNumber = ResultSet["employeenumber"].ToString();
        SelectedTeacher.HireDate = Convert.ToDateTime(ResultSet["hiredate"]);
        SelectedTeacher.Salary = Convert.ToDecimal(ResultSet["salary"]);
    }
    else
    {
        return null;
    }
}

2. 在TeacherPageController的Show方法中处理null情况：

public IActionResult Show(int TeacherId)
{
    Teacher SelectedTeacher = _api.GiveTeacherInfo(TeacherId);
    if (SelectedTeacher == null)
    {
        return NotFound("未找到对应教师信息");
    }
    return View(SelectedTeacher);
}

3. 在Show.cshtml中添加空值判断：

@model Cumulative.Models.Teacher
@if (Model != null)
{
    <h1>@Model.TeacherFName @Model.TeacherLName</h1>
}
else
{
    <h1>未找到教师信息</h1>
}

额外优化：分层设计问题 #

TeacherPageController直接注入TeacherAPIController不符合MVC分层原则，建议将数据访问逻辑抽离为独立的服务层（如TeacherService），让页面控制器和API控制器都依赖该服务，降低耦合度。

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