public async Task <int> InsertQuestionnaireAsync(QuestionnaireSubmit model)
{
using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var questionnaireId = 0;
using (var transaction = await connection.BeginTransactionAsync())
{
var insertQuestionnaire = @"INSERT INTO questionnaires (user_id, is_completed, last_updated)
OUTPUT INSERTED.[id]
VALUES (@userId, @isCompleted, @lastUpdated)";
var insertQuestionnaireQuestion = @"INSERT INTO questionnaire_questions (questionnaire_id, question_group_id, question_id)
OUTPUT INSERTED.[id]
VALUES (@questionnaireId, @questionGroupId, @questionId)";
var insertQuestionnaireAnswers = @"INSERT INTO questionnaire_answers (questionnaire_question_id, answer_id, answer_text)
OUTPUT INSERTED.[id]
VALUES (@questionnaireQuestionId, @answerId, @answerText)";
questionnaireId = await connection.ExecuteScalarAsync <int>(insertQuestionnaire,
new { userId = model.UserId, isCompleted = model.IsCompleted, lastUpdated = model.LastUpdated }, transaction);
foreach (var qa in model.QuestionAnswers)
{
var qustionnaireQuestionId = await connection.ExecuteScalarAsync <int>(insertQuestionnaireQuestion,
new { questionnaireId, questionGroupId = model.Id, questionId = qa.Id }, transaction);
foreach (var answer in qa.Answers)
{
await connection.ExecuteAsync(insertQuestionnaireAnswers,
new { questionnaireQuestionId = qustionnaireQuestionId, answerId = answer, answerText = default(string) }, transaction);
}
if (!string.IsNullOrWhiteSpace(qa.Text))
{
await connection.ExecuteAsync(insertQuestionnaireAnswers,
new { questionnaireQuestionId = qustionnaireQuestionId, answerId = default(int?), answerText = qa.Text }, transaction);
}
}
await transaction.CommitAsync();
}
return(questionnaireId);
}
public async Task <int> SubmitAnswersAsync(QuestionnaireSubmission model)
{
var entity = new QuestionnaireSubmit
{
Id = model.Id,
UserId = model.UserId,
IsCompleted = model.IsCompleted,
LastUpdated = DateTime.Now,
QuestionAnswers = model.QuestionAnswers.Select(x => new QuestionnaireQuestionAnswer
{
Id = x.Id,
Text = x.Text,
Answers = x.Answers
}).ToList()
};
return(await _repository.InsertQuestionnaireAsync(entity));
}
