public IEnumerable<CourseTakenModel> FindCourses(Guid student)
{
var sql = @"SELECT ct.Course, ct.Student, ct.Grade, s.FirstName, c.Name
FROM CoursesTaken ct
INNER JOIN Courses c ON ct.Course = c.Id
INNER JOIN Students s ON ct.Student = s.Id
WHERE ct.Student = @Student";
var coursesTaken = new List<CourseTakenModel>();
var parameter = new SqlParameter("@Student", student);
using (var connection = GetAndOpenConnection())
using (var command = GetCommand(sql, connection, parameter))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var courseTaken = new CourseTakenModel();
courseTaken.Student.Id = Guid.Parse(Convert.ToString(reader["Student"]));
courseTaken.Student.Name = Convert.ToString(reader["FirstName"]);
courseTaken.Course.Id = Guid.Parse(Convert.ToString(reader["Course"]));
courseTaken.Course.Name = Convert.ToString(reader["Name"]);
courseTaken.Grade = Convert.ToInt32(reader["Grade"]);
coursesTaken.Add(courseTaken);
}
}
return coursesTaken;
}
public void AddCourseTaken()
{
var courseTaken = new CourseTakenModel();
controller.Add(courseTaken);
mockCourseTakenRepository.Verify(r => r.Add(courseTaken), Times.Once);
mockCourseTakenRepository.Verify(r => r.Add(It.IsAny<CourseTakenModel>()), Times.Once);
}
public void CannotAddCourseTakenWithWrongCourse()
{
var newCourseTaken = new CourseTakenModel
{
Student = new NameModel { Id = course.Id, Name = course.Name },
Course = new NameModel { Id = course.Id, Name = course.Name },
Grade = Random.Next()
};
Assert.That(() => CourseTakenRepository.Add(newCourseTaken), Throws.Exception);
}
public void RemoveStudentEnrolledInAClass()
{
var courseTaken = new CourseTakenModel
{
Student = new NameModel { Id = studentId },
Course = new NameModel { Id = Guid.NewGuid() }
};
coursesTaken.Add(courseTaken);
service.Remove(studentId);
mockCourseTakenRepository.Verify(r => r.Remove(courseTaken), Times.Once);
mockCourseTakenRepository.Verify(r => r.Remove(It.IsAny<CourseTakenModel>()), Times.Once);
mockStudentRepository.Verify(r => r.Remove(studentId), Times.Once);
mockStudentRepository.Verify(r => r.Remove(It.IsAny<Guid>()), Times.Once);
}
public void RemoveCourseWithAnEnrolledStudent()
{
var courseTaken = new CourseTakenModel
{
Student = new NameModel { Id = Guid.NewGuid() },
Course = new NameModel { Id = courseId }
};
coursesTaken.Add(courseTaken);
service.Remove(courseId);
mockCourseTakenRepository.Verify(r => r.Remove(courseTaken), Times.Once);
mockCourseTakenRepository.Verify(r => r.Remove(It.IsAny<CourseTakenModel>()), Times.Once);
mockCourseRepository.Verify(r => r.Remove(courseId), Times.Once);
mockCourseRepository.Verify(r => r.Remove(It.IsAny<Guid>()), Times.Once);
}
public void Add(CourseTakenModel courseTaken)
{
var sql = @"
INSERT INTO CoursesTaken (Student, Course, Grade)
VALUES (@Student, @Course, @Grade)";
var parameters = new[]
{
new SqlParameter("@Student", courseTaken.Student.Id),
new SqlParameter("@Course", courseTaken.Course.Id),
new SqlParameter("@Grade", courseTaken.Grade)
};
using (var connection = GetAndOpenConnection())
using (var command = GetCommand(sql, connection, parameters))
command.ExecuteNonQuery();
}
public void AddCourseTaken()
{
var newCourseTaken = new CourseTakenModel
{
Student = new NameModel { Id = student.Id, Name = student.FirstName },
Course = new NameModel { Id = course.Id, Name = course.Name },
Grade = Random.Next(110)
};
CourseTakenRepository.Add(newCourseTaken);
var coursesTaken = CourseTakenRepository.FindCourses(newCourseTaken.Student.Id);
var courseTaken = coursesTaken.First(c => c.Course.Id == newCourseTaken.Course.Id);
AssertCoursesTakenAreEqual(courseTaken, newCourseTaken);
coursesTaken = CourseTakenRepository.FindStudents(newCourseTaken.Course.Id);
courseTaken = coursesTaken.First(c => c.Student.Id == newCourseTaken.Student.Id);
AssertCoursesTakenAreEqual(courseTaken, newCourseTaken);
}
private CourseTakenModel CreateCourseTaken(StudentModel student, CourseModel course)
{
var courseTaken = new CourseTakenModel
{
Student = new NameModel { Id = student.Id, Name = student.FirstName },
Course = new NameModel { Id = course.Id, Name = course.Name },
Grade = Random.Next(110)
};
var sql = @"
INSERT INTO CoursesTaken (Student, Course, Grade)
VALUES (@Student, @Course, @Grade)";
var parameters = new[]
{
new SqlParameter("@Student", courseTaken.Student.Id),
new SqlParameter("@Course", courseTaken.Course.Id),
new SqlParameter("@Grade", courseTaken.Grade)
};
using (var connection = GetAndOpenConnection())
using (var command = GetCommand(sql, connection, parameters))
command.ExecuteNonQuery();
return courseTaken;
}
private void AssertCoursesTakenAreEqual(CourseTakenModel actual, CourseTakenModel expected)
{
Assert.That(actual.Course.Id, Is.EqualTo(expected.Course.Id));
Assert.That(actual.Course.Name, Is.EqualTo(expected.Course.Name));
Assert.That(actual.Student.Id, Is.EqualTo(expected.Student.Id));
Assert.That(actual.Student.Name, Is.EqualTo(expected.Student.Name));
Assert.That(actual.Grade, Is.EqualTo(expected.Grade));
}
public void GradeCannotBeNegative()
{
var newCourseTaken = new CourseTakenModel
{
Student = new NameModel { Id = student.Id, Name = student.FirstName },
Course = new NameModel { Id = course.Id, Name = course.Name },
Grade = -1
};
Assert.That(() => CourseTakenRepository.Add(newCourseTaken), Throws.Exception);
}
public void Update(CourseTakenModel courseTaken)
{
var sql = @"
UPDATE CoursesTaken
SET Grade = @Grade
WHERE Student = @Student
AND Course = @Course";
var parameters = new[]
{
new SqlParameter("@Student", courseTaken.Student.Id),
new SqlParameter("@Course", courseTaken.Course.Id),
new SqlParameter("@Grade", courseTaken.Grade)
};
using (var connection = GetAndOpenConnection())
using (var command = GetCommand(sql, connection, parameters))
command.ExecuteNonQuery();
}
public void Update(CourseTakenModel courseTaken)
{
courseTakenRepository.Update(courseTaken);
}
public void Remove(CourseTakenModel courseTaken)
{
courseTakenRepository.Remove(courseTaken);
}
public void Add(CourseTakenModel courseTaken)
{
courseTakenRepository.Add(courseTaken);
}
public void CannotRemoveAStudentEnrolledInClasses()
{
var newStudent = CreateStudent();
var newCourse = CreateCourse();
var newCourseTaken = new CourseTakenModel { Grade = 9266 };
newCourseTaken.Student = new NameModel { Id = newStudent.Id, Name = newStudent.FirstName };
newCourseTaken.Course = new NameModel { Id = newCourse.Id, Name = newCourse.Name };
CourseTakenRepository.Add(newCourseTaken);
Assert.That(() => StudentRepository.Remove(newStudent.Id), Throws.Exception);
var students = StudentRepository.FindAll();
var student = students.First(s => s.Id == newStudent.Id);
AssertStudentsAreEqual(student, newStudent);
var coursesTaken = CourseTakenRepository.FindCourses(newCourseTaken.Student.Id);
var courseTaken = coursesTaken.First(c => c.Course.Id == newCourseTaken.Course.Id);
Assert.That(courseTaken.Course.Id, Is.EqualTo(newCourseTaken.Course.Id));
Assert.That(courseTaken.Course.Name, Is.EqualTo(newCourseTaken.Course.Name));
Assert.That(courseTaken.Student.Id, Is.EqualTo(newCourseTaken.Student.Id));
Assert.That(courseTaken.Student.Name, Is.EqualTo(newCourseTaken.Student.Name));
Assert.That(courseTaken.Grade, Is.EqualTo(newCourseTaken.Grade));
coursesTaken = CourseTakenRepository.FindStudents(newCourseTaken.Course.Id);
courseTaken = coursesTaken.First(c => c.Student.Id == newCourseTaken.Student.Id);
Assert.That(courseTaken.Course.Id, Is.EqualTo(newCourseTaken.Course.Id));
Assert.That(courseTaken.Course.Name, Is.EqualTo(newCourseTaken.Course.Name));
Assert.That(courseTaken.Student.Id, Is.EqualTo(newCourseTaken.Student.Id));
Assert.That(courseTaken.Student.Name, Is.EqualTo(newCourseTaken.Student.Name));
Assert.That(courseTaken.Grade, Is.EqualTo(newCourseTaken.Grade));
}
public void DoNotRemoveInaccurateCourseTaken()
{
var otherStudent = CreateStudent();
var otherCourse = CreateCourse();
var newFirstCourseTaken = CreateCourseTaken(student, course);
var newSecondCourseTaken = CreateCourseTaken(otherStudent, otherCourse);
var firstWrongCourseTaken = new CourseTakenModel { Course = newFirstCourseTaken.Course, Student = newSecondCourseTaken.Student };
var secondWrongCourseTaken = new CourseTakenModel { Course = newSecondCourseTaken.Course, Student = newFirstCourseTaken.Student };
CourseTakenRepository.Remove(firstWrongCourseTaken);
CourseTakenRepository.Remove(secondWrongCourseTaken);
var coursesTaken = CourseTakenRepository.FindCourses(student.Id);
var courseTaken = coursesTaken.First(c => c.Course.Id == course.Id);
AssertCoursesTakenAreEqual(courseTaken, newFirstCourseTaken);
coursesTaken = CourseTakenRepository.FindStudents(course.Id);
courseTaken = coursesTaken.First(c => c.Student.Id == student.Id);
AssertCoursesTakenAreEqual(courseTaken, newFirstCourseTaken);
coursesTaken = CourseTakenRepository.FindCourses(otherStudent.Id);
courseTaken = coursesTaken.First(c => c.Course.Id == otherCourse.Id);
AssertCoursesTakenAreEqual(courseTaken, newSecondCourseTaken);
coursesTaken = CourseTakenRepository.FindStudents(otherCourse.Id);
courseTaken = coursesTaken.First(c => c.Student.Id == otherStudent.Id);
AssertCoursesTakenAreEqual(courseTaken, newSecondCourseTaken);
}
public void Remove(CourseTakenModel courseTaken)
{
var sql = @"
DELETE FROM CoursesTaken
WHERE Student = @Student
AND Course = @Course";
var parameters = new[]
{
new SqlParameter("@Student", courseTaken.Student.Id),
new SqlParameter("@Course", courseTaken.Course.Id)
};
using (var connection = GetAndOpenConnection())
using (var command = GetCommand(sql, connection, parameters))
command.ExecuteNonQuery();
}
