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