public ApplicationDbContext(DbContextOptions <ApplicationDbContext> options)
: base(options)
{
SqlConnection conn = (SqlConnection)Database.GetDbConnection();
conn.AddAzureToken();
}
public void TruncatePreComputedNearestPostcodes(string connectionString)
{
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.AddAzureToken();
sqlConnection.Open();
using (SqlCommand sqlCmd = new SqlCommand("TRUNCATE TABLE [Address].[PreComputedNearestPostcodes]", sqlConnection))
{
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandTimeout = 30;
sqlCmd.ExecuteNonQuery();
}
}
Console.WriteLine($"Precomputed nearby postcodes truncation complete ([Address].[PreComputedNearestPostcodes])");
}
public void TruncateStagingTable(string connectionString)
{
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.AddAzureToken();
sqlConnection.Open();
using (SqlCommand sqlCmd = new SqlCommand("TRUNCATE TABLE [Staging].[Postcode_Staging]", sqlConnection))
{
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandTimeout = 30;
sqlCmd.ExecuteNonQuery();
}
}
Console.WriteLine($"Staging table ([Staging].[Postcode_Staging]) truncation complete");
}
public void LoadFromStagingTableAndSwitch(string connectionString)
{
var stopwatch = new Stopwatch();
stopwatch.Start();
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.AddAzureToken();
sqlConnection.Open();
using (SqlCommand sqlCmd = new SqlCommand("EXEC [Staging].[LoadPostcodesFromStagingTableAndSwitch]", sqlConnection))
{
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandTimeout = 0; // the merge will take a while (takes around 15 minutes on my laptop)
sqlCmd.ExecuteNonQuery();
}
}
stopwatch.Stop();
Console.WriteLine($"Loading, updating and switching took {stopwatch.Elapsed}");
}
public void LoadPostcodesIntoStagingTable(string postCodeFileLocation, string connectionString, int batchSize, decimal maxInvalidRowsPercentage)
{
Initialise();
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.AddAzureToken();
sqlConnection.Open();
TruncateStagingTable(sqlConnection);
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
try
{
DataTable dataTable = CreateDataTable();
using (TextReader reader = new StreamReader(postCodeFileLocation))
{
IEnumerable <string> lines = reader.Lines().Skip(1);
int numberOfRowsProcessedInThisBatch = 0;
foreach (string row in lines)
{
_numberOfRows++;
AddDataRowToDataTable(dataTable, row);
numberOfRowsProcessedInThisBatch++;
if (numberOfRowsProcessedInThisBatch >= batchSize)
{
BulkInsert(sqlConnection, sqlTransaction, dataTable);
Console.WriteLine($"Processed {batchSize} rows");
numberOfRowsProcessedInThisBatch = 0;
dataTable.Rows.Clear();
}
}
// insert any remaining rows
BulkInsert(sqlConnection, sqlTransaction, dataTable);
Console.WriteLine($"Processed {numberOfRowsProcessedInThisBatch} rows");
if (GetInvalidRowsPercentage() <= maxInvalidRowsPercentage)
{
Console.WriteLine("Committing transaction");
sqlTransaction.Commit();
}
else
{
Console.WriteLine();
Console.WriteLine("#############################################################################");
Console.WriteLine("## ERROR: Not committing transaction as there are too many invalid rows");
Console.WriteLine("#############################################################################");
Console.WriteLine();
sqlTransaction.Rollback();
}
}
}
catch (Exception ex)
{
sqlTransaction.Rollback();
Console.WriteLine(ex);
}
}
Console.WriteLine($"Loading postcodes into staging table took {stopWatch.Elapsed} for {_numberOfRows:N0} rows");
int validRows = _numberOfRows - _numberOfInvalidRows;
Console.WriteLine($"Total rows processed: {_numberOfRows:N0}");
Console.WriteLine($"Total valid rows: {validRows:N0}");
Console.WriteLine($"Total invalid rows: {_numberOfInvalidRows:N0}");
Console.WriteLine($"Invalid rows percentage: {Math.Round(GetInvalidRowsPercentage(), 2)}%");
Console.WriteLine($"Total terminated postcodes: {_numberOfTerminatedPostcodes}");
Console.WriteLine($"Terminated postcode percentage: {Math.Round((decimal)_numberOfTerminatedPostcodes / _numberOfRows * 100, 2)}%");
Console.WriteLine($"Total invalid postcodes: {_numberOfInvalidPostcodes:N0}");
Console.WriteLine($"Total invalid latitudes: {_numberOfInvalidLatitudes:N0}");
Console.WriteLine($"Total invalid longitudes: {_numberOfInvalidLongitudes:N0}");
Console.WriteLine();
if (_invalidPostcodes.Any())
{
Console.WriteLine($"Invalid Postcodes List (excludes nulls and empty postcodes):");
foreach (string invalidPostcode in _invalidPostcodes.OrderBy(x => x))
{
Console.WriteLine(invalidPostcode);
}
}
else
{
Console.WriteLine("There were no invalid postcodes to display (excludes null and empty postcodes)");
}
}
