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