I have a table of student details in SQL Server that looks like this:
I am using an MVC framework to get the data and display it in a certain way in the view.
This is my Model code:
// 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; }
}
}
I have created an Object in my controller to access the data:
// 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);
}
}
}
In my view, I want to return the data in this table format:
Till now I have come up with this code:
// 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>
I don't know how to write the rest of the <td> elements. Whatever I have written returns all the Student Names, including duplicates. Should I filter in the view or create separate objects in the controller? I can't use SQL functions or data tables to filter the data, only custom objects.
UPDATE
I took https://stackoverflow.com/users/1410664/caius-jard's advice and this is my new controller code and view code:-
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>
CodePudding user response：
Done in the db, your pivot looks something like:
SELECT FStudentName, ec1, ec2, ec3, ec4, ec5, ec1 ec2 ec3 ec4 ec5 as Tot,
FROM student
PIVOT(
SUM(FMarks)
FOR FSubject IN (ec1, ec2, ec3, ec4, ec5)
) x
Done in C# it might look something like:
using var c = new SqlConnection("conn str here");
//use Dapper; life is too short to spend it writing mind numbing
//while reader.read someobject.someproperty = reader.getxxx("somecolumn")
//adjust your Students class so it is called Student
//and has properties whose names match the db columns
var ss = c.Query<Student>("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);
You'll need a class StudentViewModel, with a name and 5 properties EC1 to EC5. Mostly Visual Studio will write it form you; just point to every wiggly line and accept the relevant recommendations like "Generate class StudentViewModel", "Add member.. create property EC1" etc. It will get all the types from what it knows about the Student class. The Student class is a database side thing; your view shouldn't use it. Student's properties should map to the db column names which makes life a lot easier; install Dapper (right click the project references, manage nuget packages, add Dapper. Dapper does all the querying of the db, reading the results and populating a Student instance for you
I know you said you want to do the pivot in c#, but I threw the db version in there mainly as a highlight of just how much easier it is to do it db side.. it also results in a lot less data being pulled over a network from db to server. When you write busy apps you have to make every optimization you can. There are plenty of other natty ways to pivot data but mainly I was aiming to stay away from LINQ and overly complex data structures, and give something you can hopefully follow. The main idea of a pivot is you have a list of repeated values (student id). The first time you encounter it it is not in your dictionary, so TryGetValue returns false. This is the cue to create the StudentViewModel that tracks the properties and add it to the dictionary so that next time we see the same if we are putting new data (a different mark/subject) into the already created instance. This way it doesn't matter the order of the data; by the end of the result set you'll have a dictionary of unique student id and each one StudentViewModel has up to 5 properties filled in. Your 10 row result set from the db has become 2 objects with 5 properties. All your View has to do is put the td 5 tines, one for each Mark, and you access each different ECx respectively
There is one thing C# can do that isn't so easy in the db; dynamic number of columns in the pivot. For that you would have your marks stored in a dictionary, that maps the subject name to the mark. You'd create a superset of all subject names and then when laying out your view TryGetValue for each one and out the mark if it was there. It's considerably more complex; maybe get down with the simple version presented here before heading into making a dynamic pivot