public LocationHierarchyAnalysis GetIndexedHierarchyAnalysis(string countryCode, string pName) { var analysis = new LocationHierarchyAnalysis(); using (var conn = new SqlConnection(_extDataConnectionString.ToString())) { using (var command = conn.CreateCommand()) { conn.Open(); command.CommandTimeout = 4000; command.CommandText = string.Format("SELECT [ID],[POPULATION],[PNAME],[COUNTRYCODE],[MAPPEDTOID] FROM [ExternalData].[GeoNames].[LocationHierarchyAnalysisBk] WHERE COUNTRYCODE = '{0}' and PNAME = N'{1}' and MAPPEDTOID is not null", countryCode, pName); using (var reader = command.ExecuteReader()) { while (reader.Read()) { if (reader["ID"] != DBNull.Value && reader["POPULATION"] != DBNull.Value && reader["PNAME"] != DBNull.Value && reader["MAPPEDTOID"] != DBNull.Value) { analysis = new LocationHierarchyAnalysis { Id = (long)reader["ID"], Population = (long)reader["POPULATION"], PName = reader["PNAME"].ToString(), CountryCode = reader["COUNTRYCODE"].ToString(), MappedToId = (long)reader["MAPPEDTOID"] } } ; } reader.Close(); } } } return(analysis); }
public void Resolve() { LocationHierarchyAnalysis previousProcessedLocation = null; LocationHierarchyAnalysis indexedLocation = null; var pName = string.Empty; var cnt = 0; var uCnt = 0; Console.WriteLine("STARTING..."); var countries = _adoClientService.GetCountryCodes(); foreach (var country in countries) { Console.WriteLine(string.Format("Country being processed: {0}.", country)); var notMappedDuplicateLocations = _adoClientService.GetDuplicatesWithoutMappedToId(country); Console.WriteLine(string.Format("Unmapped locations: {0}.", notMappedDuplicateLocations.Count)); foreach (var location in notMappedDuplicateLocations) { cnt++; if (previousProcessedLocation == null || !previousProcessedLocation.PName.Equals(location.PName)) { if (location.PName.Contains("'")) { pName = location.PName.Replace("'", "''"); } else { pName = location.PName; } indexedLocation = _adoClientService.GetIndexedHierarchyAnalysis(location.CountryCode, pName); if (indexedLocation != null) { uCnt++; _adoClientService.UpdateHierarchyAnalysis(location.Id, indexedLocation.Id); previousProcessedLocation = indexedLocation; } else { Console.WriteLine("-------------- > NOT INDEXED: {0}, {1}", location.Id, location.PName); } } else { uCnt++; _adoClientService.UpdateHierarchyAnalysis(location.Id, previousProcessedLocation.Id); } } } Console.WriteLine("{0} locations processed. {1} updated.", cnt, uCnt); Console.WriteLine("FINISHED..."); }