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