/// <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 void Dispose()
{
if (bulkCopy != null)
{
bulkCopy.Close();
bulkCopy.Dispose();
bulkCopy = null;
}
}
/// <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 bool ImportaDados(DataTable dt)
{
bool ret = false;
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(ConfigAplication.GetConnectString().Replace(";Unicode=True", "")))
{
bulkCopy.DestinationTableName = "OWN_FUNCESP.FUN_TBL_AD_USUARIO";
bulkCopy.ColumnMappings.Add("Nome", "nome");
bulkCopy.ColumnMappings.Add("Usuario", "login");
bulkCopy.ColumnMappings.Add("Email", "email");
bulkCopy.ColumnMappings.Add("Departamento", "departamento");
bulkCopy.ColumnMappings.Add("Data", "dt_inclusao");
try
{
bulkCopy.WriteToServer(dt);
ret = true;
}
catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema: \\n" + ex.Message);
}
finally
{
bulkCopy.Close();
}
return(ret);
}
}
/// <summary>
/// 批量插入记录
/// </summary>
/// <param name="dt">要插入的数据</param>
/// <param name="targetTable">数据库中的表</param>
public void BulkToDB(DataTable dt, string targetTable)
{
string connOrcleString = connectionDic[DbContextName];
OracleConnection conn = new OracleConnection(connOrcleString);
OracleBulkCopy bulkCopy = new OracleBulkCopy(connOrcleString, OracleBulkCopyOptions.UseInternalTransaction); //用其它源的数据有效批量加载Oracle表中
bulkCopy.BulkCopyTimeout = 260;
bulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称
bulkCopy.BatchSize = dt.Rows.Count; //每一批次中的行数
//自定义的datatable和数据库的字段进行对应
foreach (DataColumn column in dt.Columns)
{
bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
try
{
conn.Open();
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt); //将提供的数据源中的所有行复制到目标表中
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
if (bulkCopy != null)
{
bulkCopy.Close();
}
}
}
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);
}
protected virtual void Dispose(bool disposing)
{
if (disposed)
{
return;
}
if (disposing)
{
if (bulkCopy != null)
{
bulkCopy.Close();
bulkCopy = null;
}
}
disposed = true;
}
protected void InsertDataToDbBulkMethod(DataTable table)
{
OracleBulkCopy bcopy = new OracleBulkCopy(_conn as OracleConnection);
bcopy.DestinationTableName = table.TableName;
bcopy.WriteToServer(table);
bcopy.Close();
}
/// <summary>
/// Function to bulk copy to Oracle
/// </summary>
/// <param name="dr"></param>
private void OracleBulkCopy(DbDataReader dr)
{
//We create the SqlBulkCopy object with the Destination Connection String arguments and with the SqlBulkCopy options selected.
using (OracleBulkCopy oSbc = new OracleBulkCopy(sDest, (OracleBulkCopyOptions)iBcOptions))
{
//We configure the SqlBulkCopy object.
oSbc.DestinationTableName = sDestTable;
oSbc.BatchSize = iSize;
oSbc.BulkCopyTimeout = 0;
if (iNotifyAfter > 0)
{
oSbc.NotifyAfter = iNotifyAfter;
//Each time the number of rows specified in NotifyAfter property has been copied it will call to the OnSqlRowsCopied function.
oSbc.OracleRowsCopied += new OracleRowsCopiedEventHandler(OnOracleRowsCopied);
}
//We start to copy the data to the dest server.
oSbc.WriteToServer(dr);
oSbc.Close();
}
}
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);
}
public bool ImportaDados(DataTable dt)
{
bool ret = false;
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(ConfigAplication.GetConnectString().Replace(";Unicode=True", "")))
{
bulkCopy.DestinationTableName = "OWN_FUNCESP.AAT_TBL_SERASA_PEFIN";
bulkCopy.ColumnMappings.Add("EMPRESA", "COD_EMPRS");
bulkCopy.ColumnMappings.Add("MATRICULA", "NUM_RGTRO_EMPRG");
bulkCopy.ColumnMappings.Add("CONTRATO", "NUM_CONTRATO");
bulkCopy.ColumnMappings.Add("NOME", "NOM_NOME");
bulkCopy.ColumnMappings.Add("CPF", "NUM_CPF");
bulkCopy.ColumnMappings.Add("VALOR", "VLR_VALOR");
bulkCopy.ColumnMappings.Add("DT_COMPROMIS_DEV", "DAT_COMPROMIS_DEV");
bulkCopy.ColumnMappings.Add("DAT_IMPORTACAO", "DAT_IMPORTACAO");
bulkCopy.ColumnMappings.Add("TIPO", "COD_OPERACAO");
bulkCopy.ColumnMappings.Add("DESC_USER", "DESC_USER");
//bulkCopy.ColumnMappings.Add("COD_REMESSA_SERASA_PEFIN", "COD_REMESSA_SERASA_PEFIN");
try
{
bulkCopy.WriteToServer(dt);
ret = true;
}
catch (Exception ex)
{
ret = false;
throw new Exception(ex.Message + "\\n\\nVerique se a planinha contém as colunas (EMPRESA, MATRICULA, NOME, CPF, CONTRATO, DT_COMPROMIS_DEV, VALOR, TIPO)");
}
finally
{
bulkCopy.Close();
}
}
return(ret);
}
/// <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();
}
}
}
public void BulkToDB(DataTable dt, string targetTable)
{
this.Invoke(new Action(() =>
{
richTextBox4.AppendText(DateTime.Now.ToString() + "--开始插入查询结果... ");
}));
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connstr);
//System.Data.OracleClient.OracleConnection conn = new OracleConnection(conStr);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
OracleBulkCopy bulkCopy = new OracleBulkCopy(conn, OracleBulkCopyOptions.Default);
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.BulkCopyTimeout = 260;
bulkCopy.DestinationTableName = targetTable;
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
// conn.Open();
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt);
}
else
{
this.Invoke(new Action(() =>
{
richTextBox4.AppendText("没有可以插入的记录 ");
}));
}
this.Invoke(new Action(() =>
{
richTextBox4.AppendText("插入记录数:" + dt.Rows.Count.ToString() + " ");
// richTextBox3.AppendText(DateTime.Now.ToString() + "--查询报表中... ");
richTextBox4.Focus();
richTextBox4.AppendText(DateTime.Now.ToString() + "--插入完成... ");
richTextBox4.Focus();
}));
Task.Run(() => ExpFromorcl());
}
catch (Exception ex)
{
this.Invoke(new Action(() =>
{
richTextBox4.AppendText(DateTime.Now.ToString() + "--插入报错" + ex.ToString() + " ");
richTextBox4.Focus();
}));
}
finally
{
conn.Close();
if (bulkCopy != null)
{
bulkCopy.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");
}
}
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());
}
}
protected void InsertDataToDbBulkMethod(DataTable table)
{
OracleBulkCopy bcopy = new OracleBulkCopy(_conn as OracleConnection);
bcopy.DestinationTableName = table.TableName;
bcopy.WriteToServer(table);
bcopy.Close();
}