public void addMCQ(MultipleChoice question)
{
OracleCommand command = connection.CreateCommand();
command.Parameters.Add("paramQuestion", OracleDbType.Varchar2, question.question, ParameterDirection.Input);
command.CommandText = "INSERT INTO MCQS VALUES(QUESTIONS_SEQ.nextval, :1, '" + question.creatorName + "', to_date('" + question.creationTime.ToString() + "', 'DD/MM/YYYY HH:MI:SS AM'))";
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
IList<Tuple<int, String, Boolean>> answers = question.getAnswers();
foreach (var item in answers) {
if (item.Item3) {
command = connection.CreateCommand();
command.Parameters.Add("paramAnswer", OracleDbType.Varchar2, item.Item2, ParameterDirection.Input);
command.CommandText = "INSERT INTO MCQ_ANSWERS VALUES(ANSWERS_SEQ.nextval, :1)";
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
command = new OracleCommand("INSERT INTO MCQ_QUESTIONS_AND_ANSWERS VALUES(QUESTIONS_SEQ.currval, ANSWERS_SEQ.currval, 1, 'Y')");
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
else {
command = connection.CreateCommand();
command.Parameters.Add("paramAnswer", OracleDbType.Varchar2, item.Item2, ParameterDirection.Input);
command.CommandText = "INSERT INTO MCQ_ANSWERS VALUES(ANSWERS_SEQ.nextval, :1)";
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
command = new OracleCommand("INSERT INTO MCQ_QUESTIONS_AND_ANSWERS VALUES(QUESTIONS_SEQ.currval, ANSWERS_SEQ.currval, 1, 'N')");
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
}
}
public MultipleChoice getMCQ(int questionID)
{
MultipleChoice question = null;
OracleCommand command = new OracleCommand("SELECT MCQ_ID, QUESTION, CREATION_DATE FROM MCQS WHERE MCQ_ID=" + questionID);
command.Connection = this.connection;
command.CommandType = CommandType.Text;
OracleDataReader reader = command.ExecuteReader();
if (reader.Read()) {
OracleDecimal a = reader.GetDecimal(0);
int key = a.ToInt32();
String b = reader.GetString(1);
question = new MultipleChoice(key, b, Membership.GetUser().UserName, reader.GetDateTime(2));
}
command = new OracleCommand("SELECT MCQ_QUESTIONS_AND_ANSWERS.MCQA_ID, ANSWER, IS_CORRECT FROM MCQ_QUESTIONS_AND_ANSWERS LEFT JOIN MCQ_ANSWERS ON MCQ_QUESTIONS_AND_ANSWERS.MCQA_ID=MCQ_ANSWERS.MCQA_ID WHERE MCQ_QUESTIONS_AND_ANSWERS.MCQ_ID=" + questionID);
command.Connection = this.connection;
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
while (reader.Read()) {
OracleDecimal a = reader.GetDecimal(0);
int key = a.ToInt32();
String b = reader.GetString(1);
String correct = reader.GetString(2);
if (correct.Equals("Y")) {
question.addAnswer(key, b, true);
}
else {
question.addAnswer(key, b, false);
}
}
return question;
}
public void updateMCQ(MultipleChoice question)
{
OracleCommand command = connection.CreateCommand();
command.Parameters.Add("paramQuestion", OracleDbType.Varchar2, question.question, ParameterDirection.Input);
command.CommandText = "UPDATE MCQS SET QUESTION=:1 WHERE MCQ_ID=" + question.questionID;
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
IList<Tuple<int, String, Boolean>> answers = question.getAnswers();
foreach (var item in answers) {
if (item.Item1 < 0) {
if (item.Item3) {
command = connection.CreateCommand();
command.Parameters.Add("paramAnswer", OracleDbType.Varchar2, item.Item2, ParameterDirection.Input);
command.CommandText = "INSERT INTO MCQ_ANSWERS VALUES(ANSWERS_SEQ.nextval, :1)";
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
command = new OracleCommand("INSERT INTO MCQ_QUESTIONS_AND_ANSWERS VALUES(" + question.questionID + ", ANSWERS_SEQ.currval, 1, 'Y')");
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
else {
command = connection.CreateCommand();
command.Parameters.Add("paramAnswer", OracleDbType.Varchar2, item.Item2, ParameterDirection.Input);
command.CommandText = "INSERT INTO MCQ_ANSWERS VALUES(ANSWERS_SEQ.nextval, :1)";
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
command = new OracleCommand("INSERT INTO MCQ_QUESTIONS_AND_ANSWERS VALUES(" + question.questionID + ", ANSWERS_SEQ.currval, 1, 'N')");
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
}
else {
if (item.Item3) {
command = connection.CreateCommand();
command.Parameters.Add("paramAnswer", OracleDbType.Varchar2, item.Item2, ParameterDirection.Input);
command.CommandText = "UPDATE MCQ_ANSWERS SET ANSWER=:1 WHERE MCQA_ID=" + item.Item1;
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
command = new OracleCommand("UPDATE MCQ_QUESTIONS_AND_ANSWERS SET IS_CORRECT='Y' WHERE MCQ_ID=" + question.questionID + " AND MCQA_ID=" + item.Item1);
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
else {
command = connection.CreateCommand();
command.Parameters.Add("paramAnswer", OracleDbType.Varchar2, item.Item2, ParameterDirection.Input);
command.CommandText = "UPDATE MCQ_ANSWERS SET ANSWER=:1 WHERE MCQA_ID=" + item.Item1;
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
command = new OracleCommand("UPDATE MCQ_QUESTIONS_AND_ANSWERS SET IS_CORRECT='N' WHERE MCQ_ID=" + question.questionID + " AND MCQA_ID=" + item.Item1);
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
}
}
}