示例#1
0
        /// <summary>
        /// Метод, который получает все посещения по заданным параметрам
        /// </summary>
        /// <param name="studentVisit"></param>
        /// <returns></returns>
        public List <StudentVisit> GetStudentVisits(StudentVisitSearchObject studentVisit)
        {
            var studentVisits = new List <StudentVisit>();

            conn.Open();
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection  = conn;
                cmd.CommandText = StudentVisitsDbRequest(studentVisit);
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    studentVisits.Add(new StudentVisit((int)reader["idpresense"], studentVisit.Student.FirstName, studentVisit.Student.LastName, studentVisit.Student.PastName, studentVisit.Student.Group, (DateTime)reader["date"], studentVisit.Classroom, studentVisit.Subject, (bool)reader["presense"]));
                }
            }
            catch (NullReferenceException)
            {
                throw;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            return(studentVisits);
        }
示例#2
0
        private String StudentVisitsDbRequest(StudentVisitSearchObject studentVisit)
        {
            string request;
            var    issecond             = false;
            var    date                 = studentVisit.DateTime.ToString("yyyy-MM-dd");
            var    selectIdStudent      = $"select idstudent from vcsdb.students where firstname = '{studentVisit.Student.FirstName}' and lastname = '{studentVisit.Student.LastName}' and pastname = '{studentVisit.Student.PastName}'";
            var    selectIdSubject      = $"select idsubject from vcsdb.subjects where subject = '{studentVisit.Subject}'";
            var    selectIdClassroom    = $"select idclassroom from vcsdb.classrooms where classroom = '{studentVisit.Classroom}'";
            var    selectIdStudentGroup = $"select idstudentgroup from vcsdb.studentgroups where studentgroup = '{studentVisit.Student.Group}'";
            var    selectIdClass        = $"select idclass from vcsdb.shedule where ";

            request = $"SELECT * FROM vcsdb.visits " +
                      $"WHERE idstudent =({selectIdStudent}) " +
                      $"AND date = '{date}' ";

            if (studentVisit.Classroom != null)
            {
                if (issecond)
                {
                    selectIdClass += "and ";
                }
                else
                {
                    issecond = true;
                }
                selectIdClass += $"idclassroom = ({selectIdClassroom}) ";
            }
            if (studentVisit.Student.Group != null)
            {
                if (issecond)
                {
                    selectIdClass += "and ";
                }
                else
                {
                    issecond = true;
                }
                selectIdClass += $"idstudentgroup = ({selectIdStudentGroup}) ";
            }
            if (studentVisit.Subject != null)
            {
                if (issecond)
                {
                    selectIdClass += "and ";
                }
                else
                {
                    issecond = true;
                }
                selectIdClass += $"idsubject = ({selectIdSubject}) ";
            }
            if (studentVisit.Classroom != null || studentVisit.Student.Group != null || studentVisit.Subject != null)
            {
                request += $"and idclass = ({selectIdClass})";
            }
            request += ";";
            return(request);
        }