oracle怎么批量插入datatable或者list

[-一个大坑]

需要写一个搬迁数据的job，目前用的OracleBulkCopy，但是表有触发器就不能用了

[threenewbee]

是的，你需要先禁用触发器这些，才能 bulkcopy

[-一个大坑]

引用 1 楼 贵阳老马马善福专业维修游泳池堵漏防水工程 的回复:

是的，你需要先禁用触发器这些，才能 bulkcopy

先禁用触发器再插入不合适，肯定搬迁的数据会不对，只能找别的办法了

[-一个大坑]

我在网上找的插入的方法，但是会提示 无法将类型为“System.String”的对象强制转换为类型“System.Array”。

        public static int BatchInsert(string tableName, DataTable dt, string conStr, string[] strColumns)
        {

            int dtRowsCount = dt.Rows.Count;
            int iResult = 0;
            StringBuilder sbCmdText = new StringBuilder();
            if (dtRowsCount > 0)
            {
                //准备插入的SQL
                sbCmdText.Append("BEGIN  ");

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    sbCmdText.AppendFormat("INSERT INTO {0}(", tableName);
                    sbCmdText.Append(string.Join(",", strColumns));
                    sbCmdText.Append(") VALUES (");
                    sbCmdText.Append(":A00"+i + string.Join(",:A00" + i, strColumns));
                    sbCmdText.Append(");");
                }

                sbCmdText.Append("END;");

                using (OracleConnection conn = new OracleConnection(conStr))
                {
                    using (OracleCommand cmd = conn.CreateCommand())
                    {
                        //绑定批处理的行数
                        cmd.ArrayBindCount = dtRowsCount;
                        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;
                        for (int j = 0; j < dtRowsCount; j++)
                        {
                            for (int i = 0; i < strColumns.Count(); i++)
                            {
                                dbType = GetOracleDbType(dt.Rows[j][i]);
                                oraParam = new OracleParameter("A00" + j + strColumns[i], dbType);
                                oraParam.Direction = ParameterDirection.Input;
                                oraParam.OracleDbTypeEx = dbType;
                                oraParam.Value = dt.Rows[j][i];
                                cmd.Parameters.Add(oraParam);
                            }
                        }
                        //打开连接
                        conn.Open();

                        /*执行批处理*/
                        var trans = conn.BeginTransaction();
                        try
                        {
                            cmd.Transaction = trans;
                            iResult = cmd.ExecuteNonQuery();
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            throw ex;
                        }
                        finally
                        {
                            if (conn != null) conn.Close();
                        }

                    }
                }
            }
            return iResult;
        }

        /**
         * 根据数据类型获取OracleDbType
         */
        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 || value is float)
            {
                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;
        }

[donwmufromdying]

没有时效性要求的话，可以试试penthaho kettle