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