protected void ButtonLoc_Click(object sender, EventArgs e) { var datestring = datepickerLocal.Text; var dt = DateTime.ParseExact(datestring, @"MM/dd/yyyy", CultureInfo.InvariantCulture); if (dt < DateTime.Today) { lblmessageLoc.Text = "You must choose a date in the future"; } else { var connectionString = ""; try { if (FileUploadLoc.HasFile) { if (ValidFile(FileUploadLoc, "LOCAL")) { var fileName = Path.GetFileName(FileUploadLoc.PostedFile.FileName); var fileExtension = Path.GetExtension(FileUploadLoc.PostedFile.FileName); var fileLocation = Server.MapPath("~/App_Data/" + fileName); FileUploadLoc.SaveAs(fileLocation); connectionString = UploadExcel(connectionString, fileExtension, fileLocation, "LOCAL"); } } else { lblmessageLoc.Text = "Please select an Excel file to upload."; } } catch (Exception ex) { lblmessageLoc.Text = "Invalid Excel document."; //lblmessageLoc.Text = ex.Message.ToString(); } } }
protected void ButtonLoc_Click(object sender, EventArgs e) { var connectionString = ""; try { if (FileUploadLoc.HasFile) { if (ValidExport(FileUploadLoc)) { var fileName = Path.GetFileName(FileUploadLoc.PostedFile.FileName); var fileExtension = Path.GetExtension(FileUploadLoc.PostedFile.FileName); var fileLocation = Server.MapPath("~/App_Data/" + fileName); FileUploadLoc.SaveAs(fileLocation); //Check whether file extension is xls or xslx if (fileExtension == ".xls") { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=2\""; } else if (fileExtension == ".xlsx") { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=2\""; } //Create OleDB Connection and OleDb Command var con = new OleDbConnection(connectionString); var cmd = new OleDbCommand(); cmd.CommandType = CommandType.Text; cmd.Connection = con; var dAdapter = new OleDbDataAdapter(cmd); var dtExcelRecords = new DataTable(); con.Open(); var dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); var getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString(); cmd.CommandText = "SELECT * FROM [LOCAL$A3:H] WHERE F1 IS NOT NULL"; dAdapter.SelectCommand = cmd; var dsbd = new DataSet(); dAdapter.Fill(dsbd); var status = SaveLocal(dsbd); if (status == 1) { GridLocal.DataBind(); if (GridLocal.Rows.Count >= 1) { //show grid results lblmessageLoc.Text = GridLocal.Rows.Count + " Duplicates detected."; var connectLoc1 = ConfigurationManager.ConnectionStrings["SecureConnectionString"].ConnectionString; var deleteLocal1 = "DELETE FROM LocalQueue"; using (var myLocConnection1 = new SqlConnection(connectLoc1)) { myLocConnection1.Open(); var myLocCommand1 = new SqlCommand(deleteLocal1, myLocConnection1); myLocCommand1.ExecuteNonQuery(); myLocConnection1.Close(); } } else { //Fill dataset from staging table var adapterDb = new SqlDataAdapter("Select * FROM LocalQueue", strConnection); var datasetDb = new DataSet(); adapterDb.Fill(datasetDb); //Check integer status returned var newstatus = SaveNewLocal(datasetDb); if (newstatus == 1) { //Delete localQueue var connectLoc = ConfigurationManager.ConnectionStrings["SecureConnectionString"] .ConnectionString; var deleteLocal = "DELETE FROM LocalQueue"; using (var myLocConnection = new SqlConnection(connectLoc)) { myLocConnection.Open(); var myLocCommand = new SqlCommand(deleteLocal, myLocConnection); myLocCommand.ExecuteNonQuery(); myLocConnection.Close(); } lblmessageLoc.Text = "Local Queue updated successfully."; } } } else { lblmessageLoc.Text = "Upload Error.Please try again"; } con.Close(); } } else { lblmessageLoc.Text = "Please select an Excel file to upload."; } } catch (Exception ex) { lblmessageLoc.Text = "Invalid Excel document."; } }