public ActionResult Index(CourseOfferingViewModel viewModel)
{
try
{
// Populate
viewModel = new CourseOfferingViewModelFactory(viewModel, _courseService).GetViewModel();
// Validate
if (!ModelState.IsValid)
return View(viewModel);
// Bind
var courseOffering = new CourseOffering
{
CourseId = viewModel.SelectedCourse,
Semester = viewModel.Semester,
Year = viewModel.SelectedYear
};
// Save
_courseService.AddCourseOffering(courseOffering);
// Respond
Success("The Course Offering has been created", true);
}
catch (CourseOfferingExistsException)
{
Danger("Course Offering already exists.");
return View(viewModel);
}
// redirect back to the page index
return RedirectToAction("Index");
}
public void AddCourseOffering(CourseOffering offering)
{
// If a course offering already exists, throw custom exception
if (_courseOfferingRepository.CourseOfferingExists(offering))
throw new CourseOfferingExistsException();
// Insert the course offering
_courseOfferingRepository.InsertCourseOffering(offering);
}
/// <summary>
/// Adds the course offering.
/// </summary>
/// <param name="courseOffering">The course offering.</param>
public void AddCourseOffering(CourseOffering courseOffering)
{
using (_courseOfferingRepository)
{
if (_courseOfferingRepository.HasCourseOffering(courseOffering))
throw new CourseOfferingExistsException();
_courseOfferingRepository.InsertCourseOffering(courseOffering);
}
}
public List<CourseOffering> GetCourseOfferings()
{
var offerings = new List<CourseOffering>();
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
// Open a connection
conn.Open();
// Build SQL query
cmd.CommandText = "SELECT * from CourseOffering INNER JOIN Course ON CourseOffering.Course_CourseID = Course.CourseID";
// Create DataReader for storing the returning table into memory
var dataReader = cmd.ExecuteReader();
// Check if the CourseOffering table has records
if (dataReader.HasRows)
{
// Iterate through each record
while (dataReader.Read())
{
// Extract the course offering fields
// Extract the course fields
var number = dataReader["CourseID"].ToString();
var name = dataReader["CourseTitle"].ToString();
var weeklyHours = Convert.ToInt16(dataReader["HoursPerWeek"]);
var year = Convert.ToInt16(dataReader["Year"]);
var semester = dataReader["Semester"].ToString();
// Build the course object
var course = new Course(number, name, weeklyHours);
// Build the course offering object
var offering = new CourseOffering(course, semester, year);
// Append to the course offering list
offerings.Add(offering);
}
}
// Close the DataReader
dataReader.Close();
// Execute the SELECT operation
cmd.ExecuteNonQuery();
}
return offerings;
}
public void InsertCourseOffering(CourseOffering courseOffering)
{
using (var dbTransaction = _db.Database.BeginTransaction())
{
try
{
_db.CourseOfferings.Add(courseOffering);
_db.SaveChanges();
dbTransaction.Commit();
}
catch (Exception)
{
dbTransaction.Rollback();
}
}
}
public void InsertCourseOffering(CourseOffering courseOffering)
{
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
// Open a connection
conn.Open();
// Build SQL query
cmd.CommandText = "INSERT INTO CourseOffering (Year, Semester, Course_CourseID) VALUES (@year, @Semester, @courseID)";
// Insert parameters into the course offering table
cmd.Parameters.AddWithValue("@year", courseOffering.Year);
cmd.Parameters.AddWithValue("@Semester", courseOffering.Semester);
cmd.Parameters.AddWithValue("@courseID", courseOffering.CourseOffered.Number);
// Perform the INSERT operation
cmd.ExecuteNonQuery();
}
}
public void AddStudent(Student student, CourseOffering offering)
{
// If the student does not yet exist
if (!_studentRepository.StudentExists(student.Number))
{
// Insert the student into the DB
_studentRepository.InsertStudent(student);
// Register the student to the course offering
_studentRepository.RegisterStudent(student, offering);
}
else
{
// Check if student is registered in offering
var studentExists = _studentRepository.GetStudentsByOffering(offering).Find(s => s.Number == student.Number);
// If student is not registered
if (studentExists == null)
// Register the student
_studentRepository.RegisterStudent(student, offering);
}
}
public void RegisterStudent(Student student, CourseOffering offering)
{
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
// Open a connection
conn.Open();
// Build SQL query
cmd.CommandText = "INSERT INTO Registration "
+ "(Student_StudentNum, CourseOffering_Course_CourseID, CourseOffering_Year, CourseOffering_Semester) "
+ "VALUES (@studentNum, @courseID, @year, @semester)";
// Insert parameters into the course table
cmd.Parameters.AddWithValue("@studentNum", student.Number);
cmd.Parameters.AddWithValue("@courseID", offering.CourseOffered.Number);
cmd.Parameters.AddWithValue("@year", offering.Year);
cmd.Parameters.AddWithValue("@semester", offering.Semester);
// Perform the INSERT operation
cmd.ExecuteNonQuery();
}
}
public ActionResult Add(CourseOfferingViewModel input)
{
// Populate VM
var viewModel = PopulateViewModel(input);
// Validate model state
if (!ModelState.IsValid)
return View(viewModel);
// Attempt to add the offering
try
{
var offering = new CourseOffering(_courseService.GetCourseById(input.SelectedCourseId), input.Semester.ToString(), input.SelectedYear);
_courseService.AddCourseOffering(offering);
input.CourseOfferings.Add(offering);
}
catch (CourseOfferingExistsException)
{
// If the offering already exists, display error
TempData["Error"] = "Course offering already exists!";
}
return RedirectToAction("Add", input);
}
public List<Student> GetAllStudentsByOffering(CourseOffering offering) => _studentRepository.GetStudentsByOffering(offering);
public int CompareTo(CourseOffering other) => other == null ? 1 : Year.CompareTo(other.Year);
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;
}
public CourseOffering GetCourseOffering(string id, int year, string semester)
{
CourseOffering offering = null;
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
// Open a connection
conn.Open();
// Build SQL query
cmd.CommandText = "SELECT * from CourseOffering INNER JOIN Course ON CourseOffering.Course_CourseID = Course.CourseID WHERE Course_CourseID = @courseID AND Year = @year AND Semester = @semester";
// Set the course ID
cmd.Parameters.AddWithValue("@courseID", id);
cmd.Parameters.AddWithValue("@year", year);
cmd.Parameters.AddWithValue("@semester", semester);
// Create DataReader for storing the returning table into memory
var dataReader = cmd.ExecuteReader();
// Check if the CourseOffering table has records
if (dataReader.HasRows)
{
// Iterate through each record
while (dataReader.Read())
{
// Extract the course fields
var number = dataReader["CourseID"].ToString();
var name = dataReader["CourseTitle"].ToString();
var weeklyHours = Convert.ToInt16(dataReader["HoursPerWeek"]);
// Build the course object
var course = new Course(number, name, weeklyHours);
// Build the course offering object
offering = new CourseOffering(course, semester, year);
}
}
// Close the DataReader
dataReader.Close();
// Execute the SELECT operation
cmd.ExecuteNonQuery();
}
return offering;
}
public bool CourseOfferingExists(CourseOffering offering)
{
var exists = false;
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
// Open a connection
conn.Open();
// Build SQL query
cmd.CommandText = "SELECT * from CourseOffering WHERE Course_CourseID = @courseID AND Semester = @semester AND Year = @year";
// Set the course ID
cmd.Parameters.AddWithValue("@courseID", offering.CourseOffered.Number);
cmd.Parameters.AddWithValue("@semester", offering.Semester);
cmd.Parameters.AddWithValue("@year", offering.Year);
// Create DataReader for storing the returning table into memory
var dataReader = cmd.ExecuteReader();
exists = dataReader.HasRows;
// Close the DataReader
dataReader.Close();
// Execute the SELECT operation
cmd.ExecuteNonQuery();
}
return exists;
}
/// <summary>
/// Check if a Course Offering exists
/// </summary>
/// <param name="courseOffering">The course offering.</param>
/// <returns></returns>
public bool HasCourseOffering(CourseOffering courseOffering) => _db.CourseOfferings.Any(
offering =>
offering.Semester == courseOffering.Semester && offering.Year == courseOffering.Year &&
offering.CourseId == courseOffering.CourseId);
