コード例 #1
0
        private void MigrateAverPriceForTypeRoomByMetro(Connections.ConnetionToSqlServer mssql, Connections.ConnectionToPostgre postre)
        {
            string select = @"SELECT [Date]
      ,[TypeRoom]
      ,[Metro]
      ,[AverPrice]
  FROM ParseBulding.[dbo].[AverPriceForTypeRoomByMetro]";

            var reader = mssql.ExecuteReader(select);
            var list   = new List <Tuple <DateTime, string, Guid, double> >();

            if (reader != null)
            {
                while (reader.Read())
                {
                    list.Add(new Tuple <DateTime, string, Guid, double>(reader.GetDateTime(0), reader.GetString(1), reader.GetGuid(2), reader.GetDouble(3)));
                }
                reader.Close();
            }

            foreach (var tuple in list)
            {
                string insert = "INSERT INTO public.\"AverPriceForTypeRoomByMetro\"(" +
                                "\"Date\", \"TypeRoom\", \"Metro\", \"AverPrice\")" +
                                $"VALUES('{tuple.Item1.ToShortDateString()}', '{tuple.Item2}', '{tuple.Item3}', {tuple.Item4.ToString(CultureInfo.InvariantCulture)});";
                postre.ExecuteNonQuery(insert);
            }
        }
コード例 #2
0
        private void MigrateDistict(Connections.ConnetionToSqlServer mssql, Connections.ConnectionToPostgre postre)
        {
            string select = @"SELECT [ID]
      ,[Name]
  FROM [ParseBulding].[dbo].[District]";

            var reader = mssql.ExecuteReader(select);
            var list   = new List <District>();

            if (reader != null)
            {
                while (reader.Read())
                {
                    var district = new District()
                    {
                        Id = reader.GetGuid(0), Name = reader.GetString(1)
                    };
                    list.Add(district);
                }
                reader.Close();
            }

            foreach (var dist in list)
            {
                string insert = "INSERT INTO public.\"District\"(" +
                                "\"Id\", \"Name\")" +
                                $"VALUES('{dist.Id.ToString()}', '{dist.Name}');";
                postre.ExecuteNonQuery(query: insert);
            }
        }
コード例 #3
0
        private void MigrateManageCompany(Connections.ConnetionToSqlServer mssql, Connections.ConnectionToPostgre postre)
        {
            string select = @"SELECT [Id]
      ,[Name]
  FROM [ParseBulding].[dbo].[ManageCompany]";

            var reader = mssql.ExecuteReader(select);
            var list   = new List <Tuple <Guid, string> >();

            if (reader != null)
            {
                while (reader.Read())
                {
                    list.Add(new Tuple <Guid, string>(reader.GetGuid(0), reader.GetString(1)));
                }
                reader.Close();
            }

            foreach (var turple in list)
            {
                string insert = "INSERT INTO public.\"ManageCompany\"(" +
                                "\"Id\", \"Name\")" +
                                $"VALUES('{turple.Item1.ToString()}', '{turple.Item2}');";
                postre.ExecuteNonQuery(insert);
            }
        }
コード例 #4
0
        private void MigrateLots(Connections.ConnetionToSqlServer mssql, Connections.ConnectionToPostgre postre)
        {
            string select = @"SELECT [Id]
      ,[Number]
      ,[Description]
      ,[StartPrice]
      ,[Step]
      ,[Deposit]
      ,[PriceReductionInformation]
      ,[PropertyClassification]
      ,[IdMessage]
  FROM [ParseBankuprty].[dbo].[Lot]";

            var reader = mssql.ExecuteReader(select);
            var dicLot = new Dictionary <Guid, List <LotModel> >();

            if (reader != null)
            {
                while (reader.Read())
                {
                    var lot = new LotModel();
                    lot.Id          = reader.GetGuid(0);
                    lot.Number      = reader.GetInt32(1);
                    lot.Description = reader.GetString(2);
                    lot.StartPrice  = reader.GetDouble(3);
                    lot.Step        = reader.GetString(4);
                    lot.Deposit     = reader.GetString(5);
                    lot.PriceReductionInformation = reader.GetString(6);
                    lot.PropertyClassification    = reader.GetString(7);
                    var guid = reader.GetGuid(8);
                    if (!dicLot.ContainsKey(guid))
                    {
                        dicLot.Add(guid, new List <LotModel>());
                    }
                    dicLot[guid].Add(lot);
                }
                reader.Close();
            }

            foreach (var pair in dicLot)
            {
                foreach (var lot in pair.Value)
                {
                    string insert = "INSERT INTO public.\"Lots\"(" +
                                    "\"Id\", \"Number\", \"Description\", \"StartPrice\", \"Step\", \"Deposit\", \"PriceReductionInformation\", \"PropertyClassification\", \"IdMessage\")" +
                                    $"VALUES('{lot.Id}', {lot.Number}, '{lot.Description}', {lot.StartPrice.ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US"))}, '{lot.Step}', '{lot.Deposit}', '{lot.PriceReductionInformation}', '{lot.PropertyClassification}', '{pair.Key.ToString()}');";

                    postre.ExecuteNonQuery(insert);
                }
            }
        }
コード例 #5
0
        private void MigrateMetro(Connections.ConnetionToSqlServer mssql, Connections.ConnectionToPostgre postre)
        {
            string select = @"SELECT [Id]
      ,[Name]
      ,[XCoor]
      ,[YCoor]
      ,[IdRegion]
  FROM [ParseBulding].[dbo].[Metro]";

            var reader = mssql.ExecuteReader(select);
            var dict   = new Dictionary <Guid, List <Metro> >();

            if (reader != null)
            {
                while (reader.Read())
                {
                    var metro = new Metro();
                    metro.Id    = reader.GetGuid(0);
                    metro.Name  = reader.GetString(1);
                    metro.XCoor = (float)reader.GetDouble(2);
                    metro.YCoor = (float)reader.GetDouble(3);
                    var guid = reader.GetGuid(4);
                    if (!dict.ContainsKey(guid))
                    {
                        dict.Add(guid, new List <Metro>());
                    }
                    dict[guid].Add(metro);
                }
                reader.Close();
            }

            foreach (var pair in dict)
            {
                foreach (var metro in pair.Value)
                {
                    string insert = "INSERT INTO public.\"Metro\"(" +
                                    "\"Id\", \"Name\", \"XCoor\", \"YCoor\", \"IdDistrict\")" +
                                    $"VALUES('{metro.Id.ToString()}', '{metro.Name}', {metro.XCoor.ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US"))}, {metro.YCoor.ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US"))}, '{pair.Key.ToString()}'); ";
                    postre.ExecuteNonQuery(insert);
                }
            }
        }
コード例 #6
0
        private void MigrateMainInfo(Connections.ConnetionToSqlServer mssql, Connections.ConnectionToPostgre postre)
        {
            string select = @"SELECT [Id]
      ,[Street]
      ,[Number]
      ,[Bulding]
      ,[Letter]
      ,[DistrictId]
      ,[DateBulding]
      ,[SeriesID]
      ,[CountCommApartament]
      ,[DateReconstruct]
      ,[DateRepair]
      ,[BuldingArea]
      ,[LivingArea]
      ,[NoLivingArea]
      ,[Stairs]
      ,[Storeys]
      ,[Residents]
      ,[MansardArea]
      ,[HeatingCentral]
      ,[HotWaterCentral]
      ,[ElectroCentral]
      ,[GascCntral]
      ,[FlatType]
      ,[FlatNum]
      ,[InternalNum]
      ,[TepCreateDate]
      ,[ManagCompanyId]
      ,[Failure]
      ,[RepairJob]
      ,[LiftCount]
      ,[BasementArea]
      ,[Xcoor]
      ,[Ycoor]
      ,[Metro]
      ,[DistanceAndTimeOnFoot]
      ,[DistanceAndTimeOnCar]
      ,[TypeBuild]
      ,[IsRepair]
  FROM [ParseBulding].[dbo].[MainInfoAboutBulding]";

            var reader = mssql.ExecuteReader(select);
            var list   = new List <Building>();

            if (reader != null)
            {
                while (reader.Read())
                {
                    var builidng = new Building();
                    builidng.Guid      = reader.GetGuid(0);
                    builidng.Street    = reader.GetString(1);
                    builidng.Number    = reader.GetString(2);
                    builidng.Structure = reader.IsDBNull(3)? string.Empty: reader.GetString(3);
                    builidng.Liter     = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
                    builidng.District  = new District()
                    {
                        Id = reader.GetGuid(5)
                    };
                    builidng.DateBuild = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
                    builidng.SeriesId  = reader.IsDBNull(7)? Guid.Empty : reader.GetGuid(7);

                    if (reader.IsDBNull(8))
                    {
                        builidng.CountCommApartament = null;
                    }
                    else
                    {
                        builidng.CountCommApartament = reader.GetInt32(8);
                    }

                    builidng.DateReconstruct = reader.IsDBNull(9) ? string.Empty: reader.GetString(9);
                    builidng.DateRepair      = reader.IsDBNull(10) ? string.Empty : reader.GetString(10);

                    if (reader.IsDBNull(11))
                    {
                        builidng.BuldingArea = null;
                    }
                    else
                    {
                        builidng.BuldingArea = reader.GetDouble(11);
                    }

                    if (reader.IsDBNull(12))
                    {
                        builidng.LivingArea = null;
                    }
                    else
                    {
                        builidng.LivingArea = reader.GetDouble(12);
                    }

                    if (reader.IsDBNull(13))
                    {
                        builidng.NoLivingArea = null;
                    }
                    else
                    {
                        builidng.NoLivingArea = reader.GetDouble(13);
                    }

                    if (reader.IsDBNull(14))
                    {
                        builidng.Stairs = null;
                    }
                    else
                    {
                        builidng.Stairs = reader.GetInt32(14);
                    }

                    if (reader.IsDBNull(15))
                    {
                        builidng.Storeys = null;
                    }
                    else
                    {
                        builidng.Storeys = reader.GetInt32(15);
                    }

                    if (reader.IsDBNull(16))
                    {
                        builidng.Residents = null;
                    }
                    else
                    {
                        builidng.Residents = reader.GetInt32(16);
                    }

                    if (reader.IsDBNull(17))
                    {
                        builidng.MansardArea = null;
                    }
                    else
                    {
                        builidng.MansardArea = reader.GetDouble(17);
                    }

                    if (reader.IsDBNull(18))
                    {
                        builidng.HeatingCentral = null;
                    }
                    else
                    {
                        builidng.HeatingCentral = reader.GetBoolean(18);
                    }

                    if (reader.IsDBNull(19))
                    {
                        builidng.HotWaterCentral = null;
                    }
                    else
                    {
                        builidng.HotWaterCentral = reader.GetBoolean(19);
                    }

                    if (reader.IsDBNull(20))
                    {
                        builidng.ElectroCentral = null;
                    }
                    else
                    {
                        builidng.ElectroCentral = reader.GetBoolean(20);
                    }

                    if (reader.IsDBNull(21))
                    {
                        builidng.GasCentral = null;
                    }
                    else
                    {
                        builidng.GasCentral = reader.GetBoolean(21);
                    }

                    builidng.FlatType = reader.IsDBNull(22)? string.Empty : reader.GetString(22);
                    builidng.FlatNum  = reader.IsDBNull(23) ? string.Empty : reader.GetString(23);

                    if (reader.IsDBNull(24))
                    {
                        builidng.InternalNum = null;
                    }
                    else
                    {
                        builidng.InternalNum = reader.GetInt32(24);
                    }

                    if (reader.IsDBNull(25))
                    {
                        builidng.DateTep = null;
                    }
                    else
                    {
                        builidng.DateTep = reader.GetDateTime(25);
                    }

                    builidng.ManagCompanyId = reader.IsDBNull(26) ? Guid.Empty : reader.GetGuid(26);

                    if (reader.IsDBNull(27))
                    {
                        builidng.Failure = null;
                    }
                    else
                    {
                        builidng.Failure = reader.GetBoolean(27);
                    }

                    builidng.RepairJob = reader.IsDBNull(28) ? string.Empty : reader.GetString(28);

                    if (reader.IsDBNull(29))
                    {
                        builidng.LiftCount = null;
                    }
                    else
                    {
                        builidng.LiftCount = reader.GetInt32(29);
                    }

                    if (reader.IsDBNull(30))
                    {
                        builidng.BasementArea = null;
                    }
                    else
                    {
                        builidng.BasementArea = reader.GetDouble(30);
                    }

                    if (reader.IsDBNull(31))
                    {
                        builidng.XCoor = null;
                    }
                    else
                    {
                        builidng.XCoor = (float)reader.GetDouble(31);
                    }

                    if (reader.IsDBNull(32))
                    {
                        builidng.YCoor = null;
                    }
                    else
                    {
                        builidng.YCoor = (float)reader.GetDouble(32);
                    }

                    builidng.MetroObj = reader.IsDBNull(33)? null : new Metro()
                    {
                        Id = reader.GetGuid(33)
                    };
                    builidng.DistanceOnFoot = reader.IsDBNull(34) ? string.Empty : reader.GetString(34);
                    builidng.DistanceOnCar  = reader.IsDBNull(35) ? string.Empty : reader.GetString(35);
                    builidng.TypeBuilding   = reader.IsDBNull(36) ? string.Empty : reader.GetString(36);
                    builidng.IsRepair       = reader.IsDBNull(37) ? string.Empty : reader.GetString(37);
                    list.Add(builidng);
                }
                reader.Close();
            }

            foreach (var b in list)
            {
                var structure           = string.IsNullOrEmpty(b.Structure) ? "null" : $"'{b.Structure}'";
                var liter               = string.IsNullOrEmpty(b.Liter) ? "null" : $"'{b.Liter}'";
                var dateBuild           = string.IsNullOrEmpty(b.DateBuild) ? "null" : $"'{b.DateBuild}'";
                var seriesId            = b.SeriesId == Guid.Empty ? "null" : $"'{b.SeriesId}'";
                var countCommApartament = b.CountCommApartament == null ? "null" : $"{b.CountCommApartament}";
                var dateReconstruct     = string.IsNullOrEmpty(b.DateReconstruct) ? "null" : $"'{b.DateReconstruct}'";
                var dateRepair          = string.IsNullOrEmpty(b.DateRepair) ? "null" : $"'{b.DateRepair}'";
                var buldingArea         = b.BuldingArea == null ? "null" : $"{((double)b.BuldingArea).ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US"))}";
                var livingArea          = b.LivingArea == null ? "null" : $"{((double)b.LivingArea).ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US"))}";
                var noLivingArea        = b.NoLivingArea == null ? "null" : $"{((double)b.NoLivingArea).ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US"))}";
                var stairs              = b.Stairs == null ? "null" : $"{b.Stairs}";
                var storeys             = b.Storeys == null ? "null" : $"{b.Storeys}";
                var residents           = b.Residents == null ? "null" : $"{b.Residents}";
                var mansardArea         = b.MansardArea == null ? "null" : $"{((double)b.MansardArea).ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US"))}";
                var heatingCentral      = b.HeatingCentral == null ? "null" : $"{b.HeatingCentral}";
                var hotWaterCentral     = b.HotWaterCentral == null ? "null" : $"{b.HotWaterCentral}";
                var electroCentral      = b.ElectroCentral == null ? "null" : $"{b.ElectroCentral}";
                var gasCentral          = b.GasCentral == null ? "null" : $"{b.GasCentral}";
                var flatType            = string.IsNullOrEmpty(b.FlatType) ? "null" : $"'{b.FlatType}'";
                var flatNum             = string.IsNullOrEmpty(b.FlatNum) ? "null" : $"'{b.FlatNum}'";
                var internalNum         = b.InternalNum == null ? "null" : $"{b.InternalNum}";
                var dateTep             = b.DateTep == null ? "null" : $"'{((DateTime)b.DateTep).ToShortDateString()}'";
                var managCompanyId      = b.ManagCompanyId == Guid.Empty ? "null" : $"'{b.ManagCompanyId}'";
                var failure             = b.Failure == null ? "null" : $"{b.Failure}";
                var repairJob           = string.IsNullOrEmpty(b.RepairJob) ? "null" : $"'{b.RepairJob}'";
                var liftCount           = b.LiftCount == null ? "null" : $"{b.LiftCount}";
                var basementArea        = b.BasementArea == null ? "null" : $"{((double)b.BasementArea).ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US"))}";
                var xCoor               = b.XCoor == null ? "null" : $"{((float)b.XCoor).ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US"))}";
                var yCoor               = b.YCoor == null ? "null" : $"{((float)b.YCoor).ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US"))}";
                var metroObj            = b.MetroObj == null ? "null" : $"'{b.MetroObj.Id}'";
                var distanceOnFoot      = string.IsNullOrEmpty(b.DistanceOnFoot) ? "null" : $"'{b.DistanceOnFoot}'";
                var distanceOnCar       = string.IsNullOrEmpty(b.DistanceOnCar) ? "null" : $"'{b.DistanceOnCar}'";
                var typeBuilding        = string.IsNullOrEmpty(b.TypeBuilding) ? "null" : $"'{b.TypeBuilding}'";
                var isRepair            = string.IsNullOrEmpty(b.IsRepair) ? "null" : $"'{b.IsRepair}'";

                string insert = "INSERT INTO public.\"MainInfoAboutBulding\"(" +
                                "\"Id\", \"Street\", \"Number\", \"Bulding\", \"Letter\", \"DistrictId\", \"DateBulding\", \"SeriesID\", \"CountCommApartament\", \"DateReconstruct\", \"DateRepair\", \"BuldingArea\", \"LivingArea\", \"NoLivingArea\", \"Stairs\", \"Storeys\", \"Residents\", \"MansardArea\", \"HeatingCentral\", \"HotWaterCentral\", \"ElectroCentral\", \"GasCentral\", \"FlatType\", \"FlatNum\", \"InternalNum\", \"TepCreateDate\", \"ManagCompanyId\", \"Failure\", \"RepairJob\", \"LiftCount\", \"BasementArea\", \"Xcoor\", \"Ycoor\", \"Metro\", \"DistanceAndTimeOnFoot\", \"DistanceAndTimeOnCar\", \"TypeBuild\", \"IsRepair\")" +
                                $"VALUES('{b.Guid.ToString()}', '{b.Street}', '{b.Number}', {structure}, {liter}, '{b.District.Id.ToString()}', {dateBuild}, {seriesId}, {countCommApartament}, {dateReconstruct}, {dateRepair}, {buldingArea}, {livingArea}, {noLivingArea}, {stairs}, {storeys}, {residents}, {mansardArea}, {heatingCentral}, {hotWaterCentral}, {electroCentral}, {gasCentral}, {flatType}, {flatNum}, {internalNum}, {dateTep}, {managCompanyId}, {failure}, {repairJob}, {liftCount}, {basementArea}, {xCoor}, {yCoor}, {metroObj}, {distanceOnFoot}, {distanceOnCar}, {typeBuilding}, {isRepair});";

                postre.ExecuteNonQuery(insert);
            }
        }
コード例 #7
0
        private void MigrateMessage(Connections.ConnetionToSqlServer mssql, Connections.ConnectionToPostgre postre)
        {
            string select = @"SELECT [Id]
      ,[NumberMessage]
      ,[DateStart]
      ,[DateEnd]
      ,[DateStartBargaining]
      ,[Url]
      ,[Description]
      ,[DateMessage]
      ,[TypeOfBidding]
  FROM [ParseBankuprty].[dbo].[Message]
  order by [DateMessage]";

            var reader      = mssql.ExecuteReader(select);
            var listMessage = new List <MessageModel>();

            if (reader != null)
            {
                while (reader.Read())
                {
                    var message = new MessageModel();
                    message.Id     = reader.GetGuid(0);
                    message.Number = reader.GetString(1);

                    if (reader.IsDBNull(2))
                    {
                        message.DateStart = null;
                    }
                    else
                    {
                        message.DateStart = reader.GetDateTime(2);
                    }

                    if (reader.IsDBNull(3))
                    {
                        message.DateEnd = null;
                    }
                    else
                    {
                        message.DateEnd = reader.GetDateTime(3);
                    }

                    if (reader.IsDBNull(4))
                    {
                        message.DateStartBargaining = null;
                    }
                    else
                    {
                        reader.GetDateTime(4);
                    }

                    message.Url           = reader.GetString(5);
                    message.Description   = reader.GetString(6);
                    message.DateMesage    = reader.GetDateTime(7);
                    message.TypeOfBidding = reader.IsDBNull(8) ? string.Empty: reader.GetString(8);
                    listMessage.Add(message);
                }
                reader.Close();
            }

            foreach (var message in listMessage)
            {
                string dateStart           = "null";
                string dateEnd             = "null";
                string dateStartBargaining = "null";

                if (message.DateStart.HasValue)
                {
                    dateStart = $"'{((DateTime)message.DateStart).ToShortDateString()}'";
                }
                if (message.DateEnd.HasValue)
                {
                    dateEnd = $"'{((DateTime)message.DateEnd).ToShortDateString()}'";
                }
                if (message.DateStartBargaining.HasValue)
                {
                    dateStartBargaining = $"'{((DateTime)message.DateStartBargaining).ToShortDateString()}'";
                }

                string insert = "INSERT INTO public.\"Messages\"( " +
                                "\"Id\", \"NumberMessage\", \"DateStart\", \"DateEnd\", \"DateStartBargaining\", \"Url\", \"Description\", \"DateMessage\", \"TypeOfBidding\") " +
                                $"VALUES('{message.Id.ToString()}', '{message.Number}', {dateStart}, {dateEnd}, {dateStartBargaining}, '{message.Url}', '{message.Description}', '{message.DateMesage.ToShortDateString()}', '{message.TypeOfBidding}'); ";

                postre.ExecuteNonQuery(insert);
            }
        }