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 Course_elearn create_elearnCourse(Course_elearn course)
{
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 = "insert into [Elearn_course] " +
"(elearn_courseName, elearn_courseProvider, entry_date, start_date, expiry_date, status, description, categoryID, courseCreator, hoursAwarded, targetAudience, courseType) OUTPUT INSERTED.elearn_courseID " +
"values (@cName, @provider, Convert(datetime, @entry, 103), convert(datetime,@time,103), Convert(datetime,@expiry,103), @status, @desc, @category, @courseCreator, @hoursAwarded, @targetAudience, @courseType)";
comm.Parameters.AddWithValue("@cName", course.getCourseName());
if (course.getCourseProvider() != null)
{
comm.Parameters.AddWithValue("@provider", course.getCourseProvider());
}
else
{
comm.Parameters.AddWithValue("@provider", DBNull.Value);
}
comm.Parameters.AddWithValue("@entry", course.getEntryDate());
if (course.getStartDate() == null)
{
comm.Parameters.AddWithValue("@time", DBNull.Value);
}
else
{
comm.Parameters.AddWithValue("@time", course.getStartDate());
}
if (course.getExpiryDate() == null)
{
comm.Parameters.AddWithValue("@expiry", DBNull.Value);
}
else
{
comm.Parameters.AddWithValue("@expiry", course.getExpiryDate());
}
comm.Parameters.AddWithValue("@status", course.getStatus());
comm.Parameters.AddWithValue("@desc", course.getDescription());
comm.Parameters.AddWithValue("@category", course.getCategoryID());
comm.Parameters.AddWithValue("@courseCreator", course.getCourseCreator().getUserID());
comm.Parameters.AddWithValue("@hoursAwarded", course.getHoursAwarded());
comm.Parameters.AddWithValue("@targetAudience", course.getTargetAudience());
comm.Parameters.AddWithValue("@courseType", course.getCourseType());
int a = (Int32)comm.ExecuteScalar();
//need new method to create pre-requisities here to store in seperate table (pre-req table)
course.setCourseID(a);
toReturn = course;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public Course_elearn get_course_by_id(int id)
{
SqlConnection conn = new SqlConnection();
Course_elearn toReturn = null;
UserDAO userDAO = new UserDAO();
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_courseID=@id";
comm.Parameters.AddWithValue("@id", id);
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
toReturn.setEntryDate((DateTime)dr["entry_date"]);
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.setCourseCreator(userDAO.getUserByID((string)dr["courseCreator"]));
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 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);
}