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!"); } }