/// <summary>
/// Get a quiz list for a module that customers want to select
/// </summary>
/// <param name="moduleId"></param>
/// <returns></returns>
public List <QuizTransfer> GetQuizForModule(int moduleId)
{
List <QuizTransfer> quizList = new List <QuizTransfer>();
string currentQuestionType;
int currentQuizId = 0;
int currentQuestionId = 0;
ConnectDB db = new ConnectDB();
SqlCommand cmd;
SqlDataReader dr;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT DISTINCT * FROM Quiz AS Q LEFT JOIN Question_Answer AS QA ON Q.quizId = QA.quizId"
+ " LEFT JOIN Qustion_Answer_Multiple_Selection AS QAMS ON QA.questionAnswerId = QAMS.questionAnswerId"
+ " WHERE Q.moduleId = @moduleId;";
cmd.Parameters.Add("@moduleId", SqlDbType.Int).Value = moduleId;
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
//add data to quiz table
//quizId, quizName, description, passingGrade
QuizTransfer quizTransfer = new QuizTransfer();
if (currentQuizId != Convert.ToInt32(dr[0]))
{
QuizAccess quiz = new QuizAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
quiz.QuizId = 0;
}
else
{
quiz.QuizId = Convert.ToInt32(dr[0]);
}
currentQuizId = quiz.QuizId;
if (System.DBNull.Value.Equals(dr[1]))
{
quiz.QuizName = null;
}
else
{
quiz.QuizName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
quiz.Description = null;
}
else
{
quiz.Description = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
quiz.PassingGrade = 0;
}
else
{
quiz.PassingGrade = Convert.ToInt32(dr[3]);
}
if (System.DBNull.Value.Equals(dr[4]))
{
quiz.ModuleId = 0;
}
else
{
quiz.ModuleId = Convert.ToInt32(dr[4]);
}
quizTransfer.QuizDescription = quiz;
//add quiz table to quizTransfer
if (currentQuizId != 0)
{
quizList.Add(quizTransfer);
}
}
//add data to Question_Answer table
//questionAnswerId, quizId, questionDescription, type, Answer
if (!System.DBNull.Value.Equals(dr[5]))
{
if (currentQuestionId != Convert.ToInt32(dr[5]))
{
QuestionAnswerAccess question = new QuestionAnswerAccess();
QuestionTransfer mulQuestion = new QuestionTransfer();
if (System.DBNull.Value.Equals(dr[5]))
{
question.QuestionAnswerId = 0;
}
else
{
question.QuestionAnswerId = Convert.ToInt32(dr[5]);
}
currentQuestionId = question.QuestionAnswerId;
if (System.DBNull.Value.Equals(dr[6]))
{
question.QuizId = 0;
}
else
{
question.QuizId = Convert.ToInt32(dr[6]);
}
if (System.DBNull.Value.Equals(dr[7]))
{
question.QuestionDescription = null;
}
else
{
question.QuestionDescription = Convert.ToString(dr[7]);
}
if (System.DBNull.Value.Equals(dr[8]))
{
question.Type = null;
}
else
{
question.Type = Convert.ToString(dr[8]);
}
currentQuestionType = question.Type;
if (System.DBNull.Value.Equals(dr[9]))
{
question.Answer = null;
}
else
{
question.Answer = Convert.ToString(dr[9]);
}
if (currentQuestionType == "bool")
{
quizList.Last().BoolQuestion.Add(question);
quizList.Last().BoolQuestionNumber++;
}
else
{
mulQuestion.QuestionAnswer = question;
quizList.Last().MulSelQuestion.Add(mulQuestion);
quizList.Last().MulSelQuestionNumber++;
}
}
}
//add data to Qustion_Answer_Multiple_Selection table
//mulSelectionId, questionAnswerId, choiceDescription, choiceSequenceNumber
if (!System.DBNull.Value.Equals(dr[10]))
{
QuestionSelectItem selectItem = new QuestionSelectItem();
selectItem.MulSelectionId = Convert.ToInt32(dr[10]);
if (System.DBNull.Value.Equals(dr[11]))
{
selectItem.QuestionAnswerId = 0;
}
else
{
selectItem.QuestionAnswerId = Convert.ToInt32(dr[11]);
}
if (System.DBNull.Value.Equals(dr[12]))
{
selectItem.ChoiceDescription = null;
}
else
{
selectItem.ChoiceDescription = Convert.ToString(dr[12]);
}
if (System.DBNull.Value.Equals(dr[13]))
{
selectItem.ChoiceSequenceNumber = null;
}
else
{
selectItem.ChoiceSequenceNumber = Convert.ToString(dr[13]);
}
quizList.Last().MulSelQuestion.Last().SelectionItems.Add(selectItem);
quizList.Last().MulSelQuestion.Last().ItemsNumber++;
}
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
db.Close();
return(quizList);
}
/// <summary>
///
/// </summary>
/// <param name="quiz"></param>
/// <returns></returns>
public bool UpdateQuiz(QuizTransfer quiz)
{
ConnectDB db = new ConnectDB();
SqlCommand cmd;
int boolQuestionNumber = quiz.BoolQuestionNumber;
int mulQuestionNumber = quiz.MulSelQuestionNumber;
int itemNumber = 0;
//update data in Quiz table
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE Quiz SET quizName=@quizName, description=@description, passingGrade=@passingGrade, moduleId=@moduleId WHERE quizId=@quizId;";
cmd.Parameters.AddWithValue("@quizName", quiz.QuizDescription.QuizName);
cmd.Parameters.AddWithValue("@description", quiz.QuizDescription.Description);
cmd.Parameters.AddWithValue("@passingGrade", quiz.QuizDescription.PassingGrade);
cmd.Parameters.AddWithValue("@moduleId", quiz.QuizDescription.ModuleId);
cmd.Parameters.AddWithValue("@quizId", quiz.QuizDescription.QuizId);
db.changeData(cmd);
//update data in bool question table
for (int i = 0; i < boolQuestionNumber; i++)
{
cmd.CommandText = "UPDATE Question_Answer SET quizId="
+ "@quizIdbool" + i
+ ", questionDescription=@questionDescriptionbool" + i
+ ", type=@typebool" + i
+ ", Answer=@Answerbool" + i
+ " WHERE questionAnswerId=@questionAnswerIdbool" + i
+ ";";
cmd.Parameters.AddWithValue("@quizIdbool" + i, quiz.BoolQuestion.ElementAt(i).QuizId);
cmd.Parameters.AddWithValue("@questionDescriptionbool" + i, quiz.BoolQuestion.ElementAt(i).QuestionDescription);
cmd.Parameters.AddWithValue("@typebool" + i, quiz.BoolQuestion.ElementAt(i).Type);
cmd.Parameters.AddWithValue("@Answerbool" + i, quiz.BoolQuestion.ElementAt(i).Answer);
cmd.Parameters.AddWithValue("@questionAnswerIdbool" + i, quiz.BoolQuestion.ElementAt(i).QuestionAnswerId);
db.changeData(cmd);
}
//update data in multiple question table
for (int j = 0; j < mulQuestionNumber; j++)
{
cmd.CommandText = "UPDATE Question_Answer SET quizId=@quizIdmul" + j
+ ", questionDescription=@questionDescriptionmul" + j
+ ", type=@typemul" + j
+ ", Answer=@Answermul" + j
+ " WHERE questionAnswerId=@questionAnswerIdmul" + j
+ ";";
cmd.Parameters.AddWithValue("@quizIdmul" + j, quiz.MulSelQuestion.ElementAt(j).QuestionAnswer.QuizId);
cmd.Parameters.AddWithValue("@questionDescriptionmul" + j, quiz.MulSelQuestion.ElementAt(j).QuestionAnswer.QuestionDescription);
cmd.Parameters.AddWithValue("@typemul" + j, quiz.MulSelQuestion.ElementAt(j).QuestionAnswer.Type);
cmd.Parameters.AddWithValue("@Answermul" + j, quiz.MulSelQuestion.ElementAt(j).QuestionAnswer.Answer);
cmd.Parameters.AddWithValue("@questionAnswerIdmul" + j, quiz.MulSelQuestion.ElementAt(j).QuestionAnswer.QuestionAnswerId);
db.changeData(cmd);
itemNumber = quiz.MulSelQuestion.ElementAt(j).ItemsNumber;
//update data in Qustion_Answer_Multiple_Selection table
for (int k = 0; k < itemNumber; k++)
{
cmd.CommandText = "UPDATE Qustion_Answer_Multiple_Selection SET questionAnswerId=@questionAnswerId" + j + k
+ ", choiceDescription=@choiceDescription" + j + k
+ ", choiceSequenceNumber=@choiceSequenceNumber" + j + k
+ " WHERE mulSelectionId=@mulSelectionId" + j + k
+ ";";
cmd.Parameters.AddWithValue("@questionAnswerId" + j + k, quiz.MulSelQuestion.ElementAt(j).SelectionItems.ElementAt(k).QuestionAnswerId);
cmd.Parameters.AddWithValue("@choiceDescription" + j + k, quiz.MulSelQuestion.ElementAt(j).SelectionItems.ElementAt(k).ChoiceDescription);
cmd.Parameters.AddWithValue("@choiceSequenceNumber" + j + k, quiz.MulSelQuestion.ElementAt(j).SelectionItems.ElementAt(k).ChoiceSequenceNumber);
cmd.Parameters.AddWithValue("@mulSelectionId" + j + k, quiz.MulSelQuestion.ElementAt(j).SelectionItems.ElementAt(k).MulSelectionId);
db.changeData(cmd);
}
}
db.Close();
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="quiz"></param>
/// <returns></returns>
public bool AddQuiz(QuizTransfer quiz)
{
ConnectDB db = new ConnectDB();
SqlCommand cmd;
Int32 quizIdTemp = 0;
//insert a new record to Quiz table
cmd = new SqlCommand("AddQuiz");
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter RetVal = cmd.Parameters.Add("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
SqlParameter quizId = cmd.Parameters.Add("@quizId", SqlDbType.Int);
quizId.Direction = ParameterDirection.Output;
SqlParameter moduleId = cmd.Parameters.Add("@moduleId", SqlDbType.Int);
moduleId.Direction = ParameterDirection.Input;
SqlParameter passingGrade = cmd.Parameters.Add("@passingGrade", SqlDbType.Int);
passingGrade.Direction = ParameterDirection.Input;
SqlParameter quizName = cmd.Parameters.Add("@quizName", SqlDbType.VarChar, 512);
quizName.Direction = ParameterDirection.Input;
SqlParameter description = cmd.Parameters.Add("@description", SqlDbType.VarChar, 512);
description.Direction = ParameterDirection.Input;
if (quiz.QuizDescription.QuizName != null)
{
quizName.Value = quiz.QuizDescription.QuizName;
}
if (quiz.QuizDescription.Description != null)
{
description.Value = quiz.QuizDescription.Description;
}
passingGrade.Value = quiz.QuizDescription.PassingGrade;
if (quiz.QuizDescription.ModuleId != default(Int32))
{
moduleId.Value = quiz.QuizDescription.ModuleId;
}
db.changeData(cmd);
quizIdTemp = (int)quizId.Value;
//insert new records into QuestionAnswer table
if ((int)cmd.Parameters["RetVal"].Value == 0)
{
//insert bool question data into database
for (int i = 0; i < quiz.BoolQuestionNumber; i++)
{
bool blReturn = false;
int questionId;
blReturn = addQuestionTable(db, cmd, quiz.BoolQuestion.ElementAt(i), quizIdTemp, out questionId);
if (blReturn == false)
{
db.Close();
return(false);
}
}
//insert multiselection question data into database
for (int i = 0; i < quiz.MulSelQuestionNumber; i++)
{
bool blReturn = false;
int questionId;
blReturn = addQuestionTable(db, cmd, quiz.MulSelQuestion.ElementAt(i).QuestionAnswer, quizIdTemp, out questionId);
if (blReturn == false)
{
db.Close();
return(false);
}
//insert selection items data into database
int itemNumber = quiz.MulSelQuestion.ElementAt(i).ItemsNumber;
for (int j = 0; j < itemNumber; j++)
{
string questionDescription = quiz.MulSelQuestion.ElementAt(i).QuestionAnswer.QuestionDescription;
string type = quiz.MulSelQuestion.ElementAt(i).QuestionAnswer.Type;
string answer = quiz.MulSelQuestion.ElementAt(i).QuestionAnswer.Answer;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO Qustion_Answer_Multiple_Selection(questionAnswerId,choiceDescription,choiceSequenceNumber) VALUES "
+ "(@questionId" + i + j
+ ", @choiceDescription" + i + j
+ ", @choiceSequenceNumber" + i + j
+ ");";
cmd.Parameters.AddWithValue("@questionId" + i + j, questionId);
cmd.Parameters.AddWithValue("@choiceDescription" + i + j, quiz.MulSelQuestion.ElementAt(i).SelectionItems.ElementAt(j).ChoiceDescription);
cmd.Parameters.AddWithValue("@choiceSequenceNumber" + i + j, quiz.MulSelQuestion.ElementAt(i).SelectionItems.ElementAt(j).ChoiceSequenceNumber);
db.changeData(cmd);
}
}
}
else
{
db.Close();
return(false);
}
db.Close();
return(true);
}