/// <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">列映射关系(目标列,源列)</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>true成功,false失败</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.UTF8); 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); }
/// <summary> /// 快速DataReader映射的列至数据库表,并设置最大行数和每次提交行数,计算执行的时间 /// </summary> /// <param name="strConnectionString">目的数据库连接字符串</param> /// <param name="intBatchSize">每次导入多少条后提交</param> /// <param name="intTimeOut">导入最大超时时间</param> /// <param name="dataReader">要导入的DataReader</param> /// <param name="strTableName">目标表名</param> /// <param name="columnMapping">列映射关系(目标列,源列)</param> /// <param name="intCommitRowCount">导入成功行数</param> /// <param name="spentTime">消耗时间</param> /// <param name="strErrMsg">错误消息</param> /// <returns>true成功,false失败</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 { OpenConn(); oracleBulkInsert.WriteToServer(dataReader); blnFlag = true; } catch (OracleBulkInsertException ex) { strErrMsg = ex.ToString(); } finally { intCommitRowCount = oracleBulkInsert.CopiedRowCount; spentTime = oracleBulkInsert.TakeMilliseconds; } return(blnFlag); }
/// <summary> /// 快速导入dataTable映射的列名称至数据库表(目标列名区分大小写) /// </summary> /// <param name="dataTable">要导入的dataTable</param> /// <param name="tableName">目标表名</param> /// <param name="arrStrTarGetColumns">目标列名</param> /// <returns>成功返回success,否则为返回错误消息</returns> public string FastImportDatatableToDBNotClose(DataTable dataTable, string tableName, string[] arrStrTarGetColumns) { Dictionary <string, string> columnMapping = new Dictionary <string, string>(); for (int i = 0; i < arrStrTarGetColumns.Length; i++) { columnMapping.Add(arrStrTarGetColumns[i], dataTable.Columns[i].ColumnName); } OracleBulkInsert bulkCopy = new OracleBulkInsert(this._oracleConn, tableName, columnMapping); OpenConn(); bulkCopy.WriteToServer(dataTable.CreateDataReader()); return("success"); }
/// <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">列映射关系(目标列,源列)</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>true成功,false失败</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.UTF8); 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; }
/// <summary> /// 快速导入dataTable映射的列名称至数据库表(目标列名区分大小写) /// </summary> /// <param name="dataTable">要导入的dataTable</param> /// <param name="tableName">目标表名</param> /// <param name="arrStrTarGetColumns">目标列名</param> /// <returns>成功返回success,否则为返回错误消息</returns> public string FastImportDatatableToDBNotClose(DataTable dataTable, string tableName, string[] arrStrTarGetColumns) { Dictionary<string, string> columnMapping = new Dictionary<string, string>(); for (int i = 0; i < arrStrTarGetColumns.Length; i++) { columnMapping.Add(arrStrTarGetColumns[i], dataTable.Columns[i].ColumnName); } OracleBulkInsert bulkCopy = new OracleBulkInsert(this._oracleConn, tableName, columnMapping); OpenConn(); bulkCopy.WriteToServer(dataTable.CreateDataReader()); return "success"; }
/// <summary> /// 快速DataReader映射的列至数据库表,并设置最大行数和每次提交行数,计算执行的时间 /// </summary> /// <param name="strConnectionString">目的数据库连接字符串</param> /// <param name="intBatchSize">每次导入多少条后提交</param> /// <param name="intTimeOut">导入最大超时时间</param> /// <param name="dataReader">要导入的DataReader</param> /// <param name="strTableName">目标表名</param> /// <param name="columnMapping">列映射关系(目标列,源列)</param> /// <param name="intCommitRowCount">导入成功行数</param> /// <param name="spentTime">消耗时间</param> /// <param name="strErrMsg">错误消息</param> /// <returns>true成功,false失败</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 { OpenConn(); oracleBulkInsert.WriteToServer(dataReader); blnFlag = true; } catch (OracleBulkInsertException ex) { strErrMsg = ex.ToString(); } finally { intCommitRowCount = oracleBulkInsert.CopiedRowCount; spentTime = oracleBulkInsert.TakeMilliseconds; } return blnFlag; }