public CourseOffering(Course courseOffered, string semester, int year)
{
CourseOffered = courseOffered;
Semester = semester;
Students = new List<Student>();
Year = year;
}
/// <summary>
/// Adds the course.
/// </summary>
/// <param name="course">The course.</param>
/// <exception cref="CourseNumberNotUniqueException"></exception>
public void AddCourse(Course course)
{
using (_courseRepository)
{
// Check Unique on Name
if (_courseRepository.CourseNumberExists(course.CourseNumber))
throw new CourseNumberNotUniqueException();
if (_courseRepository.CourseNameExists(course.CourseName))
throw new CourseNameNotUniqueException();
_courseRepository.AddCourse(course);
}
}
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;
}
/// <summary>
/// Adds the course.
/// </summary>
/// <param name="course">The course.</param>
public void AddCourse(Course course)
{
using (var dbTransaction = _db.Database.BeginTransaction())
{
try
{
_db.Courses.Add(course);
_db.SaveChanges();
dbTransaction.Commit();
}
catch (Exception)
{
dbTransaction.Rollback();
}
}
}
public Course GetCourse(string id)
{
Course course = null;
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
// Open a connection
conn.Open();
// Build SQL query
cmd.CommandText = "SELECT * FROM Course WHERE CourseID = @courseID";
// Set the course ID
cmd.Parameters.AddWithValue("@courseID", id);
// Create DataReader for storing the returning table into memory
var dataReader = cmd.ExecuteReader();
// Check if the Course 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"]);
// Return the matched course
course = new Course(number, name, weeklyHours);
}
}
// Close the DataReader
dataReader.Close();
// Execute the SELECT operation
cmd.ExecuteNonQuery();
}
return course;
}
public void InsertCourse(Course course)
{
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
// Open a connection
conn.Open();
// Build SQL query
cmd.CommandText = "INSERT INTO Course (CourseID, CourseTitle, HoursPerWeek) VALUES (@courseID, @courseTitle, @courseHours)";
// Insert parameters into the course table
cmd.Parameters.AddWithValue("@courseID", course.Number);
cmd.Parameters.AddWithValue("@courseTitle", course.Name);
cmd.Parameters.AddWithValue("@courseHours", course.WeeklyHours);
// Perform the INSERT operation
cmd.ExecuteNonQuery();
}
}
public void AddCourse(Course course) => _courseRepository.InsertCourse(course);
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 List<Course> GetCourses()
{
var courses = new List<Course>();
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
// Open a connection
conn.Open();
// Build SQL query
cmd.CommandText = "SELECT * FROM Course";
// Create DataReader for storing the returning table into memory
var dataReader = cmd.ExecuteReader();
// Check if the Course 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);
// Append to the course list
courses.Add(course);
}
}
// Close the DataReader
dataReader.Close();
// Execute the SELECT operation
cmd.ExecuteNonQuery();
}
return courses;
}