/// <summary> /// The dispose API controller. /// </summary> /// <param name="disposing"> /// True when disposing. /// </param> protected override void Dispose(bool disposing) { if (disposing) { db.Dispose(); } base.Dispose(disposing); }
public ActionResult Index(CodePointUploadModel model) { if (ModelState.IsValid) { try { String[] validFileTypes = { ".zip" }; Boolean validFileType = false; String CodePointFolder = Constants.ConfigSettings.CodePointUploadVirtualDirectoryName; if (CodePointFolder.EndsWith(@"\")) { CodePointFolder = CodePointFolder.Substring(0, CodePointFolder.Length - 1); } // Check if config setting is valid if (String.IsNullOrEmpty(CodePointFolder) || !Directory.Exists(CodePointFolder)) { ModelState.AddModelError("", AppGlobal.Language.GetText(this, "CodePointFolderNotConfigured", "Configuration setting VirtualDirectoryNameForStoringCodePointFiles is not set or is incorrect")); } foreach (String fileType in validFileTypes) { if (model.File.FileName.ToLower().EndsWith(fileType)) { validFileType = true; break; } } if (!validFileType) { ModelState.AddModelError("File", AppGlobal.Language.GetText(this, "ZIPFilesOnly", "Please upload a ZIP file")); } else { String ZIPFile = Path.Combine(CodePointFolder, "CodePoint.zip"); // Save the zip file model.File.SaveAs(ZIPFile); // Unzip all the CSV files using (ZipArchive za = ZipFile.OpenRead(ZIPFile)) { foreach (ZipArchiveEntry entry in za.Entries.Where(entry => entry.Name.ToLower().EndsWith(".csv")).Where(entry => entry.Name.ToLower() != "code-point_open_column_headers.csv")) { entry.ExtractToFile(Path.Combine(CodePointFolder, entry.Name), true); } } // Delete the zip file System.IO.File.Delete(ZIPFile); MetadataUpload metadataUpload = new MetadataUpload { MetadataUploadTypeId = (int)Constants.MetadataUploadType.CodePoint, CreatedByUserId = Permission.GetCurrentUserGuid().ToString(), CreatedDateTimeUtc = DateTime.UtcNow, FileName = model.File.FileName, FileSizeInBytes = model.File.ContentLength, RowsBefore = db.GeoLocations.Count() }; var sw = new Stopwatch(); sw.Start(); // Import the new data String[] csvFiles = Directory.GetFiles(CodePointFolder, "*.csv"); if (csvFiles.GetLength(0) == 0) { ModelState.AddModelError("", AppGlobal.Language.GetText(this, "UnableToFindCSVFile", "Unable to find any CSV files to import")); DeleteProgressMessage(); } else { AddOrReplaceProgressMessage(AppGlobal.Language.GetText(this, "StartingImport", "Starting Import...")); Boolean cancellingImport = false; String importingMessageText = AppGlobal.Language.GetText(this, "ImportingFileXOfY", "Importing file {0} of {1}..."); String mergingMessageText = AppGlobal.Language.GetText(this, "MergeData", "Merging Data..."); String removingTempDataMessageText = AppGlobal.Language.GetText(this, "RemovingTemporaryData", "Removing Temporary Data..."); String importSuccessfulMessageText = AppGlobal.Language.GetText(this, "ImportSuccessful", "Code Point Data Successfully Imported"); String importCancelledMessageText = AppGlobal.Language.GetText(this, "ImportCancelled", "Code Point Data Import Cancelled"); String importErrorMessageText = AppGlobal.Language.GetText(this, "ImportError", "Error Importing Code Point Data : {0}"); new Thread(() => { try { ProviderPortalEntities _db = new ProviderPortalEntities(); // Open the database using (SqlConnection conn = new SqlConnection(_db.Database.Connection.ConnectionString)) { conn.Open(); using (SqlTransaction transaction = conn.BeginTransaction()) { // Truncate the temporary import table just incase there's still data in there. SqlCommand comm = new SqlCommand("TRUNCATE TABLE [dbo].[Import_GeoLocation];", conn, transaction); comm.ExecuteNonQuery(); // Setup the DataTable DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn { ColumnName = "Postcode", AllowDBNull = false, DataType = typeof(String), MaxLength = 8 }); dt.Columns.Add(new DataColumn { ColumnName = "Lat", AllowDBNull = false, DataType = typeof(Decimal) }); dt.Columns.Add(new DataColumn { ColumnName = "Lng", AllowDBNull = false, DataType = typeof(Decimal) }); dt.Columns.Add(new DataColumn { ColumnName = "Northing", AllowDBNull = false, DataType = typeof(Decimal) }); dt.Columns.Add(new DataColumn { ColumnName = "Easting", AllowDBNull = false, DataType = typeof(Decimal) }); Int32 i = 1; foreach (String csvFile in csvFiles) { // Check if we have stopped the import if (IsCancellingImport(new ProviderPortalEntities())) { break; } // Remove all the rows dt.Clear(); // Write the progress message AddOrReplaceProgressMessage(_db, String.Format(importingMessageText, i++, csvFiles.GetLength(0))); // Import the CSV file using (CsvReader csv = new CsvReader(new StreamReader(csvFile))) { const Int32 POSTCODE = 0; const Int32 EASTING = 2; const Int32 NORTHING = 3; csv.Configuration.HasHeaderRecord = false; while (csv.Read()) { String Postcode = CorrectPostcode(csv[POSTCODE]); Double Northing = Convert.ToDouble(csv[NORTHING]); Double Easting = Convert.ToDouble(csv[EASTING]); LatLon latlon = LatLonConversions.ConvertOSToLatLon(Easting, Northing); const Int32 decimalPlaces = 6; if (Postcode.IndexOf(" ") == -1) { Postcode = Postcode.Substring(0, Postcode.Length - 3) + " " + Postcode.Substring(Postcode.Length - 3, 3); } DataRow dr = dt.NewRow(); dr["Postcode"] = Postcode; dr["Lat"] = Math.Round(latlon.Latitude, decimalPlaces); dr["Lng"] = Math.Round(latlon.Longitude, decimalPlaces); dr["Northing"] = Northing; dr["Easting"] = Easting; dt.Rows.Add(dr); // Every 100 rows, check whether we are cancelling the import if (csv.Row % 100 == 0 && IsCancellingImport(new ProviderPortalEntities())) { cancellingImport = true; break; } } csv.Dispose(); // Delete the file to tidy up space as quickly as possible try { System.IO.File.Delete(csvFile); } catch { } } if (!cancellingImport) { // Copy the data to the Import_GeoLocation Table BulkImportData(conn, dt, transaction); } } cancellingImport = IsCancellingImport(new ProviderPortalEntities()); if (!cancellingImport) { // Merge the data into the GeoLocation Table AddOrReplaceProgressMessage(_db, mergingMessageText); comm = new SqlCommand("MERGE [dbo].[GeoLocation] dest USING [dbo].[Import_GeoLocation] source ON dest.Postcode = source.Postcode WHEN MATCHED THEN UPDATE SET dest.Lat = source.Lat, dest.Lng = source.Lng, dest.Northing = source.Northing, dest.Easting = source.Easting WHEN NOT MATCHED THEN INSERT (Postcode, Lat, Lng, Northing, Easting) VALUES (source.Postcode, source.Lat, source.Lng, source.Northing, source.Easting);", conn, transaction) { CommandTimeout = 3600 /* 1 Hour */ }; comm.ExecuteNonQuery(); // Update any Address Rows that don't currently have any Latitude or Longitude try { comm = new SqlCommand("UPDATE Address SET Address.Latitude = GeoLocation.Lat, Address.Longitude = GeoLocation.lng FROM Address INNER JOIN GeoLocation ON Address.Postcode = GeoLocation.Postcode WHERE Address.Latitude IS NULL AND GeoLocation.Lat IS NOT NULL;", conn, transaction) { CommandTimeout = 3600 /* 1 Hour */ }; comm.ExecuteNonQuery(); } catch {} } // Truncate the temporary import table if (!cancellingImport) { AddOrReplaceProgressMessage(_db, removingTempDataMessageText); comm = new SqlCommand("TRUNCATE TABLE [dbo].[Import_GeoLocation];", conn, transaction); comm.ExecuteNonQuery(); } if (!IsCancellingImport(new ProviderPortalEntities())) { // Commit the transaction transaction.Commit(); #region Update After Row Counts // Add the current row count to MetadataUpload // Save timings sw.Stop(); metadataUpload.DurationInMilliseconds = (int)sw.ElapsedMilliseconds; metadataUpload.RowsAfter = _db.GeoLocations.Count(); _db.MetadataUploads.Add(metadataUpload); _db.SaveChanges(); #endregion } else { // Rollback the transaction try { transaction.Rollback(); _db.Dispose(); } catch { } } // Close the database conn.Close(); } } // Delete all the uploaded and expanded files try { foreach (FileInfo file in new DirectoryInfo(CodePointFolder).GetFiles()) { file.Delete(); } } catch { } // Write Success or Cancelled message AddOrReplaceProgressMessage(_db, cancellingImport ? importCancelledMessageText : importSuccessfulMessageText, true); } catch (Exception ex) { AddOrReplaceProgressMessage(new ProviderPortalEntities(), String.Format(importErrorMessageText, ex.Message), true); // Delete all the uploaded and expanded files try { foreach (FileInfo file in new DirectoryInfo(CodePointFolder).GetFiles()) { file.Delete(); } } catch { } } }).Start(); } } } catch (Exception ex) { // Create a model error ModelState.AddModelError("", ex.Message); } } // No Errors so redirect to index which will show messages if (ModelState.IsValid) { return(RedirectToAction("Index")); } GetLastUploadDetails(model); return(View(model)); }