using Oracle.ManagedDataAccess.Client;
using SevenZip;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace Oracle11
{
    /// <summary>
    /// 数据库备份还原类(不支持并行调用)
    /// </summary>
    public static class DbHelper
    {
        #region 备份及还原
        /// <summary>
        /// 全量备份(返回一个7z压缩文件)
        /// </summary>
        /// <param name="oracleAddr">oracle地址(127.0.0.1:1521)</param>
        /// <param name="user">用户名(不区分大小写)</param>
        /// <param name="pwd">密码</param>
        /// <param name="serviceName">服务名称(orcl)</param>
        /// <param name="tables">要备份的表名(默认备份所有表)</param>
        public static string BackupDb(string dbAddr, string user, string pwd, string serviceName = "orcl", List<string> tables = null)
        {
            Directory.CreateDirectory("Backup");
            user = user.ToUpper();
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";

            if (tables == null || tables.Count == 0)
                tables = GetAllTable(dbAddr, user, pwd, serviceName);

            DirectoryInfo dir = new DirectoryInfo("Backup");
            var delFiles = dir.GetFiles();
            foreach (var item in delFiles)
            {
                if (!item.Name.ToUpper().EndsWith(".SQL") && !item.Name.ToUpper().EndsWith(".DAT") && !item.Name.ToUpper().EndsWith(".TXT"))
                    continue;
                item.Delete();
            }
            List<string> sqls = new List<string>();
            foreach (var item in tables)
            {
                var sql = $"select dbms_metadata.get_ddl('TABLE','{item}') createTableSql from dual";
                var createTableSql = OracleHelper.ExecuteScalar(connString, sql).ToString();
                createTableSql = createTableSql.Replace("\"", "").Replace($"{user}.", "").Trim().Trim("\n".ToCharArray());
                sqls.Add($"-- ----------------------------\r\n-- 创建表{item}\r\n-- ----------------------------\r\n{createTableSql}");

            }
            sqls = sqls.OrderBy(p => p.ToUpper().Contains("REFERENCES")).ToList();
            File.WriteAllText("Backup\\tables.sql", $"{string.Join("\r\n/\r\n", sqls)}");
            sqls.Clear();

            var seqs = GetAllSequenceSql(dbAddr, user, pwd, serviceName);
            foreach (var item in seqs)
            {
                var sql = item.Value.Replace("\"", "").Replace($"{user}.", "").Trim().Trim("\n".ToCharArray());
                sqls.Add($"-- ----------------------------\r\n-- 创建序列{item.Key}\r\n-- ----------------------------\r\n{sql}");
            }
            File.WriteAllText("Backup\\sequences.sql", $"{string.Join("\r\n/\r\n", sqls)}");
            sqls.Clear();

            var triggers = GetAllTriggerSql(dbAddr, user, pwd, serviceName);
            foreach (var item in triggers)
            {
                var sql = item.Value.Replace("\"", "").Replace($"{user}.", "")
                    .Replace($"ALTER TRIGGER {item.Key} ENABLE", "").Trim().Trim("\n".ToCharArray());
                sqls.Add($"-- ----------------------------\r\n-- 创建触发器{item.Key}\r\n-- ----------------------------\r\n{sql}");
            }
            File.WriteAllText("Backup\\triggers.sql", $"{string.Join("\r\n/\r\n", sqls)}");
            sqls.Clear();

            var indexs = GetAllIndexSql(dbAddr, user, pwd, serviceName);
            foreach (var item in indexs)
            {
                var sql = item.Value.Replace("\"", "").Replace($"{user}.", "").Trim().Trim("\n".ToCharArray());
                sqls.Add($"-- ----------------------------\r\n-- 创建索引{item.Key}\r\n-- ----------------------------\r\n{sql}");
            }
            File.WriteAllText("Backup\\indexes.sql", $"{string.Join("\r\n/\r\n", sqls)}");
            sqls.Clear();

            foreach (var item in tables)
            {
                var pkColumn = GetPkColumn(item, dbAddr, user, pwd, serviceName);
                if (!string.IsNullOrWhiteSpace(pkColumn))
                {
                    var pkMax = GetMaxPk(item, pkColumn, dbAddr, user, pwd, serviceName);
                    if (string.IsNullOrWhiteSpace(pkMax))
                        pkMax = "0";
                    File.AppendAllText("Backup\\MaxInfo.txt", $"{item} {pkColumn} {pkMax}\r\n");
                }
                var sql = $"select * from {item}";
                var dt = OracleHelper.ExecuteDataTable(connString, sql);
                if (dt.Rows.Count > 0)
                {
                    string file = $"Backup\\{item}.dat";
                    using (BinaryWriter br = new BinaryWriter(new FileStream(file, FileMode.Create), Encoding.UTF8))
                    {
                        Dictionary<int, Type> dic = new Dictionary<int, Type>();
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            dic.Add(i, dt.Columns[i].DataType);
                        }
                        br.Write(dt.Columns.Count);
                        foreach (DataRow dr in dt.Rows)
                        {
                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                if (dr[i] == DBNull.Value)
                                {
                                    br.Write((byte)100);
                                }
                                else if (dic[i] == typeof(string))
                                {
                                    br.Write((byte)0);
                                    br.Write(dr[i].ToString());
                                }
                                else if (dic[i] == typeof(DateTime))
                                {
                                    br.Write((byte)1);
                                    br.Write(((DateTime)dr[i]).ToBinary());
                                }
                                else if (dic[i] == typeof(long))
                                {
                                    br.Write((byte)2);
                                    br.Write((long)dr[i]);
                                }
                                else if (dic[i] == typeof(int))
                                {
                                    br.Write((byte)3);
                                    br.Write((int)dr[i]);
                                }
                                else if (dic[i] == typeof(short))
                                {
                                    br.Write((byte)4);
                                    br.Write((short)dr[i]);
                                }
                                else if (dic[i] == typeof(byte))
                                {

                                    br.Write((byte)5);
                                    br.Write((byte)dr[i]);

                                }
                                else if (dic[i] == typeof(decimal))
                                {
                                    br.Write((byte)6);
                                    br.Write(Convert.ToDecimal(dr[i]));
                                }
                                else if (dic[i] == typeof(float))
                                {
                                    br.Write((byte)7);
                                    br.Write((float)dr[i]);
                                }
                                else if (dic[i] == typeof(double))
                                {
                                    br.Write((byte)8);
                                    br.Write((double)dr[i]);
                                }
                                else if (dic[i] == typeof(byte[]))
                                {
                                    br.Write((byte)9);
                                    var data = (byte[])dr[i];
                                    br.Write(data.Length);
                                    br.Write(data);
                                }
                                else
                                {
                                    throw new Exception($"不支持类型{dic[i]}的转换");
                                }

                            }
                        }
                    }
                }
            }

            string zipDir = dir.FullName;
            string zipFile = Path.Combine(zipDir, $"all-{DateTime.Now:yyyyMMddHHmmss}.7z");
            SevenZipUtil.ZipLargeFiles(zipDir, zipFile, new string[] { "*.sql", "*.dat", "*.txt" }, true);
            return zipFile;
        }

        /// <summary>
        /// 增量备份(返回一个7z压缩文件)
        /// </summary>
        /// <param name="backup7zFile">全量备份的文件</param>
        /// <param name="dbAddr">oracle地址(127.0.0.1:1521</param>
        /// <param name="user">用户名(不区分大小写)</param>
        /// <param name="pwd">密码</param>
        /// <param name="serviceName">服务名称(orcl)</param>
        /// <param name="tables">>要备份的表名(默认备份所有表)</param>
        public static string BackupIncrementDb(string backup7zFile, string dbAddr, string user, string pwd, string serviceName = "orcl", List<string> tables = null)
        {
            string prefix = Path.GetFileNameWithoutExtension(backup7zFile);
            FileInfo f = new FileInfo(backup7zFile);
            string dirString = f.DirectoryName;
            user = user.ToUpper();
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";

            if (tables == null || tables.Count == 0)
                tables = GetAllTable(dbAddr, user, pwd, serviceName);
            DirectoryInfo dir = new DirectoryInfo(dirString);
            var delFiles = dir.GetFiles();
            foreach (var item in delFiles)
            {
                if (!item.Name.ToUpper().EndsWith(".SQL") && !item.Name.ToUpper().EndsWith(".DAT") && !item.Name.ToUpper().EndsWith(".TXT"))
                    continue;
                item.Delete();
            }
            //获取全量备份的文件和其相关的增量备份文件 fileName like [all-20240926104545] or [all-20240926104545-Increment-20240926150000]
            var fileNames = Directory.GetFiles(dirString, "*.7z").Where(p => Path.GetFileNameWithoutExtension(p).StartsWith(prefix)).Select(p => Path.GetFileNameWithoutExtension(p));
            //获取最后一个备份的文件
            var maxFileName = fileNames.OrderByDescending(p =>
            {
                var idx = p.LastIndexOf("-");
                var time = DateTime.ParseExact(p.Substring(idx + 1, 14), "yyyyMMddHHmmss", null);
                return time;
            }).FirstOrDefault();
            var maxFile = Path.Combine(dirString, maxFileName + ".7z");
            SevenZipUtil.UnZipFile(maxFile, "*.txt");
            string maxInfoFile = Path.Combine(dirString, maxFileName.Contains("Increment") ? "MaxInfo-Increment.txt" : "MaxInfo.txt");
            bool hasNew = false;
            if (File.Exists(maxInfoFile))
            {
                var lines = File.ReadAllLines(maxInfoFile);
                File.WriteAllText(maxInfoFile, "");
                foreach (var item in lines)
                {
                    var arr = item.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                    var tbName = arr[0];
                    var colName = arr[1];
                    var maxPk = arr[2];
                    string sql = $"select * from {tbName} where {colName}>{maxPk}";
                    var dt = OracleHelper.ExecuteDataTable(connString, sql);
                    if (dt.Rows.Count > 0)
                    {
                        hasNew = true;
                        sql = $"select max({colName}) from {tbName}";
                        var maxPkNew = OracleHelper.ExecuteScalar(connString, sql).ToString();
                        File.AppendAllText(Path.Combine(dirString, "MaxInfo-Increment.txt"), $"{tbName} {colName} {maxPkNew}\r\n");
                        string file = Path.Combine(dirString, $"{tbName}-Increment.dat");
                        using (BinaryWriter br = new BinaryWriter(new FileStream(file, FileMode.Create), Encoding.UTF8))
                        {
                            Dictionary<int, Type> dic = new Dictionary<int, Type>();
                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                dic.Add(i, dt.Columns[i].DataType);
                            }
                            br.Write(dt.Columns.Count);
                            foreach (DataRow dr in dt.Rows)
                            {
                                for (int i = 0; i < dt.Columns.Count; i++)
                                {
                                    if (dr[i] == DBNull.Value)
                                    {
                                        br.Write((byte)100);
                                    }
                                    else if (dic[i] == typeof(string))
                                    {
                                        br.Write((byte)0);
                                        br.Write(dr[i].ToString());
                                    }
                                    else if (dic[i] == typeof(DateTime))
                                    {
                                        br.Write((byte)1);
                                        br.Write(((DateTime)dr[i]).ToBinary());
                                    }
                                    else if (dic[i] == typeof(long))
                                    {
                                        br.Write((byte)2);
                                        br.Write((long)dr[i]);
                                    }
                                    else if (dic[i] == typeof(int))
                                    {
                                        br.Write((byte)3);
                                        br.Write((int)dr[i]);
                                    }
                                    else if (dic[i] == typeof(short))
                                    {
                                        br.Write((byte)4);
                                        br.Write((short)dr[i]);
                                    }
                                    else if (dic[i] == typeof(byte))
                                    {

                                        br.Write((byte)5);
                                        br.Write((byte)dr[i]);

                                    }
                                    else if (dic[i] == typeof(decimal))
                                    {
                                        br.Write((byte)6);
                                        br.Write(Convert.ToDecimal(dr[i]));
                                    }
                                    else if (dic[i] == typeof(float))
                                    {
                                        br.Write((byte)7);
                                        br.Write((float)dr[i]);
                                    }
                                    else if (dic[i] == typeof(double))
                                    {
                                        br.Write((byte)8);
                                        br.Write((double)dr[i]);
                                    }
                                    else if (dic[i] == typeof(byte[]))
                                    {
                                        br.Write((byte)9);
                                        var data = (byte[])dr[i];
                                        br.Write(data.Length);
                                        br.Write(data);
                                    }
                                    else
                                    {
                                        throw new Exception($"不支持类型{dic[i]}的转换");
                                    }

                                }
                            }
                        }
                    }
                    else
                    {
                        File.AppendAllText(Path.Combine(dirString, "MaxInfo-Increment.txt"), $"{tbName} {colName} {maxPk}\r\n");
                    }
                }
            }
            string zipFile = null;
            if (hasNew)
            {
                zipFile = Path.Combine(dirString, $"{prefix}-Increment-{DateTime.Now:yyyyMMddHHmmss}.7z");
                SevenZipUtil.ZipLargeFiles(dirString, zipFile, new string[] { "*Increment.dat", "*Increment.txt" }, true);
            }
            dir = new DirectoryInfo(dirString);
            delFiles = dir.GetFiles();
            foreach (var item in delFiles)
            {
                if (!item.Name.ToUpper().EndsWith(".SQL") && !item.Name.ToUpper().EndsWith(".DAT") && !item.Name.ToUpper().EndsWith(".TXT"))
                    continue;
                item.Delete();
            }
            return zipFile;
        }

        /// <summary>
        /// 获取用户下所有表
        /// </summary>
        /// <param name="dbAddr">数据库地址(127.0.0.1:1521)</param>
        /// <param name="user">用户名(不区分大小写)</param>
        /// <param name="pwd">密码</param>
        /// <param name="serviceName">服务名称(orcl)</param>
        /// <returns></returns>
        public static List<string> GetAllTable(string dbAddr, string user, string pwd, string serviceName = "orcl")
        {
            user = user.ToUpper();
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            var table = OracleHelper.ExecuteDataTable(connString, $"select table_name from all_tables where owner = '{user}'");
            var list = table.TableToListBcl<string>();
            return list;
        }

        /// <summary>
        /// 从全量备份压缩文件中还原数据库
        /// </summary>
        /// <param name="dbZipFile"></param>
        /// <param name="dbAddr"></param>
        /// <param name="user"></param>
        /// <param name="pwd"></param>
        /// <param name="sysPwd"></param>
        /// <param name="serviceName"></param>
        public static void RestoreDb(string dbZipFile, string dbAddr, string user, string pwd, string sysPwd, string serviceName = "orcl")
        {
            try
            {
                FileInfo f = new FileInfo(dbZipFile);
                dbZipFile = f.FullName;
                user = user.ToUpper();
                ClearUserObj(dbAddr, user, sysPwd, serviceName);
                CreateUserIfNotExit(dbAddr, user, pwd, sysPwd, serviceName);
                var dir = SevenZipUtil.UnZipFile(dbZipFile);
                string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
                var sequences = File.ReadAllText(Path.Combine(dir, "sequences.sql"));
                var triggers = File.ReadAllText(Path.Combine(dir, "triggers.sql"));
                var tables = File.ReadAllText(Path.Combine(dir, "tables.sql"));
                var indexes = File.ReadAllText(Path.Combine(dir, "indexes.sql"));
                var seqsDic = GetAllSequenceSql(dbAddr, user, pwd, serviceName);
                var tablesDic = GetAllTableSql(dbAddr, user, pwd, serviceName);
                var triggersDic = GetAllTriggerSql(dbAddr, user, pwd, serviceName);
                var indexesDic = GetAllIndexSql(dbAddr, user, pwd, serviceName);
                foreach (var item in sequences.Split("/".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
                {
                    string name = FindTextName(item, "-- 创建序列");
                    if (seqsDic.ContainsKey(name)) continue;
                    OracleHelper.ExecuteNonQuery(connString, item);
                }
                foreach (var item in tables.Split("/".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
                {
                    string name = FindTextName(item, "-- 创建表");
                    if (tablesDic.ContainsKey(name)) continue;
                    OracleHelper.ExecuteNonQuery(connString, item);
                }
                foreach (var item in triggers.Split("/".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
                {
                    string name = FindTextName(item, "-- 创建触发器");
                    if (triggersDic.ContainsKey(name)) continue;
                    OracleHelper.ExecuteNonQuery(connString, item);
                }
                foreach (var item in indexes.Split("/".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
                {
                    //唯一索引在创建表时会自动创建,这里只需要创建普通索引
                    string name = FindTextName(item, "-- 创建索引");
                    if (indexesDic.ContainsKey(name)) continue;
                    OracleHelper.ExecuteNonQuery(connString, item);
                }
                DisableAllFk(dbAddr, user, pwd, serviceName);//导入数据前禁用所有外键约束
                DisableAllTrigger(dbAddr, user, pwd, serviceName);//导入数据前禁用所有触发器
                var datFiles = Directory.GetFiles(dir, "*.dat");
                foreach (var item in datFiles)
                {
                    var con = new OracleConnection(connString);
                    con.Open();
                    var trans = con.BeginTransaction();
                    try
                    {
                        string tableName = Path.GetFileNameWithoutExtension(item);
                        using (BinaryReader br = new BinaryReader(new FileStream(item, FileMode.Open), Encoding.UTF8))
                        {
                            if (br.BaseStream.Position >= br.BaseStream.Length) continue;
                            int columnCount = br.ReadInt32();

                            while (br.BaseStream.Position < br.BaseStream.Length)
                            {
                                StringBuilder sb = new StringBuilder();
                                sb.Append($"insert into {tableName} values(");
                                OracleParameter[] parameters = new OracleParameter[columnCount];
                                for (int i = 0; i < columnCount; i++)
                                {
                                    parameters[i] = new OracleParameter();
                                    parameters[i].ParameterName = $"val{i}";
                                    if (i != 0)
                                        sb.Append(",");
                                    sb.Append($":val{i}");
                                    var colType = br.ReadByte();
                                    if (colType == 100)//DBNull.Value
                                    {
                                        parameters[i].Value = null;
                                    }
                                    if (colType == 0)//string
                                    {
                                        var val = br.ReadString();
                                        parameters[i].Value = val;
                                    }
                                    else if (colType == 1)//DateTime
                                    {
                                        var val = br.ReadInt64();
                                        var time = DateTime.FromBinary(val);
                                        parameters[i].Value = time;
                                    }
                                    else if (colType == 2)//long
                                    {
                                        var val = br.ReadInt64();
                                        parameters[i].Value = val;
                                    }
                                    else if (colType == 3)//int
                                    {
                                        var val = br.ReadInt32();
                                        parameters[i].Value = val;
                                    }
                                    else if (colType == 4)//short
                                    {
                                        var val = br.ReadInt16();
                                        parameters[i].Value = val;
                                    }
                                    else if (colType == 5)//byte
                                    {
                                        var val = br.ReadByte();
                                        parameters[i].Value = val;
                                    }
                                    else if (colType == 6)//decimal
                                    {
                                        var val = br.ReadDecimal();
                                        parameters[i].Value = val;
                                    }
                                    else if (colType == 7)//float
                                    {
                                        var val = br.ReadSingle();
                                        parameters[i].Value = val;
                                    }
                                    else if (colType == 8)//double
                                    {
                                        var val = br.ReadDouble();
                                        parameters[i].Value = val;
                                    }
                                    else if (colType == 9)//byte[]
                                    {
                                        var len = br.ReadInt32();
                                        var val = br.ReadBytes(len);
                                        parameters[i].Value = val;
                                    }
                                }
                                sb.Append(")");
                                var insertSql = sb.ToString();
                                OracleHelper.BatchInsert(con, trans, insertSql, parameters);
                                //OracleHelper.ExecuteNonQuery(connString, insertSql, parameters);
                            }
                        }
                        trans.Commit();
                        con.Dispose();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw;
                    }
                }
                EnableAllFk(dbAddr, user, pwd, serviceName);//导入数据后启用所有外键约束
                EnableAllTrigger(dbAddr, user, pwd, serviceName);//导入数据后启用所有触发器
                var delFile = Directory.GetFiles(dir);
                foreach (var item in delFile)
                {
                    if (item.ToUpper().EndsWith(".DAT") || item.ToUpper().EndsWith(".SQL") || item.ToUpper().EndsWith(".TXT"))
                    {
                        File.Delete(item);
                    }
                }
            }
            finally
            {
                OracleConnection.ClearAllPools();
            }
        }

        /// <summary>
        /// 从增量备份压缩文件中恢复数据
        /// </summary>
        /// <param name="dbZipFile"></param>
        /// <param name="dbAddr"></param>
        /// <param name="user"></param>
        /// <param name="pwd"></param>
        /// <param name="sysPwd"></param>
        /// <param name="serviceName"></param>
        public static void RestoreIncrementDb(string dbZipFile, string dbAddr, string user, string pwd, string sysPwd, string serviceName = "orcl")
        {
            user = user.ToUpper();
            FileInfo file = new FileInfo(dbZipFile);
            dbZipFile = file.FullName;
            var dir = SevenZipUtil.UnZipFile(dbZipFile);
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            DisableAllFk(dbAddr, user, pwd, serviceName);//导入数据前禁用所有外键约束
            DisableAllTrigger(dbAddr, user, pwd, serviceName);//导入数据前禁用所有触发器
            var datFiles = Directory.GetFiles(dir, "*.dat");
            var maxInfo = Path.Combine(dir, "MaxInfo-Increment.txt");
            var maxArr = File.ReadAllLines(maxInfo);
            List<(string, string, string)> list = new List<(string, string, string)>();
            foreach (var item in maxArr)
            {
                var arr = item.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                var tbName = arr[0];
                var colName = arr[1];
                var maxPk = arr[2];
                list.Add((tbName, colName, maxPk));
            }
            foreach (var item in datFiles)
            {
                string tableName = Path.GetFileNameWithoutExtension(item).Replace("-Increment", "");
                var tupItem = list.First(p => p.Item1 == tableName);
                string maxSql = $"select max({tupItem.Item2}) from {tupItem.Item1}";
                var maxObj = OracleHelper.ExecuteScalar(connString, maxSql);
                if (maxObj != DBNull.Value && Convert.ToInt64(maxObj) >= Convert.ToInt64(tupItem.Item3))
                    continue;//数据已存在,不要重复导入

                var con = new OracleConnection(connString);
                con.Open();
                var trans = con.BeginTransaction();
                try
                {
                    using (BinaryReader br = new BinaryReader(new FileStream(item, FileMode.Open), Encoding.UTF8))
                    {
                        if (br.BaseStream.Position >= br.BaseStream.Length) continue;
                        int columnCount = br.ReadInt32();

                        while (br.BaseStream.Position < br.BaseStream.Length)
                        {
                            StringBuilder sb = new StringBuilder();
                            sb.Append($"insert into {tableName} values(");
                            OracleParameter[] parameters = new OracleParameter[columnCount];
                            for (int i = 0; i < columnCount; i++)
                            {
                                parameters[i] = new OracleParameter();
                                parameters[i].ParameterName = $"val{i}";
                                if (i != 0)
                                    sb.Append(",");
                                sb.Append($":val{i}");
                                var colType = br.ReadByte();
                                if (colType == 100)//DBNull.Value
                                {
                                    parameters[i].Value = null;
                                }
                                if (colType == 0)//string
                                {
                                    var val = br.ReadString();
                                    parameters[i].Value = val;
                                }
                                else if (colType == 1)//DateTime
                                {
                                    var val = br.ReadInt64();
                                    var time = DateTime.FromBinary(val);
                                    parameters[i].Value = time;
                                }
                                else if (colType == 2)//long
                                {
                                    var val = br.ReadInt64();
                                    parameters[i].Value = val;
                                }
                                else if (colType == 3)//int
                                {
                                    var val = br.ReadInt32();
                                    parameters[i].Value = val;
                                }
                                else if (colType == 4)//short
                                {
                                    var val = br.ReadInt16();
                                    parameters[i].Value = val;
                                }
                                else if (colType == 5)//byte
                                {
                                    var val = br.ReadByte();
                                    parameters[i].Value = val;
                                }
                                else if (colType == 6)//decimal
                                {
                                    var val = br.ReadDecimal();
                                    parameters[i].Value = val;
                                }
                                else if (colType == 7)//float
                                {
                                    var val = br.ReadSingle();
                                    parameters[i].Value = val;
                                }
                                else if (colType == 8)//double
                                {
                                    var val = br.ReadDouble();
                                    parameters[i].Value = val;
                                }
                                else if (colType == 9)//byte[]
                                {
                                    var len = br.ReadInt32();
                                    var val = br.ReadBytes(len);
                                    parameters[i].Value = val;
                                }
                            }
                            sb.Append(")");
                            var insertSql = sb.ToString();
                            OracleHelper.BatchInsert(con, trans, insertSql, parameters);
                            //OracleHelper.ExecuteNonQuery(connString, insertSql, parameters);
                        }
                    }
                    trans.Commit();
                    con.Dispose();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
            }
            EnableAllFk(dbAddr, user, pwd, serviceName);//导入数据后启用所有外键约束
            EnableAllTrigger(dbAddr, user, pwd, serviceName);//导入数据后启用所有触发器
            var delFile = Directory.GetFiles(dir);
            foreach (var item in delFile)
            {
                if (item.ToUpper().EndsWith(".DAT") || item.ToUpper().EndsWith(".SQL") || item.ToUpper().EndsWith(".TXT"))
                {
                    File.Delete(item);
                }
            }

        }
        #endregion

        #region 数据库连接测试
        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <param name="oracleAddr">oracle地址(127.0.0.1:1521)</param>
        /// <param name="user">用户名(不区分大小写)</param>
        /// <param name="pwd">密码</param>
        /// <param name="serviceName">服务名称(orcl)</param>
        public static void TestConnection(string dbAddr, string user, string pwd, string serviceName = "orcl")
        {
            Directory.CreateDirectory("Backup");
            user = user.ToUpper();
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            OracleHelper.TestConnection(connString);
        }

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <param name="oracleAddr">oracle地址(127.0.0.1:1521)</param>
        /// <param name="sysPwd">SYS账户密码</param>
        /// <param name="serviceName">服务名称(orcl)</param>
        public static void TestSysConnection(string dbAddr, string sysPwd, string serviceName = "orcl")
        {
            Directory.CreateDirectory("Backup");
            string connString = $"Data Source={dbAddr}/orcl; User Id=SYS; Password={sysPwd};DBA Privilege=SYSDBA;";
            OracleHelper.TestConnection(connString);
        }
        #endregion

        #region private function
        //创建用户并授权
        private static void CreateUserIfNotExit(string dbAddr, string user, string pwd, string sysPwd, string serviceName = "orcl")
        {
            user = user.ToUpper();
            string connString = $"Data Source={dbAddr}/orcl; User Id=SYS; Password={sysPwd};DBA Privilege=SYSDBA;";

            //1.创建用户
            string sql = "SELECT username FROM dba_users WHERE username = :username";
            var userObj = OracleHelper.ExecuteScalar(connString, sql,
                new OracleParameter("username", user));
            if (userObj == null)
            {
                sql = $"create user {user} identified by {pwd}  default tablespace USERS  temporary tablespace TEMP  profile DEFAULT  password expire";
                OracleHelper.ExecuteNonQuery(connString, sql);
            }

            //2.给用户权限
            sql = $"grant dba to {user}";
            OracleHelper.ExecuteNonQuery(connString, sql);
            sql = $"grant create session to {user}";
            OracleHelper.ExecuteNonQuery(connString, sql);
            sql = $"grant unlimited tablespace to {user}";
            OracleHelper.ExecuteNonQuery(connString, sql);

            //新用户需要修改一次密码才能登录
            sql = $"alter user {user} identified by {pwd} account unlock";
            OracleHelper.ExecuteNonQuery(connString, sql);

        }

        //获取用户下所有表创建SQL
        private static Dictionary<string, string> GetAllTableSql(string dbAddr, string user, string pwd, string serviceName = "orcl")
        {
            user = user.ToUpper();
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            var table = OracleHelper.ExecuteDataTable(connString, $"select table_name,DBMS_METADATA.GET_DDL('TABLE',table_name) FROM all_tables where owner='{user}'");
            return table.TableToDictionary();
        }

        //获取用户下所有序列创建SQL
        private static Dictionary<string, string> GetAllSequenceSql(string dbAddr, string user, string pwd, string serviceName = "orcl")
        {
            user = user.ToUpper();
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            var table = OracleHelper.ExecuteDataTable(connString, $"select sequence_name,DBMS_METADATA.GET_DDL('SEQUENCE',sequence_name) FROM all_sequences where sequence_owner='{user}'");
            return table.TableToDictionary();
        }

        //获取用户下所有触发器创建SQL
        private static Dictionary<string, string> GetAllTriggerSql(string dbAddr, string user, string pwd, string serviceName = "orcl")
        {
            user = user.ToUpper();
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            var table = OracleHelper.ExecuteDataTable(connString, $"select trigger_name,DBMS_METADATA.GET_DDL('TRIGGER',trigger_name) FROM all_triggers where owner='{user}'");
            return table.TableToDictionary();
        }

        //获取用户下所有索引创建SQL(不含唯一索引)
        private static Dictionary<string, string> GetAllIndexSql(string dbAddr, string user, string pwd, string serviceName = "orcl")
        {
            user = user.ToUpper();
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            string sql = $"select index_name,DBMS_METADATA.GET_DDL('INDEX', index_name ) FROM all_indexes WHERE owner = '{user}' AND index_name IN( SELECT user_ind_columns.index_name FROM user_ind_columns, user_indexes WHERE user_ind_columns.index_name = user_indexes.index_name AND user_indexes.uniqueness = 'NONUNIQUE' )";
            var table = OracleHelper.ExecuteDataTable(connString, sql);
            return table.TableToDictionary();
        }

        //启用据库所有外键约束
        private static void EnableAllFk(string dbAddr, string user, string pwd, string serviceName = "orcl")
        {
            user = user.ToUpper();
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            string sql = "select 'alter table '||table_name||' enable constraint '||constraint_name from user_constraints where constraint_type='R'";
            var sqls = OracleHelper.ExecuteDataTable(connString, sql).TableToListBcl<string>();
            sql = string.Join("/", sqls);
            OracleHelper.ExecuteNonQuery(connString, sql);
        }

        //禁用据库所有外键约束
        private static void DisableAllFk(string dbAddr, string user, string pwd, string serviceName = "orcl")
        {
            user = user.ToUpper();
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            string sql = "select 'alter table '||table_name||' disable constraint '||constraint_name from user_constraints where constraint_type='R'";
            var sqls = OracleHelper.ExecuteDataTable(connString, sql).TableToListBcl<string>();
            sql = string.Join("/", sqls);
            OracleHelper.ExecuteNonQuery(connString, sql);
        }

        //启用据库所有触发器
        private static void EnableAllTrigger(string dbAddr, string user, string pwd, string serviceName = "orcl")
        {
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            var triggers = GetAllTriggerSql(dbAddr, user, pwd, serviceName).Keys.ToList();

            String sql = String.Empty;
            foreach (var t in triggers)
            {
                sql += $"alter trigger {t} enable/";
            }
            OracleHelper.ExecuteNonQuery(connString, sql);
        }

        //禁用据库所有触发器
        private static void DisableAllTrigger(string dbAddr, string user, string pwd, string serviceName = "orcl")
        {
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            var triggers = GetAllTriggerSql(dbAddr, user, pwd, serviceName).Keys.ToList();
            String sql = String.Empty;
            foreach (var t in triggers)
            {
                sql += $"alter trigger {t} disable/";
            }
            OracleHelper.ExecuteNonQuery(connString, sql);
        }
        //删除用户信息
        private static void ClearUserObj(string dbAddr, string deluser, string sysPwd, string serviceName = "orcl")
        {
            string connString = $"Data Source={dbAddr}/orcl; User Id=SYS; Password={sysPwd};DBA Privilege=SYSDBA;";

            //1.创建用户
            string sql = "SELECT username FROM dba_users WHERE username = :username";
            var userObj = OracleHelper.ExecuteScalar(connString, sql,
              new OracleParameter("username", deluser));
            if (userObj != null)
            {
                OracleHelper.ExecuteNonQuery(connString, $"drop user {deluser} cascade");
            }
        }
        //获取表的主键列名(没有主键返回null)
        private static string GetPkColumn(string tableName, string dbAddr, string user, string pwd, string serviceName = "orcl")
        {
            user = user.ToUpper();
            tableName = tableName.ToUpper();
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            string sql = $"select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = '{tableName}' AND au.OWNER='{user}'";
            var columns = OracleHelper.ExecuteDataTable(connString, sql).TableToListBcl<string>();
            return columns.FirstOrDefault();
        }

        private static string GetMaxPk(string tableName, string pkColumn, string dbAddr, string user, string pwd, string serviceName = "orcl")
        {
            user = user.ToUpper();
            tableName = tableName.ToUpper();
            string connString = $"Data Source={dbAddr}/{serviceName}; User Id={user}; Password={pwd};";
            string sql = $"select max({pkColumn}) from {tableName}";
            var valObj = OracleHelper.ExecuteScalar(connString, sql);
            var maxPk = valObj.ToString();
            return maxPk;
        }

        private static string FindTextName(string text, string filter)
        {
            var lines = text.Split("\r\n".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
            foreach (var line in lines)
            {
                if (line.StartsWith(filter))
                {
                    return line.Substring(filter.Length).ToUpper();
                }
            }
            return null;
        }
        #endregion
    }
}