private void BTN_GRP_DATA_YUKLE_Click(object sender, EventArgs e) { if (CHK_TRUNCATE_TABLE.Checked) { using (SqlConnection myConnectionTable = new SqlConnection(_GLOBAL_PARAMETRELER._CONNECTION_STRING.ToString())) { myConnectionTable.Open(); SqlCommand myCmd = new SqlCommand(); myCmd.CommandText = " truncate table dbo._TEMP_GRP_DATA ; truncate table DATA_TELEVIZYON_GRP"; myCmd.Connection = myConnectionTable; myCmd.ExecuteNonQuery(); myConnectionTable.Close(); } TXT_BX_LOG.Text += " GRP Temp table Temizlendi " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); } //using (SqlConnection conn = new SqlConnection(_GLOBAL_PARAMETRELER._CONNECTION_STRING)) //{ // SqlCommand command = new SqlCommand(); // conn.Open(); // command.Connection = conn; // //command.CommandText = " BULK INSERT dbo._TEMP_GRP_DATA FROM '" + BTN_FILE_SELECT.EditValue + "' WITH (DATAFILETYPE = 'widechar',FIRSTROW = 2, FIELDTERMINATOR = ';', ROWTERMINATOR = '\r\n',CODEPAGE = 'RAW')"; // command.CommandText = " BULK INSERT _TEMP_GRP_DATA FROM 'F:\\_MIGRA_DATA\\1-15 Aug.csv' WITH(CODEPAGE = 1254, FIRSTROW = 2) "; //" BULK INSERT _DATA_TEMP FROM '" + BTN_FILE_SELECT.EditValue + "' WITH (DATAFILETYPE ='WIDECHAR',FIRSTROW = 2, FIELDTERMINATOR = ';', ROWTERMINATOR = '\r\n', CODEPAGE = '1254')"; // command.CommandTimeout = 0; // // "BULK INSERT Northwind.dbo.[Order Details]" +, DATAFILETYPE ='WIDECHAR', // //@"FROM 'f:\orders\lineitem.tbl'" + // //"WITH" + // //"(" + // //"FIELDTERMINATOR = '|'," + // //"ROWTERMINATOR = ':\n'," + // //"FIRE_TRIGGERS" + // //")"; // command.ExecuteNonQuery(); //} Stopwatch sw = new Stopwatch(); sw.Start(); //SqlConnection conn = new SqlConnection(_GLOBAL_PARAMETRELER._CONNECTION_STRING); //SqlCommand command = new SqlCommand(); //conn.Open(); //command.Connection = conn; ////command.CommandText = " BULK INSERT dbo._TEMP_GRP_DATA FROM '" + BTN_FILE_SELECT.EditValue + "' WITH (DATAFILETYPE = 'widechar',FIRSTROW = 2, FIELDTERMINATOR = ';', ROWTERMINATOR = '\r\n',CODEPAGE = 'RAW')"; //command.CommandText = " BULK INSERT _TEMP_GRP_DATA FROM '" + BTN_FILE_SELECT.EditValue + "' WITH (DATAFILETYPE ='WIDECHAR',FIRSTROW = 2, FIELDTERMINATOR = ';', ROWTERMINATOR = '\r\n', CODEPAGE = '1254')"; //command.CommandTimeout = 0; //// "BULK INSERT Northwind.dbo.[Order Details]" +, DATAFILETYPE ='WIDECHAR', ////@"FROM 'f:\orders\lineitem.tbl'" + ////"WITH" + ////"(" + ////"FIELDTERMINATOR = '|'," + ////"ROWTERMINATOR = ':\n'," + ////"FIRE_TRIGGERS" + ////")"; //command.ExecuteNonQuery(); DataTable csvData = new DataTable(); string[] result = null; if (BTN_GRP_FILE_SELECT.EditValue == null) { return; } using (StreamReader reader = new StreamReader(BTN_GRP_FILE_SELECT.EditValue.ToString())) { result = reader.ReadLine().Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries); int col = 0; foreach (string column in result) { col++; DataColumn datecolumn = new DataColumn(column); datecolumn.AllowDBNull = true; csvData.Columns.Add(datecolumn + col.ToString()); } string line; while ((line = reader.ReadLine()) != null) { result = line.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries); csvData.Rows.Add(result); } reader.Close(); } TXT_BX_LOG.Text += " GRP table yapısı okunuyor " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); using (SqlConnection cn = new SqlConnection(_GLOBAL_PARAMETRELER._CONNECTION_STRING.ToString())) { cn.Open(); using (SqlBulkCopy copy = new SqlBulkCopy(cn)) { copy.ColumnMappings.Clear(); for (int i = 0; i <= csvData.Columns.Count - 1; i++) { copy.ColumnMappings.Add(i, i); } copy.BulkCopyTimeout = 0; copy.DestinationTableName = "_TEMP_GRP_DATA"; copy.WriteToServer(csvData); } } //TXT_BX_LOG.Text += " GRP Temp table data yüklendi " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); using (SqlConnection connection = new SqlConnection(_GLOBAL_PARAMETRELER._CONNECTION_STRING)) { SqlCommand commands = new SqlCommand("SELECT * FROM dbo.ADM_MECRA_KODU_KONTROL ", connection); connection.Open(); SqlDataReader reader = commands.ExecuteReader(); SqlConnection myConnectionTable = new SqlConnection(_GLOBAL_PARAMETRELER._CONNECTION_STRING.ToString()); myConnectionTable.Open(); while (reader.Read()) { SqlCommand myCmd = new SqlCommand(); myCmd.Parameters.AddWithValue("@MECRA_KODU", reader["MECRA_KODU"]); myCmd.Parameters.AddWithValue("@MECRA_KODU_CONVERT", reader["MECRA_KODU_CONVERT"]); //myCmd.CommandText = " UPDATE dbo.DATA_TELEVIZYON_GRP SET Channel= @MECRA_KODU_CONVERT WHERE Channel=@MECRA_KODU "; myCmd.CommandText = " UPDATE dbo._TEMP_GRP_DATA SET Channel= @MECRA_KODU_CONVERT WHERE Channel=@MECRA_KODU "; myCmd.Connection = myConnectionTable; myCmd.ExecuteNonQuery(); } //myCmd.Connection.Close(); // Call Close when done reading. myConnectionTable.Close(); reader.Close(); } DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(_GLOBAL_PARAMETRELER._CONNECTION_STRING.ToString())) { SqlDataAdapter adapter = new SqlDataAdapter() { SelectCommand = new SqlCommand("SELECT top 1000 * FROM dbo._TEMP_GRP_DATA", conn) }; adapter.Fill(ds, "_TEMP_GRP_DATA"); DataViewManager dvManager = new DataViewManager(ds); DataView _TEMP_GRP_DATA = dvManager.CreateDataView(ds.Tables[0]); gridControl_DATA.DataSource = _TEMP_GRP_DATA; } TXT_BX_LOG.Text += " Kanal İsimleri düzeltildi " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); TXT_BX_LOG.Text += " GRP dataları Convert ediliyor " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); DATA_MANAGER DT = new DATA_YUKLE.DATA_MANAGER(); DT.GRP_CONVERT_ROW_ADD(); TXT_BX_LOG.Text += " GRP dataları Convert işlemi tamamlandı " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); sw.Stop(); MessageBox.Show((sw.ElapsedMilliseconds / 1000.00).ToString()); }
private void BTN_SQL_YUKLE_Click(object sender, EventArgs e) { DATA_MANAGER DT = new DATA_YUKLE.DATA_MANAGER(); using (SqlConnection connection = new SqlConnection(_GLOBAL_PARAMETRELER._CONNECTION_STRING)) { string queryString = "SELECT * FROM dbo.ADM_MECRA_KODU_KONTROL "; SqlCommand commands = new SqlCommand(queryString, connection); connection.Open(); SqlDataReader reader = commands.ExecuteReader(); SqlConnection myConnectionTable = new SqlConnection(_GLOBAL_PARAMETRELER._CONNECTION_STRING.ToString()); myConnectionTable.Open(); while (reader.Read()) { SqlCommand myCmd = new SqlCommand(); myCmd.Parameters.AddWithValue("@MECRA_KODU", reader["MECRA_KODU"]); myCmd.Parameters.AddWithValue("@MECRA_KODU_CONVERT", reader["MECRA_KODU_CONVERT"]); myCmd.CommandText = " UPDATE dbo._TEMP_ADEX_DATA SET ANA_YAYIN=@MECRA_KODU_CONVERT ,MEDYA= @MECRA_KODU_CONVERT WHERE MEDYA=@MECRA_KODU "; myCmd.Connection = myConnectionTable; myCmd.ExecuteNonQuery(); } myConnectionTable.Close(); reader.Close(); } TXT_BX_LOG.Text += " ADEX Kanal İsimleri düzeltildi " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); if (CHK_VERIKONTROLU.Checked) { TXT_BX_LOG.Text += " Tarhi Kontrolu Yapılıyor " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); using (SqlConnection connection = new SqlConnection(_GLOBAL_PARAMETRELER._CONNECTION_STRING)) { SqlCommand commands = new SqlCommand("SELECT MIN(CONVERT(datetime, TARIH, 104)) AS MIN, MAX(CONVERT(datetime, TARIH, 104)) AS MAX, YAYIN_SINIFI FROM dbo._TEMP_ADEX_DATA GROUP BY YAYIN_SINIFI", connection); connection.Open(); SqlDataReader reader = commands.ExecuteReader(); SqlConnection myConnectionTable = new SqlConnection(_GLOBAL_PARAMETRELER._CONNECTION_STRING.ToString()); myConnectionTable.Open(); while (reader.Read()) { TXT_BX_LOG.Text += reader["MIN"].ToString() + "-" + reader["MIN"].ToString() + " " + reader["YAYIN_SINIFI"].ToString() + " Tarhi Kontrolu Yapılıyor " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); string TABLES = ""; SqlCommand myCmd = new SqlCommand(); myCmd.Parameters.AddWithValue("@MINTARIH", reader["MIN"]); myCmd.Parameters.AddWithValue("@MAXTARIH", reader["MAX"]); myCmd.Parameters.AddWithValue("@YAYIN_SINIFI", reader["YAYIN_SINIFI"]); if (reader["YAYIN_SINIFI"].ToString() == "DERGI") { TABLES = "dbo.[DATA_BASIN_ADEX]"; } if (reader["YAYIN_SINIFI"].ToString() == "GAZETE") { TABLES = "dbo.[DATA_BASIN_ADEX]"; } if (reader["YAYIN_SINIFI"].ToString() == "OUTDOOR") { TABLES = "dbo.[DATA_OUTDOOR_ADEX]"; } if (reader["YAYIN_SINIFI"].ToString() == "RADYO") { TABLES = "dbo.[DATA_RADYO_ADEX]"; } if (reader["YAYIN_SINIFI"].ToString() == "SINEMA") { TABLES = "dbo.[DATA_SINEMA_ADEX]"; } if (reader["YAYIN_SINIFI"].ToString() == "TELEVIZYON") { TABLES = "dbo.[DATA_TELEVIZYON_ADEX_2018]"; } if (reader["YAYIN_SINIFI"].ToString() == "YEREL TV") { TABLES = "dbo.[DATA_TELEVIZYON_ADEX_2018]"; } myCmd.CommandText = " DELETE " + TABLES + " WHERE TARIH>=@MINTARIH and TARIH<=@MAXTARIH and YAYIN_SINIFI=@YAYIN_SINIFI "; myCmd.Connection = myConnectionTable; myCmd.ExecuteNonQuery(); } myConnectionTable.Close(); reader.Close(); } TXT_BX_LOG.Text += " Tarhi Kontrolu Tamamlandı " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); } TXT_BX_LOG.Text += " Telvizyon datası yükleniyor " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); if (CHK_TELEVIZYON.Checked) { DT.TELEVIZYON_ROW_ADD(); TXT_BX_LOG.Text += " Telvizyon datası yükleniyor " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); TXT_BX_LOG.Text += " Dergi datası yükleniyor " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); } if (CHK_DERGI.Checked) { DT.DERGI_ROW_ADD(); TXT_BX_LOG.Text += " Dergi datası Yüklendi " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); TXT_BX_LOG.Text += " Gazete datası yükleniyor " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); } if (CHK_GAZETE.Checked) { DT.GAZETE_ROW_ADD(); TXT_BX_LOG.Text += " Gazete datası Yüklendi " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); TXT_BX_LOG.Text += " Radyo datası yükleniyor " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); } if (CHK_RADYO.Checked) { DT.RADYO_ROW_ADD(); TXT_BX_LOG.Text += " Radyo datası Yüklendi " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); TXT_BX_LOG.Text += " Sinema datası yükleniyor " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); } if (CHK_SINEMA.Checked) { DT.SINEMA_ROW_ADD(); TXT_BX_LOG.Text += " Sinema Datası Yüklendi " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); TXT_BX_LOG.Text += " Outdoor Datası yükleniyor " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); } if (CHK_OUTDOOR.Checked) { DT.OUTDOOR_ROW_ADD(); TXT_BX_LOG.Text += " Outdoor Datası Yüklendi " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); TXT_BX_LOG.Text += " GRP Datası ADEX Convert ediliyor " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); } if (CHK_GRP.Checked) { DT.GRP_MACH_TELEVIZYON_UPDATE(CMB_AY.Text); TXT_BX_LOG.Text += " GRP Datası Adex Convert edildi " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); } //if (TOGGLE_OUTDOOR_ALL_DELETE.IsOn) DT.OUTDOOR_ALL_DELETE(); // DT.YENI_DATA_INDEX_ROW_ADD(); }