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