public void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
//conexion a base de datos para ralizar todo el proceso de volcado
//con respectivas validaciones de autonomia
double contin = 1;
String glo = @"Server=" + SERV + ";Database=bd_est_airunity;UserID=" + USER + ";Password=" + PASS + "";
MySqlConnection con = new MySqlConnection(glo); // Conexion Global
String ins = @"Server=" + SERV + ";Database=bd_est_airunity;UserID=" + USER + ";Password=" + PASS + "";
MySqlConnection con_aux = new MySqlConnection(ins); // Conexion auxiliar
double count = Files_to_Upload.Length;
//MessageBox.Show("Cuantos: " + count);
foreach (string s in Files_to_Upload)
{ if (backgroundWorker1.CancellationPending)
{ e.Cancel = true;
return;
}
inter_camp = true;
result = Path.GetFileNameWithoutExtension(s);
string TableName = result.Split('_')[0];
anti_blank = TableName.Split(' ');//SOLUCION Problema del Blanck
//MessageBox.Show(anti_blank[0]);
string TN_Val = "";
prim = false;
foreach (string Cad in anti_blank)
{ if (prim == true)
{ TN_Val = TN_Val + "_" + Cad;
}
else
{ TN_Val = Cad;
}
prim = true;
}
//se crearan las tablas, una por una, primero la tabla, luego los campos y el insert
//antes de pasar a una siguiente tabla
string query = "CREATE TABLE IF NOT EXISTS `tbl_" + TN_Val + "` (`ID` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci AUTO_INCREMENT=1";
MySqlCommand cmd = new MySqlCommand(query, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
this.listBox1.Items.Add("File #: " + contin + " // Table: " + TN_Val + " // Inserted into DataBase");
try
{ using (var fs = File.OpenRead(s))
using (var reader = new StreamReader(fs))
//se abre el while de la lectura del archivo .csv
while (!reader.EndOfStream)
{ var line = reader.ReadLine();
values = line.Split(',');
if (inter_camp == true)
{ foreach (string Col in values)
{
anti_blank = Col.Split(' ');//SOLUCION Problema del Blanck
string Col_Val = "";
prim = false;
foreach (string Cad in anti_blank)
{ if (prim == true)
{ Col_Val = Col_Val + "_" + Cad;
}
else
{ Col_Val = Cad;
}
prim = true;
}
//MessageBox.Show(Col_Val);
// Entradas A foreach para validar Campos en la Tabla respectiva.
string query_verif = "SHOW COLUMNS FROM tbl_" + TN_Val + " WHERE Field = '" + Col_Val + "'";
MySqlCommand cmd_verif = new MySqlCommand(query_verif, con);
con.Open();
MySqlDataReader myreader = cmd_verif.ExecuteReader();
if (myreader.Read())
{
}
else
{
//listBox1.Items.Add("Field: " + Col_Val + " // into Table: " + TN_Val + " // Will be added");
//listBox1.Items.Add("//");
if (Col_Val == "DateTimeStart" || Col_Val == "DateTimeEnd" || Col_Val == "DateAndTimeStart" || Col_Val == "DateAndTimeEnd")
{ string query_col = "ALTER TABLE `tbl_" + TN_Val + "` ADD `" + Col_Val + "` TIMESTAMP NOT NULL DEFAULT '0000-00-00'";
MySqlCommand cmd_col = new MySqlCommand(query_col, con_aux);
con_aux.Open();
cmd_col.ExecuteNonQuery();
con_aux.Close();
}
else
{ string query_col = "ALTER TABLE `tbl_" + TN_Val + "` ADD `" + Col_Val + "` VARCHAR(50) NOT NULL";
MySqlCommand cmd_col = new MySqlCommand(query_col, con_aux);
con_aux.Open();
cmd_col.ExecuteNonQuery();
con_aux.Close();
}
}
con.Close();
inter_camp = false;
}
}
else
{ string ins_line = ",";
bool coma = false;
foreach (string ins_val in values)
{ if (coma == true)
{ ins_line = ins_line + ",";
}
//MessageBox.Show(ins_val);
string[] anti_backslash = ins_val.Split('\\');//SOLUCION Problema del Backslash
if (anti_backslash[0] == "C:" || anti_backslash[0] == "D:")
{ //MessageBox.Show("Existe el backslash");
ins_line = ins_line + "'" + ins_val + "\\" + "'";
//MessageBox.Show(ins_line);
}
else
{ //MessageBox.Show("NOO Existe el backslash");
ins_line = ins_line + "'" + ins_val + "'";
}
coma = true;
}
try
{ string query_col = "INSERT INTO `tbl_" + TN_Val + "` VALUES ('null'" + ins_line + ")"; //MessageBox.Show("Query: " + query_col ); para saber el query
MySqlCommand cmd_col = new MySqlCommand(query_col, con_aux);
con_aux.Open();
cmd_col.ExecuteNonQuery();
con_aux.Close();
Thread.Sleep(50);
}
catch (MySqlException ex)
{ listBox1.Items.Add("File #: " + contin + " // Table: " + TN_Val + " // ERROR"); listBox1.Items.Add("---"); MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
con_aux.Close();
//throw;
}
//MessageBox.Show("insert Into Realizado /// Revisa la Tabla ");
}
//GC.SuppressFinalize(reader);
}
listBox1.Items.Add("Success");
}
catch (Exception ex)
{ listBox1.Items.Add("AT File #: " + contin + " Try's ERROR"); listBox1.Items.Add("---"); MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
//GC.SuppressFinalize(this);
double Progreso = (contin / count) * 100;
backgroundWorker1.ReportProgress((int)Progreso);
listBox1.Items.Add("//"); contin = contin + 1;
}
}
