ASP.NET MVC无需SQL函数实现学生成绩行转列视图展示咨询
2026-10-3
问题描述
我在SQL Server中有一张存储学生明细数据的表，结构如下：
我使用MVC框架拉取数据，需要在视图中按指定格式展示数据，当前已编写的Model代码如下：
// Students.cs using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace Vegam.Models { public class Students { public int studentId { get; set; } public string studentName { get; set; } public string subject { get; set; } public int marks { get; set; } public List<Students> studentInfo { get; set; } } }
我在控制器中创建了对象访问数据库，代码如下：
// StudentController.cs using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using Vegam.Models; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace Vegam.Controllers { public class StudentController : Controller { // GET: Student public ActionResult Index(Students students) { string connection = ConfigurationManager.ConnectionStrings["StudentsConnection"].ConnectionString; SqlConnection sqlConnection = new SqlConnection(connection); string query = "select * from [dbo].[Student]"; SqlCommand sqlCommand = new SqlCommand(query); sqlCommand.Connection = sqlConnection; sqlConnection.Open(); SqlDataReader sdr = sqlCommand.ExecuteReader(); List<Students> studentsModel = new List<Students>(); if(sdr.HasRows) { while(sdr.Read()) { var studentDetails = new Students(); studentDetails.studentId = (int) sdr["FStudentID"]; studentDetails.studentName = sdr["FStudentName"].ToString(); studentDetails.subject = sdr["FSubject"].ToString(); studentDetails.marks = (int) sdr["FMarks"]; studentsModel.Add(studentDetails); } students.studentInfo = studentsModel; sqlConnection.Close(); } return View("Index", students); } } }
我需要在视图中输出的目标表格格式如下：
当前已编写的视图代码如下：
// Index.cshtml @model Vegam.Models.Students @{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Index</title> </head> <body> <center> <h1>Answer 1</h1> @if(Model != null) { if(Model.studentInfo.Count > 0) { <table> <tr> <th>Student Name</th> <th>EC1</th> <th>EC2</th> <th>EC3</th> <th>EC4</th> <th>EC5</th> <th>Total</th> </tr> @foreach(var item in Model.studentInfo) { <tr> <td>@Html.DisplayFor(m => item.studentName)</td> </tr> } </table> } } </center> </body> </html>
目前我遇到的问题是：现有代码会输出重复的学生姓名，不知道剩余的<td>元素如何编写。我不能使用SQL函数或DataTable过滤数据，仅允许使用自定义对象，请问应该在视图层过滤还是在控制器层创建单独对象处理？
更新
我采纳了网友Caius Jard的建议，更新后的控制器和视图代码如下：
public ActionResult Index(Students students) { using (var c = new SqlConnection(ConfigurationManager.ConnectionStrings["StudentsConnection"].ConnectionString)) { var ss = c.Query<Students>("select * from student"); var d = new Dictionary<int, StudentViewModel>(); foreach (var s in ss) { StudentViewModel svm; if (!d.TryGetValue(s.FStudentID, out svm)){ d[s.FStudentID] = svm = new StudentViewModel(); svm.Name = s.FStudentName; } if (s.FSubject == "EC1") svm.EC1 = s.FMarks; else if (s.FSubject == "EC2") svm.EC2 = s.FMarks; else if (s.FSubject == "EC3") svm.EC3 = s.FMarks; else if (s.FSubject == "EC4") svm.EC4 = s.FMarks; else if (s.FSubject == "EC5") svm.EC5 = s.FMarks; } return View("Index", d.Values); } }
@model Vegam.Models.StudentViewModel @{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Index</title> </head> <body> <center> <h1>Answer 1</h1> @if (Model != null) { <table> <tr> <th>Student Name</th> <th>EC1</th> <th>EC2</th> <th>EC3</th> <th>EC4</th> <th>EC5</th> <th>Total</th> </tr> <tr> <td>@Model.Name</td> </tr> </table> } </center> </body> </html>
请问该方案是否可以优化，是否有更合理的实现方式？
方案优化建议
行转列的逻辑要放在控制器层处理，不要放到视图里，MVC的设计原则就是视图仅负责渲染逻辑，避免复杂数据处理代码耦合在视图中，后续维护更方便。
你当前的改造方案逻辑是正确的，还有以下几个可优化的点：
- 补全
StudentViewModel的属性，新增自动计算的总分属性，不需要手动赋值，后续修改统计规则只需要改这一处即可：
public class StudentViewModel { public string Name { get; set; } public int EC1 { get; set; } public int EC2 { get; set; } public int EC3 { get; set; } public int EC4 { get; set; } public int EC5 { get; set; } // 自动计算总分 public int Total => EC1 + EC2 + EC3 + EC4 + EC5; }
- 用Linq的GroupBy代替手动维护Dictionary做行转列，代码更简洁可读性更高：
public ActionResult Index() { using (var c = new SqlConnection(ConfigurationManager.ConnectionStrings["StudentsConnection"].ConnectionString)) { var rawData = c.Query<Students>("select * from student").ToList(); var viewData = rawData .GroupBy(s => new { s.studentId, s.studentName }) .Select(g => new StudentViewModel { Name = g.Key.studentName, EC1 = g.FirstOrDefault(x => x.subject == "EC1")?.marks ?? 0, EC2 = g.FirstOrDefault(x => x.subject == "EC2")?.marks ?? 0, EC3 = g.FirstOrDefault(x => x.subject == "EC3")?.marks ?? 0, EC4 = g.FirstOrDefault(x => x.subject == "EC4")?.marks ?? 0, EC5 = g.FirstOrDefault(x => x.subject == "EC5")?.marks ?? 0 }).ToList(); return View("Index", viewData); } }
- 修正视图的Model类型声明，你控制器返回的是
StudentViewModel的集合，视图要对应声明为IEnumerable类型，再循环渲染所有学生行：
@model IEnumerable<Vegam.Models.StudentViewModel> @{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Index</title> </head> <body> <center> <h1>Answer 1</h1> @if (Model != null && Model.Any()) { <table border="1"> <tr> <th>Student Name</th> <th>EC1</th> <th>EC2</th> <th>EC3</th> <th>EC4</th> <th>EC5</th> <th>Total</th> </tr> @foreach(var student in Model) { <tr> <td>@student.Name</td> <td>@student.EC1</td> <td>@student.EC2</td> <td>@student.EC3</td> <td>@student.EC4</td> <td>@student.EC5</td> <td>@student.Total</td> </tr> } </table> } </center> </body> </html>
- 原始
Students实体类里的studentInfo属性属于冗余字段，你现在的逻辑已经不需要用到，可以删除减少冗余。
内容的提问来源于stack exchange，提问作者Sh4dy
相关产品推荐
相关产品推荐
