public static List <Quiz> GetQuizByUserID(int subID) { QuizTableAdapter quizData = new QuizTableAdapter(); QuestionTableAdapter questionData = new QuestionTableAdapter(); AnswerTableAdapter answerData = new AnswerTableAdapter(); List <Quiz> quizzes = new List <Quiz>(); List <Question> questions = new List <Question>(); List <Answer> answers = new List <Answer>(); foreach (MultipleChoiceDataSet.QuizRow q in quizData.GetData()) { if (q.By_user == subID) { quizzes.Add(new Quiz(q.Title, q.Subject_id, q.By_user)); foreach (MultipleChoiceDataSet.QuestionRow question in questionData.GetDataByQuizID(q.Quiz_id)) { if (question.Quiz_id == q.Quiz_id) { Question tempq = new Question(question.Question, question.By_user, question.Subject_id, DateTime.Now); //questions.Add(new Question(question.Question, question.By_user, question.Subject_id, DateTime.Now)); foreach (MultipleChoiceDataSet.AnswerRow an in answerData.GetDataBy(question.Question_id)) { //answers.Add(new Answer(an.Answer,an.Correct)); tempq.AddAnswer(new Answer(an.Answer, an.Correct)); } quizzes[quizzes.Count - 1].AddQuestion(tempq, question.Subject_id); } } } } return(quizzes); }
/*public static void AddQuestionWithAnswersToDb(QuestionWithAnswers questionWithAnswers, long quizId) { AddQuestionToDb(questionWithAnswers.QuestionText, quizId); var questionId = GetIdOfQuestionText(questionWithAnswers.QuestionText); using (var connection = new SqlConnection(ConnectionString)) { connection.Open(); foreach (var answer in questionWithAnswers.Answers) { var answerCmd = new SqlCommand( "INSERT INTO Answer (Text, IsCorrect, QuestionId) VALUES (@Text, @IsCorrect, @QuestionId)", connection); answerCmd.Parameters.Add(@"Text", SqlDbType.VarChar, 250).Value = answer.answerText; answerCmd.Parameters.Add(@"IsCorrect", SqlDbType.Bit).Value = answer.isCorrect; answerCmd.Parameters.Add(@"QuestionId", SqlDbType.BigInt).Value = questionId; answerCmd.Prepare(); answerCmd.ExecuteNonQuery(); } } }*/ public static void AddQuestionToDb(string questionText, long quizId) { var tableAdapter = new QuestionTableAdapter(); tableAdapter.Insert(questionText, quizId); /*using (var connection = new SqlConnection(ConnectionString)) { connection.Open(); var questionCmd = new SqlCommand("INSERT INTO Question (Text, QuizId) VALUES (@Text, @QuizId)", connection); questionCmd.Parameters.Add(@"Text", SqlDbType.VarChar, 250).Value = questionText; questionCmd.Parameters.Add(@"QuizId", SqlDbType.BigInt).Value = quizId; questionCmd.Prepare(); questionCmd.ExecuteNonQuery(); }*/ }
public static void DeleteQuestionWithAnswersFromDb(long questionId) { var tableAdapter = new QuestionTableAdapter(); tableAdapter.DeleteQuestionWithAnswers(questionId); /*Old version with stored procedure, may be removed if tableAdapter works as it should. using (var connection = new SqlConnection(ConnectionString)) { connection.Open(); var cmd = new SqlCommand("DeleteQuestionWithAnswers", connection) {CommandType = CommandType.StoredProcedure}; cmd.Parameters.Add("@questionid", SqlDbType.BigInt).Value = questionId; cmd.Prepare(); cmd.ExecuteNonQuery(); }*/ }
public static List<QuestionWithAnswers> GetQuestions(long quizId) { var tableAdapter = new QuestionTableAdapter(); var dataTable = tableAdapter.GetQuestions(quizId); var questions = new List<QuestionWithAnswers>(); foreach (var row in dataTable) { var question = new QuestionWithAnswers { Id = row.QuestionId, QuestionText = row.Text, QuizId = row.QuizId }; questions.Add(question); } return questions; }
public static QuestionWithAnswers GetNextQuestion(long quizId, long previousQuestionId) { var tableAdapter = new QuestionTableAdapter(); var dataTable = tableAdapter.GetNextQuestion(previousQuestionId, quizId); foreach (var row in dataTable) { var questionWithAnswers = new QuestionWithAnswers { Id = row.QuestionId, QuestionText = row.Text, QuizId = quizId }; return questionWithAnswers; } return null; /*using (var connection = new SqlConnection(ConnectionString)) { var questionWithAnswers = new QuestionWithAnswers(); connection.Open(); var questionCmd = new SqlCommand("SELECT * FROM Question WHERE QuestionId > @QuestionId AND QuizId = @QuizId", connection); questionCmd.Parameters.Add(@"QuestionId", SqlDbType.BigInt).Value = previousQuestionId; questionCmd.Parameters.Add(@"QuizId", SqlDbType.BigInt).Value = quizId; questionCmd.Prepare(); var reader = questionCmd.ExecuteReader(); while (reader.Read()) { questionWithAnswers.Id = (long) reader["QuestionId"]; questionWithAnswers.QuestionText = (string) reader["Text"]; questionWithAnswers.QuizId = (long) reader["QuizId"]; return questionWithAnswers; } } return null;*/ }
public static long GetIdOfQuestionText(string questionText) { var tableAdapter = new QuestionTableAdapter(); var datatable = tableAdapter.GetIdFromQuestionText(questionText); foreach(var row in datatable){ return row.QuestionId; } return -1; /*using (var connection = new SqlConnection(ConnectionString)) { connection.Open(); var questionCmd = new SqlCommand("SELECT QuestionId FROM Question WHERE Text = @Text", connection); questionCmd.Parameters.Add(@"Text", SqlDbType.VarChar, 250).Value = questionText; questionCmd.Prepare(); var reader = questionCmd.ExecuteReader(); while (reader.Read()) { return (long) reader["QuestionId"]; } return -1; }*/ }