예제 #1
0
        /// <summary>
        /// Operations to be performed during the upgrade process.
        /// </summary>
        public override void Up()
        {
            Sql("DELETE [MetaFirstNameGenderLookup]");
            ExecuteSqlInsert(
                "INSERT INTO [MetaFirstNameGenderLookup] ([FirstName],[MaleCount],[FemaleCount],[Country],[Language],[TotalCount],[FemalePercent],[MalePercent],[Guid])",
                MigrationSQL._201707311527250_FirstNameGender);

            // use BulkInsert for LastName since there are 150,000+ rowsa
            List <Rock.Model.MetaLastNameLookup> metaLastNameLookupsToInsert = new List <Model.MetaLastNameLookup>();
            List <string> lastNameLines;

            using (var lastNameZip = new System.IO.Compression.GZipStream(new MemoryStream(MigrationSQL._201707311527250_LastName), System.IO.Compression.CompressionMode.Decompress, false))
            {
                var lastNameText = Encoding.ASCII.GetString(lastNameZip.ReadBytesToEnd());
                lastNameLines = lastNameText.Split(new[] { "\n" }, StringSplitOptions.None).ToList();
            }

            foreach (var lastNameLine in lastNameLines)
            {
                var lastNameColValues = lastNameLine.Split(new[] { ',' }, StringSplitOptions.None).ToList();
                if (lastNameColValues.Count == 4)
                {
                    var metaLastNameLookup = new Rock.Model.MetaLastNameLookup();
                    metaLastNameLookup.LastName    = lastNameColValues[0].TrimStart(new[] { '\'' }).TrimEnd(new[] { '\'' });
                    metaLastNameLookup.Count       = lastNameColValues[1].AsInteger();
                    metaLastNameLookup.Rank        = lastNameColValues[2].AsInteger();
                    metaLastNameLookup.CountIn100k = lastNameColValues[3].AsDecimalOrNull();
                    metaLastNameLookup.Guid        = Guid.NewGuid();
                    metaLastNameLookupsToInsert.Add(metaLastNameLookup);
                }
            }

            using (var rockContext = new Rock.Data.RockContext())
            {
                rockContext.Database.ExecuteSqlCommand("DELETE [MetaLastNameLookup]");
                rockContext.Database.ExecuteSqlCommand("ALTER TABLE MetaLastNameLookup ALTER COLUMN LastName NVARCHAR(100)");
                rockContext.BulkInsert(metaLastNameLookupsToInsert);
            }

            Sql("DELETE [MetaNickNameLookup]");
            ExecuteSqlInsert(
                "INSERT INTO [MetaNickNameLookup] ([FirstName],[NickName],[Gender],[Count],[Guid])",
                MigrationSQL._201707311527250_NickName);

            Sql("DELETE [MetaPersonicxLifestageGroup]");
            ExecuteSqlInsert(
                "INSERT INTO [MetaPersonicxLifestageGroup] ([LifestyleGroupCode],[LifestyleGroupName],[Description],[Summary],[PercentUS],[LifeStage],[MaritalStatus],[HomeOwnership],[Children],[Income],[IncomeRank],[Urbanicity], [UrbanicityRank], [NetWorth], [NetworthRank],[DetailsUrl],[Guid])",
                MigrationSQL._201707311527250_PersonicxGroup);

            Sql("DELETE [MetaPersonicxLifestageCluster]");
            ExecuteSqlInsert(
                "INSERT INTO [MetaPersonicxLifestageCluster] ([LifestyleClusterCode],[LifestyleClusterName],[Description],[Summary],[PercentUS],[LifeStage],[MaritalStatus],[HomeOwnership],[Children],[Income],[IncomeRank],[Urbanicity], [UrbanicityRank], [NetWorth], [NetworthRank],[MetaPersonicxLifestyleGroupId],[DetailsUrl],[Guid])",
                MigrationSQL._201707311527250_PersonicxCluster);
        }
        /// <summary>
        /// Operations to be performed during the upgrade process.
        /// </summary>
        public override void Up()
        {
            Sql("DELETE [MetaFirstNameGenderLookup]");
            ExecuteSqlInsert(
                "INSERT INTO [MetaFirstNameGenderLookup] ([FirstName],[MaleCount],[FemaleCount],[Country],[Language],[TotalCount],[FemalePercent],[MalePercent],[Guid])",
                MigrationSQL._201707311527250_FirstNameGender);

            // use BulkInsert for LastName since there are 150,000+ rows
            List <Rock.Model.MetaLastNameLookup> metaLastNameLookupsToInsert = new List <Model.MetaLastNameLookup>();
            List <string> lastNameLines;

            using (var lastNameZip = new System.IO.Compression.GZipStream(new MemoryStream(MigrationSQL._201707311527250_LastName), System.IO.Compression.CompressionMode.Decompress, false))
            {
                var lastNameText = Encoding.ASCII.GetString(lastNameZip.ReadBytesToEnd());
                lastNameLines = lastNameText.Split(new[] { "\n" }, StringSplitOptions.None).ToList();
            }

            foreach (var lastNameLine in lastNameLines)
            {
                var lastNameColValues = lastNameLine.Split(new[] { ',' }, StringSplitOptions.None).ToList();
                if (lastNameColValues.Count == 4)
                {
                    var metaLastNameLookup = new Rock.Model.MetaLastNameLookup();
                    metaLastNameLookup.LastName    = lastNameColValues[0].TrimStart(new[] { '\'' }).TrimEnd(new[] { '\'' });
                    metaLastNameLookup.Count       = lastNameColValues[1].AsInteger();
                    metaLastNameLookup.Rank        = lastNameColValues[2].AsInteger();
                    metaLastNameLookup.CountIn100k = lastNameColValues[3].AsDecimalOrNull();
                    metaLastNameLookup.Guid        = Guid.NewGuid();
                    metaLastNameLookupsToInsert.Add(metaLastNameLookup);
                }
            }

            using (var rockContext = new Rock.Data.RockContext())
            {
                rockContext.Database.ExecuteSqlCommand("DELETE [MetaLastNameLookup]");
                rockContext.Database.ExecuteSqlCommand("ALTER TABLE MetaLastNameLookup ALTER COLUMN LastName NVARCHAR(100)");
                rockContext.BulkInsertWithConditionalCacheUse(metaLastNameLookupsToInsert, false);
            }

            Sql("DELETE [MetaNickNameLookup]");
            ExecuteSqlInsert(
                "INSERT INTO [MetaNickNameLookup] ([FirstName],[NickName],[Gender],[Count],[Guid])",
                MigrationSQL._201707311527250_NickName);
        }