/// <summary> /// Opens a QuizInstance of a quiz and returns it /// </summary> /// <param name="quiz">The Quiz that is to be opened</param> /// <param name="user">The User who opens it</param> internal QuizInstance OpenQuiz(Quiz quiz, User user) { QuizInstance quizInstance = new QuizInstance(); quizInstance.Owner = user; quizInstance.Quiz = quiz; quizInstance.StartDate = DateTime.Now; quizInstance.Open = true; quizInstance.Running = false; var command = new SqlCommand(); command.CommandText = @"INSERT INTO QuizInstance(UserId, QuizId, StartDate, [Open], Running) " + "VALUES (@userId, @quizId, @startDate, @open, @running); Select Scope_Identity()"; command.Parameters.AddWithValue("@userId", user.Id); command.Parameters.AddWithValue("@quizId", quiz.Id); command.Parameters.AddWithValue("@startDate", quizInstance.StartDate); command.Parameters.AddWithValue("@open", quizInstance.Open); command.Parameters.AddWithValue("@running", quizInstance.Running); quizInstance.Id = repository.ExecuteStatement(command); //If an error occurs, return null instead if (quizInstance.Id == 0) { quizInstance = null; } return(quizInstance); }
/// <summary> /// Saves recieved Answers and returns the id of the correct Answer. /// </summary> /// <param name="question">The question the answers are connected to</param> /// <returns>The id of the correct answer</returns> public int SaveAnswers(Question question) { int returnKey = -1; using (TransactionScope scope = new TransactionScope()) { foreach (Answer answer in question.Answers) { SqlCommand command = new SqlCommand(); command.CommandText = @"INSERT INTO Answer(Text, QuestionId) VALUES (@Text, @QuestionId);"; SetParametres(command, question.Id, answer); if (answer.Equals(question.CorrectAnswer)) { command.CommandText += " Select Scope_Identity()"; //The Id of the correct Answer is saved returnKey = repository.ExecuteStatement(command); } else { repository.ExecuteStatement(command); } } // The Complete method commits the transaction. If an exception has been thrown, // Complete is not called and the transaction is rolled back. scope.Complete(); } return(returnKey); }
/// <summary> /// Tests saving quiz to database /// </summary> /// <param name="quiz"></param> public void SaveQuiz(Quiz quiz) { var command = new SqlCommand(); command.CommandText = @"INSERT INTO Quiz(Title, Description, CreateDate, UpdatedDate, AuthorId) VALUES (@Title, @Description, @CreateDate, @UpdatedDate, @AuthorId)"; command.Parameters.AddWithValue("@Title", quiz.Title); command.Parameters.AddWithValue("@Description", quiz.Description); command.Parameters.AddWithValue("@CreateDate", quiz.CreateDate); command.Parameters.AddWithValue("@UpdatedDate", quiz.UpdatedDate); command.Parameters.AddWithValue("@AuthorId", quiz.Author.Id); repository.ExecuteStatement(command); }
/// <summary> /// Saves a Question in the DB. Passes attached Answers along to DBAnswer. /// DBAnswer returns the id for the correct Answer. Finaly, Question is updated with correct AnswerId. /// The Connection is created here, and the Transaction handled here, not in DBAnswer. /// </summary> /// <param name="question">The question that is to be inserted</param> public void SaveQuestion(Question question) { using (TransactionScope scope = new TransactionScope()) { SqlCommand command = new SqlCommand(); command.CommandText = @"INSERT INTO Question(Header, Text, CreateDate) VALUES (@Header, @Text, @CreateDate); Select Scope_Identity()"; command.Parameters.AddWithValue("@Header", question.Header); command.Parameters.AddWithValue("@Text", question.Text); command.Parameters.AddWithValue("@CreateDate", question.CreateDate); //Returns Id for the newly inserted Question question.Id = repository.ExecuteStatement(command); if (question.Id < 1) { scope.Dispose(); return; } //Save the answers on the question int correctAnswerId = new DBAnswer().SaveAnswers(question); if (correctAnswerId < 1) { scope.Dispose(); return; } command.Dispose(); //update Question is updated with CorrectAnswerId command.CommandText = @"UPDATE Question SET [CorrectAnswer] = @CorrectAnswer WHERE Id = @id"; command.Parameters.AddWithValue("@id", question.Id); command.Parameters.AddWithValue("@CorrectAnswer", correctAnswerId); if (repository.ExecuteStatement(command) < 1) { scope.Dispose(); } scope.Complete(); } }
/// <summary> /// Inserts the given user into the database. OBS! Remember to hash the password first! /// </summary> /// <param name="user">The user that is to be inserted</param> public int InsertUser(User user) { var command = new SqlCommand(); command.CommandText = @"INSERT INTO [User] (Username, Password, Salt) VALUES (@username, @password, @salt)"; command.Parameters.AddWithValue("@username", user.Username); command.Parameters.AddWithValue("@password", user.Password); command.Parameters.AddWithValue("@salt", user.Salt); return(repository.ExecuteStatement(command)); }
/// <summary> /// Sets a time stamp on a QuestionInstance /// </summary> /// <param name="quizInstance">The instance, that is to be closed</param> public void SetQuestionInstanceTimeOut(int quizInstanceId, int questionId, DateTime time) { var command = new SqlCommand(); command.CommandText = @"UPDATE QuestionInstance SET TimeOut = @time WHERE QuizInstanceId = @quizInstanceId AND QuestionId = @questionId"; command.Parameters.AddWithValue("@time", time); command.Parameters.AddWithValue("@quizInstanceId", quizInstanceId); command.Parameters.AddWithValue("@questionId", questionId); repository.ExecuteStatement(command); }
/// <summary> /// Inserts the given contestant into the database. /// </summary> /// <param name="Contestant">The contestant that is to be inserted</param> public void InsertContestant(Contestant contestant, int quizInstanceId) { SqlCommand command = new SqlCommand(); //contestant.Name = Repository.TrimString(contestant.Name); //string commandText = @"INSERT INTO Contestant (Id, Name, QuizInstanceId) //VALUES ('"+contestant.Id+"', '"+contestant.Name+"', "+quizInstanceId+");"; command.CommandText = "INSERT INTO Contestant (Id, Name, QuizInstanceId) VALUES (@Id, @Name, @QuizInstanceId);"; //command.Parameters.Add("@Id", SqlDbType.UniqueIdentifier); //command.Parameters["@Id"].Value = Guid.NewGuid(); command.Parameters.AddWithValue("@Id", contestant.Id); command.Parameters.AddWithValue("@Name", contestant.Name); command.Parameters.AddWithValue("@QuizInstanceId", quizInstanceId); repository.ExecuteStatement(command); }