大数据量导入Oracle（第二版）

更新内容:

1.添加超大文本导入(原只支持DataReader)

2.支持从第x行数据开始导入

3.决定是否保存数据行号

4.列映射不区分大小写

5.支持导入常量列

6.相同源列导入多目标列只分配一次内存，更加节约内存

7.增加了源列自动检测功能

调用示例如下：

1.导入DataReader 

/// <summary>
        /// 快速导入DataReader至Oracle
        /// </summary>
        /// <param name="strConnectionString">oracle连接字符串如："Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521))(CONNECT_DATA=(SID=yyyy)));User Id=sys;Password=sys;"</param>
        /// <param name="intTimeOut">导入最大超时时间</param>
        /// <param name="dataReader">要导入的DataReader</param>
        /// <param name="strTableName">目标表名</param>
        /// <param name="columnMapping">列映射关系</param>
        /// <param name="intBatchSize">每次导入多少条后提交</param>
        /// <param name="intCommitRowCount">导入成功行数</param>
        /// <param name="spentTime">消耗时间</param>
        /// <param name="strErrMsg">错误消息</param>
        /// <returns></returns>
        public bool FastImportDataReaderToDb(string strConnectionString, int intBatchSize, int intTimeOut, IDataReader dataReader,
                                      string strTableName,
                                      Dictionary<string, string> columnMapping, out int intCommitRowCount, out long spentTime, out string strErrMsg)
        {
            bool blnFlag = false;
            intCommitRowCount = 0;
            spentTime = 0;
            strErrMsg = string.Empty;

            OracleBulkInsert oracleBulkInsert = new OracleBulkInsert(strConnectionString, strTableName, columnMapping, null, 1, null, intBatchSize, intTimeOut);

            try
            {
                oracleBulkInsert.WriteToServer(dataReader);
                blnFlag = true;
            }
            catch (OracleBulkInsertException ex)
            {
                strErrMsg = ex.ToString();
            }
            finally
            {
                intCommitRowCount = oracleBulkInsert.CopiedRowCount;
                spentTime = oracleBulkInsert.TakeMilliseconds;
            }

            return blnFlag;
        }

2.导入文本文件

/// <summary>
        /// 快速导入文本文件至数据库表
        /// </summary>
        /// <param name="strConnectionString">目的数据库连接字符串,oracle连接字符串如："Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521))(CONNECT_DATA=(SID=yyyy)));User Id=sys;Password=sys;"</param>
        /// <param name="intTimeOut">导入最大超时时间</param>
        /// <param name="strTxtFile">要导入的文本文件路径</param>
        /// <param name="strHead">列头格式带分隔符</param>
        /// <param name="strDelimited">列分隔符</param>
        /// <param name="strTableName">目标表名</param>
        /// <param name="columnMapping">列映射关系,Txt第一行提供列名</param>
        /// <param name="intBatchSize">每次导入多少条后提交</param>
        /// <param name="extraMapping">额外列映射</param>
        /// <param name="strRowNumberColumn">保存行号列名(null:不保存行号)</param>
        /// <param name="intCommitRowCount">导入成功行数</param>
        /// <param name="lSpentTime">消耗时间</param>
        /// <param name="strErrMsg">错误消息</param>
        /// <param name="intStartRow">从第X行开始导入</param>
        /// <returns></returns>
        public bool FastImportTextToDb(string strConnectionString, int intBatchSize, int intTimeOut, string strTxtFile, string strHead, string strDelimited, string strTableName, Dictionary<string, string> columnMapping, Dictionary<string, object> extraMapping, uint intStartRow, string strRowNumberColumn, out int intCommitRowCount, out long lSpentTime, out string strErrMsg)
        {
            bool blnFlag = false;
            intCommitRowCount = 0;
            lSpentTime = 0;
            strErrMsg = string.Empty;

            if (!".txt".Equals(Path.GetExtension(strTxtFile), StringComparison.CurrentCultureIgnoreCase))
            {
                throw new Exception("只支持导入txt文件...");
            }

            FileStream fileStream = new FileStream(strTxtFile, FileMode.Open, FileAccess.Read, FileShare.None);
            StreamReader streamReader = new StreamReader(fileStream, Encoding.Default);

            OracleBulkInsert oracleBulkInsert = new OracleBulkInsert(strConnectionString, strTableName, columnMapping, extraMapping, intStartRow, strRowNumberColumn, intBatchSize, intTimeOut);

            try
            {
                oracleBulkInsert.WriteToServer(streamReader, strHead, strDelimited);
                blnFlag = true;
            }
            catch (OracleBulkInsertException ex)
            {
                strErrMsg = ex.ToString();
            }
            finally
            {
                intCommitRowCount = oracleBulkInsert.CopiedRowCount;
                lSpentTime = oracleBulkInsert.TakeMilliseconds;
            }
            return blnFlag;
        }

源码下载:/Files/xiaobier/OracleBulkInsert.rar