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);
}
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);
}
