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