/// <summary>
/// Import the given questions into the DB
/// </summary>
public void ImportQuestions(NSurveyQuestion importQuestions, int userId)
{
IQuestion question = QuestionFactory.Create();
this.TurnOverPrimaryKeys(importQuestions);
question.ImportQuestions(importQuestions, userId);
}
private void ImportXMLButton_Click(object sender, System.EventArgs e)
{
if (ImportFile.PostedFile != null)
{
NSurveyQuestion importedQuestions = new NSurveyQuestion();
try
{
importedQuestions.ReadXml(System.Xml.XmlReader.Create(ImportFile.PostedFile.InputStream, (new System.Xml.XmlReaderSettings()
{
XmlResolver = null
})));
SetImportedQuestionsDefaults(importedQuestions);
new Question().ImportQuestions(importedQuestions, NSurveyUser.Identity.UserId);
if (_libraryId == -1)
{
UINavigator.NavigateToSurveyBuilder(SurveyId, MenuIndex);
}
else
{
UINavigator.NavigateToLibraryTemplates(getSurveyId(), _libraryId, MenuIndex);
}
}
catch (Exception ex)
{
((PageBase)Page).ShowErrorMessage(MessageLabel, ex.Message);
MessageLabel.Visible = true;
}
}
}
/// <summary>
/// Set the imported questions local values (SurveyId, libraryid etc...)
/// </summary>
private void SetImportedQuestionsDefaults(NSurveyQuestion importedQuestions)
{
int questionDisplayOrder = _displayOrder;
foreach (NSurveyQuestion.QuestionRow question in importedQuestions.Question)
{
if (_libraryId == -1)
{
question.SurveyId = SurveyId;
question.PageNumber = _pageNumber;
question.DisplayOrder = questionDisplayOrder;
question.SetLibraryIdNull();
}
else
{
question.SetSurveyIdNull();
question.PageNumber = 1;
question.DisplayOrder = 1;
question.LibraryId = _libraryId;
}
questionDisplayOrder++;
}
// Prevents SQL injection from custom hand written datasources Sql answer types in the import Xml
if (!GlobalConfig.SqlBasedAnswerTypesAllowed || !CheckRight(NSurveyRights.SqlAnswerTypesEdition, false))
{
foreach (NSurveyQuestion.AnswerTypeRow answerType in importedQuestions.AnswerType)
{
answerType.DataSource = null;
}
}
}
/// <summary>
/// Returns all question details, answers and answer types
/// </summary>
public NSurveyQuestion GetQuestionForExport(int questionId)
{
NSurveyQuestion questionForExport = QuestionFactory.Create().GetQuestionForExport(questionId);
int num = 1;
foreach (NSurveyQuestion.QuestionRow row in questionForExport.Question)
{
row.QuestionId = num;
num++;
}
return(questionForExport);
}
/// <summary>
/// Invert main table keys signs to avoid key collisions on insertion
/// of new row with existing keys in the dataset that have not
/// yet been updated with their "live" DB key.
/// Changes will casade to child tables
/// </summary>
private void TurnOverPrimaryKeys(NSurveyQuestion importQuestions)
{
foreach (NSurveyQuestion.AnswerTypeRow row in importQuestions.AnswerType)
{
row.AnswerTypeID = -row.AnswerTypeID;
}
foreach (NSurveyQuestion.RegularExpressionRow row2 in importQuestions.RegularExpression)
{
row2.RegularExpressionId = -row2.RegularExpressionId;
}
foreach (NSurveyQuestion.QuestionRow row3 in importQuestions.Question)
{
row3.QuestionId = -row3.QuestionId;
}
foreach (NSurveyQuestion.AnswerRow row4 in importQuestions.Answer)
{
row4.AnswerId = -row4.AnswerId;
}
}
/// <summary>
/// Invert main table keys signs to avoid key collisions on insertion
/// of new row with existing keys in the dataset that have not
/// yet been updated with their "live" DB key.
/// Changes will casade to child tables
/// </summary>
private void TurnOverPrimaryKeys(NSurveyQuestion importQuestions)
{
foreach (NSurveyQuestion.AnswerTypeRow row in importQuestions.AnswerType)
{
row.AnswerTypeID = -row.AnswerTypeID;
}
foreach (NSurveyQuestion.RegularExpressionRow row2 in importQuestions.RegularExpression)
{
row2.RegularExpressionId = -row2.RegularExpressionId;
}
foreach (NSurveyQuestion.QuestionRow row3 in importQuestions.Question)
{
row3.QuestionId = -row3.QuestionId;
}
foreach (NSurveyQuestion.AnswerRow row4 in importQuestions.Answer)
{
row4.AnswerId = -row4.AnswerId;
}
}
/// <summary>
/// Import the given questions into the DB
/// </summary>
public void ImportQuestions(NSurveyQuestion importQuestions, int userId)
{
IQuestion question = QuestionFactory.Create();
this.TurnOverPrimaryKeys(importQuestions);
question.ImportQuestions(importQuestions, userId);
}
/// <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>
/// Returns all question details, answers and answer types
/// </summary>
public NSurveyQuestion GetQuestionForExport(int questionId)
{
NSurveyQuestion dataSet = new NSurveyQuestion();
ArrayList commandParameters = new ArrayList();
{
commandParameters.Add(new SqlParameter("@QuestionId", questionId).SqlValue);
}
DbConnection.db.LoadDataSet("vts_spQuestionGetForExport", dataSet,
new string[] { "AnswerType", "RegularExpression", "Question", "Answer", "AnswerConnection", "ChildQuestion", "AnswerProperty",
"QuestionSectionOption", "QuestionSectionGridAnswer","MultiLanguageText","QuestionGroups" },
commandParameters.ToArray());
return dataSet;
}
/// <summary>
/// Set the imported questions local values (SurveyId, libraryid etc...)
/// </summary>
private void SetImportedQuestionsDefaults(NSurveyQuestion importedQuestions)
{
int questionDisplayOrder = _displayOrder;
foreach (NSurveyQuestion.QuestionRow question in importedQuestions.Question)
{
if (_libraryId == -1)
{
question.SurveyId = SurveyId;
question.PageNumber = _pageNumber;
question.DisplayOrder = questionDisplayOrder;
question.SetLibraryIdNull();
}
else
{
question.SetSurveyIdNull();
question.PageNumber = 1;
question.DisplayOrder = 1;
question.LibraryId = _libraryId;
}
questionDisplayOrder++;
}
// Prevents SQL injection from custom hand written datasources Sql answer types in the import Xml
if (!GlobalConfig.SqlBasedAnswerTypesAllowed || !CheckRight(NSurveyRights.SqlAnswerTypesEdition, false))
{
foreach (NSurveyQuestion.AnswerTypeRow answerType in importedQuestions.AnswerType)
{
answerType.DataSource = null;
}
}
}
private void ImportXMLButton_Click(object sender, System.EventArgs e)
{
if (ImportFile.PostedFile != null)
{
NSurveyQuestion importedQuestions = new NSurveyQuestion();
try
{
importedQuestions.ReadXml(ImportFile.PostedFile.InputStream);
SetImportedQuestionsDefaults(importedQuestions);
new Question().ImportQuestions(importedQuestions, NSurveyUser.Identity.UserId);
if (_libraryId == -1)
{
UINavigator.NavigateToSurveyBuilder(SurveyId, MenuIndex);
}
else
{
UINavigator.NavigateToLibraryTemplates(getSurveyId(), _libraryId, MenuIndex);
}
}
catch (Exception ex)
{
((PageBase)Page).ShowErrorMessage(MessageLabel, ex.Message);
MessageLabel.Visible = true;
}
}
}
