private void UpdateZipStreetsDownloaded(DB.VoteZipNew.ZipStreetsDownloadedTable table,
ref int updateErrorCount)
{
DB.VoteZipNew.ZipStreetsDownloaded.UpdateTable(table, 0,
ConflictOption.CompareAllSearchableValues, true);
var errors = table.Count(row => row.HasErrors);
if (errors > 0)
{
updateErrorCount += errors;
AppendStatusText("{0} update errors", updateErrorCount);
}
}
private int LoadState(TextReader textReader, int skipRows)
{
var rowCount = 0;
var updateErrorCount = 0;
using (var csvReader = new CsvReader(textReader, true))
{
var headers = csvReader.GetFieldHeaders();
// process skips
if (skipRows > 0)
{
AppendStatusText("Skipping {0} rows", skipRows);
while (skipRows > 0 && csvReader.ReadNextRecord())
{
skipRows--;
rowCount++;
}
}
if (ToCsv)
{
var csvWriter = new SimpleCsvWriter();
while (csvReader.ReadNextRecord())
{
var error = false;
rowCount++;
try
{
foreach (var field in headers)
{
var value = csvReader[field];
// The incoming data has a few anomolies...
value = FixupField(field, value);
//if (field == "AddressPrimaryLowNumber" ||
// field == "AddressPrimaryHighNumber")
// value = FixNumericPrimaryAddress(value);
csvWriter.AddField(value);
}
}
catch (Exception ex)
{
error = true;
AppendStatusText("Row {0} skipped due to {1}", rowCount, ex.Message);
}
if (!error)
{
csvWriter.Write(_TextWriter);
}
else
{
csvWriter.Clear();
}
if (rowCount % 50000 == 0) // report every 50000 rows
{
AppendStatusText("{0} rows written", rowCount);
}
}
}
else
{
var table = new DB.VoteZipNew.ZipStreetsDownloadedTable();
while (csvReader.ReadNextRecord())
{
var row = table.NewRow();
var error = false;
rowCount++;
try
{
foreach (var field in headers)
{
var value = csvReader[field];
// The incoming data has a few anomolies...
value = FixupField(field, value);
//if (field == "AddressPrimaryLowNumber" ||
// field == "AddressPrimaryHighNumber")
// value = FixNumericPrimaryAddress(field, value);
row[field] = value;
}
}
catch (Exception ex)
{
error = true;
AppendStatusText("Row {0} skipped due to {1}", rowCount, ex.Message);
}
if (!error)
{
table.AddRow(row);
}
if (rowCount % 1000 == 0) // flush every 1000 rows
{
UpdateZipStreetsDownloaded(table, ref updateErrorCount);
table = new DB.VoteZipNew.ZipStreetsDownloadedTable();
}
if (rowCount % 50000 == 0) // report every 50000 rows
{
AppendStatusText("{0} rows written", rowCount);
}
}
UpdateZipStreetsDownloaded(table, ref updateErrorCount);
}
}
return(rowCount);
}
private void ApplyUpdates(TextReader textReader)
{
// In suppressUpdate mode, this keeps track of rows that would have been deleted
Dictionary <string, object> phantomDeletes = new Dictionary <string, object>();
int adds = 0;
int deletes = 0;
int couldNotAdd = 0;
int couldNotDelete = 0;
int rowCount = 0;
bool suppressUpdate = this.Invoke(() => SuppressUpdateCheckBox.Checked);
string actionField = "Action";
using (var csvReader = new CsvReader(textReader, true))
{
int fieldCount = csvReader.FieldCount;
string[] headers = csvReader.GetFieldHeaders();
while (csvReader.ReadNextRecord())
{
string action = csvReader[actionField].ToString();
string updateKey = csvReader["UpdateKey"].ToString();
UpdatedStreets[new StreetInfo(
csvReader["ZipCode"],
csvReader["StPreDirAbbr"],
csvReader["StName"],
csvReader["StSuffixAbbr"],
csvReader["StPostDirAbbr"])] = null;
switch (action)
{
case "A":
if (suppressUpdate)
{
if (!phantomDeletes.ContainsKey(updateKey) &&
DB.VoteZipNew.ZipStreetsDownloaded.UpdateKeyExists(updateKey))
{
couldNotAdd++;
}
else
{
adds++;
}
}
else
{
try
{
var table = new DB.VoteZipNew.ZipStreetsDownloadedTable();
var row = table.NewRow();
foreach (string field in headers)
{
string value = csvReader[field];
// The incoming data has a few anomolies...
if (field == "AddressPrimaryLowNumber" ||
field == "AddressPrimaryHighNumber")
{
value = FixNumericPrimaryAddress(value);
}
row[field] = value;
}
table.AddRow(row);
DB.VoteZipNew.ZipStreetsDownloaded.UpdateTable(table, 0);
adds++;
AddToList(csvReader);
}
catch
{
couldNotAdd++;
}
}
break;
case "D":
if (suppressUpdate)
{
if (DB.VoteZipNew.ZipStreetsDownloaded.UpdateKeyExists(updateKey))
{
deletes++;
if (!phantomDeletes.ContainsKey(updateKey))
{
phantomDeletes.Add(updateKey, null);
}
}
else
{
couldNotDelete++;
}
}
else
{
try
{
int deleted = DB.VoteZipNew.ZipStreetsDownloaded.DeleteByUpdateKey(updateKey, 0);
if (deleted != 1)
{
throw new VoteException();
}
deletes++;
AddToList(csvReader);
}
catch
{
couldNotDelete++;
}
}
break;
default:
AppendStatusText("Invalid action: {0}", csvReader[actionField]);
break;
}
rowCount++;
if (rowCount % 1000 == 0)
{
AppendStatusText("{0} rows processed", rowCount);
}
}
}
AppendStatusText("Processed data, {0} rows", rowCount);
AppendStatusText("Adds: {0}", adds);
AppendStatusText("Deletes: {0}", deletes);
AppendStatusText("Could not add: {0}", couldNotAdd);
AppendStatusText("Could not delete: {0}", couldNotDelete);
}