public void BulkLoadEscaping()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), name2 VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 200; i++)
sw.WriteLine(i + ",col1\tstill col1,col2");
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.EscapeCharacter = '\t';
loader.FieldTerminator = ",";
int count = loader.Load();
Assert.AreEqual(200, count);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(200, dt.Rows.Count);
Assert.AreEqual("col1still col1", dt.Rows[0][1]);
Assert.AreEqual("col2", dt.Rows[0][2].ToString().Trim());
}
public void BulkLoadSimple()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 200; i++)
sw.WriteLine(i + "\t'Test'");
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
int count = loader.Load();
Assert.AreEqual(200, count);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(200, dt.Rows.Count);
Assert.AreEqual("'Test'", dt.Rows[0][1].ToString().Trim());
}
public static void LoadFileToDb(string connStr, string tableName, string filePath)
{
if (filePath == null || !File.Exists(filePath))
{
return;
}
var conn = new MySqlConnection(connStr);
var bl = new MySqlBulkLoader(conn)
{
TableName = tableName, FieldTerminator = ",", LineTerminator = "\r\n", FileName = filePath
};
conn.Open();
MySqlTransaction trans = conn.BeginTransaction();
try
{
// Upload data from file
int count = bl.Load();
trans.Commit();
conn.Close();
}
catch (Exception ex)
{
trans.Rollback();
}
}
public void BulkLoadColumnOrder()
{
execSQL(@"CREATE TABLE Test (id INT NOT NULL, n1 VARCHAR(250), n2 VARCHAR(250),
n3 VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
sw.WriteLine(i + ",col3,col2,col1");
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = Environment.NewLine;
loader.Columns.Add("id");
loader.Columns.Add("n3");
loader.Columns.Add("n2");
loader.Columns.Add("n1");
int count = loader.Load();
Assert.AreEqual(20, count);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(20, dt.Rows.Count);
Assert.AreEqual("col1", dt.Rows[0][1]);
Assert.AreEqual("col2", dt.Rows[0][2]);
Assert.AreEqual("col3", dt.Rows[0][3].ToString().Trim());
}
public static void ExecuteOperation(string rutaInicial, string procesoId)
{
ILog Logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
Logger.Info("Inicia UploadOperation de Proceso : " + procesoId);
var myguid = Guid.NewGuid();
var rutaFinal = ConfigurationManager.AppSettings["rutaIN"] + myguid.ToString() + ".csv";
Logger.Info("rutaFinalBaseExtra = " + rutaFinal);
using (var writter = new StreamWriter(rutaFinal))
{
using (var reader = new System.IO.StreamReader(rutaInicial, System.Text.Encoding.Default, false))
{
var esPrimero = true;
while (!reader.EndOfStream)
{
var lineFull = reader.ReadLine();
if (!esPrimero)
{
string filaInsert = string.Empty;
int contador = 0;
foreach (var item in lineFull.Split('|'))
{
if (contador == 0)
{
var columna = item.PadLeft(7, '0');
filaInsert = filaInsert + columna + "," + procesoId;
}
else
{
filaInsert = filaInsert + "," + item;
}
contador++;
}
writter.Write(filaInsert + "\n");
}
else
{
esPrimero = false;
}
}
}
}
using (var conexion = new MySqlConnection(ConfigurationManager.AppSettings["cadenaConexion"]))
{
var bulkLoader = new MySqlBulkLoader(conexion)
{
TableName = "VariableExtra",
FieldTerminator = ",",
LineTerminator = "\n",
FileName = rutaFinal,
NumberOfLinesToSkip = 0
};
var numberOfInsertedRows = bulkLoader.Load();
}
Logger.Info("Termina UploadOperation de Proceso : " + procesoId);
}
public void BulkLoadSimple2()
{
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 200; i++)
sw.Write(i + ",'Test' xxx");
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = "xxx";
int count = loader.Load();
Assert.AreEqual(200, count);
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Test", conn);
Assert.AreEqual(200, cmd.ExecuteScalar());
}
public static void ExecuteOperation(string rutaInicial, string tipo, string descripcion)
{
ILog Logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
Logger.Info("Inicia UploadOperation de ConjuntoQV Tipo : " + tipo);
var myguid = Guid.NewGuid();
var rutaFinal = ConfigurationManager.AppSettings["rutaIN"] + myguid.ToString() + ".dat";
Logger.Info("rutaFinalBase = " + rutaFinal);
using (var writter = new StreamWriter(rutaFinal))
{
using (var reader = new System.IO.StreamReader(rutaInicial))
{
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
line = line.PadLeft(7, '0');
writter.Write(line + "\t" + tipo + "\t" + descripcion + "\n");
}
}
}
using (var conexion = new MySqlConnection(ConfigurationManager.AppSettings["cadenaConexion"]))
{
var bulkLoader = new MySqlBulkLoader(conexion)
{
TableName = "Conjunto",
FieldTerminator = "\t",
LineTerminator = "\n",
FileName = rutaFinal
};
var numberOfInsertedRows = bulkLoader.Load();
}
Logger.Info("Termina UploadOperation de ConjuntoQV Tipo : " + tipo);
}
static void Main(string[] args)
{
#region Inicializar Globales
log4net.Config.XmlConfigurator.Configure();
ILog Logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
#endregion
try
{
if (args.Length>0)
{
Logger.Info("args[0] = " + args[0]);
Logger.Info("args[1] = " + args[1]);
Logger.Info("args[2] = " + args[2]);
Logger.Info("args[3] = " + args[3]);
var myguid = Guid.NewGuid();
var rutaFinal = ConfigurationManager.AppSettings["rutaIN"] + myguid.ToString() + ".csv";
Logger.Info("rutaFinal = " + rutaFinal);
WritterCSV(args[0], args[1], args[2], args[3], rutaFinal);
using (var conexion = new MySqlConnection(ConfigurationManager.AppSettings["cadenaConexion"]))
{
using (MySqlCommand comando = new MySqlCommand("DeleteFolioByUser"))
{
comando.CommandType = CommandType.StoredProcedure;
comando.Connection = conexion;
conexion.Open();
comando.Parameters.Add("_IdentificadorUser", MySqlDbType.VarChar, 100).Value = args[1];
var resultado = comando.ExecuteNonQuery();
if (resultado == 0)
Logger.Info("Error al eliminar Folio del usuario = " + args[1]);
}
var bulkLoader = new MySqlBulkLoader(conexion)
{
TableName = "Folio",
FieldTerminator = ",",
LineTerminator = "\n",
FileName = rutaFinal,
NumberOfLinesToSkip = 0,
Columns = { "Folio", "Estado", "IdentificadorUser" }
};
var numberOfInsertedRows = bulkLoader.Load();
}
}
else
{
Logger.Info("No hay parámetros");
}
}
catch (Exception ex)
{
Logger.ErrorFormat("Mensaje: {0} Trace: {1}", ex.Message, ex.StackTrace);
}
}
public void BulkLoadSimple4()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 100; i++)
sw.Write("aaa" + i + ",'Test' xxx");
for (int i = 100; i < 200; i++)
sw.Write("bbb" + i + ",'Test' xxx");
for (int i = 200; i < 300; i++)
sw.Write("aaa" + i + ",'Test' xxx");
for (int i = 300; i < 400; i++)
sw.Write("bbb" + i + ",'Test' xxx");
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = "xxx";
loader.LinePrefix = "bbb";
int count = loader.Load();
Assert.AreEqual(200, count);
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM Test", conn);
Assert.AreEqual(200, cmd.ExecuteScalar());
}
private void btnBulkCopy_Click(object sender, EventArgs e)
{
ILog Logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
Stream myStream = null;
OpenFileDialog openFileDialog1 = new OpenFileDialog();
lblFechaInicioText.Text = "";
lblFechaFinText.Text = "";
lblResultado.Text = "";
openFileDialog1.InitialDirectory = "D:\\";
openFileDialog1.Filter = "Text files (*.txt)|*.txt";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
btnBulkCopy.Enabled = false;
if ((myStream = openFileDialog1.OpenFile()) != null)
{
using (myStream)
{
using (var conexion = new MySqlConnection(ConfigurationManager.AppSettings["cadenaConexion"]))
{
lblFechaInicioText.Text = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString();
using (MySqlCommand comando = new MySqlCommand("DeleteCliente"))
{
comando.CommandType = CommandType.StoredProcedure;
comando.Connection = conexion;
conexion.Open();
comando.CommandTimeout = int.MaxValue;
var resultado = comando.ExecuteNonQuery();
}
using (MySqlCommand comando = new MySqlCommand("InsertCampania"))
{
comando.CommandType = CommandType.StoredProcedure;
comando.Connection = conexion;
comando.CommandTimeout = int.MaxValue;
var fechaInicio = string.Empty;
var fechaFin = string.Empty;
fechaInicio = dtpInicio.Value.Year + "-";
if(dtpInicio.Value.Month < 10 )
fechaInicio = fechaInicio + "0" + dtpInicio.Value.Month + "-";
else
fechaInicio = fechaInicio + dtpInicio.Value.Month + "-";
if (dtpInicio.Value.Day < 10)
fechaInicio = fechaInicio + "0" + dtpInicio.Value.Day;
else
fechaInicio = fechaInicio + dtpInicio.Value.Day;
fechaFin = dtpFin.Value.Year + "-";
if (dtpFin.Value.Month < 10)
fechaFin = fechaFin + "0" + dtpFin.Value.Month + "-";
else
fechaFin = fechaFin + dtpFin.Value.Month + "-";
if (dtpFin.Value.Day < 10)
fechaFin = fechaFin + "0" + dtpFin.Value.Day;
else
fechaFin = fechaFin + dtpFin.Value.Day;
comando.Parameters.Add("_fechaInicio", MySqlDbType.VarChar).Value = fechaInicio;
comando.Parameters.Add("_fechaFin", MySqlDbType.VarChar).Value = fechaFin;
var resultado = comando.ExecuteNonQuery();
}
var bulkLoader = new MySqlBulkLoader(conexion)
{
TableName = "cliente",
FieldTerminator = "\t",
LineTerminator = "\n",
FileName = openFileDialog1.FileName,
NumberOfLinesToSkip = 1
};
var numberOfInsertedRows = bulkLoader.Load();
using (MySqlCommand comando = new MySqlCommand("UpdateCliente"))
{
comando.CommandType = CommandType.StoredProcedure;
comando.Connection = conexion;
comando.CommandTimeout = int.MaxValue;
var resultado = comando.ExecuteNonQuery();
conexion.Close();
}
lblFechaFinText.Text = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString();
lblResultado.Text = "Se Insertaron " + numberOfInsertedRows + " registros";
}
}
}
btnBulkCopy.Enabled = true;
}
catch (Exception ex)
{
lblResultado.Text = "Hubo un error en la carga.";
Logger.Info(ex.Message);
}
}
}
private void uploadDB(string txtFileFullPath, string databaseName, string userID, string userPwd)
{
dbID = userID;
dbPwd = userPwd;
dbName = databaseName;
string conStr = "Server=localhost;Database=" + dbName + ";Uid=" + dbID + ";Pwd=" + dbPwd;
MySqlConnection con = new MySqlConnection(conStr);
MySqlCommand comm = con.CreateCommand();
try
{
MySqlBulkLoader bl = new MySqlBulkLoader(con);
bl.TableName = "`" + dbName + "`.`measured_data`";
bl.FieldTerminator = ";";
bl.LineTerminator = "\n";
bl.NumberOfLinesToSkip = 1;
bl.FileName = txtFileFullPath;
con.Open();
int count = bl.Load();
con.Close();
}
catch (MySqlException e)
{
MessageBox.Show("실행이 없거나, ID와 비밀번호가 틀립니다. 확인해주세요.");
}
}
static void Main(string[] args)
{
#region Inicializar Globales
log4net.Config.XmlConfigurator.Configure();
ILog Logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
#endregion
#region AgregarTarea
try
{
var archivoAppSetting = ConfigurationManager.AppSettings["archivos"];
var rutaINAppSetting = ConfigurationManager.AppSettings["rutaIN"];
if (!string.IsNullOrEmpty(archivoAppSetting))
{
var archivos = archivoAppSetting.Split(',');
if (archivos.Count() > 0)
{
Logger.Info("Inicio UploadFileExtactTarget");
foreach (var archivo in archivos)
{
var rutaTotal = @"" + rutaINAppSetting + archivo;
Logger.Info("Cargando " + rutaTotal);
var fileName = archivo.ToLower().Replace(".csv", "");
using (var conexion = new MySqlConnection(ConfigurationManager.AppSettings["cadenaConexion"]))
{
var bulkLoader = new MySqlBulkLoader(conexion)
{
TableName = fileName,
FieldTerminator = ",",
LineTerminator = "\n",
FileName = rutaTotal,
NumberOfLinesToSkip = 1
};
var numberOfInsertedRows = bulkLoader.Load();
}
Logger.Info("Finalizó " + rutaTotal);
}
Logger.Info("Fin UploadFileExtactTarget");
}
else
{
Logger.Info("El appSettings denominado archivos está vacío o no está separado por comas");
}
}
else
{
Logger.Info("El appSettings denominado archivos está vacío");
}
}
catch (Exception ex)
{
Logger.ErrorFormat("Mensaje: {0} Trace: {1}", ex.Message, ex.StackTrace);
}
#endregion AgregarTarea
}
public void BulkInsert(string csvfile, string table, string fieldterminator, string lineterminator, int linestoskip)
{
try
{
MySqlBulkLoader bl = new MySqlBulkLoader(sqlconn);
bl.TableName = table;
bl.FieldTerminator = fieldterminator;
bl.LineTerminator = lineterminator;
bl.FileName = csvfile;
bl.NumberOfLinesToSkip = linestoskip;
bl.Load();
}
catch (Exception e)
{
throw e;
}
}
public string BulkDataInsert(string tableName, string fieldTerminator, string lineTerminator, string fileName, int numberOfLineToSkip)
{
try
{
string msg = string.Empty;
MySqlBulkLoader bulkLoader = new MySqlBulkLoader(SqlConnectionObj);
bulkLoader.TableName = tableName;
bulkLoader.FieldTerminator = fieldTerminator;
bulkLoader.LineTerminator = lineTerminator;
bulkLoader.FileName = fileName;
bulkLoader.NumberOfLinesToSkip = numberOfLineToSkip;
int rows = bulkLoader.Load();
return "Data Success fully Inserted";
}
catch (Exception exception)
{
return "Exception from Bulk Insert. Message: " + exception.Message;
}
}
private void btnBulkCopy_Click(object sender, EventArgs e)
{
ILog Logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
Stream myStream = null;
OpenFileDialog openFileDialog1 = new OpenFileDialog();
lblFechaInicioText.Text = "";
lblFechaFinText.Text = "";
lblResultado.Text = "";
openFileDialog1.InitialDirectory = "D:\\";
openFileDialog1.Filter = "Text files (*.dat)|*.dat";
openFileDialog1.FilterIndex = 1;
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
string periodo = Path.GetFileNameWithoutExtension(openFileDialog1.FileName);
int existePeriodo = 0;
using (var conexion = new MySqlConnection(ConfigurationManager.AppSettings["cadenaConexion"]))
{
using (MySqlCommand comando = new MySqlCommand("VerificarExistePeriodo"))
{
comando.CommandType = CommandType.StoredProcedure;
comando.Connection = conexion;
conexion.Open();
comando.CommandTimeout = int.MaxValue;
comando.Parameters.Add("_periodo", MySqlDbType.VarChar).Value = periodo;
using (var lector = comando.ExecuteReader())
{
if (lector.Read())
{
existePeriodo = lector.IsDBNull(lector.GetOrdinal("Existe")) ? default(int) : lector.GetInt32(lector.GetOrdinal("Existe"));
}
}
}
}
if (existePeriodo==1)
{
if (MessageBox.Show(this, "El Periodo " + periodo +" ya existe, si usted continúa el proceso se eliminarán los registros actuales de este periodo y se ingresarán los de esta carga",
"Mensaje", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
{
btnBulkCopy.Enabled = false;
if ((myStream = openFileDialog1.OpenFile()) != null)
{
using (myStream)
{
using (var conexion = new MySqlConnection(ConfigurationManager.AppSettings["cadenaConexion"]))
{
lblFechaInicioText.Text = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString();
using (MySqlCommand comando = new MySqlCommand("DeleteVentaByPeriodo"))
{
comando.CommandType = CommandType.StoredProcedure;
comando.Connection = conexion;
conexion.Open();
comando.CommandTimeout = int.MaxValue;
comando.Parameters.Add("_periodo", MySqlDbType.VarChar).Value = periodo;
var resultado = comando.ExecuteNonQuery();
}
var bulkLoader = new MySqlBulkLoader(conexion)
{
TableName = "Venta",
FieldTerminator = "\t",
LineTerminator = "\n",
FileName = openFileDialog1.FileName,
NumberOfLinesToSkip = 1
};
var numberOfInsertedRows = bulkLoader.Load();
using (MySqlCommand comando = new MySqlCommand("DeleteInsertPeriodo"))
{
comando.CommandType = CommandType.StoredProcedure;
comando.Connection = conexion;
comando.CommandTimeout = int.MaxValue;
var resultado = comando.ExecuteNonQuery();
conexion.Close();
}
lblFechaFinText.Text = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString();
lblResultado.Text = "Se Insertaron " + numberOfInsertedRows + " registros";
}
}
}
btnBulkCopy.Enabled = true;
}
else
{
lblResultado.Text = "Se canceló la carga";
}
}
else
{
btnBulkCopy.Enabled = false;
if ((myStream = openFileDialog1.OpenFile()) != null)
{
using (myStream)
{
using (var conexion = new MySqlConnection(ConfigurationManager.AppSettings["cadenaConexion"]))
{
lblFechaInicioText.Text = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString();
using (MySqlCommand comando = new MySqlCommand("Conectarse"))
{
comando.CommandType = CommandType.StoredProcedure;
comando.Connection = conexion;
conexion.Open();
comando.CommandTimeout = int.MaxValue;
var resultado = comando.ExecuteNonQuery();
}
var bulkLoader = new MySqlBulkLoader(conexion)
{
TableName = "Venta",
FieldTerminator = "\t",
LineTerminator = "\n",
FileName = openFileDialog1.FileName,
NumberOfLinesToSkip = 1
};
var numberOfInsertedRows = bulkLoader.Load();
using (MySqlCommand comando = new MySqlCommand("DeleteInsertPeriodo"))
{
comando.CommandType = CommandType.StoredProcedure;
comando.Connection = conexion;
comando.CommandTimeout = int.MaxValue;
var resultado = comando.ExecuteNonQuery();
conexion.Close();
}
lblFechaFinText.Text = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString();
lblResultado.Text = "Se Insertaron " + numberOfInsertedRows + " registros";
}
}
}
btnBulkCopy.Enabled = true;
}
}
catch (Exception ex)
{
lblResultado.Text = "Hubo un error en la carga.";
Logger.Info(ex.Message);
}
}
}
/// <summary>
/// 批量入库
/// </summary>
/// <param name="typeName">类型名称，对应AppSetting中的配置</param>
/// <param name="resource">结果集</param>
public void BulkInsert(string typeName, DataTable resource)
{
FuncSaveData(new FunctionParms()
{
FunctionName = string.Format("uspClear{0}Temp", typeName)//uspClearFrontTemp
});
string tempFileDir = AppDomain.CurrentDomain.BaseDirectory + "TempFolder\\";
string strFile = tempFileDir + "Temp" + DateTime.Now.Ticks.ToString() + ".csv";//Create directory if not exist... Make sure directory has required rights..
if (!Directory.Exists(tempFileDir))
Directory.CreateDirectory(tempFileDir);//If file does not exist then create it and right data into it..
if (!File.Exists(strFile))
{
FileStream fs = new FileStream(strFile, FileMode.Create, FileAccess.Write);
fs.Close();
fs.Dispose();
}
CreateCSVfile(resource, strFile);
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();// mysql.data.dll 5.6以上版本 目前用6.9.8
MySqlBulkLoader bulkLoader = new MySqlBulkLoader(conn);
bulkLoader.TableName = string.Format("import_{0}_temp", typeName.ToLower());
bulkLoader.FieldTerminator = ",";
bulkLoader.LineTerminator = "\r\n";
bulkLoader.FileName = strFile;
bulkLoader.NumberOfLinesToSkip = 0;
bulkLoader.Load();
File.Delete(strFile);
}
//using (SqlBulkCopy bulkCopy = new SqlBulkCopy("Server=localhost;Database=crm_4s;Uid=root;Pwd=;"))
//{
// bulkCopy.BatchSize = 5000;
// bulkCopy.DestinationTableName = "import_front_temp";
// bulkCopy.WriteToServer(resource);
//}
FuncSaveData(new FunctionParms()
{
FunctionName = string.Format("uspSync{0}Records", typeName)//uspImortFrontRecords
});
}
public static void UploadOperation(string rutaInicial, string tipo, string campaniaId, string baseGc, string rutaBaseGC, string tipoBaseGC)
{
ILog Logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
Logger.Info("Inicia UploadOperation de Campaña : " + campaniaId);
var myguid = Guid.NewGuid();
var rutaFinal = ConfigurationManager.AppSettings["rutaIN"] + myguid.ToString() + ".csv";
Logger.Info("rutaFinalFolio = " + rutaFinal);
WritterCSVFolio(rutaInicial, tipo, campaniaId, rutaFinal);
using (var conexion = new MySqlConnection(ConfigurationManager.AppSettings["cadenaConexion"]))
{
var bulkLoader = new MySqlBulkLoader(conexion)
{
TableName = "Folio",
FieldTerminator = ",",
LineTerminator = "\n",
FileName = rutaFinal,
NumberOfLinesToSkip = 0,
Columns = { "Folio", "Estado", "CampaniaId" }
};
var numberOfInsertedRows = bulkLoader.Load();
}
if (baseGc == "1")
{
myguid = Guid.NewGuid();
rutaFinal = ConfigurationManager.AppSettings["rutaIN"] + myguid.ToString() + ".csv";
Logger.Info("rutaFinalBaseGC = " + rutaFinal);
WritterCSVFolio(rutaBaseGC, tipoBaseGC, campaniaId, rutaFinal);
using (var conexion = new MySqlConnection(ConfigurationManager.AppSettings["cadenaConexion"]))
{
var bulkLoader = new MySqlBulkLoader(conexion)
{
TableName = "FolioEspejo",
FieldTerminator = ",",
LineTerminator = "\n",
FileName = rutaFinal,
NumberOfLinesToSkip = 0,
Columns = { "Folio", "Estado", "CampaniaId" }
};
var numberOfInsertedRows = bulkLoader.Load();
using (MySqlCommand comando = new MySqlCommand("UpdateFolioMasivoBaseGC"))
{
comando.CommandType = CommandType.StoredProcedure;
comando.Connection = conexion;
conexion.Open();
comando.Parameters.Add("_campaniaId", MySqlDbType.Int32).Value = campaniaId;
var resultado = comando.ExecuteNonQuery();
}
}
}
else
{
using (var conexion = new MySqlConnection(ConfigurationManager.AppSettings["cadenaConexion"]))
{
using (MySqlCommand comando = new MySqlCommand("UpdateFolioRandom"))
{
comando.CommandType = CommandType.StoredProcedure;
comando.Connection = conexion;
conexion.Open();
comando.Parameters.Add("_campaniaId", MySqlDbType.Int32).Value = campaniaId;
comando.Parameters.Add("_porcentaje", MySqlDbType.Decimal, 2).Value = rutaBaseGC;
var resultado = comando.ExecuteNonQuery();
}
}
}
Logger.Info("Termina UploadOperation de Campaña : " + campaniaId);
}
public void BulkLoadConflictOptionIgnore()
{
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
sw.WriteLine(i + ",col1");
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
int count = loader.Load();
Assert.AreEqual(20, count);
path = Path.GetTempFileName();
sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
sw.WriteLine(i + ",col2");
sw.Flush();
sw.Close();
loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.ConflictOption = MySqlBulkLoaderConflictOption.Ignore;
loader.Load();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(20, dt.Rows.Count);
Assert.AreEqual("col1", dt.Rows[0][1].ToString().Trim());
}
public string BulkDataInsert(string tableName, char fieldQuotationCharacter, string fieldTerminator, string lineTerminator, string fileName, int numberOfLineToSkip)
{
string msg = string.Empty;
MySqlBulkLoader bulkLoader = new MySqlBulkLoader(SqlConnectionObj);
bulkLoader.Timeout = 1000 * 60 * 5;
bulkLoader.TableName = tableName;
bulkLoader.FieldQuotationOptional = false;
bulkLoader.FieldQuotationCharacter = fieldQuotationCharacter;
bulkLoader.FieldTerminator = fieldTerminator;
bulkLoader.LineTerminator = lineTerminator;
bulkLoader.FileName = fileName;
bulkLoader.NumberOfLinesToSkip = numberOfLineToSkip;
int rows = bulkLoader.Load();
return msg;
}
public static void DataInitializer()
{
string dirPath = "D:/Documents/Bengkel App/Data/";
string accFile = "Account Jurnal.xlsx";
string invFile = "Inv.xlsx";
string citFile = "City.xlsx";
string balJournal = "BalanceJournal.xlsx";
try
{
// todo: read excel inventory and acc and insert into temporary table
DataTable resultInv = DataExportImportUtils.CreateDataTableFromExcel(dirPath + invFile, true);
DataTable resultAcc = DataExportImportUtils.CreateDataTableFromExcel(dirPath + accFile, true);
DataTable resultCit = DataExportImportUtils.CreateDataTableFromExcel(dirPath + citFile, true);
DataTable resultCatJournal = DataExportImportUtils.CreateDataTableFromExcel(dirPath + balJournal, true);
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"CREATE TABLE `temp_catjournal` (
`Code` varchar(100) DEFAULT NULL,
`Name` varchar(100) DEFAULT NULL,
`Description` varchar(100) DEFAULT NULL,
`Value` varchar(100) DEFAULT NULL,
`Parent` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
catch (Exception ex)
{
if (ex != null) { }
// do nothing
}
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"CREATE TABLE `temp_cit` (
`Id` varchar(100) DEFAULT NULL,
`Code` varchar(100) DEFAULT NULL,
`Name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
catch (Exception ex)
{
if (ex != null) { }
// do nothing
}
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"CREATE TABLE `temp_inv` (
`Kode` varchar(100) DEFAULT NULL,
`Nama` varchar(100) DEFAULT NULL,
`Qmin` double DEFAULT NULL,
`Unit` varchar(10) DEFAULT NULL,
`Chusr` int(11) DEFAULT NULL,
`Chtime` varchar(100) DEFAULT NULL,
`Jenis` varchar(100) DEFAULT NULL,
`Price` double DEFAULT NULL,
`ACC` varchar(45) DEFAULT NULL,
`LAMA` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
catch (Exception ex)
{
if (ex != null) { }
// do nothing
}
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"CREATE TABLE `temp_acc` (
`Kode` varchar(100) DEFAULT NULL,
`Nama` varchar(100) DEFAULT NULL,
`Induk` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
catch (Exception ex)
{
if (ex != null) { }
// do nothing
}
if (resultCatJournal != null)
{
resultCatJournal.ExportDataTableToCsv(dirPath + "catjournal.csv");
// insert into database
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
conn.Open();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "temp_catjournal";
loader.FieldTerminator = "\t";
loader.LineTerminator = "\n";
loader.FileName = dirPath + "catjournal.csv";
loader.NumberOfLinesToSkip = 1;
int inserted = loader.Load();
Console.WriteLine("Total rows: " + inserted);
conn.Close();
}
// insert into database main ref
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"INSERT INTO `references` (`Code`, `Name`, `Description`, `Value`, `ParentId`)
SELECT REPLACE(REPLACE(`code`, '\r', ''), '\n', ''), `Name`, `Description`, `Value`,
(SELECT `Id` FROM `references` WHERE `Code`=REPLACE(REPLACE(`parent`, '\r', ''), '\n', ''))
FROM temp_catjournal";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
// using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
// {
// MySqlCommand cmd = conn.CreateCommand();
// cmd.CommandText = @"UPDATE `references` a
// SET a.ParentId = (SELECT z.Id FROM (SELECT x.*, y.Code `Kode` FROM `references` x, temp_catjournal y WHERE x.Code=y.Parent) z WHERE z.Kode=a.Code)";
// cmd.CommandType = CommandType.Text;
// conn.Open();
// cmd.ExecuteNonQuery();
// conn.Clone();
// }
}
if (resultCit != null)
{
resultCit.ExportDataTableToCsv(dirPath + "city.csv");
// insert into database
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
conn.Open();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "temp_cit";
loader.FieldTerminator = "\t";
loader.LineTerminator = "\n";
loader.FileName = dirPath + "city.csv";
loader.NumberOfLinesToSkip = 1;
int inserted = loader.Load();
Console.WriteLine("Total rows: " + inserted);
conn.Close();
}
// insert into database city
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"INSERT INTO cities (`Code`, `Name`)
SELECT `Code`, `Name`
FROM temp_cit";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
if (resultInv != null)
{
// fix typing error
foreach (DataRow iRow in resultInv.Rows)
{
if (iRow["Kode"].ToString().Contains("["))
{
iRow["Kode"] = iRow["Kode"].ToString().Replace("[", "");
}
if (iRow["Unit"].ToString().Contains("PV"))
{
iRow["Unit"] = "PC";
}
}
// convert to csv
resultInv.ExportDataTableToCsv(dirPath + "inv.csv");
// insert into database
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
conn.Open();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "temp_inv";
loader.FieldTerminator = "\t";
loader.LineTerminator = "\n";
loader.FileName = dirPath + "inv.csv";
loader.NumberOfLinesToSkip = 1;
int inserted = loader.Load();
Console.WriteLine("Total rows: " + inserted);
conn.Close();
}
// generate main sparepart data
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"INSERT INTO spareparts (`Code`, `Name`, `StockQty`, `UnitReferenceId`, `CategoryReferenceId`, `CreateDate`, `CreateUserId`, `ModifyDate`, `ModifyUserId`, `Status`)
SELECT Kode, Nama, 0,
IFNULL((SELECT a.`Id` FROM `references` a WHERE a.`ParentId`=(SELECT Id FROM `references` WHERE Code='REF_SPAREPARTUNIT') AND a.`Code`=Unit),
(SELECT a.`Id` FROM `references` a WHERE a.`ParentId`=(SELECT Id FROM `references` WHERE Code='REF_SPAREPARTUNIT') AND a.`Code`='-')),
IFNULL((SELECT a.`Id` FROM `references` a WHERE a.`ParentId`=(SELECT Id FROM `references` WHERE Code='REF_SPAREPARTCATEGORY') AND a.`Code`=SUBSTR(Kode, 1, LENGTH(a.`Code`))),
(SELECT a.`Id` FROM `references` a WHERE a.`ParentId`=(SELECT Id FROM `references` WHERE Code='REF_SPAREPARTCATEGORY') AND a.`Code`='-')),
current_date(), (SELECT Id FROM users WHERE UserName='******'),
current_date(), (SELECT Id FROM users WHERE UserName='******'), 1
FROM temp_inv";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM temp_inv";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
if (resultAcc != null)
{
foreach (DataRow row in resultAcc.Rows)
{
row["Kode"] = row["Kode"].ToString().Trim();
try
{
row["Induk"] = row["Induk"].ToString();
}
catch { }
}
// convert to csv
resultAcc.ExportDataTableToCsv(dirPath + "acc.csv");
// insert into database
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
conn.Open();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "temp_acc";
loader.FieldTerminator = "\t";
loader.LineTerminator = "\n";
loader.FileName = dirPath + "acc.csv";
loader.NumberOfLinesToSkip = 1;
int inserted = loader.Load();
Console.WriteLine("Total rows: " + inserted);
conn.Close();
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"UPDATE temp_acc SET Induk=REPLACE(REPLACE(Induk, '\r', ''), '\n', '')";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
// generate main account data
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"INSERT INTO journalmasters (`Code`, `Name`, `ParentId`)
SELECT `Kode`, `Nama`,
(SELECT a.Id FROM journalmasters a, temp_acc b WHERE a.Code=b.Induk)
FROM temp_acc";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"UPDATE journalmasters a
SET a.ParentId = (SELECT z.Id FROM (SELECT x.*, y.Kode FROM journalmasters x, temp_acc y WHERE x.Code=y.Induk) z WHERE z.Kode=a.Code)";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DROP TABLE temp_catjournal";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DROP TABLE temp_cit";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DROP TABLE temp_inv";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString))
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DROP TABLE temp_acc";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Clone();
}
}
catch (Exception ex)
{
throw ex;
}
}