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