protected void btnProcesar_Click(object sender, EventArgs e) { //System.Threading.Thread.Sleep(5000); //LOGISTICA_ME5A try { if (FileUpload1.FileBytes.Length <= 0 && FileUpload2.FileBytes.Length <= 0 && FileUpload3.FileBytes.Length <= 0 && FileUpload4.FileBytes.Length <= 0 && FileUpload5.FileBytes.Length <= 0 && FileUpload6.FileBytes.Length <= 0) { } else { BL_LOGISTICA obj = new BL_LOGISTICA(); DataTable dtResultado = new DataTable(); dtResultado = obj.Eliminar_Tabla_ReporteGeneral(); string path = Path.GetFileName(FileUpload1.PostedFile.FileName); string fileExtension = Path.GetExtension(path).ToLower(); if (ValidaExtension(fileExtension)) { Bulk_Insert(FileUpload1, "dbo.LOGISTICA_ME5A", img1); } else { UC_MessageBox.Show(Page, Page.GetType(), "Formato no permitido para ME5A"); return; } //////////////// ME2N /////////////////// string path2 = Path.GetFileName(FileUpload2.PostedFile.FileName); string fileExtension2 = Path.GetExtension(path2).ToLower(); if (ValidaExtension(fileExtension2)) { Bulk_Insert(FileUpload2, "dbo.LOGISTICA_ME2N", img2); } else { UC_MessageBox.Show(Page, Page.GetType(), "Formato no permitido para ME2N"); return; } //////////////// ZMM033 /////////////////// string path3 = Path.GetFileName(FileUpload3.PostedFile.FileName); string fileExtension3 = Path.GetExtension(path3).ToLower(); if (ValidaExtension(fileExtension3)) { Bulk_Insert(FileUpload3, "dbo.LOGISTICA_ZMM033", img3); } else { UC_MessageBox.Show(Page, Page.GetType(), "Formato no permitido para ZMM033"); return; } //////////////// ZMM033C /////////////////// string path4 = Path.GetFileName(FileUpload4.PostedFile.FileName); string fileExtension4 = Path.GetExtension(path4).ToLower(); if (ValidaExtension(fileExtension4)) { Bulk_Insert_Vacios(FileUpload4, "dbo.LOGISTICA_ZMM033", img4); Bulk_Insert_Vacios(FileUpload4, "dbo.LOGISTICA_ZMM033C", img4); } else { UC_MessageBox.Show(Page, Page.GetType(), "Formato no permitido para ZMM033 Concluidas"); return; } //////////////// ME80FN /////////////////// string path5 = Path.GetFileName(FileUpload5.PostedFile.FileName); string fileExtension5 = Path.GetExtension(path5).ToLower(); if (ValidaExtension(fileExtension5)) { Bulk_Insert(FileUpload5, "dbo.LOGISTICA_ME80FN_REP", img5); } else { UC_MessageBox.Show(Page, Page.GetType(), "Formato no permitido para ME80FN Repartos"); return; } //////////////// ME80FN_HIS /////////////////// string path6 = Path.GetFileName(FileUpload6.PostedFile.FileName); string fileExtension6 = Path.GetExtension(path6).ToLower(); if (ValidaExtension(fileExtension6)) { Bulk_Insert(FileUpload6, "dbo.LOGISTICA_ME80FN_HIS", img6); } else { UC_MessageBox.Show(Page, Page.GetType(), "Formato no permitido para ME80FN Historicos"); return; } //////////////// SSK /////////////////// //string path7 = Path.GetFileName(FileUpload7.PostedFile.FileName); //string fileExtension7 = Path.GetExtension(path7).ToLower(); //if (ValidaExtension(fileExtension7)) //{ // //Bulk_Insert // Bulk_Insert(FileUpload7, "dbo.LOGISTICA_REG_ALMACEN", img7); //} //else //{ // UC_MessageBox.Show(Page, Page.GetType(), "Formato no permitido para Almacen SSK"); // return; //} //exportarXLS_Plantilla(); exportarXLS(); } } catch (Exception ex) { // hay borrar los datos BL_LOGISTICA obj = new BL_LOGISTICA(); DataTable dtResultado = new DataTable(); dtResultado = obj.Eliminar_Tabla_ReporteGeneral(); string cleanMessage = ex.Message + " Intente cambiar la extension de los Archivo Almacen a .XLS(libro 97-2003)";; ScriptManager.RegisterStartupScript(this, typeof(Page), "invocarfuncion", "doAlert('" + cleanMessage + "');", true); } }
protected void Bulk_Insert_Vacios(FileUpload FileUpload1, string tabla, Image img) { string cleanMessage; try { string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName); string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName); string connectionString = string.Empty; string fullPath = Path.Combine(Server.MapPath("~/File/"), fileName); if (System.IO.File.Exists(fullPath)) { File.Delete(fullPath); } FileUpload1.SaveAs(fullPath); if (fileExtension.ToUpper() == ".XLS") { connectionString = (Convert.ToString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=") + fullPath) + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""; } else if (fileExtension.ToUpper() == ".XLSX") { connectionString = (Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=") + fullPath) + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } OleDbConnection con = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = con; OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd); DataTable dtExcelRecords = new DataTable(); con.Open(); DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString(); cmd.CommandText = (Convert.ToString("SELECT * FROM [") + getExcelSheetName) + "]"; dAdapter.SelectCommand = cmd; dAdapter.Fill(dtExcelRecords); con.Close(); string consString = ConfigurationManager.ConnectionStrings["Conexion"].ConnectionString; using (SqlConnection conx = new SqlConnection(consString)) { conx.Open(); // Get a reference to a single row in the table. DataRow[] rowArray = dtExcelRecords.Select(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conx)) { bulkCopy.DestinationTableName = tabla; try { // Write the array of rows to the destination. bulkCopy.BulkCopyTimeout = 5000; bulkCopy.BatchSize = 50000; bulkCopy.WriteToServer(rowArray); img.Visible = true; img.ImageUrl = "~/imagenes/check.png"; } catch (Exception ex) { img.Visible = true; img.ImageUrl = "~/imagenes/Error.png"; UC_MessageBox.Show(Page, Page.GetType(), "Error en archivo : " + ex.Message); return; } } }//using } catch (System.IO.IOException e) { img.Visible = true; img.ImageUrl = "~/imagenes/Error.png"; // hay borrar los datos BL_LOGISTICA obj = new BL_LOGISTICA(); DataTable dtResultado = new DataTable(); dtResultado = obj.Eliminar_Tabla_ReporteGeneral(); cleanMessage = e.Message + " Intente cambiar la extension de los Archivos a .XLS(libro 97-2003)"; ScriptManager.RegisterStartupScript(this, typeof(Page), "invocarfuncion", "doAlert('" + cleanMessage + "');", true); } }