【优化】C#利用ODP.NET往oracle中高效插入百万数据

1.将字典Dictionary<string, object[]>  改为Dictionary<string, object>，如果是object[]  在调用时某些转换不了

2.将OracleDbType类型判断改为数组型

    public class BatchHelper
    {
        private static readonly string ConnString = System.Configuration.ConfigurationManager.ConnectionStrings["MDBSConnString"].ConnectionString;

        /// <summary>
        ///   批量插入数据
        /// </summary>
        /// <param name="tableName"> 表名称 </param>
        /// <param name="columnRowData"> 键-值存储的批量数据：键是列名称，值是该列对应的数据集合 </param>
        /// <returns></returns>
        public static int BatchInsert(string tableName, Dictionary<string, object> columnRowData, int length)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("tableName", "必须指定批量插入的表名称");
            }

            if (columnRowData == null || columnRowData.Count < 1)
            {
                throw new ArgumentException("必须指定批量插入的字段名称", "columnRowData");
            }

            int iResult = 0;
            string[] dbColumns = columnRowData.Keys.ToArray();
            StringBuilder sbCmdText = new StringBuilder();
            if (columnRowData.Count > 0)
            {
                // 准备插入SQL
                sbCmdText.AppendFormat("INSERT INTO {0} (", tableName);
                sbCmdText.Append(string.Join(",", dbColumns));
                sbCmdText.Append(") VALUES (");
                sbCmdText.Append(":" + string.Join(", :", dbColumns));
                sbCmdText.Append(") ");

                using (OracleConnection conn = new OracleConnection(ConnString))
                {
                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();

                        // 绑定批处理的行数
                        cmd.ArrayBindCount = length; //columnRowData.Values.First().Length; // 很重要
                        cmd.BindByName = true;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sbCmdText.ToString();
                        cmd.CommandTimeout = 600; // 10分钟
                        // 创建参数
                        OracleParameter oraParam;
                        OracleDbType dbType = OracleDbType.Object;
                        foreach (string colName in dbColumns)
                        {
                            dbType = GetOracleDbType(columnRowData[colName]);
                            oraParam = new OracleParameter(colName, dbType);
                            oraParam.Direction = ParameterDirection.Input;
                            oraParam.OracleDbTypeEx = dbType;

                            oraParam.Value = columnRowData[colName];
                            cmd.Parameters.Add(oraParam);
                        }

                        // 执行批处理
                        var trans = conn.BeginTransaction();
                        try
                        {
                            cmd.Transaction = trans;
                            iResult = cmd.ExecuteNonQuery();
                            trans.Commit();
                        }
                        catch (Exception dbex)
                        {
                            trans.Rollback();
                            throw dbex;
                        }
                    }
                }
            }

            return iResult;
        }


        ///   批量更新数据
        /// </summary>
        /// <param name="tableName"> 表名称 </param>
        /// <param name="keyColumName"> 主键列名称 </param>
        /// <param name="columnRowData"> 键-值存储的批量数据：键是列名称，值是该列对应的数据集合 </param>
        /// <returns></returns>
        public static int BatchUpdate(string tableName, string keyColumName, Dictionary<string, object[]> columnRowData)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("tableName", "必须指定批量插入的表名称");
            }

            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("keyColumName", "必须指定批量插入表的主键列名称");
            }

            if (columnRowData == null || columnRowData.Count < 1)
            {
                throw new ArgumentException("必须指定批量插入的字段名称", "columnRowData");
            }

            int iResult = 0;
            string[] dbColumns = columnRowData.Keys.ToArray();
            StringBuilder sbCmdText = new StringBuilder();
            if (columnRowData.Count > 0)
            {
                // 准备更新SQL
                sbCmdText.AppendFormat("update {0} set  ", tableName);
                foreach (var col in dbColumns)
                {
                    if (keyColumName.Equals(col, StringComparison.OrdinalIgnoreCase))
                    {
                        continue;
                    }
                    sbCmdText.AppendFormat("{0} = :{0} ,", col);
                }
                sbCmdText.Remove(sbCmdText.Length - 1, 1);
                sbCmdText.AppendFormat(" where {0} = :{0}", keyColumName);

                using (OracleConnection conn = new OracleConnection(ConnString))
                {
                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();

                        // 绑定批处理的行数
                        cmd.ArrayBindCount = columnRowData.Values.First().Length; // 很重要
                        cmd.BindByName = true;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sbCmdText.ToString();
                        cmd.CommandTimeout = 600; // 10分钟
                        // 创建参数
                        OracleParameter oraParam;
                        List<IDbDataParameter> cacher = new List<IDbDataParameter>();
                        OracleDbType dbType = OracleDbType.Object;
                        foreach (string colName in dbColumns)
                        {
                            dbType = GetOracleDbType(columnRowData[colName][0]);
                            oraParam = new OracleParameter(colName, dbType);
                            oraParam.Direction = ParameterDirection.Input;
                            oraParam.OracleDbTypeEx = dbType;

                            oraParam.Value = columnRowData[colName];
                            cmd.Parameters.Add(oraParam);
                        }

                        // 执行批处理
                        var trans = conn.BeginTransaction();
                        try
                        {
                            cmd.Transaction = trans;
                            iResult = cmd.ExecuteNonQuery();
                            trans.Commit();
                        }
                        catch (Exception dbex)
                        {
                            trans.Rollback();
                            throw dbex;
                        }
                    }
                }
            }

            return iResult;
        }


        /// <summary>
        ///   根据数据类型获取OracleDbType
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        private static OracleDbType GetOracleDbType(object value)
        {
            OracleDbType dataType = OracleDbType.Object;
            if (value is string[])
            {
                dataType = OracleDbType.Varchar2;
            }
            else if (value is DateTime[])
            {
                dataType = OracleDbType.TimeStamp;
            }
            else if (value is int[] || value is short[])
            {
                dataType = OracleDbType.Int32;
            }
            else if (value is long[])
            {
                dataType = OracleDbType.Int64;
            }
            else if (value is decimal[] || value is double[])
            {
                dataType = OracleDbType.Decimal;
            }
            else if (value is Guid[])
            {
                dataType = OracleDbType.Varchar2;
            }
            else if (value is bool[] || value is Boolean[])
            {
                dataType = OracleDbType.Byte;
            }
            else if (value is byte[])
            {
                dataType = OracleDbType.Blob;
            }
            else if (value is char[])
            {
                dataType = OracleDbType.Char;
            }

            return dataType;
        }

    }