private void insertDataPktCabang(String filename)
{
using (var csv = new CsvReader(new StreamReader(filename), true))
{
using (var sbc = new SqlBulkCopy(Variables.connectionString))
{
sbc.DestinationTableName = "dbo.Cabang";
sbc.BatchSize = 1000;
sbc.AddColumnMapping(0, 0);
sbc.AddColumnMapping(4, 1);
sbc.WriteToServer(csv);
}
}
using (SqlConnection sql = new SqlConnection(Variables.connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
sql.Open();
cmd.Connection = sql;
cmd.CommandText = "UPDATE CABANG SET kodeCabang = RIGHT(kodeCabang,4)";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE CABANG SET kodeCabang = substring(kodeCabang, patindex('%[^0]%',kodeCabang), 10)";
cmd.ExecuteNonQuery();
sql.Close();
}
}
}
private void inputOpti()
{
OpenFileDialog of = new OpenFileDialog();
of.Filter = Variables.csvFilter;
String connectionString = Variables.connectionString;
if (of.ShowDialog() == DialogResult.OK)
{
loadForm.ShowSplashScreen();
try
{
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
using (SqlCommand command = new SqlCommand("DELETE FROM Opti", con))
{
command.ExecuteNonQuery();
}
using (SqlCommand command = new SqlCommand("DBCC CHECKIDENT('Opti',RESEED,1)", con))
{
command.ExecuteNonQuery();
}
con.Close();
}
}
catch (SystemException ex)
{
MessageBox.Show(string.Format("An error occurred: {0}", ex.Message));
}
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("CREATE TABLE #TEMP ([idCashpoint] VARCHAR(255), [tanggal] VARCHAR(30), [prediksi] BIGINT)", con))
{
cmd.ExecuteNonQuery();
}
using (var csv = new CachedCsvReader(new StreamReader(of.FileName), true))
{
// Field headers will automatically be used as column names
try
{
using (var sbc = new SqlBulkCopy(con))
{
sbc.DestinationTableName = "dbo.#TEMP";
sbc.BatchSize = 1000;
sbc.AddColumnMapping(0, 0);
sbc.AddColumnMapping(12, 2);
sbc.AddColumnMapping(2, 1);
sbc.WriteToServer(csv);
}
}
catch (Exception err)
{
MessageBox.Show(err.ToString());
}
}
SqlCommand command = new SqlCommand();
command.Connection = con;
command.CommandText = "UPDATE #TEMP SET tanggal = REPLACE(tanggal,' M', ' ') WHERE tanggal like '%M%'";
command.ExecuteNonQuery();
command.CommandText = "UPDATE #TEMP SET tanggal = REPLACE(tanggal,' S', ' ') WHERE tanggal like '%S%'";
command.ExecuteNonQuery();
command.CommandText = "UPDATE #TEMP SET tanggal = REPLACE(tanggal,' U', '') WHERE tanggal like '%U%'";
command.ExecuteNonQuery();
command.CommandText = "UPDATE #TEMP SET tanggal = REPLACE(tanggal,' H', '') WHERE tanggal like '%H%'";
command.ExecuteNonQuery();
command.CommandText = "DELETE FROM #TEMP WHERE [idCashpoint] IN('AVERAGE','SUMMARY','ATM Horizons - Detail','Cashpoint')";
command.ExecuteNonQuery();
command.CommandText = "UPDATE #TEMP SET tanggal = RTRIM(tanggal)";
command.ExecuteNonQuery();
//SqlDataReader reader;
//command.CommandText = "SELECT * FROM #TEMP";
//reader = command.ExecuteReader();
//while (reader.Read())
//{
// Console.WriteLine(reader[0] + " " + reader[1] + " " + reader[2]);
//}
//reader.Close();
//command.CommandText = "DBCC CHECKIDENT(\"dbo.Opti\", RESEED, 0)";
//command.ExecuteNonQuery();
try
{
command.CommandText = "INSERT INTO Opti(idCashpoint, tanggal, prediksi) SELECT idCashpoint, CAST(tanggal AS DATE), prediksi FROM #TEMP TT";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE #TEMP";
command.ExecuteNonQuery();
}
catch (Exception er)
{
MessageBox.Show("File opti belum sesuai template");
}
con.Close();
}
loadForm.CloseForm();
MessageBox.Show("Done!");
}
}
