public Course_elearn get_course_by_name(Course_elearn getThis) { SqlConnection conn = new SqlConnection(); Course_elearn 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 Elearn_course where elearn_courseName =@name"; comm.Parameters.AddWithValue("@name", getThis.getCourseName()); SqlDataReader dr = comm.ExecuteReader(); while (dr.Read()) { toReturn = new Course_elearn(); int cid = (int)dr["elearn_courseID"]; //1 toReturn.setCourseID(cid); toReturn.setCourseName((string)dr["elearn_courseName"]); //2 if (!dr.IsDBNull(4)) { toReturn.setCourseProvider((string)dr["elearn_courseProvider"]); } ; toReturn.setStartDate((DateTime)dr["start_date"]);//3 if (!dr.IsDBNull(4)) { toReturn.setExpiryDate((DateTime)dr["expiry_date"]); } toReturn.setStatus((string)dr["status"]); //4 //get the prereq toReturn.setDescription((string)dr["description"]); //6 ArrayList list = getPrereqOfCourse(cid); //5 if (list != null) { toReturn.setPrerequisite(list); //retrieve arraylist of all prereq course_elearn objects } toReturn.setCategoryID((int)dr["categoryID"]); //7 toReturn.setHoursAwarded((double)dr["hoursAwarded"]); if (!dr.IsDBNull(11)) { toReturn.setTargetAudience((string)dr["targetAudience"]); } toReturn.setCourseType((string)dr["courseType"]); } dr.Close(); } catch (SqlException ex) { throw ex; } finally { conn.Close(); } return(toReturn); }
public ArrayList getPrereqOfCourse(int course_id) { ArrayList toReturn_list = new ArrayList(); SqlConnection conn = new SqlConnection(); Course_elearn 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 Elearn_course ce inner join " + "(select prereq_courseID from Elearn_prerequisites where elearn_courseID = @check) " + "as temp on ce.elearn_courseID = temp.prereq_courseID;"; //get data of all courses that are prereqs comm.Parameters.AddWithValue("@check", course_id); SqlDataReader dr = comm.ExecuteReader(); while (dr.Read()) { toReturn = new Course_elearn(); toReturn.setCourseID((int)dr["elearn_courseID"]); toReturn.setCourseName((string)dr["elearn_courseName"]); if (!dr.IsDBNull(4)) { toReturn.setCourseProvider((string)dr["elearn_courseProvider"]); } ; toReturn.setStartDate((DateTime)dr["start_date"]); if (!dr.IsDBNull(4)) { toReturn.setExpiryDate((DateTime)dr["expiry_date"]); } toReturn.setStatus((string)dr["status"]); toReturn.setDescription((string)dr["description"]); toReturn.setCategoryID((int)dr["categoryID"]); toReturn.setHoursAwarded((double)dr["hoursAwarded"]); if (!dr.IsDBNull(11)) { toReturn.setTargetAudience((string)dr["targetAudience"]); } toReturn.setCourseType((string)dr["courseType"]); toReturn_list.Add(toReturn); //parse as course_elearn object to store and return in arraylist } dr.Close(); } catch (SqlException ex) { throw ex; } finally { conn.Close(); } return(toReturn_list); }
public List <Course_elearn> viewAvailablePrerequisiteCourses() { SqlConnection conn = new SqlConnection(); List <Course_elearn> toReturn_list = new List <Course_elearn>(); Course_elearn toReturn; 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 [Elearn_course] where status = 'active' and start_date<=getDate()"; SqlDataReader dr = comm.ExecuteReader(); while (dr.Read()) { toReturn = new Course_elearn(); toReturn.setCourseID((int)dr["elearn_courseID"]); //1 toReturn.setCourseName((string)dr["elearn_courseName"]); //2 if (!dr.IsDBNull(4)) { toReturn.setCourseProvider((string)dr["elearn_courseProvider"]); } ; toReturn.setStartDate((DateTime)dr["start_date"]);//3 if (!dr.IsDBNull(4)) { toReturn.setExpiryDate((DateTime)dr["expiry_date"]); } toReturn.setStatus((string)dr["status"]); //4 //get the prereq toReturn.setDescription((string)dr["description"]); //6 ArrayList list = getPrereqOfCourse((int)dr["elearn_courseID"]); //5 if (list != null) { toReturn.setPrerequisite(list); //retrieve arraylist of all prereq course_elearn objects } toReturn.setCategoryID((int)dr["categoryID"]); //7 toReturn.setHoursAwarded((double)dr["hoursAwarded"]); if (!dr.IsDBNull(11)) { toReturn.setTargetAudience((string)dr["targetAudience"]); } toReturn.setCourseType((string)dr["courseType"]); toReturn_list.Add(toReturn); //add to arraylist to return of all courses related to given category } dr.Close(); } catch (SqlException ex) { throw ex; } finally { conn.Close(); } return(toReturn_list); }