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 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 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 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 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;
}
}
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 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와 비밀번호가 틀립니다. 확인해주세요.");
}
}
/// <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 MySqlBulkCopyWrapper(MySqlBulkLoader copyTool)
{
_sbc = copyTool;
}
/// <summary>
///大批量数据插入
/// </summary>
/// <param name="table">数据表</param>
/// <param name="connectionString">数据库连接字符串</param>
/// <returns></returns>
public bool MySqlBulkInsert(DataTable table, string connectionString)
{
try
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
MySqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
NumberOfLinesToSkip = 0,
TableName = table.TableName,
};
bulk.Timeout = CommandTimeOut;
bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());
tran.Commit();
return true;
}
catch
{
tran.Rollback();
return false;
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return false;
}
}
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
}
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);
}
}
}
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 async ValueTask WriteToServerAsync(IOBehavior ioBehavior, CancellationToken cancellationToken)
#endif
{
var tableName = DestinationTableName ?? throw new InvalidOperationException("DestinationTableName must be set before calling WriteToServer");
var bulkLoader = new MySqlBulkLoader(m_connection)
{
CharacterSet = "utf8mb4",
EscapeCharacter = '\\',
FieldQuotationCharacter = '\0',
FieldTerminator = "\t",
LinePrefix = null,
LineTerminator = "\n",
Local = true,
NumberOfLinesToSkip = 0,
Source = this,
TableName = QuoteIdentifier(tableName),
Timeout = BulkCopyTimeout,
};
var closeConnection = false;
if (m_connection.State != ConnectionState.Open)
{
m_connection.Open();
closeConnection = true;
}
using (var cmd = new MySqlCommand("select * from " + QuoteIdentifier(tableName) + ";", m_connection, m_transaction))
using (var reader = (MySqlDataReader)await cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly, ioBehavior, cancellationToken).ConfigureAwait(false))
{
var schema = reader.GetColumnSchema();
for (var i = 0; i < schema.Count; i++)
{
if (schema[i].DataTypeName == "BIT")
{
bulkLoader.Columns.Add($"@col{i}");
bulkLoader.Expressions.Add($"`{reader.GetName(i)}` = CAST(@col{i} AS UNSIGNED)");
}
else if (schema[i].DataTypeName == "YEAR")
{
// the current code can't distinguish between 0 = 0000 and 0 = 2000
throw new NotSupportedException("'YEAR' columns are not supported by MySqlBulkLoader.");
}
else
{
var type = schema[i].DataType;
if (type == typeof(byte[]) || (type == typeof(Guid) && (m_connection.GuidFormat == MySqlGuidFormat.Binary16 || m_connection.GuidFormat == MySqlGuidFormat.LittleEndianBinary16 || m_connection.GuidFormat == MySqlGuidFormat.TimeSwapBinary16)))
{
bulkLoader.Columns.Add($"@col{i}");
bulkLoader.Expressions.Add($"`{reader.GetName(i)}` = UNHEX(@col{i})");
}
else
{
bulkLoader.Columns.Add(QuoteIdentifier(reader.GetName(i)));
}
}
}
}
await bulkLoader.LoadAsync(ioBehavior, cancellationToken).ConfigureAwait(false);
if (closeConnection)
{
m_connection.Close();
}
#if !NETSTANDARD2_1 && !NETCOREAPP3_0
return(default);
private async ValueTask WriteToServerAsync(IOBehavior ioBehavior, CancellationToken cancellationToken)
#endif
{
var tableName = DestinationTableName ?? throw new InvalidOperationException("DestinationTableName must be set before calling WriteToServer");
m_wasAborted = false;
Log.Info("Starting bulk copy to {0}", tableName);
var bulkLoader = new MySqlBulkLoader(m_connection)
{
CharacterSet = "utf8mb4",
EscapeCharacter = '\\',
FieldQuotationCharacter = '\0',
FieldTerminator = "\t",
LinePrefix = null,
LineTerminator = "\n",
Local = true,
NumberOfLinesToSkip = 0,
Source = this,
TableName = tableName,
Timeout = BulkCopyTimeout,
};
var closeConnection = false;
if (m_connection.State != ConnectionState.Open)
{
m_connection.Open();
closeConnection = true;
}
// merge column mappings with the destination schema
var columnMappings = new List <MySqlBulkCopyColumnMapping>(ColumnMappings);
var addDefaultMappings = columnMappings.Count == 0;
using (var cmd = new MySqlCommand("select * from " + tableName + ";", m_connection, m_transaction))
using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly, ioBehavior, cancellationToken).ConfigureAwait(false))
{
var schema = reader.GetColumnSchema();
for (var i = 0; i < Math.Min(m_valuesEnumerator !.FieldCount, schema.Count); i++)
{
var destinationColumn = reader.GetName(i);
if (schema[i].DataTypeName == "BIT")
{
AddColumnMapping(columnMappings, addDefaultMappings, i, destinationColumn, $"@`\uE002\bcol{i}`", $"%COL% = CAST(%VAR% AS UNSIGNED)");
}
else if (schema[i].DataTypeName == "YEAR")
{
// the current code can't distinguish between 0 = 0000 and 0 = 2000
throw new NotSupportedException("'YEAR' columns are not supported by MySqlBulkLoader.");
}
else
{
var type = schema[i].DataType;
if (type == typeof(byte[]) || (type == typeof(Guid) && (m_connection.GuidFormat == MySqlGuidFormat.Binary16 || m_connection.GuidFormat == MySqlGuidFormat.LittleEndianBinary16 || m_connection.GuidFormat == MySqlGuidFormat.TimeSwapBinary16)))
{
AddColumnMapping(columnMappings, addDefaultMappings, i, destinationColumn, $"@`\uE002\bcol{i}`", $"%COL% = UNHEX(%VAR%)");
}
else if (addDefaultMappings)
{
Log.Debug("Adding default column mapping from SourceOrdinal {0} to DestinationColumn {1}", i, destinationColumn);
columnMappings.Add(new MySqlBulkCopyColumnMapping(i, destinationColumn));
}
}
}
}
// set columns and expressions from the column mappings
for (var i = 0; i < m_valuesEnumerator.FieldCount; i++)
{
var columnMapping = columnMappings.FirstOrDefault(x => x.SourceOrdinal == i);
if (columnMapping is null)
{
Log.Debug("Ignoring column with SourceOrdinal {0}", i);
bulkLoader.Columns.Add("@`\uE002\bignore`");
}
else
{
if (columnMapping.DestinationColumn.Length == 0)
{
throw new InvalidOperationException("MySqlBulkCopyColumnMapping.DestinationName is not set for SourceOrdinal {0}".FormatInvariant(columnMapping.SourceOrdinal));
}
if (columnMapping.DestinationColumn[0] == '@')
{
bulkLoader.Columns.Add(columnMapping.DestinationColumn);
}
else
{
bulkLoader.Columns.Add(QuoteIdentifier(columnMapping.DestinationColumn));
}
if (columnMapping.Expression is object)
{
bulkLoader.Expressions.Add(columnMapping.Expression);
}
}
}
foreach (var columnMapping in columnMappings)
{
if (columnMapping.SourceOrdinal < 0 || columnMapping.SourceOrdinal >= m_valuesEnumerator.FieldCount)
{
throw new InvalidOperationException("SourceOrdinal {0} is an invalid value".FormatInvariant(columnMapping.SourceOrdinal));
}
}
var rowsInserted = await bulkLoader.LoadAsync(ioBehavior, cancellationToken).ConfigureAwait(false);
if (closeConnection)
{
m_connection.Close();
}
Log.Info("Finished bulk copy to {0}", tableName);
if (!m_wasAborted && rowsInserted != RowsCopied)
{
Log.Error("Bulk copy to DestinationTableName={0} failed; RowsCopied={1}; RowsInserted={2}", tableName, RowsCopied, rowsInserted);
throw new MySqlException(MySqlErrorCode.BulkCopyFailed, "{0} rows were copied to {1} but only {2} were inserted.".FormatInvariant(RowsCopied, tableName, rowsInserted));
}
#if !NETSTANDARD2_1 && !NETCOREAPP3_0
return(default);
/// <summary>
/// 将文本数据 导入到库
/// </summary>
/// <param name="filePath"></param>
/// <param name="transaction"></param>
/// <returns></returns>
private int ImportDataFromFile(string filePath, IDbTransaction transaction = null)
{
if (!File.Exists(filePath))
{
throw new FileNotFoundException(string.Concat("the data file can not be found at ", filePath));
}
using (MySqlConnection conn = new MySqlConnection(this.ConnectionString))
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
IDbTransaction dbTran = transaction;
bool isInnerTran = false;
if (null == dbTran)
{
dbTran = conn.BeginTransaction();
isInnerTran = true;
}
try
{
//step 1 load data to db
MySqlBulkLoader bcp = new MySqlBulkLoader(conn);
bcp.TableName = this.TableName;
bcp.Timeout = this.TimeOut;
bcp.FieldTerminator = ",";
bcp.FieldQuotationCharacter = '"';
bcp.EscapeCharacter = '"';
bcp.LineTerminator = "\r\n";
bcp.FileName = filePath;
bcp.NumberOfLinesToSkip = 0;
int result = bcp.Load();
//批量插入模式的 自增id不连续 这是mysql 的设计 预留不确定的自增数 反正自增不连续不影响业务
#region 资料
/*
* http://www.cnblogs.com/zhoujinyi/p/3433823.html
* 0：通过表锁的方式进行，也就是所有类型的insert都用AUTO-inc locking。
* 1：默认值，对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作，对于bulk insert 则还是使用表锁的方式进行。
* 2：对所有的insert-like 自增长值的产生使用互斥量机制完成，性能最高，并发插入可能导致自增值不连续，可能会导致Statement 的 Replication 出现不一致，使用该模式，需要用 Row Replication的模式
*
* ALTER TABLE students AUTO_INCREMENT = 11;
*
* show variables like 'innodb_autoinc_lock_mode';
*
* SELECT Auto_increment
* FROM information_schema.`TABLES`
* WHERE Table_Schema='demodb'
* AND table_name = 'students'
*/
#endregion
if (isInnerTran == true)
{
//内部事务，完毕后提交
dbTran.Commit();
}
//异步删除文件
Task.Factory.StartNew(() => {
File.Delete(filePath);
});
return(result);
}
catch (Exception ex)
{
if (null != dbTran)
{
dbTran.Rollback();
}
throw ex;
}
finally
{
if (null != dbTran)
{
dbTran.Dispose();
}
}
}
}
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;
}
}
