private static void CheckCanada()
{
try
{
var notFound = 0;
var total = 0;
var missed = 0;
var connectionString = @"server=.\SQL2014;initial catalog=GeoLocation;integrated security=SSPI;";
using (var context = new GeoLocationEntities(connectionString))
{
using (var textReader = File.OpenText(@"C:\temp\CanadianZipCodes201805.csv"))
{
using (var csv = new CsvReader(textReader))
{
while (csv.Read())
{
var postalCode = csv.GetField <string>(0).Replace(" ", "").Trim();
var city = csv.GetField <string>(1);
var prov = csv.GetField <string>(2);
if (!context.CanadaPostalCode.Any(x => x.PostalCode == postalCode))
{
notFound++;
Console.WriteLine($"Total={total}, NotFound={notFound}, PostalCode={postalCode}");
//Get same city
var template = context.CanadaPostalCode.FirstOrDefault(x => x.City == city);
if (template != null)
{
//Add new
context.AddItem(new CanadaPostalCode {
PostalCode = postalCode, City = template.City, Latitude = template.Latitude, Longitude = template.Longitude
});
context.SaveChanges();
}
else
{
missed++;
}
}
total++;
}
}
}
}
Console.WriteLine($"Total={total}, NotFound={notFound}, Missed={missed}");
}
catch (Exception ex)
{
throw;
}
}
private static void ResetPopulations()
{
try
{
var index = 0;
var connectionString = @"server=.\SQL2014;initial catalog=GeoLocation;integrated security=SSPI;";
using (var context = new GeoLocationEntities(connectionString))
{
var allZips = context.City.ToList();
var states = context.State.ToList();
var input = @"C:\temp\cities.csv";
using (var sr = new StreamReader(input))
{
using (var csv = new CsvReader(sr))
{
while (csv.Read())
{
var record = csv.GetRecord <CityItem>();
var population = record.Population.ToInt();
if (population > 0)
{
var state = states.Where(x => x.Name.Match(record.State)).Select(x => x.Abbr).FirstOrDefault();
var dbZip = allZips.FirstOrDefault(x => x.Name.Match(record.City) && x.State == state);
if (dbZip == null)
{
context.AddItem(new Gravitybox.GeoLocation.EFDAL.Entity.City {
Name = record.City, Population = population, State = state
});
Console.WriteLine($"Index={index}, Action=Add, City={record.City}, State={state}, Population={record.Population}");
}
else if (dbZip != null && dbZip.Population != population)
{
Console.WriteLine($"Index={index}, Action=Update, City={record.City}, State={state}, Population={record.Population}, Population2={dbZip.Population}");
dbZip.Population = population;
}
}
index++;
}
}
}
context.SaveChanges();
}
}
catch (Exception ex)
{
throw;
}
}
