ODP方式，大批量数据写入ORACLE数据库

项目中在同步数据的时候，需要把获得的数据DataTable，写入oracle数据库

因为System.Data.OracleClient写入方式写入大批量数据特别慢，改用Oracle.DataAccess写入方式（上代码）：

ODP工具类：

需要引入命名空间：

using Oracle.DataAccess;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

ODP_Inserter
{
        /// <summary>
        /// 数据库连接串
        /// </summary>
        private string strWMSConn = string.Empty;

        public string StrWMSConn
        {
            get
            {
                if (strWMSConn == string.Empty)
                {
                    return GetConnectionString();
                }
                else
                {
                    return strWMSConn;
                }
            }
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        public OracleAccessBatcher()
        {
            //加载数据库连接串
            if (strWMSConn == string.Empty)
            {
                GetConnectionString();
            }
        }

        /// <summary>
        /// 加载数据库连接串
        /// </summary>
        private string GetConnectionString()
        {
            System.Configuration.AppSettingsReader reader = new System.Configuration.AppSettingsReader();
            strWMSConn = reader.GetValue("B2BDataBase", typeof(string)).ToString();
            return strWMSConn;
        }

        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <param name="tableName">目的表名称</param>
        /// <param name="dataTable">数据源(列名与目的表一致)</param>
        /// <returns></returns>
        public void BatchInsert(string tableName, DataTable dataTable)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("tableName", "必须指定批量插入的表名称");
            }

            if (dataTable == null || dataTable.Rows.Count < 1)
            {
                throw new ArgumentException("必须指定批量插入的数据源", "dataTable");
            }

            using (OracleConnection conn = new OracleConnection(strWMSConn))
            {
                try
                {
                    conn.Open();

                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        // 绑定批处理的行数
                        cmd.ArrayBindCount = dataTable.Rows.Count;
                        cmd.BindByName = true;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = GenerateInsertSql(cmd, tableName, dataTable);
                        cmd.CommandTimeout = 600; // 10分钟

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception exp)
                {
                    throw exp;
                }
                finally
                {
                    conn.Close();
                }
            }

        }

        /// <summary>
        /// 批量更新数据
        /// </summary>
        /// <param name="tableName">目的表名</param>
        /// <param name="keyColumns">条件列名数组(值与目的表列名一致)</param>
        /// <param name="dataTable">数据源(列名与目的表一致)</param>
        /// <returns></returns>
        public int BatchUpdate(string tableName, string[] keyColumns, DataTable dataTable)
        {
            // 检查输入
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("tableName", "必须指定批量更新的表名称");
            }

            if (keyColumns == null || keyColumns.Length == 0)
            {
                throw new ArgumentException("必须指定批量更新表的条件列数组", "keyColumns");
            }

            if (dataTable == null || dataTable.Rows.Count < 1)
            {
                throw new ArgumentException("必须指定批量更新的数据源", "dataTable");
            }

            // 无需更新
            if (keyColumns.Length >= dataTable.Columns.Count)
            {
                throw new ArgumentException("目的表不存在需要更新的列名", "keyColumns&dataTable");
            }

            // 条件列是否在表列名中
            foreach (string colName in keyColumns)
            {
                if (!dataTable.Columns.Contains(colName))
                {
                    throw new ArgumentException("用于更新条件的列名不在目的表中", "dataTable");
                }
            }

            int iResult = 0;
            using (OracleConnection conn = new OracleConnection(strWMSConn))
            {
                try
                {
                    conn.Open();

                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        // 绑定批处理的行数
                        cmd.ArrayBindCount = dataTable.Rows.Count;
                        cmd.BindByName = true;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = GenerateUpdateSql(cmd, tableName, keyColumns, dataTable);
                        cmd.CommandTimeout = 600; // 10分钟

                        iResult = cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception exp)
                {
                    throw exp;
                }
                finally
                {
                    conn.Close();
                }
            }

            return iResult;
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="tableName">目标表</param>
        /// <param name="columnName">列名(与目的表列名一致)</param>
        /// <param name="columnValue">列值</param>
        public void BatchDelete(string tableName, string columnName, string columnValue)
        {
            // 检查输入
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException("tableName", "必须指定批量更新的表名称");
            }

            if (string.IsNullOrEmpty(columnName))
            {
                throw new ArgumentNullException("columnValue", "必须指定删除条件的列名");
            }

            string strCmdText = string.Format("delete from {0} where {1} = '{2}'", tableName, columnName, columnValue);

            using (OracleConnection conn = new OracleConnection(strWMSConn))
            {
                try
                {
                    conn.Open();

                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        // 绑定批处理的行数
                        //cmd.ArrayBindCount = dataTable.Rows.Count;
                        cmd.BindByName = true;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = strCmdText;
                        cmd.CommandTimeout = 600; // 10分钟

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception exp)
                {
                    throw exp;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 生成插入数据的sql语句
        /// </summary>
        /// <param name="command">SQL命令</param>
        /// <param name="tableName">目的表名称</param>
        /// <param name="table">目的表数据</param>
        /// <returns></returns>
        private string GenerateInsertSql(OracleCommand command, string tableName, DataTable table)
        {
            int cols = table.Columns.Count;
            int rows = table.Rows.Count;

            StringBuilder names = new StringBuilder();
            StringBuilder values = new StringBuilder();

            for (int i = 0; i < cols; i++)
            {
                DataColumn column = table.Columns[i];
                OracleParameter param = new OracleParameter(column.ColumnName, this.GetOracleDbType(column.DataType));
                //OracleParameter param = new OracleParameter(column.ColumnName, OracleDbType.Varchar2);

                string[] data = new string[rows];
                for (int j = 0; j < rows; j++)
                {
                    data[j] = table.Rows[j][column.ColumnName].ToString().TrimEnd();
                }

                param.Direction = ParameterDirection.Input;
                param.Value = data;
                command.Parameters.Add(param);

                if (names.Length > 0)
                {
                    names.Append(",");
                    values.Append(",");
                }
                names.AppendFormat("{0}", column.ColumnName);
                values.AppendFormat("{0}{1}", ":", column.ColumnName);
            }
            return string.Format("INSERT INTO {0}({1}) VALUES ({2})", tableName, names, values);
        }

        /// <summary>
        /// 生成更新数据的sql语句
        /// </summary>
        /// <param name="command"></param>
        /// <param name="tableName"></param>
        /// <param name="keyColumns"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        private string GenerateUpdateSql(OracleCommand command, string tableName, string[] keyColumns, DataTable table)
        {
            int cols = table.Columns.Count;
            int rows = table.Rows.Count;

            StringBuilder sets = new StringBuilder();
            StringBuilder wheres = new StringBuilder();

            for (int i = 0; i < cols; i++)
            {
                DataColumn column = table.Columns[i];

                // 是否为条件列
                bool isCond = false;
                foreach (string cod in keyColumns)
                {
                    isCond = cod.Equals(column.ColumnName);
                    if (isCond)
                    {
                        break;
                    }
                }


                string[] data = new string[rows];
                for (int j = 0; j < rows; j++)
                {
                    data[j] = table.Rows[j][column.ColumnName].ToString().TrimEnd();
                }

                // 设定参数
                OracleParameter param;
                OracleDbType dbType = OracleDbType.Varchar2;

                dbType = this.GetOracleDbType(column.DataType);
                param = new OracleParameter(column.ColumnName, dbType);
                param.Direction = ParameterDirection.Input;
                param.Value = data;
                command.Parameters.Add(param);

                // 条件列
                if (isCond)
                {
                    if (wheres.Length > 0)
                    {
                        wheres.Append(" and ");
                    }

                    wheres.AppendFormat("{0} = :{0}", column.ColumnName);
                }
                else
                {
                    if (sets.Length > 0)
                    {
                        sets.Append(",");
                    }
                    sets.AppendFormat("{0} = :{0}", column.ColumnName);
                }
            }
            return string.Format("update {0} set {1} where {2}", tableName, sets, wheres);
        }

        /// <summary>
        ///  根据数据类型获取OracleDbType
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>        
        private OracleDbType GetOracleDbType(object value)
        {
            //OracleDbType dataType = OracleDbType.Object;
            OracleDbType dataType = OracleDbType.Varchar2;

            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;
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="strSql"></param>
        public void ExecuteSql(string strSql)
        {
            using (OracleConnection conn = new OracleConnection(strWMSConn))
            {
                try
                {
                    conn.Open();

                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        cmd.BindByName = true;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = strSql;
                        cmd.CommandTimeout = 600;

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception exp)
                {
                    throw exp;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

调用：

 ODP_Inserter batchInsert = new ODP_Inserter();

batchInsert.BatchInsert("table_name", ConvertTable);//table_name为数据库表名称，ConvertTable为要写入的DataTable

使用的时候，注意DataTable的数据类型