public static List <MarkDTO> loadMarkByNameWithoutSchoolYear(string idStudent, string nameClass, string semester, string nameSubject) { string sCommand = @"select* from dbo.Mark where IDStudent = '" + idStudent + "' and nameClass = '" + nameClass + "' and semester = " + int.Parse(semester) + "and IDSubject = '" + SubjectDTO.getIDSubject(nameSubject) + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); List <MarkDTO> result = new List <MarkDTO>(); if (dt.Rows.Count <= 0) { DataProvider.CloseConnection(con); return(null); } else { for (int i = 0; i < dt.Rows.Count; i++) { MarkDTO temp = new MarkDTO(); temp.IDStudent = dt.Rows[i]["IDStudent"].ToString(); // temp.IdTeacher = dt.Rows[i]["IDTeacher"].ToString(); temp.NameClass = dt.Rows[i]["nameClass"].ToString(); temp.SchoolYear = dt.Rows[i]["schoolYear"].ToString(); temp.Subject.IdSubject = dt.Rows[i]["IDSubject"].ToString(); temp.Subject.NameSubject = SubjectDTO.getNameSubject(temp.Subject.IdSubject); temp.Semester = int.Parse(dt.Rows[i]["Semester"].ToString()); if (dt.Rows[i]["FirstFifteenMinutes"].ToString() != "") { temp.FirstFifteenMinutesMark = double.Parse(dt.Rows[i]["FirstFifteenMinutes"].ToString()); } if (dt.Rows[i]["SecondFifteenMinutes"].ToString() != "") { temp.SecondFifteenMinutesMark = double.Parse(dt.Rows[i]["SecondFifteenMinutes"].ToString()); } if (dt.Rows[i]["ThirdFifteenMinutes"].ToString() != "") { temp.ThirdFifteenMinutesMark = double.Parse(dt.Rows[i]["ThirdFifteenMinutes"].ToString()); } if (dt.Rows[i]["FirstFortyFiveMinutes"].ToString() != "") { temp.FirstFortyFiveMinutesMark = double.Parse(dt.Rows[i]["FirstFortyFiveMinutes"].ToString()); } if (dt.Rows[i]["SecondFortyFiveMinutes"].ToString() != "") { temp.SecondFortyFiveMinutesMark = double.Parse(dt.Rows[i]["SecondFortyFiveMinutes"].ToString()); } if (dt.Rows[i]["ThirdFortyFiveMinutes"].ToString() != "") { temp.ThirdFortyFiveMinutesMark = double.Parse(dt.Rows[i]["ThirdFortyFiveMinutes"].ToString()); } if (dt.Rows[i]["SemesterMark"].ToString() != "") { temp.SemesterScore = double.Parse(dt.Rows[i]["SemesterMark"].ToString()); } result.Add(temp); } DataProvider.CloseConnection(con); return(result); } }
public static List <MarkDTO> searchStudent_Mark(string keyWord, string nameSubject, string nameClass, string schoolYear, string semester) { if (nameSubject == "All") { string sCommand = @"Select * from Mark M join Student S on (S.IDStudent = M.IDStudent) where ((S.IDStudent like '%" + keyWord + "%') or (S.Name like N'%" + keyWord + "%')) and M.nameClass = '" + nameClass + "' and M.schoolYear = '" + schoolYear + "' and M.Semester = " + int.Parse(semester); con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count <= 0) { DataProvider.CloseConnection(con); return(null); } else { int n = dt.Rows.Count; List <MarkDTO> result = new List <MarkDTO>(); for (int i = 0; i < n; i++) { MarkDTO temp = new MarkDTO(); temp.IDStudent = dt.Rows[i]["IDStudent"].ToString(); // temp.IdTeacher = dt.Rows[i]["IDTeacher"].ToString(); temp.NameClass = dt.Rows[i]["nameClass"].ToString(); temp.SchoolYear = dt.Rows[i]["schoolYear"].ToString(); temp.Subject.IdSubject = dt.Rows[i]["IDSubject"].ToString(); temp.NameStudent = getNameStudent(temp.IDStudent); temp.Subject.NameSubject = SubjectDTO.getNameSubject(temp.Subject.IdSubject); temp.Semester = int.Parse(dt.Rows[i]["Semester"].ToString()); if (dt.Rows[i]["FirstFifteenMinutes"].ToString() != "") { temp.FirstFifteenMinutesMark = double.Parse(dt.Rows[i]["FirstFifteenMinutes"].ToString()); } if (dt.Rows[i]["SecondFifteenMinutes"].ToString() != "") { temp.SecondFifteenMinutesMark = double.Parse(dt.Rows[i]["SecondFifteenMinutes"].ToString()); } if (dt.Rows[i]["ThirdFifteenMinutes"].ToString() != "") { temp.ThirdFifteenMinutesMark = double.Parse(dt.Rows[i]["ThirdFifteenMinutes"].ToString()); } if (dt.Rows[i]["FirstFortyFiveMinutes"].ToString() != "") { temp.FirstFortyFiveMinutesMark = double.Parse(dt.Rows[i]["FirstFortyFiveMinutes"].ToString()); } if (dt.Rows[i]["SecondFortyFiveMinutes"].ToString() != "") { temp.SecondFortyFiveMinutesMark = double.Parse(dt.Rows[i]["SecondFortyFiveMinutes"].ToString()); } if (dt.Rows[i]["ThirdFortyFiveMinutes"].ToString() != "") { temp.ThirdFortyFiveMinutesMark = double.Parse(dt.Rows[i]["ThirdFortyFiveMinutes"].ToString()); } if (dt.Rows[i]["SemesterMark"].ToString() != "") { temp.SemesterScore = double.Parse(dt.Rows[i]["SemesterMark"].ToString()); } result.Add(temp); } DataProvider.CloseConnection(con); return(result); } } else { string sCommand = @"Select * from Mark M join Student S on (S.IDStudent = M.IDStudent) where ((S.IDStudent like '%" + keyWord + "%') or (S.Name like N'%" + keyWord + "%')) and M.nameClass = '" + nameClass + "' and M.schoolYear = '" + schoolYear + "' and M.Semester = " + int.Parse(semester) + "and M.IDSubject = '" + SubjectDTO.getIDSubject(nameSubject) + "'"; con = DataProvider.OpenConnection(); DataTable dt = DataProvider.GetDataTable(sCommand, con); if (dt.Rows.Count <= 0) { DataProvider.CloseConnection(con); return(null); } else { int n = dt.Rows.Count; List <MarkDTO> result = new List <MarkDTO>(); for (int i = 0; i < n; i++) { MarkDTO temp = new MarkDTO(); temp.IDStudent = dt.Rows[i]["IDStudent"].ToString(); // temp.IdTeacher = dt.Rows[i]["IDTeacher"].ToString(); temp.NameClass = dt.Rows[i]["nameClass"].ToString(); temp.SchoolYear = dt.Rows[i]["schoolYear"].ToString(); temp.Subject.IdSubject = dt.Rows[i]["IDSubject"].ToString(); temp.NameStudent = getNameStudent(temp.IDStudent); temp.Subject.NameSubject = SubjectDTO.getNameSubject(temp.Subject.IdSubject); temp.Semester = int.Parse(dt.Rows[i]["Semester"].ToString()); if (dt.Rows[i]["FirstFifteenMinutes"].ToString() != "") { temp.FirstFifteenMinutesMark = double.Parse(dt.Rows[i]["FirstFifteenMinutes"].ToString()); } if (dt.Rows[i]["SecondFifteenMinutes"].ToString() != "") { temp.SecondFifteenMinutesMark = double.Parse(dt.Rows[i]["SecondFifteenMinutes"].ToString()); } if (dt.Rows[i]["ThirdFifteenMinutes"].ToString() != "") { temp.ThirdFifteenMinutesMark = double.Parse(dt.Rows[i]["ThirdFifteenMinutes"].ToString()); } if (dt.Rows[i]["FirstFortyFiveMinutes"].ToString() != "") { temp.FirstFortyFiveMinutesMark = double.Parse(dt.Rows[i]["FirstFortyFiveMinutes"].ToString()); } if (dt.Rows[i]["SecondFortyFiveMinutes"].ToString() != "") { temp.SecondFortyFiveMinutesMark = double.Parse(dt.Rows[i]["SecondFortyFiveMinutes"].ToString()); } if (dt.Rows[i]["ThirdFortyFiveMinutes"].ToString() != "") { temp.ThirdFortyFiveMinutesMark = double.Parse(dt.Rows[i]["ThirdFortyFiveMinutes"].ToString()); } if (dt.Rows[i]["SemesterMark"].ToString() != "") { temp.SemesterScore = double.Parse(dt.Rows[i]["SemesterMark"].ToString()); } result.Add(temp); } DataProvider.CloseConnection(con); return(result); } } }