/// <summary>
///大批量数据插入
/// </summary>
/// <param name="table">数据表</param>
/// <param name="connectionString">数据库连接字符串</param>
/// <returns></returns>
public bool OracleBulkInsert(DataTable table, string connectionString)
{
try
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
OracleTransaction trans = connection.BeginTransaction();
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connection))
{
//设置源表名称
bulkCopy.DestinationTableName = table.TableName;
//设置超时限制
bulkCopy.BulkCopyTimeout = CommandTimeOut;
//要写入列
foreach (DataColumn dtColumn in table.Columns)
{
bulkCopy.ColumnMappings.Add(dtColumn.ColumnName.ToUpper(), dtColumn.ColumnName.ToUpper());
}
try
{
// 写入
bulkCopy.WriteToServer(table);
// 提交事务
trans.Commit();
return true;
}
catch
{
trans.Rollback();
bulkCopy.Close();
return false;
}
finally
{
connection.Close();
connection.Dispose();
bulkCopy.Close();
bulkCopy.Dispose();
}
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return false;
}
}
public long LoadData(IDataReader reader, string fileName, ITableDefinition tableDefinition)
{
long rowsCopied;
OracleBulkCopy oracleBulkCopy = null;
try
{
oracleBulkCopy = new OracleBulkCopy(_dbContext.ConnectionString)
{
DestinationTableName = tableDefinition.Name,
BatchSize = BatchSize,
BulkCopyTimeout = 3 * 60 * 60
};
// 3 hours
using (reader)
{
oracleBulkCopy.WriteToServer(reader);
rowsCopied = reader.RecordsAffected;
}
}
catch (Exception ex)
{
throw new ADatabaseException("OracleBulkCopy threw error", ex);
}
finally
{
if (oracleBulkCopy != null)
{
oracleBulkCopy.Close();
oracleBulkCopy.Dispose();
}
}
return(rowsCopied);
}
public void Dispose()
{
if (bulkCopy != null)
{
bulkCopy.Close();
bulkCopy.Dispose();
bulkCopy = null;
}
}
/// <summary>
/// 导入数据到目标表
/// </summary>
/// <param name="session">数据库连接Session</param>
/// <param name="tableName">表名</param>
/// <param name="filePath">数据文件流路径，支持xls、xlsx、csv文件</param>
/// <param name="columnCont">字段数量</param>
/// <returns>成功行数</returns>
public static int Import(IDbSession session, string tableName, string filePath, int columnCont = 0)
{
if (string.IsNullOrWhiteSpace(tableName))
{
throw new ArgumentNullException("tableName");
}
if (string.IsNullOrWhiteSpace(filePath))
{
throw new ArgumentNullException("filePath");
}
int rows;
using (var tran = session.Connection.BeginTransaction())
{
var oracleBulkCopy = new OracleBulkCopy(session.Connection)
{
Timeout = 30 * 60, DestinationTableName = tableName
};
try
{
var extension = Path.GetExtension(filePath).ToLower();
DataTable dt;
if (extension.StartsWith(".xls") || extension.StartsWith(".xlsx"))
{
dt = ExcelHelper.ExportExcelDataTable(filePath);
}
else if (extension == ".csv" || extension == ".txt")
{
dt = ExcelHelper.ExportCsvDataReader(filePath);
}
else
{
throw new Exception("暂不支持：{0} 文件".FormatWith(extension));
}
if (columnCont > 0 && dt.Columns.Count != columnCont)
{
throw new Exception("字段数量不一致（{0} / {1}）".FormatWith(columnCont, dt.Columns.Count));
}
rows = oracleBulkCopy.WriteToServer(dt);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw new UserFriendlyException("导入失败: " + ex.GetTrueMessage(), ex.GetBaseException());
}
finally { oracleBulkCopy.Dispose(); }
}
return(rows);
}
/// <summary>
///大批量数据插入
/// </summary>
/// <param name="table">数据表</param>
/// <param name="connectionString">数据库连接字符串</param>
/// <returns></returns>
public bool OracleBulkInsert(DataTable table, string connectionString)
{
try
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
OracleTransaction trans = connection.BeginTransaction();
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connection))
{
//设置源表名称
bulkCopy.DestinationTableName = table.TableName;
//设置超时限制
bulkCopy.BulkCopyTimeout = CommandTimeOut;
//要写入列
foreach (DataColumn dtColumn in table.Columns)
{
bulkCopy.ColumnMappings.Add(dtColumn.ColumnName.ToUpper(), dtColumn.ColumnName.ToUpper());
}
try
{
// 写入
bulkCopy.WriteToServer(table);
// 提交事务
trans.Commit();
return(true);
}
catch
{
trans.Rollback();
bulkCopy.Close();
return(false);
}
finally
{
connection.Close();
connection.Dispose();
bulkCopy.Close();
bulkCopy.Dispose();
}
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return(false);
}
}
public override void ExecuteBulkCopy(IDataReader reader, string destinationTable, IList <KeyValuePair <string, string> > mappings)
{
if (reader == null)
{
Logger.Log(LevelEnum.Error, Resources.ERR_DBREADER_ISNULL);
throw new ArgumentNullException(nameof(reader), Resources.ERR_DBREADER_ISNULL);
}
if (string.IsNullOrWhiteSpace(destinationTable))
{
Logger.Log(LevelEnum.Error, Resources.ERR_TABLENAME_ISNULL);
throw new ArgumentNullException(nameof(destinationTable), Resources.ERR_TABLENAME_ISNULL);
}
Logger.LogFormat(LevelEnum.Verbose, Resources.VERBOSE_BULK_INSERT, destinationTable);
IConfigurationProvider config = Bootstrapper.GetContainer().Resolve <IConfigurationProvider>();
using (OracleConnection destinationConnection = (OracleConnection)CreateConnection())
{
destinationConnection.Open();
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName = destinationTable;
bulkCopy.BatchSize = Convert.ToInt32(config.GetAppSettings(DrakeQuest.Configuration.Constants.ConfigBulkCopyBatchSize));
bulkCopy.BulkCopyTimeout = Convert.ToInt32(config.GetAppSettings(DrakeQuest.Configuration.Constants.ConfigBulkCopyTimeout));
if (mappings != null)
{
foreach (var mapping in mappings)
{
bulkCopy.ColumnMappings.Add(mapping.Key, mapping.Value);
}
}
bulkCopy.WriteToServer(reader);
//bulkCopy.OracleRowsCopied += new OracleRowsCopiedEventHandler(bulkCopy_OracleRowsCopied);
bulkCopy.Close();
bulkCopy.Dispose();
destinationConnection.Dispose();
}
}
Logger.LogFormat(LevelEnum.Verbose, Resources.VERBOSE_BULK_INSERT_END, destinationTable);
}
/// <summary>use to insert data using bulk method to the table</summary>
/// <param name="destinationtablename">the table in which the data will be inserted</param>
/// <param name="sourcedatatable"> the datatable contains the data to be inserted</param>
/// <param name="columnmapping">column mapping used just in case the column structure between source and destination is different</param>
public void DoBulkCopy(string destinationtablename, DataTable sourcedatatable, params OracleBulkCopyColumnMapping[] columnmapping)
{
try
{
if (oraCon.State == ConnectionState.Closed)
{
oraCon.Open();
}
doClearOracleParameters();
OracleBulkCopy mybulkcopy = new OracleBulkCopy(oraCon);
mybulkcopy.DestinationTableName = destinationtablename;
if (columnmapping != null && columnmapping.Length > 0)
{
for (int i = 0; i < columnmapping.Length; i++)
{
mybulkcopy.ColumnMappings.Add(columnmapping[i]);
}
}
mybulkcopy.WriteToServer(sourcedatatable);
mybulkcopy.Close();
mybulkcopy.Dispose();
clearOracleParameter = true;
doClearOracleParameters();
}
catch (Exception) { throw; }
finally
{
if (oraCon.State == ConnectionState.Open)
{
oraCon.Close();
}
}
}
static void Main(string[] args)
{
var txt = string.Empty;
try
{
Console.WriteLine($"{DateTime.Now.ToString("HH:mm:ss")} 内存表开始循环组装数据...");
var data = new DataTable();
data.Columns.Add("ID", typeof(decimal));
data.Columns.Add("LOGID", typeof(decimal));
data.Columns.Add("PF", typeof(decimal));
data.Columns.Add("VAL", typeof(double));
data.Columns.Add("ITEMID", typeof(decimal));
data.Columns.Add("SITEID", typeof(decimal));
for (int i = 0; i < 1000000; i++)
{
var row = data.NewRow();
row["ID"] = i;
row["LOGID"] = i;
row["PF"] = i;
row["VAL"] = i;
row["ITEMID"] = i;
row["SITEID"] = i;
data.Rows.Add(row);
}
Console.WriteLine($"{DateTime.Now.ToString("HH:mm:ss")} 内存表组装数据完成...");
var st = Stopwatch.StartNew();
Console.WriteLine($"{DateTime.Now.ToString("HH:mm:ss")} 开始往数据库写入数据...");
var connString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= 192.168.0.106)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=HLINK;Password=123456";
var conn = new OracleConnection(connString);
conn.Open();
var ocp = new OracleBulkCopy(conn);
ocp.BatchSize = 1000000;
ocp.BulkCopyOptions = OracleBulkCopyOptions.UseInternalTransaction;
ocp.BulkCopyTimeout = 360000;
ocp.DestinationTableName = "RESULT_ITEM";
foreach (DataColumn item in data.Columns)
{
ocp.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
ocp.WriteToServer(data);
ocp.Close();
ocp.Dispose();
st.Stop();
Console.WriteLine($"{DateTime.Now.ToString("HH:mm:ss")} 数据库写入完成,数据量 {data.Rows.Count},耗时[{st.Elapsed.TotalSeconds}]...");
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
do
{
txt = Console.ReadLine();
} while (txt != "EXIT");
}
}
//Interface para o w(DataTable) do ODP.NET
//http://docs.oracle.com/html/E10927_01/OracleBulkCopyClass.htm#BIGCDJDD
public bool WriteToServer(DataTable dados, string tabela)
{
ClearMessages();
OracleBulkCopy bulkCopy = new OracleBulkCopy(myConnection);
bulkCopy.DestinationTableName = tabela;
bulkCopy.BulkCopyTimeout = 3600;
try
{
bulkCopy.WriteToServer(dados);
}
catch (Exception e)
{
ERROR = e.Message;
if (e.InnerException != null)
{
ERROR += e.InnerException.Message;
}
return false;
}
bulkCopy.Dispose();
return true;
}
/// <summary>
/// Does the actual bulk inserts.
/// </summary>
/// <param name="table"></param>
/// <param name="table_name"></param>
/// <param name="batch_size"></param>
private void BulkCopy(DataTable table, string table_name, int batch_size)
{
if (table != null && table.Rows.Count > 0)
{
OracleBulkCopy bulk = new OracleBulkCopy(_connection, OracleBulkCopyOptions.Default);
bulk.BatchSize = batch_size;
bulk.DestinationTableName = table_name;
bulk.WriteToServer(table);
bulk.Dispose();
bulk = null;
Console.WriteLine("Inserted {0} records into {1}!", table.Rows.Count, table_name);
}
}
private void button10_Click(object sender, EventArgs e)
{
Stopwatch _stopwatch = new Stopwatch();
String _Results;
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
OracleCommand _cmdObj = _connObj.CreateCommand();
//Clear all products
_cmdObj.CommandText = "DELETE FROM Products";
_cmdObj.ExecuteNonQuery();
_cmdObj.CommandText = "INSERT INTO Products(ID, Name, Price) VALUES(:ID, :Name, :Price)";
OracleParameter _IDParam = new OracleParameter("ID", OracleDbType.Varchar2);
_cmdObj.Parameters.Add(_IDParam);
OracleParameter _nameParam = new OracleParameter("Name", OracleDbType.Varchar2);
_cmdObj.Parameters.Add(_nameParam);
OracleParameter _priceParam = new OracleParameter("Price", OracleDbType.Decimal);
_cmdObj.Parameters.Add(_priceParam);
_stopwatch.Start();
for (int i = 1; i <= 50000; i++)
{
_IDParam.Value = "E" + Convert.ToString(i);
_nameParam.Value = "Test Product" + Convert.ToString(i);
_priceParam.Value = 100;
_cmdObj.ExecuteNonQuery();
}
_stopwatch.Stop();
_cmdObj.Dispose();
//Oracle Bulk Copy
_Results = "Without Oracle Bulk Copy:\t" + _stopwatch.Elapsed.TotalSeconds.ToString() + " seconds\n";
_cmdObj = _connObj.CreateCommand();
_cmdObj.CommandText = "DELETE FROM Products";
_cmdObj.ExecuteNonQuery();
_cmdObj.Dispose();
DataTable _dataTbl = new DataTable("SourceTable");
_dataTbl.Columns.Add(new DataColumn("ID", System.Type.GetType("System.String")));
_dataTbl.Columns.Add(new DataColumn("Name", System.Type.GetType("System.String")));
_dataTbl.Columns.Add(new DataColumn("Price", System.Type.GetType("System.String")));
_stopwatch.Reset();
_stopwatch.Start();
for (int i = 1; i <= 50000; i++)
{
DataRow _newrow = _dataTbl.NewRow();
_newrow["ID"] = "E" + Convert.ToString(i);
_newrow["Name"] = "Test Product" + Convert.ToString(i);
_newrow["Price"] = 100;
_dataTbl.Rows.Add(_newrow);
}
_stopwatch.Stop();
OracleBulkCopy _bulkCopy = new OracleBulkCopy(_connObj);
_bulkCopy.DestinationTableName = "Products";
_stopwatch.Start();
_bulkCopy.WriteToServer(_dataTbl);
_stopwatch.Stop();
_Results += "With Oracle Bulk Copy:\t" + _stopwatch.Elapsed.TotalSeconds.ToString() + " seconds\n";
_bulkCopy.Close();
_bulkCopy.Dispose();
_bulkCopy = null;
MessageBox.Show(_Results);
_connObj.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
