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