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);
        }
示例#3
0
            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());
            }
示例#4
0
            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);
                }
            }