public ActionResult Add(StudentViewModel input)
{
// Populate VM
var viewModel = PopulateViewModel(input);
// Validate model state
if (!ModelState.IsValid) return View(input);
// Attempt to add the student to the database/course offering
try
{
// Get the created student from the factory
var studentFactory = new StudentFactory();
var student = studentFactory.CreateStudent(input.Number, input.Name, input.Type);
// Get the course offering
var offering = _courseService.GetCourseOffering(input.SelectedCourseId, input.SelectedYear, input.SelectedSemester);
// Add the student
_courseService.AddStudent(student, offering);
}
catch (Exception)
{
TempData["Error"] = "Something went wrong";
}
return RedirectToAction("Add", viewModel);
}
public List<Student> GetStudentsByOffering(CourseOffering offering)
{
var students = new List<Student>();
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
// Open a connection
conn.Open();
// Build SQL query
cmd.CommandText = "SELECT s.StudentNum, s.Name, s.Type FROM Student s "
+ "JOIN Registration r ON s.StudentNum = r.Student_StudentNum "
+ "WHERE r.CourseOffering_Course_CourseID=@courseID "
+ " AND r.CourseOffering_Year = @year "
+ " AND r.CourseOffering_Semester = @Semester";
cmd.Parameters.AddWithValue("@courseID", offering.CourseOffered.Number);
cmd.Parameters.AddWithValue("@year", offering.Year);
cmd.Parameters.AddWithValue("@Semester", offering.Semester);
// Create DataReader for storing the returning table into memory
var dataReader = cmd.ExecuteReader();
// If records exist
if (dataReader.HasRows)
{
// Iterate through each record
while (dataReader.Read())
{
// Extract the course fields
var number = Convert.ToInt16(dataReader["StudentNum"]);
var name = dataReader["Name"].ToString();
var type = dataReader["Type"].ToString();
// Get the created student from the factory
var studentFactory = new StudentFactory();
var student = studentFactory.CreateStudent(number, name, type);
// Append to the course list
students.Add(student);
}
}
// Close the DataReader
dataReader.Close();
// Execute the SELECT operation
cmd.ExecuteNonQuery();
}
return students;
}