public static List <AnswerModel> Select(List <string> answers)
{
DataAccess.DataHandler datahandler = new DataAccess.DataHandler();
List <AnswerModel> answersList = new List <AnswerModel>();
DataTable datatable = new DataTable();
string query = "select * from Answer where ";
for (int i = 0; i < answers.Count; i++)
{
query = query + "questionId = " + answers[i];
if (i < answers.Count - 1)
{
query = query + " or ";
}
else
{
}
}
using (datatable = datahandler.Select(query))
{
foreach (DataRow item in datatable.Rows)
{
answersList.Add(new AnswerModel(int.Parse(item["id"].ToString()), int.Parse(item["questionId"].ToString()), item["Answer"].ToString()));
}
}
return(answersList);
}
public List <QuestionModel> Select(List <string> outcomes)
{
DataAccess.DataHandler datahandler = new DataAccess.DataHandler();
List <QuestionModel> questionsList = new List <QuestionModel>();
DataTable datatable = new DataTable();
string query = "select * from Question inner join OutcomeDetails on OutcomeDetails.id = Question.OutcomeDetailsID where ";
for (int i = 0; i < outcomes.Count; i++)
{
query = query + "OutcomeDetails.Material = '" + outcomes[i] + "'";
if (i <= outcomes.Count - 1)
{
}
else
{
query = query + " or ";
}
}
using (datatable = datahandler.Select(query))
{
foreach (DataRow item in datatable.Rows)
{
questionsList.Add(new QuestionModel(int.Parse(item["id"].ToString()), int.Parse(item["OutcomeDetailsID"].ToString()), int.Parse(item["QuestionTypeID"].ToString()), item["Questions"].ToString(), int.Parse(item["QuestionWeight"].ToString())));
}
}
return(questionsList);
}
public bool RemoveOutcomeDetails(List <string> where)
{
bool isDeleted = false;
string tableName = "OutcomeDetails";
DataAccess.DataHandler dh = new DataAccess.DataHandler();
isDeleted = dh.Delete(tableName, where);
return(isDeleted);
}
public bool RemoveQuestionChoice(List <string> where)
{
bool isDeleted = false;
string tableName = "Choices";
DataAccess.DataHandler dh = new DataAccess.DataHandler();
isDeleted = dh.Delete(tableName, where);
return(isDeleted);
}
public bool RemoveLecturer(List <string> where)
{
bool isDeleted = false;
string tableName = "Lecturer";
DataAccess.DataHandler dh = new DataAccess.DataHandler();
isDeleted = dh.Delete(tableName, where);
return(isDeleted);
}
public List <LecturerModel> GetLecturer(List <string> columns = null, List <string> where = null)
{
string tableName = "Lecturer";
List <LecturerModel> lecturers = new List <LecturerModel>();
List <string> records = new List <string>();
DataAccess.DataHandler dh = new DataAccess.DataHandler();
records = dh.Select(tableName, columns, where);
Dictionary <string, string> fieldValues = new Dictionary <string, string>();
foreach (string item in records)
{
fieldValues.Clear();
int counter = 1;
string[] fields = item.Split(',');
foreach (string attribute in fields)
{
switch (counter)
{
case 1:
fieldValues.Add("id", attribute);
break;
case 2:
fieldValues.Add("AccountID", attribute);
break;
case 3:
fieldValues.Add("name", attribute);
break;
case 4:
fieldValues.Add("surname", attribute);
break;
case 5:
fieldValues.Add("cell", attribute);
break;
case 6:
fieldValues.Add("email", attribute);
break;
case 7:
fieldValues.Add("LecturerLevel", attribute);
break;
}
counter++;
}
lecturers.Add(ConvertToLecturer(fieldValues));
}
return(lecturers);
}
public static List <SubjectModel> GetSubject(List <string> columns = null, List <string> where = null)
{
string tableName = "Subjects";
List <SubjectModel> subjects = new List <SubjectModel>();
List <string> records = new List <string>();
DataAccess.DataHandler dh = new DataAccess.DataHandler();
records = dh.Select(tableName, columns, where);
Dictionary <string, string> fieldValues = new Dictionary <string, string>();
foreach (string item in records)
{
fieldValues.Clear();
int counter = 1;
string[] fields = item.Split(',');
foreach (string attribute in fields)
{
switch (counter)
{
case 1:
fieldValues.Add("id", attribute);
break;
case 2:
fieldValues.Add("name", attribute);
break;
case 3:
fieldValues.Add("code", attribute);
break;
case 4:
fieldValues.Add("duration", attribute);
break;
case 5:
fieldValues.Add("SubjectWeight", attribute);
break;
case 6:
fieldValues.Add("SubjectDescription", attribute);
break;
}
counter++;
}
subjects.Add(ConvertToSubject(fieldValues));
}
return(subjects);
}
public bool ChangeOutcome(List <string> where)
{
bool isUpdated = false;
List <string> colValues = ConvertToColumnValues(this);
Dictionary <string, string> colValuePairs = new Dictionary <string, string>();
string tableName = "Outcome";
DataAccess.DataHandler dh = new DataAccess.DataHandler();
colValuePairs.Add("SubjectID", colValues[0]);
colValuePairs.Add("name", colValues[1]);
isUpdated = dh.Update(tableName, colValuePairs, where);
return(isUpdated);
}
public List <LecturerCourseSubjectModel> GetLecturerCourseSubject(List <string> columns = null, List <string> where = null)
{
string tableName = "LecturerCourseSubject";
List <LecturerCourseSubjectModel> lecturerCourseSubjects = new List <LecturerCourseSubjectModel>();
List <string> records = new List <string>();
DataAccess.DataHandler dh = new DataAccess.DataHandler();
records = dh.Select(tableName, columns, where);
Dictionary <string, string> fieldValues = new Dictionary <string, string>();
foreach (string item in records)
{
int counter = 1;
string[] fields = item.Split(',');
foreach (string attribute in fields)
{
switch (counter)
{
case 1:
fieldValues.Add("id", attribute);
break;
case 2:
fieldValues.Add("LecturerID", attribute);
break;
case 3:
fieldValues.Add("CourseSubjectID", attribute);
break;
case 4:
fieldValues.Add("startDate", attribute);
break;
case 5:
fieldValues.Add("endDate", attribute);
break;
case 6:
fieldValues.Add("isFlagged", attribute);
break;
}
counter++;
}
lecturerCourseSubjects.Add(ConvertToLecturerCourseSubject(fieldValues));
}
return(lecturerCourseSubjects);
}
public bool ChangeQuestion(List <string> where)
{
bool isUpdated = false;
List <string> colValues = ConvertToColumnValues(this);
Dictionary <string, string> colValuePairs = new Dictionary <string, string>();
string tableName = "Question";
DataAccess.DataHandler dh = new DataAccess.DataHandler();
colValuePairs.Add("OutcomeDetailsID", colValues[0]);
colValuePairs.Add("QuestionTypeID", colValues[1]);
colValuePairs.Add("Questions", colValues[2]);
colValuePairs.Add("QuestionWeight", colValues[3]);
isUpdated = dh.Update(tableName, colValuePairs, where);
return(isUpdated);
}
public bool ChangeAccount(List <string> where)
{
bool isUpdated = false;
List <string> colValues = ConvertToColumnValues(this);
Dictionary <string, string> colValuePairs = new Dictionary <string, string>();
string tableName = "Account";
DataAccess.DataHandler dh = new DataAccess.DataHandler();
colValuePairs.Add("username", colValues[0]);
colValuePairs.Add("AccPassword", colValues[1]);
colValuePairs.Add("AccPriority", colValues[2]);
colValuePairs.Add("isAdmin", colValues[3]);
isUpdated = dh.Update(tableName, colValuePairs, where);
return(isUpdated);
}
public bool AddAnswer()
{
bool isInserted = false;
string tableName = "Answer";
List <string> columns = new List <string>(), colValues = ConvertToColumnValues(this);
DataAccess.DataHandler dh = new DataAccess.DataHandler();
columns.Add("questionId");
columns.Add("Answer");
isInserted = dh.Insert(tableName, columns, colValues);
columns.Clear();
colValues.Clear();
return(isInserted);
}
public bool ChangeCourse(List <string> where)
{
bool isUpdated = false;
List <string> colValues = ConvertToColumnValues(this);
Dictionary <string, string> colValuePairs = new Dictionary <string, string>();
string tableName = "Course";
DataAccess.DataHandler dh = new DataAccess.DataHandler();
colValuePairs.Add("code", colValues[0]);
colValuePairs.Add("name", colValues[1]);
colValuePairs.Add("NQFLevel", colValues[2]);
colValuePairs.Add("CourseDescription", colValues[3]);
isUpdated = dh.Update(tableName, colValuePairs, where);
return(isUpdated);
}
public bool AddOutcome()
{
bool isInserted = false;
string tableName = "Outcome";
List <string> columns = new List <string>(), colValues = ConvertToColumnValues(this);
DataAccess.DataHandler dh = new DataAccess.DataHandler();
columns.Add("SubjectID");
columns.Add("name");
isInserted = dh.Insert(tableName, columns, colValues);
columns.Clear();
colValues.Clear();
return(isInserted);
}
public List <AccountModel> GetAccount(List <string> columns = null, List <string> where = null)
{
string tableName = "Account";
List <AccountModel> accounts = new List <AccountModel>();
List <string> records = new List <string>();
DataAccess.DataHandler dh = new DataAccess.DataHandler();
records = dh.Select(tableName, columns, where);
Dictionary <string, string> fieldValues = new Dictionary <string, string>();
foreach (string item in records)
{
fieldValues.Clear();
int counter = 1;
string[] fields = item.Split(',');
foreach (string attribute in fields)
{
switch (counter)
{
case 1:
fieldValues.Add("id", attribute);
break;
case 2:
fieldValues.Add("username", attribute);
break;
case 3:
fieldValues.Add("AccPassword", attribute);
break;
case 4:
fieldValues.Add("AccPriority", attribute);
break;
case 5:
fieldValues.Add("isAdmin", attribute);
break;
}
counter++;
}
accounts.Add(ConvertToAccount(fieldValues));
}
return(accounts);
}
public List <QuestionModel> GetQuestion(List <string> columns = null, List <string> where = null)
{
string tableName = "Question";
List <QuestionModel> questions = new List <QuestionModel>();
List <string> records = new List <string>();
DataAccess.DataHandler dh = new DataAccess.DataHandler();
records = dh.Select(tableName, columns, where);
Dictionary <string, string> fieldValues = new Dictionary <string, string>();
foreach (string item in records)
{
fieldValues.Clear();
int counter = 1;
string[] fields = item.Split(',');
foreach (string attribute in fields)
{
switch (counter)
{
case 1:
fieldValues.Add("id", attribute);
break;
case 2:
fieldValues.Add("OutcomeDetailsID", attribute);
break;
case 3:
fieldValues.Add("QuestionTypeID", attribute);
break;
case 4:
fieldValues.Add("Questions", attribute);
break;
case 5:
fieldValues.Add("QuestionWeight", attribute);
break;
}
counter++;
}
questions.Add(ConvertToQuestion(fieldValues));
}
return(questions);
}
public bool ChangeLecturerCourseSubject(List <string> where)
{
bool isUpdated = false;
List <string> colValues = ConvertToColumnValues(this);
Dictionary <string, string> colValuePairs = new Dictionary <string, string>();
string tableName = "LecturerCourseSubject";
DataAccess.DataHandler dh = new DataAccess.DataHandler();
colValuePairs.Add("LecturerID", colValues[0]);
colValuePairs.Add("CourseSubjectID", colValues[1]);
colValuePairs.Add("startDate", colValues[2]);
colValuePairs.Add("endDate", colValues[3]);
colValuePairs.Add("isFlagged", colValues[4]);
isUpdated = dh.Update(tableName, colValuePairs, where);
return(isUpdated);
}
public bool ChangeSubjects(List <string> where)
{
bool isUpdated = false;
List <string> colValues = ConvertToColumnValues(this);
Dictionary <string, string> colValuePairs = new Dictionary <string, string>();
string tableName = "Subjects";
DataAccess.DataHandler dh = new DataAccess.DataHandler();
colValuePairs.Add("name", colValues[0]);
colValuePairs.Add("code", colValues[1]);
colValuePairs.Add("duration", colValues[2]);
colValuePairs.Add("SubjectWeight", colValues[3]);
colValuePairs.Add("SubjectDescription", colValues[4]);
isUpdated = dh.Update(tableName, colValuePairs, where);
return(isUpdated);
}
public static List <CourseModel> GetCourse(List <string> columns = null, List <string> where = null)
{
string tableName = "Course";
List <CourseModel> courses = new List <CourseModel>();
List <string> records = new List <string>();
DataAccess.DataHandler dh = new DataAccess.DataHandler();
records = dh.Select(tableName, columns, where);
Dictionary <string, string> fieldValues = new Dictionary <string, string>();
foreach (string item in records)
{
int counter = 1;
string[] fields = item.Split(',');
foreach (string attribute in fields)
{
switch (counter)
{
case 1:
fieldValues.Add("id", attribute);
break;
case 2:
fieldValues.Add("code", attribute);
break;
case 3:
fieldValues.Add("name", attribute);
break;
case 4:
fieldValues.Add("NQFLevel", attribute);
break;
case 5:
fieldValues.Add("CourseDescription", attribute);
break;
}
counter++;
}
courses.Add(ConvertToCourse(fieldValues));
}
return(courses);
}
public bool AddAccount()
{
bool isInserted = false;
string tableName = "Account";
List <string> columns = new List <string>(), colValues = ConvertToColumnValues(this);
DataAccess.DataHandler dh = new DataAccess.DataHandler();
columns.Add("username");
columns.Add("AccPassword");
columns.Add("AccPriority");
columns.Add("isAdmin");
isInserted = dh.Insert(tableName, columns, colValues);
columns.Clear();
colValues.Clear();
return(isInserted);
}
public bool AddQuestion(QuestionModel obj)
{
bool isInserted = false;
string tableName = "Question";
List <string> columns = new List <string>(), colValues = ConvertToColumnValues(obj);
DataAccess.DataHandler dh = new DataAccess.DataHandler();
columns.Add("OutcomeDetailsID");
columns.Add("QuestionTypeID");
columns.Add("Questions");
columns.Add("QuestionWeight");
isInserted = dh.Insert(tableName, columns, colValues);
columns.Clear();
colValues.Clear();
return(isInserted);
}
public bool ChangeLecturer(List <string> where)
{
bool isUpdated = false;
List <string> colValues = ConvertToColumnValues(this);
Dictionary <string, string> colValuePairs = new Dictionary <string, string>();
string tableName = "Lecturer";
DataAccess.DataHandler dh = new DataAccess.DataHandler();
colValuePairs.Add("AccountID", colValues[0]);
colValuePairs.Add("name", colValues[1]);
colValuePairs.Add("surname", colValues[2]);
colValuePairs.Add("cell", colValues[3]);
colValuePairs.Add("email", colValues[4]);
colValuePairs.Add("LecturerLevel", colValues[5]);
isUpdated = dh.Update(tableName, colValuePairs, where);
return(isUpdated);
}
public bool AddCourse()
{
bool isInserted = false;
string tableName = "Course";
List <string> columns = new List <string>(), colValues = ConvertToColumnValues(this);
DataAccess.DataHandler dh = new DataAccess.DataHandler();
columns.Add("code");
columns.Add("name");
columns.Add("NQFLevel");
columns.Add("CourseDescription");
isInserted = dh.Insert(tableName, columns, colValues);
columns.Clear();
colValues.Clear();
return(isInserted);
}
public static List <PracticalWeightModel> Select(string question)
{
DataAccess.DataHandler datahandler = new DataAccess.DataHandler();
List <PracticalWeightModel> practicalWeightsList = new List <PracticalWeightModel>();
DataTable datatable = new DataTable();
string query = "select * from PracticalWeight where QuestionId = " + question;
using (datatable = datahandler.Select(query))
{
foreach (DataRow item in datatable.Rows)
{
practicalWeightsList.Add(new PracticalWeightModel(int.Parse(item["id"].ToString()), int.Parse(item["QuestionId"].ToString()), int.Parse(item["practicalQuestionWeight"].ToString()), item["QuestionBreakDown"].ToString()));
}
}
return(practicalWeightsList);
}
public bool AddLecturerCourseSubject()
{
bool isInserted = false;
string tableName = "LecturerCourseSubject";
List <string> columns = new List <string>(), colValues = ConvertToColumnValues(this);
DataAccess.DataHandler dh = new DataAccess.DataHandler();
columns.Add("LecturerID");
columns.Add("CourseSubjectID");
columns.Add("startDate");
columns.Add("endDate");
columns.Add("isFlagged");
isInserted = dh.Insert(tableName, columns, colValues);
columns.Clear();
colValues.Clear();
return(isInserted);
}
public bool AddSubject()
{
bool isInserted = false;
string tableName = "Subjects";
List <string> columns = new List <string>(), colValues = ConvertToColumnValues(this);
DataAccess.DataHandler dh = new DataAccess.DataHandler();
columns.Add("name");
columns.Add("code");
columns.Add("duration");
columns.Add("SubjectWeight");
columns.Add("SubjectDescription");
isInserted = dh.Insert(tableName, columns, colValues);
columns.Clear();
colValues.Clear();
return(isInserted);
}
public bool AddLecturer()
{
bool isInserted = false;
string tableName = "Lecturer";
List <string> columns = new List <string>(), colValues = ConvertToColumnValues(this);
DataAccess.DataHandler dh = new DataAccess.DataHandler();
columns.Add("AccountID");
columns.Add("name");
columns.Add("surname");
columns.Add("cell");
columns.Add("email");
columns.Add("LecturerLevel");
isInserted = dh.Insert(tableName, columns, colValues);
columns.Clear();
colValues.Clear();
return(isInserted);
}
public static List <AnswerModel> GetAnswer(List <string> columns = null, List <string> where = null)
{
string tableName = "Answer";
List <AnswerModel> answers = new List <AnswerModel>();
List <string> records = new List <string>();
DataAccess.DataHandler dh = new DataAccess.DataHandler();
records = dh.Select(tableName, columns, where);
Dictionary <string, string> fieldValues = new Dictionary <string, string>();
foreach (string item in records)
{
fieldValues.Clear();
int counter = 1;
string[] fields = item.Split(',');
foreach (string attribute in fields)
{
switch (counter)
{
case 1:
fieldValues.Add("id", attribute);
break;
case 2:
fieldValues.Add("questionId", attribute);
break;
case 3:
fieldValues.Add("Answer", attribute);
break;
}
counter++;
}
answers.Add(ConvertToAnswer(fieldValues));
}
return(answers);
}
public static List <OutcomeModel> GetOutcome(List <string> columns = null, List <string> where = null)
{
string tableName = "Outcome";
List <OutcomeModel> outcomes = new List <OutcomeModel>();
List <string> records = new List <string>();
DataAccess.DataHandler dh = new DataAccess.DataHandler();
records = dh.Select(tableName, columns, where);
Dictionary <string, string> fieldValues = new Dictionary <string, string>();
foreach (string item in records)
{
fieldValues.Clear();
int counter = 1;
string[] fields = item.Split(',');
foreach (string attribute in fields)
{
switch (counter)
{
case 1:
fieldValues.Add("id", attribute);
break;
case 2:
fieldValues.Add("SubjectID", attribute);
break;
case 3:
fieldValues.Add("name", attribute);
break;
}
counter++;
}
outcomes.Add(ConvertToOutcome(fieldValues));
}
return(outcomes);
}
