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