public static Course Find(int id)
{
SqlConnection conn = DB.Connection();
SqlDataReader rdr = null;
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM courses WHERE id = @CourseId;", conn);
SqlParameter courseIdParameter = new SqlParameter();
courseIdParameter.ParameterName = "@CourseId";
courseIdParameter.Value = id.ToString();
cmd.Parameters.Add(courseIdParameter);
rdr = cmd.ExecuteReader();
int foundCourseId = 0;
string foundCourseName = null;
int foundCourseDepartmentID = 0;
while(rdr.Read())
{
foundCourseName = rdr.GetString(0);
foundCourseDepartmentID = rdr.GetInt32(1);
foundCourseId = rdr.GetInt32(2);
}
Course foundCourse = new Course(foundCourseName, foundCourseDepartmentID, foundCourseId);
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return foundCourse;
}
public void Test_Equal_ReturnsTrueIfNamesAndEnrollmentDatesAreTheSame()
{
//Arrange, Act
Course firstCourse = new Course("CS101", 1);
Course secondCourse = new Course("CS101", 1);
//Assert
Assert.Equal(firstCourse, secondCourse);
}
public void Test_Find_FindsCourseInDatabase()
{
//Arrange
Course testCourse = new Course("CS101", 1);
testCourse.Save();
//Act
Course foundCourse = Course.Find(testCourse.GetId());
//Assert
Assert.Equal(testCourse, foundCourse);
}
public void Test_AddCourse_DisplaysAddedCourses()
{
Student testStudent2 = new Student("Bob", enrollmentDate);
testStudent2.Save();
Course testCourse = new Course("CS101", 1);
testCourse.Save();
testStudent2.AddCourse(testCourse.GetId());
List<Course> resultList = testStudent2.GetCourses();
List<Course> expectedList= new List<Course>{testCourse};
Assert.Equal(expectedList, resultList);
}
public void Test_Save_SavesToDatabase()
{
//Arrange
Course testCourse = new Course("CS101", 1);
//Act
testCourse.Save();
List<Course> result = Course.GetAll();
List<Course> testList = new List<Course>{testCourse};
//Assert
Assert.Equal(testList, result);
}
public void Test_Save_AssignsIdToObject()
{
//Arrange
Course testCourse = new Course("CS101", 1);
//Act
testCourse.Save();
Course savedCourse = Course.GetAll()[0];
int result = savedCourse.GetId();
int testId = testCourse.GetId();
//Assert
Assert.Equal(testId, result);
}
public void Test_DropCourse_DropsSelectedCourse()
{
Student testStudent = new Student("Bob", enrollmentDate);
testStudent.Save();
Course testCourse1 = new Course("CS101", 1);
testCourse1.Save();
testStudent.AddCourse(testCourse1.GetId());
Course testCourse2 = new Course("PHIL101", 2);
testCourse2.Save();
testStudent.AddCourse(testCourse2.GetId());
testStudent.DropCourse(testCourse1.GetId());
List<Course> resultList = testStudent.GetCourses();
List<Course> expectedList= new List<Course>{testCourse2};
Assert.Equal(expectedList, resultList);
}
public List<Course> GetCourses()
{
SqlConnection conn = DB.Connection();
SqlDataReader rdr;
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT courses.* FROM students JOIN class_enrollment ON (students.id = class_enrollment.student_id) JOIN courses ON (class_enrollment.course_id = courses.id) WHERE students.id = @StudentId;", conn);
SqlParameter idParameter = new SqlParameter();
idParameter.ParameterName = "@StudentId";
idParameter.Value = _id;
cmd.Parameters.Add(idParameter);
rdr = cmd.ExecuteReader();
List<Course> foundCourses = new List<Course>{};
string foundCourseName = null;
int foundCourseDeparmentId = 0;
int foundCourseId = 0;
while(rdr.Read())
{
foundCourseName = rdr.GetString(0);
foundCourseDeparmentId = rdr.GetInt32(1);
foundCourseId = rdr.GetInt32(2);
Course foundCourse = new Course(foundCourseName, foundCourseDeparmentId, foundCourseId);
foundCourses.Add(foundCourse);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return foundCourses;
}
public static List<Course> GetAll()
{
List<Course> allCourses = new List<Course> {};
SqlConnection conn = DB.Connection();
SqlDataReader rdr = null;
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM courses;", conn);
rdr = cmd.ExecuteReader();
while(rdr.Read())
{
string courseName = rdr.GetString(0);
int courseDate = rdr.GetInt32(1);
int courseId = rdr.GetInt32(2);
Course newCourse = new Course(courseName, courseDate, courseId);
allCourses.Add(newCourse);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return allCourses;
}
