/// <summary>
/// Fill parameters of SQL command from local geocoder record.
/// </summary>
/// <param name="localGeocoderRecord">Source record.</param>
/// <param name="command">Command to fill.</param>
private void _FillRecordCommandParameters(NameAddressRecord localGeocoderRecord,
SqlCeCommand command)
{
_FillCommandParameters(localGeocoderRecord.NameAddress, command);
_AddParameter(command, X_PARAMETER_NAME, localGeocoderRecord.GeoLocation.X);
_AddParameter(command, Y_PARAMETER_NAME, localGeocoderRecord.GeoLocation.Y);
Address matchedAddress = localGeocoderRecord.MatchedAddress;
_AddParameter(command, MUNIT_PARAMETER_NAME, matchedAddress.Unit);
_AddParameter(command, MADDRESSLINE_PARAMETER_NAME, matchedAddress.AddressLine);
_AddParameter(command, MLOCALITY1_PARAMETER_NAME, matchedAddress.Locality1);
_AddParameter(command, MLOCALITY2_PARAMETER_NAME, matchedAddress.Locality2);
_AddParameter(command, MLOCALITY3_PARAMETER_NAME, matchedAddress.Locality3);
_AddParameter(command, MCOUNTYPREFECTURE_PARAMETER_NAME, matchedAddress.CountyPrefecture);
_AddParameter(command, MPOSTALCODE1_PARAMETER_NAME, matchedAddress.PostalCode1);
_AddParameter(command, MPOSTALCODE2_PARAMETER_NAME, matchedAddress.PostalCode2);
_AddParameter(command, MSTATEPROVINCE_PARAMETER_NAME, matchedAddress.StateProvince);
_AddParameter(command, MCOUNTRY_PARAMETER_NAME, matchedAddress.Country);
_AddParameter(command, MFULL_ADDRESS_PARAMETER_NAME, matchedAddress.FullAddress);
string matchMethod = localGeocoderRecord.MatchedAddress.MatchMethod;
if (string.IsNullOrEmpty(matchMethod))
{
matchMethod = localGeocoderRecord.NameAddress.Address.MatchMethod;
}
_AddParameter(command, MATCHMETHOD_PARAMETER_NAME, matchMethod);
}
/// <summary>
/// Read record from database.
/// </summary>
/// <param name="reader">Database reader.</param>
/// <returns>Readed record.</returns>
private NameAddressRecord _ReadRecord(SqlCeDataReader reader)
{
NameAddressRecord nameAddressRecord = new NameAddressRecord();
NameAddress nameAddress = new NameAddress();
nameAddressRecord.NameAddress = nameAddress;
nameAddress.Name = (string)reader[NAME_FIELD_NAME];
Address address = new Address();
nameAddress.Address = address;
address.Unit = (string)reader[UNIT_FIELD_NAME];
address.AddressLine = (string)reader[ADDRESSLINE_FIELD_NAME];
address.Locality1 = (string)reader[LOCALITY1_FIELD_NAME];
address.Locality2 = (string)reader[LOCALITY2_FIELD_NAME];
address.Locality3 = (string)reader[LOCALITY3_FIELD_NAME];
address.CountyPrefecture = (string)reader[COUNTYPREFECTURE_FIELD_NAME];
address.PostalCode1 = (string)reader[POSTALCODE1_FIELD_NAME];
address.PostalCode2 = (string)reader[POSTALCODE2_FIELD_NAME];
address.StateProvince = (string)reader[STATEPROVINCE_FIELD_NAME];
address.Country = (string)reader[COUNTRY_FIELD_NAME];
address.FullAddress = (string)reader[FULL_ADDRESS_FIELD_NAME];
float x = (float)reader[X_FIELD_NAME];
float y = (float)reader[Y_FIELD_NAME];
nameAddressRecord.GeoLocation = new ESRI.ArcLogistics.Geometry.Point(x, y);
Address matchedAddress = new Address();
nameAddressRecord.MatchedAddress = matchedAddress;
matchedAddress.Unit = (string)reader[MUNIT_FIELD_NAME];
matchedAddress.AddressLine = (string)reader[MADDRESSLINE_FIELD_NAME];
matchedAddress.Locality1 = (string)reader[MLOCALITY1_FIELD_NAME];
matchedAddress.Locality2 = (string)reader[MLOCALITY2_FIELD_NAME];
matchedAddress.Locality3 = (string)reader[MLOCALITY3_FIELD_NAME];
matchedAddress.CountyPrefecture = (string)reader[MCOUNTYPREFECTURE_FIELD_NAME];
matchedAddress.PostalCode1 = (string)reader[MPOSTALCODE1_FIELD_NAME];
matchedAddress.PostalCode2 = (string)reader[MPOSTALCODE2_FIELD_NAME];
matchedAddress.StateProvince = (string)reader[MSTATEPROVINCE_FIELD_NAME];
matchedAddress.Country = (string)reader[MCOUNTRY_FIELD_NAME];
matchedAddress.FullAddress = (string)reader[MFULL_ADDRESS_FIELD_NAME];
string matchMethod = (string)reader[MATCHMETHOD_FIELD_NAME];
if (CommonHelpers.IsAllAddressFieldsEmpty(matchedAddress) && string.IsNullOrEmpty(matchedAddress.MatchMethod))
{
address.MatchMethod = matchMethod;
}
else
{
matchedAddress.MatchMethod = matchMethod;
}
return(nameAddressRecord);
}
/// <summary>
/// Convert local storage record to address candidate.
/// </summary>
/// <param name="nameAddressRecord">Local storage record.</param>
/// <returns>Address candidate.</returns>
private AddressCandidate _ConvertToCandidate(NameAddressRecord nameAddressRecord)
{
Debug.Assert(nameAddressRecord != null);
AddressCandidate candidateFromLocalStorage = new AddressCandidate();
// Candidate from local storage have maximum score.
candidateFromLocalStorage.Score = MAXIMUM_SCORE;
// Set candidate geolocation.
candidateFromLocalStorage.GeoLocation = new ESRI.ArcLogistics.Geometry.Point(
nameAddressRecord.GeoLocation.X, nameAddressRecord.GeoLocation.Y);
// Set candidate address.
Address candidateAddress = new Address();
candidateFromLocalStorage.Address = candidateAddress;
Address matchedAddress = nameAddressRecord.MatchedAddress;
Address address = nameAddressRecord.NameAddress.Address;
Address addressToCopy;
if (CommonHelpers.IsAllAddressFieldsEmpty(matchedAddress) && string.IsNullOrEmpty(matchedAddress.MatchMethod))
{
addressToCopy = address;
}
else
{
addressToCopy = matchedAddress;
}
addressToCopy.CopyTo(candidateAddress);
GeocodeHelpers.SetFullAddress(candidateAddress);
// Set locator.
foreach (LocatorInfo locator in App.Current.Geocoder.Locators)
{
if (locator.Title.Equals(candidateFromLocalStorage.Address.MatchMethod,
System.StringComparison.OrdinalIgnoreCase) ||
locator.Name.Equals(candidateFromLocalStorage.Address.MatchMethod,
System.StringComparison.OrdinalIgnoreCase))
{
candidateFromLocalStorage.Locator = locator;
break;
}
}
candidateFromLocalStorage.AddressType = ArcGiscomGeocoder.LocalStorageAddressType;
return(candidateFromLocalStorage);
}
/// <summary>
/// Updates name/address pair.
/// </summary>
/// <param name="localGeocoderRecord">Local geocoder record.</param>
/// <param name="format">Current Address Format.</param>
public void InsertOrUpdate(NameAddressRecord localGeocoderRecord, AddressFormat format)
{
Debug.Assert(localGeocoderRecord != null);
Debug.Assert(_IsInitialized());
try
{
NameAddressRecord recordFromDB = _GetRecordFromDB(
localGeocoderRecord.NameAddress, format);
string queryStr = string.Empty;
if (recordFromDB != null)
{
// Need to update record.
// Choose queries in dependence of used address format.
if (format == AddressFormat.SingleField)
{
queryStr = FULL_ADDRESS_UPDATE_QUERY;
}
else if (format == AddressFormat.MultipleFields)
{
queryStr = UPDATE_QUERY;
}
else
{
// Do nothing.
}
}
else
{
// Need to insert record.
queryStr = INSERT_QUERY;
}
SqlCeCommand command = new SqlCeCommand(queryStr, _connection);
_FillRecordCommandParameters(localGeocoderRecord, command);
int count = command.ExecuteNonQuery();
Debug.Assert(count == 1);
}
catch (Exception ex)
{
// Exception can be thrown if some other instance of the
// application added the item before this one.
Logger.Warning(ex);
}
}
/// <summary>
/// Get record from database.
/// </summary>
/// <param name="nameAddress">Name\address pair to find.</param>
/// <param name="format">Current Address Format.</param>
/// <returns>Extracted record.</returns>
private NameAddressRecord _GetRecordFromDB(NameAddress nameAddress, AddressFormat format)
{
NameAddressRecord nameAddressRecord = null;
string queryStr = string.Empty;
// Choose queries in dependence of used address format.
if (format == AddressFormat.SingleField)
{
queryStr = FULL_ADDRESS_SELECT_QUERY;
}
else if (format == AddressFormat.MultipleFields)
{
queryStr = SELECT_QUERY;
}
else
{
// Do nothing.
}
SqlCeCommand command = new SqlCeCommand(queryStr, _connection);
_FillCommandParameters(nameAddress, command);
SqlCeDataReader reader = null;
try
{
reader = command.ExecuteReader();
if (reader.Read())
{
nameAddressRecord = _ReadRecord(reader);
}
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(nameAddressRecord);
}
/// <summary>
/// Searches in local storage by name/address pair.
/// </summary>
/// <param name="nameAddress">Name/address pair to search.</param>
/// <param name="format">Current Address Format.</param>
/// <returns>Name address record.</returns>
public NameAddressRecord Search(NameAddress nameAddress, AddressFormat format)
{
Debug.Assert(nameAddress != null);
Debug.Assert(_IsInitialized());
NameAddressRecord recordFromDB = null;
try
{
recordFromDB = _GetRecordFromDB(nameAddress, format);
}
catch (Exception ex)
{
Logger.Warning(ex);
}
return(recordFromDB);
}
/// <summary>
/// Get address candidate from local storage.
/// </summary>
/// <param name="nameAddress">Name\Address pair to geocode.</param>
/// <returns>Address candidate if exists in local storage. Null otherwise.</returns>
public AddressCandidate Geocode(NameAddress nameAddress)
{
Debug.Assert(nameAddress != null);
Debug.Assert(_nameAddressStorage != null);
// Search record in storage.
NameAddressRecord nameAddressRecord = _nameAddressStorage.Search(
nameAddress, App.Current.Geocoder.AddressFormat);
AddressCandidate candidateFromLocalStorage = null;
// Extract candidate from record, if record exists.
if (nameAddressRecord != null)
{
candidateFromLocalStorage = _ConvertToCandidate(nameAddressRecord);
}
return(candidateFromLocalStorage);
}
/// <summary>
/// Convert local storage record to address candidate.
/// </summary>
/// <param name="nameAddressRecord">Local storage record.</param>
/// <returns>Address candidate.</returns>
private AddressCandidate _ConvertToCandidate(NameAddressRecord nameAddressRecord)
{
Debug.Assert(nameAddressRecord != null);
AddressCandidate candidateFromLocalStorage = new AddressCandidate();
// Candidate from local storage have maximum score.
candidateFromLocalStorage.Score = MAXIMUM_SCORE;
// Set candidate geolocation.
candidateFromLocalStorage.GeoLocation = new ESRI.ArcLogistics.Geometry.Point(
nameAddressRecord.GeoLocation.X, nameAddressRecord.GeoLocation.Y);
// Set candidate address.
Address candidateAddress = new Address();
candidateFromLocalStorage.Address = candidateAddress;
Address matchedAddress = nameAddressRecord.MatchedAddress;
Address address = nameAddressRecord.NameAddress.Address;
Address addressToCopy;
if (CommonHelpers.IsAllAddressFieldsEmpty(matchedAddress) && string.IsNullOrEmpty(matchedAddress.MatchMethod))
{
addressToCopy = address;
}
else
{
addressToCopy = matchedAddress;
}
addressToCopy.CopyTo(candidateAddress);
GeocodeHelpers.SetFullAddress(candidateAddress);
// Set locator.
foreach (LocatorInfo locator in App.Current.Geocoder.Locators)
{
if (locator.Title.Equals(candidateFromLocalStorage.Address.MatchMethod,
System.StringComparison.OrdinalIgnoreCase) ||
locator.Name.Equals(candidateFromLocalStorage.Address.MatchMethod,
System.StringComparison.OrdinalIgnoreCase))
{
candidateFromLocalStorage.Locator = locator;
break;
}
}
candidateFromLocalStorage.AddressType = ArcGiscomGeocoder.LocalStorageAddressType;
return candidateFromLocalStorage;
}
/// <summary>
/// Read record from database.
/// </summary>
/// <param name="reader">Database reader.</param>
/// <returns>Readed record.</returns>
private NameAddressRecord _ReadRecord(SqlCeDataReader reader)
{
NameAddressRecord nameAddressRecord = new NameAddressRecord();
NameAddress nameAddress = new NameAddress();
nameAddressRecord.NameAddress = nameAddress;
nameAddress.Name = (string)reader[NAME_FIELD_NAME];
Address address = new Address();
nameAddress.Address = address;
address.Unit = (string)reader[UNIT_FIELD_NAME];
address.AddressLine = (string)reader[ADDRESSLINE_FIELD_NAME];
address.Locality1 = (string)reader[LOCALITY1_FIELD_NAME];
address.Locality2 = (string)reader[LOCALITY2_FIELD_NAME];
address.Locality3 = (string)reader[LOCALITY3_FIELD_NAME];
address.CountyPrefecture = (string)reader[COUNTYPREFECTURE_FIELD_NAME];
address.PostalCode1 = (string)reader[POSTALCODE1_FIELD_NAME];
address.PostalCode2 = (string)reader[POSTALCODE2_FIELD_NAME];
address.StateProvince = (string)reader[STATEPROVINCE_FIELD_NAME];
address.Country = (string)reader[COUNTRY_FIELD_NAME];
address.FullAddress = (string)reader[FULL_ADDRESS_FIELD_NAME];
float x = (float)reader[X_FIELD_NAME];
float y = (float)reader[Y_FIELD_NAME];
nameAddressRecord.GeoLocation = new ESRI.ArcLogistics.Geometry.Point(x, y);
Address matchedAddress = new Address();
nameAddressRecord.MatchedAddress = matchedAddress;
matchedAddress.Unit = (string)reader[MUNIT_FIELD_NAME];
matchedAddress.AddressLine = (string)reader[MADDRESSLINE_FIELD_NAME];
matchedAddress.Locality1 = (string)reader[MLOCALITY1_FIELD_NAME];
matchedAddress.Locality2 = (string)reader[MLOCALITY2_FIELD_NAME];
matchedAddress.Locality3 = (string)reader[MLOCALITY3_FIELD_NAME];
matchedAddress.CountyPrefecture = (string)reader[MCOUNTYPREFECTURE_FIELD_NAME];
matchedAddress.PostalCode1 = (string)reader[MPOSTALCODE1_FIELD_NAME];
matchedAddress.PostalCode2 = (string)reader[MPOSTALCODE2_FIELD_NAME];
matchedAddress.StateProvince = (string)reader[MSTATEPROVINCE_FIELD_NAME];
matchedAddress.Country = (string)reader[MCOUNTRY_FIELD_NAME];
matchedAddress.FullAddress = (string)reader[MFULL_ADDRESS_FIELD_NAME];
string matchMethod = (string)reader[MATCHMETHOD_FIELD_NAME];
if (CommonHelpers.IsAllAddressFieldsEmpty(matchedAddress) && string.IsNullOrEmpty(matchedAddress.MatchMethod))
{
address.MatchMethod = matchMethod;
}
else
{
matchedAddress.MatchMethod = matchMethod;
}
return nameAddressRecord;
}
/// <summary>
/// Fill parameters of SQL command from local geocoder record.
/// </summary>
/// <param name="localGeocoderRecord">Source record.</param>
/// <param name="command">Command to fill.</param>
private void _FillRecordCommandParameters(NameAddressRecord localGeocoderRecord,
SqlCeCommand command)
{
_FillCommandParameters(localGeocoderRecord.NameAddress, command);
_AddParameter(command, X_PARAMETER_NAME, localGeocoderRecord.GeoLocation.X);
_AddParameter(command, Y_PARAMETER_NAME, localGeocoderRecord.GeoLocation.Y);
Address matchedAddress = localGeocoderRecord.MatchedAddress;
_AddParameter(command, MUNIT_PARAMETER_NAME, matchedAddress.Unit);
_AddParameter(command, MADDRESSLINE_PARAMETER_NAME, matchedAddress.AddressLine);
_AddParameter(command, MLOCALITY1_PARAMETER_NAME, matchedAddress.Locality1);
_AddParameter(command, MLOCALITY2_PARAMETER_NAME, matchedAddress.Locality2);
_AddParameter(command, MLOCALITY3_PARAMETER_NAME, matchedAddress.Locality3);
_AddParameter(command, MCOUNTYPREFECTURE_PARAMETER_NAME, matchedAddress.CountyPrefecture);
_AddParameter(command, MPOSTALCODE1_PARAMETER_NAME, matchedAddress.PostalCode1);
_AddParameter(command, MPOSTALCODE2_PARAMETER_NAME, matchedAddress.PostalCode2);
_AddParameter(command, MSTATEPROVINCE_PARAMETER_NAME, matchedAddress.StateProvince);
_AddParameter(command, MCOUNTRY_PARAMETER_NAME, matchedAddress.Country);
_AddParameter(command, MFULL_ADDRESS_PARAMETER_NAME, matchedAddress.FullAddress);
string matchMethod = localGeocoderRecord.MatchedAddress.MatchMethod;
if (string.IsNullOrEmpty(matchMethod))
{
matchMethod = localGeocoderRecord.NameAddress.Address.MatchMethod;
}
_AddParameter(command, MATCHMETHOD_PARAMETER_NAME, matchMethod);
}
/// <summary>
/// Updates name/address pair.
/// </summary>
/// <param name="localGeocoderRecord">Local geocoder record.</param>
/// <param name="format">Current Address Format.</param>
public void InsertOrUpdate(NameAddressRecord localGeocoderRecord, AddressFormat format)
{
Debug.Assert(localGeocoderRecord != null);
Debug.Assert(_IsInitialized());
try
{
NameAddressRecord recordFromDB = _GetRecordFromDB(
localGeocoderRecord.NameAddress, format);
string queryStr = string.Empty;
if (recordFromDB != null)
{
// Need to update record.
// Choose queries in dependence of used address format.
if (format == AddressFormat.SingleField)
queryStr = FULL_ADDRESS_UPDATE_QUERY;
else if (format == AddressFormat.MultipleFields)
queryStr = UPDATE_QUERY;
else
{
// Do nothing.
}
}
else
{
// Need to insert record.
queryStr = INSERT_QUERY;
}
SqlCeCommand command = new SqlCeCommand(queryStr, _connection);
_FillRecordCommandParameters(localGeocoderRecord, command);
int count = command.ExecuteNonQuery();
Debug.Assert(count == 1);
}
catch (Exception ex)
{
// Exception can be thrown if some other instance of the
// application added the item before this one.
Logger.Warning(ex);
}
}
