public List <Competency> getCompentencyByMSID(string MSID)
{
List <string> getCourseLevelandNameByIDlist = new List <string>();
List <double> getDataFromQuizTablelist = new List <double>();
List <Competency> getCompentencyByMSIDlist = new List <Competency>();
connectionString = ConfigurationManager.ConnectionStrings["ConnectionStringTo2043"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmdFectCoursesByMSID = new SqlCommand(" select * from CT_EMPLOYEE_COMPETENCY where MSID = '" + MSID + "'", conn);
SqlDataReader srFectCoursesByMSID = cmdFectCoursesByMSID.ExecuteReader();
while (srFectCoursesByMSID.Read())
{
Competency competencyObj = new Competency();
competencyObj.autoGeneratedID = Convert.ToInt32(srFectCoursesByMSID["EMP_COMP_ID"]);
competencyObj.Emp_ID = Convert.ToInt32(srFectCoursesByMSID["EMP_ID"]);
competencyObj.MSID = srFectCoursesByMSID["MSID"].ToString();
competencyObj.courseID = Convert.ToInt32(srFectCoursesByMSID["Compentecy_ID"]);
getCourseLevelandNameByIDlist = getCourseLevelandNameByID(competencyObj.courseID);
competencyObj.compLevel = getCourseLevelandNameByIDlist[0];
competencyObj.courseName = getCourseLevelandNameByIDlist[1];
competencyObj.quizID = srFectCoursesByMSID["Quiz_ID"].ToString();
completionStatusFromDB = srFectCoursesByMSID["Completion_Status"].ToString();
competencyObj.enrollDate = Convert.ToDateTime(srFectCoursesByMSID["EnrollDate"]);
if (completionStatusFromDB.Equals("False"))
{
competencyObj.completionStatus = "Incomplete";
}
else
{
competencyObj.completionStatus = "Complete";
}
getDataFromQuizTablelist = getDataFromQuizTable(competencyObj.quizID);
competencyObj.marksObtained = getDataFromQuizTablelist[0];
competencyObj.attemptCount = ((int)getDataFromQuizTablelist[1]);
getCompentencyByMSIDlist.Add(competencyObj);
}
}
return(getCompentencyByMSIDlist);
}
public List <Competency> getCoursesFromDB(string compLevel)
{
List <Competency> coursesFromDBlist = new List <Competency>();
string courseName = string.Empty;
string connectionString = string.Empty;
connectionString = ConfigurationManager.ConnectionStrings["ConnectionStringTo2043"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmdToSelectCourses = new SqlCommand("select * from CT_MasterCompetency where COMPETENCY_LEVEL = '" + compLevel + "'", conn);
SqlDataReader readCoursesFromDB = cmdToSelectCourses.ExecuteReader();
while (readCoursesFromDB.Read())
{
Competency compObj = new Competency();
compObj.courseID = Convert.ToInt32(readCoursesFromDB["COMPETENCY_ID"]);
compObj.courseName = readCoursesFromDB["COMPETENCY_NAME"].ToString();
coursesFromDBlist.Add(compObj);
}
}
return(coursesFromDBlist);
}