// //////////////////////////////////////////////////////////////////////// // INITIAL EVENTS // protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { // Validate query string if (((string)Request.QueryString["source_page"] == null) || ((string)Request.QueryString["client_id"] == null) || ((string)Request.QueryString["project_id"] == null)) { Response.Redirect("./../../error_page.aspx?error=" + "Invalid query string in bulk_upload.aspx"); } // Tag Page TagPage(); // Prepare initial data Session.Remove("bulkUpload"); // Initialize viewstate variables ViewState["StepFrom"] = "Out"; // ... Initialize tables bulkUpload = new BulkUploadTDS.BulkUploadDataTable(); // ... Store tables Session["bulkUpload"] = bulkUpload; // StepSection1In wzBulkUpload.ActiveStepIndex = 0; StepBeginIn(); } else { // Restore tables bulkUpload = (BulkUploadTDS.BulkUploadDataTable)Session["bulkUpload"]; } // control for postback hdfTag.Value = DateTime.Now.ToLongTimeString(); }
// ///////////////////////////////////////////////////////////////////////////////////////////////////// // // STEP1 - BEGIN // // //////////////////////////////////////////////////////////////////////// // STEP1 - BEGIN - METHODS // private void StepBeginIn() { // Set instruction Label instruction = (Label)this.Master.FindControl("lblInstruction"); instruction.Text = "Please select a Microsoft Excel file"; //--- Initialize BulkUploadTDS dataSet = new BulkUploadTDS(); dataSet.BulkUpload.Merge(bulkUpload, true); BulkUpload model = new BulkUpload(dataSet); model.Table.Rows.Clear(); bulkUpload = dataSet.BulkUpload; Session["bulkUpload"] = dataSet.BulkUpload; }
private bool ProcessBulkUpload(string fName, out string bulkUploadResultMessage) { bool bulkUploadProccessed = true; bulkUploadResultMessage = ""; AssetSewerSectionGateway assetSewerSectionGateway = new AssetSewerSectionGateway(); AppSettingsReader appSettingReader = new AppSettingsReader(); string excelConnectionString = appSettingReader.GetValue("ExcelConnectionString", typeof(System.String)).ToString() + fName + ";"; OleDbConnection connection = new OleDbConnection(excelConnectionString); OleDbCommand command = null; OleDbDataReader dataReader = null; try { ArrayList validatedIds = new ArrayList(); try { connection.Open(); command = new OleDbCommand("select * from [sections]", connection); dataReader = command.ExecuteReader(); while (dataReader.Read()) { if (!IsEmptyRow(dataReader)) { string raWork = ""; try { raWork = dataReader.GetValue(dataReader.GetOrdinal("RAWork")).ToString().ToUpper().Trim(); } catch { } string fllWork = ""; try { fllWork = dataReader.GetValue(dataReader.GetOrdinal("FLLWork")).ToString().ToUpper().Trim(); } catch { } string jlWork = ""; try { jlWork = dataReader.GetValue(dataReader.GetOrdinal("JLWork")).ToString().ToUpper().Trim(); } catch { } if (bulkUploadProccessed) { if (((raWork != null) && (raWork != "")) || ((fllWork != null) && (fllWork != "")) || ((jlWork != null) && (jlWork != ""))) { if ((raWork.ToUpper() == "YES") || (fllWork.ToUpper() == "YES") || (jlWork.ToUpper() == "YES")) { bulkUploadProccessed = true; } else { bulkUploadResultMessage = "At least one work must exist in 'Works' columns (sections data range). Bulk upload ABORTED."; bulkUploadProccessed = false; } } else { bulkUploadResultMessage = "At least one work must exist in 'Works' columns (sections data range). Bulk upload ABORTED."; bulkUploadProccessed = false; } if (!bulkUploadProccessed) { break; } } } } dataReader.Close(); connection.Close(); } catch (Exception ex) { bulkUploadResultMessage = "You did not define the 'sections' data range. Bulk upload ABORTED. Original message: " + ex.Message; bulkUploadProccessed = false; if (connection.State == ConnectionState.Open) { connection.Close(); } } //--- Process bulk upload if (bulkUploadProccessed) { connection.Open(); command = new OleDbCommand("select * from [sections]", connection); dataReader = command.ExecuteReader(); int id = 0; while (dataReader.Read()) { if (!IsEmptyRow(dataReader)) { string street = ""; string clientId = ""; string subArea = ""; string usmh = ""; string dsmh = ""; string mapSize = ""; string confirmedSize = ""; string mapLength = ""; string actualLength = ""; bool raWork = false; string raComments = ""; bool fllWork = false; string fllComments = ""; bool jlWork = false; string dataCell = null; string dataCellToUpper = null; //--- ... fill section row for (int i = 0; i < dataReader.FieldCount; i++) { dataCell = dataReader.GetValue(i).ToString().Trim(); dataCellToUpper = dataReader.GetValue(i).ToString().Trim().ToUpper(); switch (dataReader.GetName(i).Trim()) { case "Street": if (dataCellToUpper != "NULL") { street = dataCell; } else { street = ""; } break; case "Client ID": if (dataCellToUpper != "NULL") { clientId = dataCell; } else { clientId = ""; } break; case "SubArea": if (dataCellToUpper != "NULL") { subArea = dataCell; } else { subArea = ""; } break; case "USMH": if (dataCellToUpper != "NULL") { usmh = dataCell; } else { usmh = ""; } break; case "DSMH": if (dataCellToUpper != "NULL") { dsmh = dataCell; } else { dsmh = ""; } break; case "MapSize": if (dataCellToUpper != "NULL" && dataCellToUpper != "") { if (Distance.IsValidDistance(dataCell)) { mapSize = dataCell; } else { bulkUploadResultMessage = "Invalid value in 'MapSize' column (" + id + "). Bulk upload ABORTED."; bulkUploadProccessed = false; } } else { mapSize = ""; } break; case "ConfirmedSize": if (dataCellToUpper != "NULL" && dataCellToUpper != "") { if (Distance.IsValidDistance(dataCell)) { confirmedSize = dataCell; } else { bulkUploadResultMessage = "Invalid value in 'ConfirmedSize' column (" + id + "). Bulk upload ABORTED."; bulkUploadProccessed = false; } } else { confirmedSize = ""; } break; case "MapLength": if (dataCellToUpper != "NULL" && dataCellToUpper != "") { if (Distance.IsValidDistance(dataCell)) { mapLength = dataCell; } else { bulkUploadResultMessage = "Invalid value in 'MapLength' column (" + id + "). Bulk upload ABORTED."; bulkUploadProccessed = false; } } else { mapLength = ""; } break; case "ActualLength": if (dataCellToUpper != "NULL" && dataCellToUpper != "") { if (Distance.IsValidDistance(dataCell)) { actualLength = dataCell; } else { bulkUploadResultMessage = "Invalid value in 'ActualLength' column (" + id + "). Bulk upload ABORTED."; bulkUploadProccessed = false; } } else { actualLength = ""; } break; case "RAWork": if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO")) { raWork = (dataCellToUpper == "YES") ? true : false; } else { bulkUploadResultMessage = "Invalid value in 'RAWork' column (" + id + "). Bulk upload ABORTED."; bulkUploadProccessed = false; } break; case "RAComments": if (dataCellToUpper != "NULL") { raComments = dataCell; } else { raComments = ""; } break; case "FLLWork": if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO")) { fllWork = (dataCellToUpper == "YES") ? true : false; } else { bulkUploadResultMessage = "Invalid value in 'FLLWork' column (" + id + "). Bulk upload ABORTED."; bulkUploadProccessed = false; } break; case "FLLComments": if (dataCellToUpper != "NULL") { fllComments = dataCell; } else { fllComments = ""; } break; case "JLWork": if ((dataCellToUpper == "YES") || (dataCellToUpper == "NO")) { jlWork = (dataCellToUpper == "YES") ? true : false; } else { bulkUploadResultMessage = "Invalid value in 'JLWork' column (" + id + "). Bulk upload ABORTED."; bulkUploadProccessed = false; } break; default: bulkUploadResultMessage = "Invalid column name '" + dataReader.GetName(i) + "' in section data range."; bulkUploadProccessed = false; break; } if (!bulkUploadProccessed) { break; } } if (bulkUploadProccessed) { //--- Initialize BulkUploadTDS dataSet = new BulkUploadTDS(); dataSet.BulkUpload.Merge(bulkUpload, true); BulkUpload model = new BulkUpload(dataSet); id = id + 1; model.Insert(id.ToString(), street, clientId, subArea, usmh, dsmh, mapSize, confirmedSize, mapLength, actualLength, raWork, raComments, fllWork, fllComments, jlWork); bulkUpload = dataSet.BulkUpload; Session["bulkUpload"] = dataSet.BulkUpload; } } } dataReader.Close(); connection.Close(); } } catch (Exception ex) { if (!dataReader.IsClosed) { dataReader.Close(); } if (connection.State == ConnectionState.Open) { connection.Close(); } throw ex; } return (bulkUploadProccessed) ? true : false; }