// ReSharper disable MemberCanBePrivate.Global
// ReSharper disable MemberCanBeProtected.Global
// ReSharper disable UnusedMember.Global
// ReSharper disable UnusedMethodReturnValue.Global
// ReSharper disable UnusedAutoPropertyAccessor.Global
// ReSharper disable UnassignedField.Global
#endregion ReSharper disable
public static UszdTable GetDataByZipPlus4List(
IEnumerable <ZipPlus4> zipPlus4List, int commandTimeout = -1)
{
var sbWhere = new StringBuilder();
sbWhere.Append("(");
var first = true;
foreach (var zipPlus4 in zipPlus4List)
{
if (!first)
{
sbWhere.Append(" OR ");
}
first = false;
sbWhere.Append("(ZIP5='");
sbWhere.Append(zipPlus4.Zip5);
sbWhere.Append("' AND ZIP4='");
sbWhere.Append(zipPlus4.Zip4);
sbWhere.Append("')");
}
sbWhere.Append(")");
var cmdText = $"{SelectAllCommandText} WHERE {sbWhere}";
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
return(FillTable(cmd, UszdTable.ColumnSet.All));
}
public static ZipCitiesDownloadedTable GetCityAliasesDataLikeCityAliasName(
string cityAliasName, int commandTimeout)
{
var cmdText = SelectCityAliasesCommandText +
" WHERE CityAliasName LIKE @CityAliasName";
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
VoteZipNewLocalDb.AddCommandParameter(cmd, "CityAliasName", cityAliasName);
return(FillTable(cmd, ZipCitiesDownloadedTable.ColumnSet.CityAliases));
}
public static UszdTable GetDataByZip4List(
string zip5, IEnumerable <string> zip4List, int commandTimeout = -1)
{
var cmdText =
$"{SelectAllCommandText} WHERE ZIP5=@Zip5 AND ZIP4 IN ('{string.Join("','", zip4List)}')";
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
VoteZipNewLocalDb.AddCommandParameter(cmd, "Zip5", zip5);
return(FillTable(cmd, UszdTable.ColumnSet.All));
}
public static UszdTable GetDataByZipPlus4Range(
string zip5, string zip4Low, string zip4High, int commandTimeout = -1)
{
var cmdText = SelectAllCommandText +
" WHERE ZIP5=@Zip5 AND ZIP4 >= @Zip4Low AND ZIP4 <= @Zip4High";
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
VoteZipNewLocalDb.AddCommandParameter(cmd, "Zip5", zip5);
VoteZipNewLocalDb.AddCommandParameter(cmd, "Zip4Low", zip4Low);
VoteZipNewLocalDb.AddCommandParameter(cmd, "Zip4High", zip4High);
return(FillTable(cmd, UszdTable.ColumnSet.All));
}
public static ZipCitiesDownloadedTable GetCityAliasesDataByStateLikeCityAliasName
(string state, string cityAliasName, int commandTimeout)
{
const string cityCondition = " CityAliasName LIKE @CityAliasName ";
var cmdText = SelectCityAliasesCommandText + " WHERE State=@State AND" +
cityCondition;
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, commandTimeout);
VoteZipNewLocalDb.AddCommandParameter(cmd, "State", state);
VoteZipNewLocalDb.AddCommandParameter(cmd, "CityAliasName", cityAliasName);
return(FillTable(cmd, ZipCitiesDownloadedTable.ColumnSet.CityAliases));
}
public static string GetPrimaryCityByZipCode(string zipCode)
{
const string cmdText = "SELECT City FROM ZipCitiesDownloaded" +
" WHERE ZipCode=@ZipCode" + " AND PrimaryRecord='P'";
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, -1);
VoteZipNewLocalDb.AddCommandParameter(cmd, "ZipCode", zipCode);
var result = VoteZipNewLocalDb.ExecuteScalar(cmd);
if ((result == null) || (result == DBNull.Value))
{
return(null);
}
return(result as string);
}
public static string GetFirstStateByZipCode(string zipCode)
{
var cmdText = "SELECT State FROM ZipCitiesDownloaded WHERE ZipCode=@ZipCode";
cmdText = VoteDb.InjectSqlLimit(cmdText, 1);
var cmd = VoteZipNewLocalDb.GetCommand(cmdText, -1);
VoteZipNewLocalDb.AddCommandParameter(cmd, "ZipCode", zipCode);
var result = VoteZipNewLocalDb.ExecuteScalar(cmd);
if ((result == null) || (result == DBNull.Value))
{
return(null);
}
return(result as string);
}