コード例 #1
0
        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);
        }
コード例 #2
0
        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);
        }