public List <StudentDto> Handle(GetListQuery query)
{
string sql = @"
SELECT s.StudentID Id, s.Name, s.Email,
s.FirstCourseName Course1, s.FirstCourseCredits Course1Credits, s.FirstCourseGrade Course1Grade,
s.SecondCourseName Course2, s.SecondCourseCredits Course2Credits, s.SecondCourseGrade Course2Grade
FROM dbo.Student s
WHERE (s.FirstCourseName = @Course
OR s.SecondCourseName = @Course
OR @Course IS NULL)
AND (s.NumberOfEnrollments = @Number
OR @Number IS NULL)
ORDER BY s.StudentID ASC";
using (SqlConnection connection = new SqlConnection(_connectionString.Value))
{
List <StudentDto> students = connection
.Query <StudentDto>(sql, new
{
Course = query.EnrolledIn,
Number = query.NumberOfCourses
})
.ToList();
return(students);
}
}
public List <StudentDto> Handle(GetListQuery query)
{
IReadOnlyList <Student> students = _studentRepository.GetAll();
var dtos = students.Select(p => ConvertToDto(p)).ToList();
return(dtos);
}
public List <StudentDto> Handle(GetListQuery query)
{
var unitOfWork = new UnitOfWork(_sessionFactory);
return(new StudentRepository(unitOfWork)
.GetList(query.EnrolledIn, query.NumberOfCourses)
.Select(x => ConvertToDto(x))
.ToList());
}
public List <StudentDto> Handle(GetListQuery query)
{
string sql = @"
SELECT s.*, e.Grade, c.Name as CourseName, c.Credits
FROM dbo.Student as s
LEFT JOIN (
SELECT e.StudentID, COUNT(*) as Number
FROM dbo.Enrollment e
GROUP BY e.StudentID
) as t ON s.StudentID = t.StudentID
LEFT JOIN dbo.Enrollment e ON e.StudentID = s.StudentID
LEFT JOIN dbo.Course c ON e.CourseID = c.CourseID
WHERE (c.Name = @Course OR @Course IS NULL)
AND (ISNULL(t.Number, 0) = @Number OR @Number IS NULL)
ORDER BY s.StudentID ASC";
using (SqlConnection connection = new SqlConnection(_connectionString.Value))
{
List <StudentDto> result = new List <StudentDto>();
List <StudentInDB> students = connection
.Query <StudentInDB>(sql, new
{
Course = query.EnrolledIn,
Number = query.NumberOfCourses
})
.ToList();
List <long> ids = students
.GroupBy(x => x.StudentID)
.Select(x => x.Key)
.ToList();
foreach (long id in ids)
{
List <StudentInDB> data = students
.Where(x => x.StudentID == id)
.ToList();
var dto = new StudentDto
{
Id = data[0].StudentID,
Name = data[0].Name,
Email = data[0].Email,
Course1 = data[0].CourseName,
Course1Credits = data[0].Credits,
Course1Grade = data[0]?.Grade.ToString()
};
if (data.Count > 1)
{
dto.Course2 = data[1].CourseName;
dto.Course2Credits = data[1].Credits;
dto.Course2Grade = data[1]?.Grade.ToString();
}
result.Add(dto);
}
return(result);
}
}