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