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