public User login(string userID, string password)
{
SqlConnection conn = new SqlConnection();
User toReturn = new User();
UserDAO uDao = 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 [User] where userID=@userID and password=@password";
comm.Parameters.AddWithValue("@userID", userID);
comm.Parameters.AddWithValue("@password", password);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
toReturn = new User();
toReturn.setUserID((string)dr["userID"]);
toReturn.setName((string)dr["name"]);
toReturn.setJobTitle((string)dr["job_title"]);
toReturn.setJobCategory((string)dr["job_category"]);
toReturn.setEmail((string)dr["email"]);
toReturn.setStartDate(dr.GetDateTime(3));
toReturn.setContact((string)dr["contactNumber"]);
toReturn.setAddress((string)dr["address"]);
//toReturn.setPasswordHashed((string)dr["password"]);
if (!dr.IsDBNull(6))
{
toReturn.setSupervisor((string)dr["supervisor"]);
}
else
{
toReturn.setSupervisor("NA");
}
ArrayList roles = uDao.getRolesByID(userID);
toReturn.setRoles(roles);
if (!dr.IsDBNull(8))
{
toReturn.setDepartment((string)dr["dept_name"]);
}
else
{
toReturn.setDepartment("NA");
}
toReturn.setStatus((string)dr["status"]);
}
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);
}
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);
}