/// <summary>
/// Import the given questions into the DB
/// </summary>
public void ImportQuestions(NSurveyQuestion importQuestions, int userId)
{
SqlConnection sqlConnection = new SqlConnection(DbConnection.NewDbConnectionString);
sqlConnection.Open();
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
SqlCommand insertCommand = new AnswerType().GetInsertAnswerTypeCommand(sqlConnection, sqlTransaction, userId);
SqlCommand command2 = new RegularExpression().GetInsertRegularExpressionCommand(sqlConnection, sqlTransaction, userId);
SqlCommand insertQuestionCommand = this.GetInsertQuestionCommand(sqlConnection, sqlTransaction);
SqlCommand insertChildQuestionCommand = this.GetInsertChildQuestionCommand(sqlConnection, sqlTransaction);
SqlCommand insertAnswerCommand = new Answer().GetInsertAnswerCommand(sqlConnection, sqlTransaction);
SqlCommand insertAnswerConnectionCommand = new Answer().GetInsertAnswerConnectionCommand(sqlConnection, sqlTransaction);
SqlCommand insertAnswerPropertyCommand = new Answer().GetInsertAnswerPropertyCommand(sqlConnection, sqlTransaction);
SqlCommand command8 = this.GetInsertQuestionSectionCommand(sqlConnection, sqlTransaction, "");
SqlCommand insertQuestionSectionGridAnswersCommand = this.GetInsertQuestionSectionGridAnswersCommand(sqlConnection, sqlTransaction);
try
{
// Add Question groups so we can attach them to questions
int surveyId = importQuestions.Question.First().SurveyId;
// Add Question groups so we can attach them to questions
string defaultLang = null;
var groups = new QuestionGroups();
var existingGroups = groups.GetAll(defaultLang).QuestionGroups;
foreach (var qgrp in importQuestions.QuestionGroups
.OrderBy(x => x.IsParentGroupIdNull() ? 0 : 1)) //Load parent groups first
{
var grpHere = existingGroups.FirstOrDefault(x => x.GroupName == qgrp.GroupName);
int groupIdHere;
if (grpHere == null)
{
int? parentGroupId = null;
if (!qgrp.IsParentGroupIdNull()) //Has Parent Group
{
var pgrp = importQuestions.QuestionGroups.SingleOrDefault(x => x.ID == qgrp.ParentGroupId);
if (pgrp != null)
{
var exPar = groups.GetAll(defaultLang).QuestionGroups
.FirstOrDefault(x => x.GroupName == pgrp.GroupName);
if (exPar != null) parentGroupId = exPar.ID;
}
}
groups.AddNewGroup(qgrp.GroupName, parentGroupId, defaultLang);
var grt = groups.GetAll(defaultLang).QuestionGroups
.FirstOrDefault(x => x.GroupName == qgrp.GroupName);
groupIdHere = grt.ID;
}
else
{
groupIdHere = grpHere.ID;
}
importQuestions.Question
.Where(x => x.QuestionGroupId == qgrp.OldId)
.ToList().ForEach(x => x.QuestionGroupId = groupIdHere);
}
DbConnection.db.UpdateDataSet(importQuestions, "AnswerType", insertCommand, new SqlCommand(), insertCommand, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importQuestions, "RegularExpression", command2, new SqlCommand(), command2, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importQuestions, "Question", insertQuestionCommand, new SqlCommand(), insertQuestionCommand, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importQuestions, "Answer", insertAnswerCommand, new SqlCommand(), insertAnswerCommand, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importQuestions, "AnswerConnection", insertAnswerConnectionCommand, new SqlCommand(), insertAnswerConnectionCommand, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importQuestions, "ChildQuestion", insertChildQuestionCommand, new SqlCommand(), insertChildQuestionCommand, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importQuestions, "AnswerProperty", insertAnswerPropertyCommand, new SqlCommand(), insertAnswerPropertyCommand, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importQuestions, "QuestionSectionOption", command8, new SqlCommand(), command8, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importQuestions, "QuestionSectionGridAnswer", insertQuestionSectionGridAnswersCommand, new SqlCommand(), insertQuestionSectionGridAnswersCommand, UpdateBehavior.Transactional);
var multiLanguage = new MultiLanguage();
int newQuestionId = importQuestions.Question[0].QuestionId;
foreach (var langText in importQuestions.MultiLanguageText)
{
var localGroups = groups.GetAll(defaultLang).QuestionGroups;
//Process Survey level
if (langText.LanguageMessageTypeId == 10)
{
var impGrp = importQuestions.QuestionGroups.SingleOrDefault(x => x.OldId == langText.LanguageItemId);
if (impGrp != null)
{
var localGrp = localGroups.SingleOrDefault(x => x.GroupName == impGrp.GroupName);
try
{
if (localGrp != null)
multiLanguage.AddMultiLanguageText(localGrp.ID, langText.LanguageCode, langText.LanguageMessageTypeId, langText.ItemText);
}
catch (Exception ex) { }
}
}
if (langText.LanguageMessageTypeId == 3 || langText.LanguageMessageTypeId == 11 || langText.LanguageMessageTypeId == 12)
multiLanguage.AddMultiLanguageText(newQuestionId
, langText.LanguageCode, langText.LanguageMessageTypeId, langText.ItemText);
if (langText.LanguageMessageTypeId == 1 || langText.LanguageMessageTypeId == 2 || langText.LanguageMessageTypeId == 13)
multiLanguage.AddMultiLanguageText(importQuestions.Answer.Single(x => x.OldId == langText.LanguageItemId).AnswerId
, langText.LanguageCode, langText.LanguageMessageTypeId, langText.ItemText);
}
sqlTransaction.Commit();
sqlConnection.Close();
}
catch (Exception exception)
{
sqlTransaction.Rollback();
throw exception;
}
}
/// <summary>
/// Import the given surveys into the DB
/// </summary>
public void ImportSurveys(NSurveyForm importSurveys, int userId, int folderId)
{
// Prepare connection and transaction
SqlConnection dbConnection = new SqlConnection(DbConnection.NewDbConnectionString);
dbConnection.Open();
SqlTransaction insertTransaction = dbConnection.BeginTransaction();
// Setup the insert commands for the survey, answer types, questions and answers
SqlCommand addNewAnswerType = new AnswerType().GetInsertAnswerTypeCommand(dbConnection, insertTransaction, userId);
SqlCommand addNewRegularExpression = new RegularExpression().GetInsertRegularExpressionCommand(dbConnection, insertTransaction, userId);
SqlCommand addNewSurvey = GetInsertSurveyCommand(dbConnection, insertTransaction);
SqlCommand addNewQuestion = new Question().GetInsertQuestionCommand(dbConnection, insertTransaction);
SqlCommand addNewChildQuestion = new Question().GetInsertChildQuestionCommand(dbConnection, insertTransaction);
SqlCommand addNewAnswer = new Answer().GetInsertAnswerCommand(dbConnection, insertTransaction);
SqlCommand addNewAnswerConnection = new Answer().GetInsertAnswerConnectionCommand(dbConnection, insertTransaction);
SqlCommand addNewAnswerProperty = new Answer().GetInsertAnswerPropertyCommand(dbConnection, insertTransaction);
SqlCommand addNewQuestionSection = new Question().GetInsertQuestionSectionCommand(dbConnection, insertTransaction, "");
SqlCommand addNewQuestionSectionGridAnswers = new Question().GetInsertQuestionSectionGridAnswersCommand(dbConnection, insertTransaction);
// Save the data in the DB
try
{
importSurveys.Survey[0].FolderId = folderId;
addNewSurvey.Parameters.Add(new SqlParameter("@MultiLanguageModeId", SqlDbType.Int, 4, "MultiLanguageModeId"));
addNewSurvey.Parameters.Add(new SqlParameter("@ThankYouMessage", SqlDbType.NVarChar, 4000, "ThankYouMessage"));
DbConnection.db.UpdateDataSet(importSurveys, "AnswerType", addNewAnswerType, new SqlCommand(), addNewAnswerType, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "RegularExpression", addNewRegularExpression, new SqlCommand(), addNewRegularExpression, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "Survey", addNewSurvey, new SqlCommand(), addNewSurvey, UpdateBehavior.Transactional);
// Add Question groups so we can attach them to questions
var groups = new QuestionGroups();
var defl = importSurveys.SurveyLanguage.SingleOrDefault(x => x.DefaultLanguage);
string defaultLang = defl == null ? null : defl.LanguageCode;
defaultLang = defaultLang ?? "en-US";
if (defaultLang != null) //Load groups only if default language is not null
{
var existingGroups = groups.GetAll(defaultLang).QuestionGroups;
foreach (var qgrp in importSurveys.QuestionGroup
.OrderBy(x => x.IsParentGroupIdNull() ? 0 : 1)) //Load parent groups first
{
var grpHere = existingGroups.FirstOrDefault(x => x.GroupName == qgrp.GroupName);
int groupIdHere;
if (grpHere == null)
{
int? parentGroupId = null;
if (!qgrp.IsParentGroupIdNull()) //Has Parent Group
{
var pgrp = importSurveys.QuestionGroup.SingleOrDefault(x => x.Id == qgrp.ParentGroupId);
if (pgrp != null)
{
var exPar = groups.GetAll(defaultLang).QuestionGroups
.FirstOrDefault(x => x.GroupName == pgrp.GroupName);
if (exPar != null) parentGroupId = exPar.ID;
}
}
groups.AddNewGroup(qgrp.GroupName, parentGroupId, defaultLang);
var grt = groups.GetAll(defaultLang).QuestionGroups
.FirstOrDefault(x => x.GroupName == qgrp.GroupName);
groupIdHere = grt.ID;
}
else
{
groupIdHere = grpHere.ID;
}
importSurveys.Question
.Where(x =>!x.IsQuestionGroupIdNull() && (x.QuestionGroupId == qgrp.OldId))
.ToList().ForEach(x => x.QuestionGroupId = groupIdHere);
}
}
DbConnection.db.UpdateDataSet(importSurveys, "Question", addNewQuestion, new SqlCommand(), addNewQuestion, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "Answer", addNewAnswer, new SqlCommand(), addNewAnswer, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "AnswerConnection", addNewAnswerConnection, new SqlCommand(), addNewAnswerConnection, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "ChildQuestion", addNewChildQuestion, new SqlCommand(), addNewChildQuestion, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "AnswerProperty", addNewAnswerProperty, new SqlCommand(), addNewAnswerProperty, UpdateBehavior.Transactional);
DbConnection.db.UpdateDataSet(importSurveys, "QuestionSectionOption", addNewQuestionSection, new SqlCommand(), addNewQuestionSection, UpdateBehavior.Transactional);
int newSurveyId = importSurveys.Survey[0].SurveyID;
DbConnection.db.UpdateDataSet(importSurveys, "QuestionSectionGridAnswer", addNewQuestionSectionGridAnswers, new SqlCommand(), addNewQuestionSectionGridAnswers, UpdateBehavior.Transactional);
insertTransaction.Commit();
var multiLanguage = new MultiLanguage();
foreach (var lang in importSurveys.SurveyLanguage)
multiLanguage.UpdateSurveyLanguage(newSurveyId, lang.LanguageCode, lang.DefaultLanguage, Constants.Constants.EntitySurvey);
var localGroups = groups.GetAll(defaultLang).QuestionGroups;
foreach (var langText in importSurveys.MultiLanguageText)
{
//Process Survey level
if (langText.LanguageMessageTypeId == 10)
{
var impGrp = importSurveys.QuestionGroup.SingleOrDefault(x => x.OldId == langText.LanguageItemId);
if (impGrp != null)
{
var localGrp = localGroups.SingleOrDefault(x => x.GroupName == impGrp.GroupName);
try
{
if (localGrp != null)
multiLanguage.AddMultiLanguageText(localGrp.ID, langText.LanguageCode, langText.LanguageMessageTypeId, langText.ItemText);
}
catch (Exception ex) { }
}
}
if (langText.LanguageMessageTypeId == 4 || langText.LanguageMessageTypeId == 5)
multiLanguage.AddMultiLanguageText(newSurveyId, langText.LanguageCode, langText.LanguageMessageTypeId, langText.ItemText);
if (langText.LanguageMessageTypeId == 3 || langText.LanguageMessageTypeId == 11 || langText.LanguageMessageTypeId == 12)
multiLanguage.AddMultiLanguageText(importSurveys.Question.Single(x => x.OldQuestionId == langText.LanguageItemId).QuestionId
, langText.LanguageCode, langText.LanguageMessageTypeId, langText.ItemText);
if (langText.LanguageMessageTypeId == 1 || langText.LanguageMessageTypeId == 2 || langText.LanguageMessageTypeId == 13)
multiLanguage.AddMultiLanguageText(importSurveys.Answer.Single(x => x.OldAnswerId == langText.LanguageItemId).AnswerId
, langText.LanguageCode, langText.LanguageMessageTypeId, langText.ItemText);
}
dbConnection.Close();
}
catch (Exception e)
{
throw (e);
}
}