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