private void ExportDataBtn_Click(object sender, RoutedEventArgs e) { string host = host_export.Text; string port = port_export.Text; string user = user_export.Text; string pwd = pwd_export.Text; string database = database_export.Text; string day_id = dateBox.Text; string dir = @dir_export.Text; logBox.Document.Blocks.Clear(); //不能选择今天已经之后的日期 DateTime today = Convert.ToDateTime(DateTime.Now.ToShortDateString()); DateTime day = Convert.ToDateTime(day_id); if (day >= today) { this.WriteExportLog("日期选择异常", "error"); MessageBox.Show("请选择今天之前的日期!"); return; } //判断是否有这天的数据 string sql = "select * from B_K_LTE_TDD_HOUR where day_id = '" + day_id + "'"; string sql1 = "select * from B_K_LTE_FDD_HOUR where day_id = '" + day_id + "'"; MySQLConn mysql = new MySQLConn(); MySqlConnection conn = mysql.GetMySqlConn(host, port, user, pwd, database); conn.Open(); try { DataTable dt = mysql.QuerySql(sql, conn); DataTable dt1 = mysql.QuerySql(sql1, conn); if (dt.Rows.Count == 0 && dt1.Rows.Count == 0) { this.WriteExportLog("该天无数据", "error"); MessageBox.Show("选择日期没有数据,请重新选择"); return; } } catch (Exception ex) { MessageBox.Show("发生异常: " + ex.Message, "连接异常", MessageBoxButton.OK, MessageBoxImage.Warning); this.WriteExportLog("连接异常", "error"); return; } //创建文件夹 dir += @"\" + day_id; Directory.CreateDirectory(dir); //获取全部表名 sql = "select table_name from information_schema.tables where table_schema = '" + database + "' and table_name like 'B_K_%' order by table_name;"; DataTable allTableNames = mysql.QuerySql(sql, conn); foreach (DataRow name in allTableNames.Rows) { string tableName = name[0].ToString(); this.WriteExportLog("开始导出表" + tableName, "info"); //获取日期字段是day还是day_id sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '" + tableName + "' and table_schema = '" + database + "' and COLUMN_NAME like 'day%' ;"; DataTable dt_day = mysql.QuerySql(sql, conn); string day_column = dt_day.Rows[0][0].ToString(); //创建csv文件 string fileName = tableName + ".csv"; MyFile mf = new MyFile(); FileStream fs = mf.CreateFileStream(dir, fileName); StreamWriter writer = new StreamWriter(fs); //获取数据 sql = "select * from " + tableName + " where " + day_column + " = '" + day_id + "'"; DataTable table = mysql.QuerySql(sql, conn); int columnCount = table.Columns.Count; string filedRow = ""; DataColumnCollection columns = table.Columns; foreach (DataColumn column in columns) { filedRow += column.ColumnName + ","; } filedRow = filedRow.Substring(0, filedRow.Length - 1); writer.WriteLine(filedRow); foreach (DataRow values in table.Rows) { string oneRowStr = ""; for (int i = 0; i < columnCount; i++) { oneRowStr += values[i].ToString() + ","; } oneRowStr = oneRowStr.Substring(0, oneRowStr.Length - 1); writer.WriteLine(oneRowStr); } this.WriteExportLog("表" + tableName + "导出完成", "info"); writer.Close(); } conn.Close(); this.WriteExportLog("执行完成", "info"); MessageBox.Show("导出数据已经完成!"); //把连接信息写入配置文件,方便之后使用 Configuration cfa = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); cfa.AppSettings.Settings["export_host"].Value = host; cfa.AppSettings.Settings["export_port"].Value = port; cfa.AppSettings.Settings["export_user"].Value = user; cfa.AppSettings.Settings["export_pwd"].Value = pwd; cfa.AppSettings.Settings["export_database"].Value = database; cfa.Save(ConfigurationSaveMode.Modified); }
private void ImportDataBtn_Click(object sender, RoutedEventArgs e) { string host = host_import.Text; string port = port_import.Text; string user = user_import.Text; string pwd = pwd_import.Text; string database = database_import.Text; string dirPath = importFilePath.Text; string province = provinceBox.Text; MyFile mf = new MyFile(); List <FileInfo> fileList = mf.GetFileNamesFromDir(dirPath); if (fileList.Count == 0) { this.WriteImportLog("选择的文件夹没有csv文件,请重新选择", "error"); MessageBox.Show("选择的文件夹没有csv文件,请重新选择"); return; } string[] dirArr = dirPath.Split('\\'); string day_id = dirArr[dirArr.Length - 1]; //实例化一个Regex对象 Regex re = new Regex(@"^^\d{4}-\d{2}-\d{2}$"); //验证数据是否匹配 if (re.IsMatch(day_id) == true) { this.WriteImportLog("选择的日期为:" + day_id, "info"); } else { this.WriteImportLog("选择的文件夹为:" + day_id + ",应该选择导出时已日期命名的文件夹", "error"); MessageBox.Show("选择的文件夹有误,请重新选择"); return; } MySQLConn mysql = new MySQLConn(); MySqlConnection conn = mysql.GetMySqlConn(host, port, user, pwd, database); conn.Open(); this.WriteImportLog("开始导入", "info"); foreach (FileInfo fl in fileList) { string tableName = fl.Name.Split('.')[0]; string filePath = fl.FullName; //获取日期字段是day还是day_id string sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '" + tableName + "' and table_schema = '" + database + "' and COLUMN_NAME like 'day%' ;"; string day_column = ""; try { DataTable dt_day = mysql.QuerySql(sql, conn); day_column = dt_day.Rows[0][0].ToString(); } catch (Exception ex) { MessageBox.Show("发生异常: " + ex.Message, "连接异常", MessageBoxButton.OK, MessageBoxImage.Warning); this.WriteImportLog("连接异常", "error"); return; } //先删除所选日期的数据 string deleteSql = ""; if (province == "") { deleteSql = "delete from `" + tableName + "` where " + day_column + "='" + day_id + "';"; } else { deleteSql = "delete from `" + tableName + "` where " + day_column + "='" + day_id + "' and province = '" + province + "';"; } mysql.ExecuteSql(deleteSql, conn); //导入数据 string loadFileSql = "LOAD DATA LOCAL INFILE '" + filePath + "' INTO TABLE " + tableName + " FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; "; loadFileSql = loadFileSql.Replace("\\", "/"); mysql.ExecuteSql(loadFileSql, conn); this.WriteImportLog(tableName + " 导入完成", "info"); } conn.Close(); this.WriteImportLog("导入结束", "info"); MessageBox.Show("导入数据已经完成!"); //把连接信息写入配置文件,方便之后使用 Configuration cfa = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); cfa.AppSettings.Settings["import_host"].Value = host; cfa.AppSettings.Settings["import_port"].Value = port; cfa.AppSettings.Settings["import_user"].Value = user; cfa.AppSettings.Settings["import_pwd"].Value = pwd; cfa.AppSettings.Settings["import_database"].Value = database; cfa.AppSettings.Settings["import_province"].Value = province; cfa.Save(ConfigurationSaveMode.Modified); }