更新内容:
1.添加超大文本导入(原只支持DataReader)
2.支持从第x行数据开始导入
3.决定是否保存数据行号
4.列映射不区分大小写
5.支持导入常量列
6.相同源列导入多目标列只分配一次内存,更加节约内存
7.增加了源列自动检测功能
调用示例如下:
1.导入DataReader
View Code
///
<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;
}
/// 快速导入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.导入文本文件
View Code
///
<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;
}
/// 快速导入文本文件至数据库表
/// </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;
}