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