public static void TruncateTable(int commandTimeout)
{
string cmdText = "TRUNCATE TABLE USZDNew";
DbCommand cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
VoteZipNewLocalDb.ExecuteNonQuery(cmd);
}
public static void Create(int commandTimeout)
{
const string cmdText = "CREATE TABLE `ZipStreetsTemp` (" +
" `ZipCode` char(5) NOT NULL," + " `UpdateKey` varchar(14) NOT NULL," +
" `StPreDirAbbr` varchar(2) NOT NULL," + " `StName` varchar(28) NOT NULL," +
" `StSuffixAbbr` varchar(4) NOT NULL," +
" `StPostDirAbbr` varchar(2) NOT NULL," +
" `AddressPrimaryLowNumber` varchar(10) NOT NULL," +
" `AddressPrimaryHighNumber` varchar(10) NOT NULL," +
" `AddressPrimaryEvenOdd` varchar(1) NOT NULL," +
" `BuildingName` varchar(40) NOT NULL," +
" `AddressSecAbbr` varchar(4) NOT NULL," +
" `AddressSecLowNumber` varchar(10) NOT NULL," +
" `AddressSecHighNumber` varchar(10) NOT NULL," +
" `AddressSecOddEven` varchar(1) NOT NULL," +
" `Metaphone` varchar(28) NOT NULL," + " `StateCode` char(2) NOT NULL," +
" `Congress` char(3) NOT NULL," + " `StateSenate` char(3) NOT NULL," +
" `StateHouse` char(3) NOT NULL," + " `County` char(3) NOT NULL," +
" PRIMARY KEY (`UpdateKey`)," + " KEY `index2` (`ZipCode`,`StName`)," +
" KEY `index3` (`ZipCode`,`Metaphone`)" +
" ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
VoteZipNewLocalDb.ExecuteNonQuery(cmd);
}
public static void Drop(int commandTimeout)
{
const string cmdText = "DROP TABLE IF EXISTS ZipCitiesDownloadedTemp";
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
VoteZipNewLocalDb.ExecuteNonQuery(cmd);
}
public static ZipSingleUszdTable GetDataByZipCodes(
string[] zipCodes, int commandTimeout)
{
var cmdText = $"{SelectAllCommandText} WHERE ZipCode IN ('{string.Join("','", zipCodes)}')";
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
return(FillTable(cmd, ZipSingleUszdTable.ColumnSet.All));
}
public static void DeleteByLdsStateCode(string ldsStateCode, int commandTimeout)
{
const string cmdText = "DELETE FROM USZDNew WHERE ST=@LdsStateCode";
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
VoteZipNewLocalDb.AddCommandParameter(cmd, "LdsStateCode", ldsStateCode);
VoteZipNewLocalDb.ExecuteNonQuery(cmd);
}
public static void Create(int commandTimeout)
{
const string cmdText = "CREATE TABLE `ZipSingleUSZDTemp` (" +
" `ZipCode` char(5) NOT NULL," + " `Congress` char(2) NOT NULL," +
" `StateSenate` char(3) NOT NULL," + " `StateHouse` char(3) NOT NULL," +
" `LdsStateCode` char(2) NOT NULL," + " `County` char(3) NOT NULL," +
" `StateCode` char(2) NOT NULL," + " PRIMARY KEY (`ZipCode`)" +
" ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
VoteZipNewLocalDb.ExecuteNonQuery(cmd);
}
private void ProcessOneDataFile(string path)
{
AppendStatusText("Processing file {0}", path);
string cmdText = string.Format(
"LOAD DATA INFILE '{0}' into table `votezipnew`.`uszdnew` " +
"FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\' " +
"LINES STARTING BY '' TERMINATED BY '\\r\\n';",
// need to double up the backslashes in the path
path.Replace(@"\", @"\\"));
DbCommand cmd = VoteZipNewLocalDb.GetCommand(cmdText, 0);
VoteZipNewLocalDb.ExecuteNonQuery(cmd);
}
private void ProcessDataFile()
{
var path = DataPathTextBox.Text.Trim();
AppendStatusText("Processing file {0}", path);
// problem with @varaibles & @parameters
var cmdText =
$"LOAD DATA INFILE '{path.Replace(@"\", @"\\")}' into table `votezipnew`.`uszdnew` " +
"FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\' " +
"LINES STARTING BY '' TERMINATED BY '\\r\\n' " +
"(ZIP5,ZIP4,@dummy,@dummy,@dummy,ST,CNTY,C,RT,F,CD,SD,HD)";
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, 0);
VoteZipNewLocalDb.AddCommandParameter(cmd, "dummy", string.Empty);
VoteZipNewLocalDb.ExecuteNonQuery(cmd);
}
public static void Create(int commandTimeout)
{
const string cmdText = "CREATE TABLE `ZipCitiesDownloadedTemp` (" +
" `ZipCode` char(5) DEFAULT NULL," + " `City` varchar(35) DEFAULT NULL," +
" `State` char(2) DEFAULT NULL," + " `County` varchar(45) DEFAULT NULL," +
" `AreaCode` varchar(55) DEFAULT NULL," + " `CityType` char(1) DEFAULT NULL," +
" `CityAliasAbbreviation` varchar(13) DEFAULT NULL," +
" `CityAliasName` varchar(35) DEFAULT NULL," +
" `Latitude` decimal(18,0) DEFAULT NULL," +
" `Longitude` decimal(18,0) DEFAULT NULL," +
" `TimeZone` char(2) DEFAULT NULL," + " `Elevation` int(10) DEFAULT NULL," +
" `CountyFIPS` char(3) DEFAULT NULL," +
" `DayLightSaving` char(1) DEFAULT NULL," +
" `PreferredLastLineKey` varchar(10) DEFAULT NULL," +
" `ClassificationCode` char(1) DEFAULT NULL," +
" `MultiCounty` char(1) DEFAULT NULL," + " `StateFIPS` char(2) DEFAULT NULL," +
" `CityStateKey` char(6) DEFAULT NULL," +
" `CityAliasCode` varchar(5) DEFAULT NULL," +
" `PrimaryRecord` char(1) DEFAULT NULL," +
" `CityMixedCase` varchar(35) DEFAULT NULL," +
" `CityAliasMixedCase` varchar(35) DEFAULT NULL," +
" `StateANSI` varchar(2) DEFAULT NULL," +
" `CountyANSI` varchar(3) DEFAULT NULL," +
" `FacilityCode` varchar(1) DEFAULT NULL," +
" `CityDeliveryIndicator` varchar(1) DEFAULT NULL," +
" `CarrierRouteRateSortation` varchar(1) DEFAULT NULL," +
" `FinanceNumber` varchar(6) DEFAULT NULL," +
" `UniqueZIPName` varchar(1) DEFAULT NULL," +
" `MetaphoneAliasName` varchar(35) DEFAULT NULL," +
" `MetaphoneAliasAbbreviation` varchar(13) DEFAULT NULL," +
" UNIQUE KEY `index4` (`ZipCode`,`CityAliasName`)," +
" KEY `index1` (`ZipCode`)," + " KEY `index2` (`State`,`CityAliasName`)," +
" KEY `index3` (`CityAliasName`)," +
" KEY `index5` (`State`,`MetaphoneAliasName`)," +
" KEY `index6` (`State`,`CityAliasAbbreviation`)," +
" KEY `index7` (`CityAliasAbbreviation`)," +
" KEY `index8` (`State`,`MetaphoneAliasAbbreviation`)" +
" ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
VoteZipNewLocalDb.ExecuteNonQuery(cmd);
}
//public static ZipStreetsTable GetDataByZipCode(
// string zipCode, string houseNumber, string directionalPrefix,
// string streetNamePattern, string streetSuffix, string directionalSuffix)
//{
// return GetDataByZipCode(
// zipCode, houseNumber, directionalPrefix, streetNamePattern, streetSuffix,
// directionalSuffix, -1);
//}
//public static ZipStreetsTable GetDataByZipCode(
// string zipCode, string houseNumber, string directionalPrefix,
// string streetNamePattern, string streetSuffix, string directionalSuffix,
// int commandTimeout)
//{
// var houseNumberCondition = string.Empty;
// var evenOddCondition = string.Empty;
// if (houseNumber != null)
// {
// // We include wildcards (both numbers empty)
// // We also match the length to prevent false matches. Only need to
// // check one length since they should be the same.
// houseNumberCondition = " AND (@HouseNumber >= AddressPrimaryLowNumber " +
// " AND @HouseNumber <= AddressPrimaryHighNumber " +
// " AND Length(@HouseNumber) = Length(AddressPrimaryHighNumber) " +
// " OR AddressPrimaryLowNumber = '' AND AddressPrimaryHighNumber = '') ";
// // the evenOddCondition is based on the last numeric digit, and excludes
// // the non-matching condition (ie "even" becomes <> 'O') to allow 'B' through
// for (var n = houseNumber.Length - 1; n >= 0; n--)
// {
// var lastDigit = houseNumber[n];
// if (char.IsDigit(lastDigit))
// {
// evenOddCondition = lastDigit % 2 == 0
// ? " AND AddressPrimaryEvenOdd <> 'O' "
// : " AND AddressPrimaryEvenOdd <> 'E' ";
// break;
// }
// }
// }
// var directionalPrefixCondition = string.Empty;
// if (directionalPrefix != null)
// directionalPrefixCondition = " AND @DirectionalPrefix = StPreDirAbbr ";
// var streetSuffixCondition = string.Empty;
// if (streetSuffix != null)
// streetSuffixCondition = " AND @StreetSuffix = StSuffixAbbr ";
// var directionalSuffixCondition = string.Empty;
// if (directionalSuffix != null)
// directionalSuffixCondition = " AND @DirectionalSuffix = StPostDirAbbr ";
// var cmdText =
// string.Format(
// SelectAllCommandText +
// " WHERE ZipCode=@ZipCode AND StName LIKE @StreetNamePattern" +
// " {0} {1} {2} {3} {4}", houseNumberCondition, directionalPrefixCondition,
// streetSuffixCondition, directionalSuffixCondition, evenOddCondition);
// var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
// VoteZipNewLocalDb.AddCommandParameter(cmd, "ZipCode", zipCode);
// VoteZipNewLocalDb.AddCommandParameter(
// cmd, "StreetNamePattern", streetNamePattern);
// if (houseNumber != null)
// VoteZipNewLocalDb.AddCommandParameter(cmd, "HouseNumber", houseNumber);
// if (directionalPrefix != null)
// VoteZipNewLocalDb.AddCommandParameter(
// cmd, "DirectionalPrefix", directionalPrefix);
// if (streetSuffix != null)
// VoteZipNewLocalDb.AddCommandParameter(cmd, "StreetSuffix", streetSuffix);
// if (directionalSuffix != null)
// VoteZipNewLocalDb.AddCommandParameter(
// cmd, "DirectionalSuffix", directionalSuffix);
// return FillTable(cmd);
//}
//// These versions uses a zipCode array
//public static ZipStreetsTable GetDataByZipCodes(string[] zipCodes)
//{
// return GetDataByZipCodes(zipCodes, -1);
//}
//public static ZipStreetsTable GetDataByZipCodes(
// string[] zipCodes, int commandTimeout)
//{
// // We violate the "only-parameters" rule because they can't be used with IN
// string zipCodeCondition;
// if (zipCodes.Length == 1)
// zipCodeCondition = " ZipCode='" + zipCodes[0] + "' ";
// else
// zipCodeCondition = " ZipCode IN ('" + string.Join("','", zipCodes) + "') ";
// var cmdText = string.Format(SelectAllCommandText + " WHERE" + zipCodeCondition);
// var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
// return FillTable(cmd);
//}
//public static ZipStreetsTable GetDataByZipCodes(
// string[] zipCodes, string houseNumber, string directionalPrefix,
// string streetNamePattern, string streetSuffix, string directionalSuffix,
// bool useMetaphone)
//{
// return GetDataByZipCodes(
// zipCodes, houseNumber, directionalPrefix, streetNamePattern, streetSuffix,
// directionalSuffix, useMetaphone, -1);
//}
public static ZipStreetsTable GetDataByZipCodes(
string[] zipCodes, string houseNumber, string directionalPrefix,
string streetNamePattern, string streetSuffix, string directionalSuffix,
bool useMetaphone, int commandTimeout)
{
// We violate the "only-parameters" rule because they can't be used with IN
string zipCodeCondition;
if (zipCodes.Length == 1)
{
zipCodeCondition = " AND ZipCode='" + zipCodes[0] + "' ";
}
else
{
zipCodeCondition = " AND ZipCode IN ('" + string.Join("','", zipCodes) +
"') ";
}
var houseNumberCondition = string.Empty;
var evenOddCondition = string.Empty;
if (!string.IsNullOrEmpty(houseNumber))
{
// We include wildcards (both numbers empty)
// We also match the length to prevent false matches. Only need to
// check one length since they should be the same.
houseNumberCondition = " AND (@HouseNumber >= AddressPrimaryLowNumber " +
" AND @HouseNumber <= AddressPrimaryHighNumber " +
" AND Length(@HouseNumber) = Length(AddressPrimaryHighNumber) " +
" OR AddressPrimaryLowNumber = '' AND AddressPrimaryHighNumber = '') ";
// the evenOddCondition is based on the last numeric digit, and excludes
// the non-matching condition (ie "even" becomes <> 'O') to allow 'B' to match
for (var n = houseNumber.Length - 1; n >= 0; n--)
{
var lastDigit = houseNumber[n];
if (!char.IsDigit(lastDigit))
{
continue;
}
evenOddCondition = lastDigit % 2 == 0
? " AND AddressPrimaryEvenOdd <> 'O' "
: " AND AddressPrimaryEvenOdd <> 'E' ";
break;
}
}
string streetCondition;
if (useMetaphone)
{
streetNamePattern = streetNamePattern.Replace('%', ' ')
.Trim();
streetCondition = " Metaphone = @StreetNamePattern ";
}
else
{
streetCondition = " StName LIKE @StreetNamePattern ";
}
var directionalPrefixCondition = string.Empty;
if (!string.IsNullOrEmpty(directionalPrefix))
{
directionalPrefixCondition = " AND @DirectionalPrefix = StPreDirAbbr ";
}
var streetSuffixCondition = string.Empty;
if (!string.IsNullOrEmpty(streetSuffix))
{
streetSuffixCondition = " AND @StreetSuffix = StSuffixAbbr ";
}
var directionalSuffixCondition = string.Empty;
if (!string.IsNullOrEmpty(directionalSuffix))
{
directionalSuffixCondition = " AND @DirectionalSuffix = StPostDirAbbr ";
}
var cmdText =
string.Format(
SelectAllCommandText + " WHERE {0} {1} {2} {3} {4} {5} {6}",
streetCondition, zipCodeCondition, houseNumberCondition,
directionalPrefixCondition, streetSuffixCondition,
directionalSuffixCondition, evenOddCondition);
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
VoteZipNewLocalDb.AddCommandParameter(
cmd, "StreetNamePattern", streetNamePattern);
if (houseNumber != null)
{
VoteZipNewLocalDb.AddCommandParameter(cmd, "HouseNumber", houseNumber);
}
if (directionalPrefix != null)
{
VoteZipNewLocalDb.AddCommandParameter(
cmd, "DirectionalPrefix", directionalPrefix);
}
if (streetSuffix != null)
{
VoteZipNewLocalDb.AddCommandParameter(cmd, "StreetSuffix", streetSuffix);
}
if (directionalSuffix != null)
{
VoteZipNewLocalDb.AddCommandParameter(
cmd, "DirectionalSuffix", directionalSuffix);
}
return(FillTable(cmd));
}
