public void addSRQ(ShortResponse question)
{
OracleCommand command = connection.CreateCommand();
command.Parameters.Add("paramQuestion", OracleDbType.Varchar2, question.question, ParameterDirection.Input);
command.CommandText = "INSERT INTO SRQS 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();
command = connection.CreateCommand();
command.Parameters.Add("paramKeyword", OracleDbType.Varchar2, question.keyword, ParameterDirection.Input);
command.CommandText = "INSERT INTO SRQ_KEYWORDS VALUES(SRQK_SEQ.nextval, :1)";
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
command = connection.CreateCommand();
command.CommandText = "INSERT INTO SRQ_QUESTIONS_AND_KEYWORDS VALUES(QUESTIONS_SEQ.currval, SRQK_SEQ.currval)";
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
public void updateSRQ(ShortResponse question)
{
OracleCommand command = connection.CreateCommand();
command.Parameters.Add("paramQuestion", OracleDbType.Varchar2, question.question, ParameterDirection.Input);
command.CommandText = "UPDATE SRQS SET QUESTION=:1 WHERE SRQ_ID=" + question.questionID;
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
command = connection.CreateCommand();
command.Parameters.Add("paramKeyword", OracleDbType.Varchar2, question.keyword, ParameterDirection.Input);
command.CommandText = "UPDATE SRQ_KEYWORDS SET KEYWORD=:1 WHERE SRQK_ID=" + question.keywordID;
command.Connection = this.connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
public ShortResponse getSRQ(int questionID)
{
ShortResponse question = null;
OracleCommand command = new OracleCommand("SELECT SRQ_ID, QUESTION, CREATION_DATE FROM SRQS WHERE SRQ_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 ShortResponse(key, b, Membership.GetUser().UserName, reader.GetDateTime(2));
}
command = new OracleCommand("SELECT SRQ_QUESTIONS_AND_KEYWORDS.SRQK_ID, KEYWORD FROM SRQ_QUESTIONS_AND_KEYWORDS LEFT JOIN SRQ_KEYWORDS ON SRQ_QUESTIONS_AND_KEYWORDS.SRQK_ID=SRQ_KEYWORDS.SRQK_ID WHERE SRQ_QUESTIONS_AND_KEYWORDS.SRQ_ID=" + questionID);
command.Connection = this.connection;
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
if (reader.Read()) {
OracleDecimal a = reader.GetDecimal(0);
int key = a.ToInt32();
String b = reader.GetString(1);
question.keywordID = key;
question.keyword = b;
}
return question;
}
