/// <summary> /// Import the given surveys into the DB /// </summary> public void ImportSurveys(NSurveyForm importSurveys, int userId, int folderId) { ISurvey survey = SurveyFactory.Create(); this.TurnOverPrimaryKeys(importSurveys); survey.ImportSurveys(importSurveys, userId, folderId); }
private void ImportXMLButton_Click(object sender, System.EventArgs e) { if (ImportFile.PostedFile != null) { NSurveyForm importedSurveys = new NSurveyForm(); try { importedSurveys.ReadXml(System.Xml.XmlReader.Create(ImportFile.PostedFile.InputStream, (new System.Xml.XmlReaderSettings() { XmlResolver = null }))); if (importedSurveys.Survey.Rows.Count > 0) { // Prevents SQL injection from custom hand written datasources Sql answer types in the import Xml if (!GlobalConfig.SqlBasedAnswerTypesAllowed || !(((PageBase)Page).NSurveyUser.Identity.IsAdmin || ((PageBase)Page).CheckRight(NSurveyRights.SqlAnswerTypesEdition, false))) { foreach (NSurveyForm.AnswerTypeRow answerType in importedSurveys.AnswerType) { answerType.DataSource = null; } } new Survey().ImportSurveys(importedSurveys, ((PageBase)Page).NSurveyUser.Identity.UserId, ((PageBase)Page).SelectedFolderId ?? -1); Surveys srv = new Surveys(); srv.SetFolderId(((PageBase)Page).SelectedFolderId, importedSurveys.Survey[0].SurveyID); AssignSurveyToUser(importedSurveys.Survey[0].SurveyID); SurveyId = importedSurveys.Survey[0].SurveyID; UINavigator.NavigateToSurveyBuilder(importedSurveys.Survey[0].SurveyID, 4); ((PageBase)Page).ShowNormalMessage(MessageLabel, ((PageBase)Page).GetPageResource("SurveyImported")); } else { ((PageBase)Page).ShowErrorMessage(MessageLabel, ((PageBase)Page).GetPageResource("SurveyNotImported")); } MessageLabel.Visible = true; } catch (Exception ex) { if (ex.Message == "DUPLICATEFOLDER") { ((PageBase)Page).ShowErrorMessage(MessageLabel, ((PageBase)Page).GetPageResource("SurveyImportDuplicate")); } else { ((PageBase)Page).ShowErrorMessage(MessageLabel, ((PageBase)Page).GetPageResource("Exception") + " " + ex.Message); } MessageLabel.Visible = true; } } }
private void ExportSurveyButton_Click(object sender, System.EventArgs e) { NSurveyForm surveyForm = null; Response.Charset = "utf-8"; Response.ContentType = "application/octet-stream"; Response.ContentType = "text/xml"; Response.AddHeader("Content-Disposition", "attachment; filename=\"SurveyExport" + SurveyId + ".xml\""); surveyForm = new Surveys().GetFormForExport(SurveyId); surveyForm.WriteXml(Response.OutputStream, System.Data.XmlWriteMode.IgnoreSchema); Response.End(); }
/// <summary> /// Returns all the survey, questions, answers /// for a survey /// </summary> public NSurveyForm GetFormForExport(int surveyId) { NSurveyForm formForExport = SurveyFactory.Create().GetFormForExport(surveyId); if (formForExport.Survey.Rows.Count > 0) { formForExport.Survey[0].SurveyID = 1; int num = 1; foreach (NSurveyForm.QuestionRow row in formForExport.Survey[0].GetQuestionRows()) { row.QuestionId = num; num++; } } return(formForExport); }
/// <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(NSurveyForm importSurveys) { foreach (NSurveyForm.AnswerTypeRow row in importSurveys.AnswerType) { row.AnswerTypeID = (short)-row.AnswerTypeID; } foreach (NSurveyForm.RegularExpressionRow row2 in importSurveys.RegularExpression) { row2.RegularExpressionId = -row2.RegularExpressionId; } foreach (NSurveyForm.QuestionRow row3 in importSurveys.Question) { row3.QuestionId = -row3.QuestionId; } foreach (NSurveyForm.AnswerRow row4 in importSurveys.Answer) { row4.AnswerId = -row4.AnswerId; } }
private void ImportXMLButton_Click(object sender, System.EventArgs e) { if (ImportFile.PostedFile != null) { NSurveyForm importedSurveys = new NSurveyForm(); try { importedSurveys.ReadXml(ImportFile.PostedFile.InputStream); if (importedSurveys.Survey.Rows.Count > 0) { // Prevents SQL injection from custom hand written datasources Sql answer types in the import Xml if (!GlobalConfig.SqlBasedAnswerTypesAllowed || !(((PageBase)Page).NSurveyUser.Identity.IsAdmin || ((PageBase)Page).CheckRight(NSurveyRights.SqlAnswerTypesEdition, false))) { foreach (NSurveyForm.AnswerTypeRow answerType in importedSurveys.AnswerType) { answerType.DataSource = null; } } new Survey().ImportSurveys(importedSurveys, ((PageBase)Page).NSurveyUser.Identity.UserId, ((PageBase)Page).SelectedFolderId ?? -1); Surveys srv = new Surveys(); srv.SetFolderId(((PageBase)Page).SelectedFolderId, importedSurveys.Survey[0].SurveyID); AssignSurveyToUser(importedSurveys.Survey[0].SurveyID); SurveyId = importedSurveys.Survey[0].SurveyID; UINavigator.NavigateToSurveyBuilder(importedSurveys.Survey[0].SurveyID, 4); ((PageBase)Page).ShowNormalMessage(MessageLabel, ((PageBase)Page).GetPageResource("SurveyImported")); } else ((PageBase)Page).ShowErrorMessage(MessageLabel, ((PageBase)Page).GetPageResource("SurveyNotImported")); MessageLabel.Visible = true; } catch (Exception ex) { if (ex.Message == "DUPLICATEFOLDER") ((PageBase)Page).ShowErrorMessage(MessageLabel, ((PageBase)Page).GetPageResource("SurveyImportDuplicate") ); else ((PageBase)Page).ShowErrorMessage(MessageLabel, ((PageBase)Page).GetPageResource("Exception") + " " + ex.Message); MessageLabel.Visible = true; } } }
/// <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(NSurveyForm importSurveys) { foreach (NSurveyForm.AnswerTypeRow row in importSurveys.AnswerType) { row.AnswerTypeID = (short)-row.AnswerTypeID; } foreach (NSurveyForm.RegularExpressionRow row2 in importSurveys.RegularExpression) { row2.RegularExpressionId = -row2.RegularExpressionId; } foreach (NSurveyForm.QuestionRow row3 in importSurveys.Question) { row3.QuestionId = -row3.QuestionId; } foreach (NSurveyForm.AnswerRow row4 in importSurveys.Answer) { row4.AnswerId = -row4.AnswerId; } }
/// <summary> /// Import the given surveys into the DB /// </summary> public void ImportSurveys(NSurveyForm importSurveys, int userId,int folderId) { ISurvey survey = SurveyFactory.Create(); this.TurnOverPrimaryKeys(importSurveys); survey.ImportSurveys(importSurveys, userId,folderId); }
/// <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); } }
/// <summary> /// Returns all the survey, questions, answers /// for a survey /// </summary> public NSurveyForm GetFormForExport(int surveyId) { NSurveyForm surveyForm = new NSurveyForm(); DbConnection.db.LoadDataSet("vts_spSurveyGetForExport", surveyForm, new string[] { "AnswerType", "RegularExpression", "Survey", "Question", "Answer", "AnswerConnection", "ChildQuestion", "AnswerProperty", "QuestionSectionOption", "QuestionSectionGridAnswer", "Surveylanguage", "MultiLanguageText", "QuestionGroup" }, new SqlParameter("@SurveyId", surveyId).SqlValue); return surveyForm; }