Esempio n. 1
0
        /// <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);
        }
Esempio n. 2
0
        /// <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);
        }
Esempio n. 3
0
        /// <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);
        }