/// <summary> /// insert data into QuestionAnswer table /// </summary> /// <param name="db"></param> /// <param name="cmd"></param> /// <param name="question"></param> /// <param name="quizIdTemp"></param> /// <param name="questionId"></param> /// <returns></returns> public bool addQuestionTable(ConnectDB db, SqlCommand cmd, QuestionAnswerAccess question, int quizIdTemp, out int questionId) { cmd = new SqlCommand("AddQuestion"); cmd.CommandType = CommandType.StoredProcedure; SqlParameter RetValQ1 = cmd.Parameters.Add("RetValQ1", SqlDbType.Int); RetValQ1.Direction = ParameterDirection.ReturnValue; SqlParameter questionAnswerId = cmd.Parameters.Add("@questionAnswerId", SqlDbType.Int); questionAnswerId.Direction = ParameterDirection.Output; SqlParameter quizIdQ = cmd.Parameters.Add("@quizId", SqlDbType.Int); quizIdQ.Direction = ParameterDirection.Input; SqlParameter questionDescription = cmd.Parameters.Add("@questionDescription", SqlDbType.VarChar, 255); questionDescription.Direction = ParameterDirection.Input; SqlParameter type = cmd.Parameters.Add("@type", SqlDbType.VarChar, 35); type.Direction = ParameterDirection.Input; SqlParameter Answer = cmd.Parameters.Add("@Answer", SqlDbType.VarChar, 255); Answer.Direction = ParameterDirection.Input; quizIdQ.Value = quizIdTemp; if (question.QuestionDescription != null) { questionDescription.Value = question.QuestionDescription; } if (question.Type != null) { type.Value = question.Type; } if (question.Answer != default(string)) { Answer.Value = question.Answer; } db.changeData(cmd); questionId = (int)cmd.Parameters["@questionAnswerId"].Value; if ((int)cmd.Parameters["RetValQ1"].Value != 0) { return(false); } return(true); }
/// <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); }