public ArrayList getPrereqOfQuiz(int quizID) { ArrayList toReturn_list = new ArrayList(); SqlConnection conn = new SqlConnection(); Quiz toReturn = null; try { conn = new SqlConnection(); string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString(); conn.ConnectionString = connstr; conn.Open(); SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = "select * from Quiz q inner join " + "(select prereq_quizID from QuizPrerequisite where quizID = @check) " + "as temp on q.quizID = temp.prereq_quizID;"; comm.Parameters.AddWithValue("@check", quizID); SqlDataReader dr = comm.ExecuteReader(); while (dr.Read()) { toReturn = new Quiz(); toReturn.setQuizID((int)dr["quizID"]); toReturn.setTitle((string)dr["title"]); toReturn.setDescription((string)dr["description"]); toReturn.setPassingGrade((int)dr["passingGrade"]); toReturn.setRandomOrder((string)dr["randomOrder"]); toReturn.setStatus((string)dr["status"]); Course_elearnDAO ceDAO = new Course_elearnDAO(); toReturn.setMainCourse(ceDAO.get_course_by_id((int)dr["elearn_courseID"])); toReturn.setTimeLimit((int)dr["timeLimit"]); toReturn.setMultipleAttempts((string)dr["multipleAttempts"]); toReturn.setNumberOfAttempts((int)dr["numberOfAttempts"]); toReturn.setDisplayAnswer((string)dr["displayAnswer"]); toReturn_list.Add(toReturn); } dr.Close(); } catch (SqlException ex) { throw ex; } finally { conn.Close(); } return(toReturn_list); }
public List <Quiz> getAllQuizByCourseIDActiveAndInactive(int courseID) { SqlConnection conn = new SqlConnection(); List <Quiz> toReturn = new List <Quiz>(); try { conn = new SqlConnection(); string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString(); conn.ConnectionString = connstr; conn.Open(); SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = "select * from [Quiz] where elearn_courseID=@elearn_courseID"; comm.Parameters.AddWithValue("@elearn_courseID", courseID); SqlDataReader dr = comm.ExecuteReader(); while (dr.Read()) { Quiz q = new Quiz(); q.setQuizID((int)dr["quizID"]); q.setTitle((string)dr["title"]); q.setDescription((string)dr["description"]); q.setPassingGrade((int)dr["passingGrade"]); q.setRandomOrder((string)dr["randomOrder"]); q.setStatus((string)dr["status"]); Course_elearnDAO ceDAO = new Course_elearnDAO(); q.setMainCourse(ceDAO.get_course_by_id((int)dr["elearn_courseID"])); q.setTimeLimit((int)dr["timeLimit"]); q.setMultipleAttempts((string)dr["multipleAttempts"]); q.setNumberOfAttempts((int)dr["numberOfAttempts"]); q.setDisplayAnswer((string)dr["displayAnswer"]); toReturn.Add(q); } dr.Close(); } catch (SqlException ex) { throw ex; } finally { conn.Close(); } return(toReturn); }
public Quiz getQuizByID(int quizID) { SqlConnection conn = new SqlConnection(); Quiz toReturn = null; try { conn = new SqlConnection(); string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString(); conn.ConnectionString = connstr; conn.Open(); SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = "select * from [Quiz] q inner join [Elearn_course] ec on q.elearn_courseID = ec.elearn_courseID where q.quizID=@quizID"; comm.Parameters.AddWithValue("@quizID", quizID); SqlDataReader dr = comm.ExecuteReader(); while (dr.Read()) { toReturn = new Quiz(); toReturn.setQuizID((int)dr["quizID"]); toReturn.setTitle((string)dr["title"]); toReturn.setDescription((string)dr["description"]); toReturn.setPassingGrade((int)dr["passingGrade"]); toReturn.setRandomOrder((string)dr["randomOrder"]); toReturn.setStatus((string)dr["status"]); toReturn.setTimeLimit((int)dr["timeLimit"]); toReturn.setMultipleAttempts((string)dr["multipleAttempts"]); toReturn.setNumberOfAttempts((int)dr["numberOfAttempts"]); toReturn.setDisplayAnswer((string)dr["displayAnswer"]); Course_elearnDAO ceDAO = new Course_elearnDAO(); //toReturn.setMainCourse(ceDAO.get_course_by_id((int)dr["elearn_courseID"])); Course_elearn course = new Course_elearn(); UserDAO userDAO = new UserDAO(); int cid = (int)dr["elearn_courseID"]; //1 course.setCourseID(cid); course.setCourseName((string)dr["elearn_courseName"]); //2 if (!dr.IsDBNull(4)) { course.setCourseProvider((string)dr["elearn_courseProvider"]); } ; course.setStartDate((DateTime)dr["start_date"]);//3 if (!dr.IsDBNull(4)) { course.setExpiryDate((DateTime)dr["expiry_date"]); } course.setStatus((string)dr["status"]); //4 //get the prereq course.setDescription((string)dr["description"]); //6 course.setEntryDate((DateTime)dr["entry_date"]); ArrayList list = ceDAO.getPrereqOfCourse(cid); //5 if (list != null) { course.setPrerequisite(list); //retrieve arraylist of all prereq course_elearn objects } course.setCategoryID((int)dr["categoryID"]); //7 course.setCourseCreator(userDAO.getUserByID((string)dr["courseCreator"])); course.setHoursAwarded((double)dr["hoursAwarded"]); if (!dr.IsDBNull(11)) { course.setTargetAudience((string)dr["targetAudience"]); } toReturn.setMainCourse(course); } dr.Close(); } catch (SqlException ex) { throw ex; } finally { conn.Close(); } return(toReturn); }