/// <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;
}
}
/// <summary>
/// Bulk copies a set of objects to the server.
/// </summary>
/// <param name="connection">The connection to use.</param>
/// <param name="tableName">The name of the table.</param>
/// <param name="reader">The reader to read objects from.</param>
/// <param name="configure">A callback method to configure the bulk copy object.</param>
/// <param name="options">Options for initializing the bulk copy object.</param>
/// <param name="transaction">An optional transaction to participate in.</param>
public override void BulkCopy(IDbConnection connection, string tableName, IDataReader reader, Action<InsightBulkCopy> configure, InsightBulkCopyOptions options, IDbTransaction transaction)
{
if (reader == null) throw new ArgumentNullException("reader");
if (transaction != null)
throw new ArgumentException("OracleProvider does not support external transactions for bulk copy", "transaction");
OracleBulkCopyOptions oracleOptions = OracleBulkCopyOptions.Default;
if (options.HasFlag(InsightBulkCopyOptions.UseInternalTransaction))
oracleOptions |= OracleBulkCopyOptions.UseInternalTransaction;
using (var bulk = new OracleBulkCopy((OracleConnection)connection, oracleOptions))
using (var insightBulk = new OracleInsightBulkCopy(bulk))
{
bulk.DestinationTableName = tableName;
// map the columns by name, in case we skipped a readonly column
foreach (DataRow row in reader.GetSchemaTable().Rows)
bulk.ColumnMappings.Add((string)row["ColumnName"], (string)row["ColumnName"]);
if (configure != null)
configure(insightBulk);
bulk.WriteToServer(reader);
}
}
/// <summary>
/// 将DataTable中的数据向数据库中的表里插入数据
/// </summary>
/// <param name="dt">需要插入数据库的DataTable</param>
/// <param name="targetTable">数据库中已经存在的目标表</param>
public void InsertDatabase(DataTable dt, string targetTable)
{
string _connStr = ConfigurationManager.ConnectionStrings["OracleConnection"].ToString();
OracleConnection conn = new OracleConnection(_connStr);
OracleBulkCopy oracleBulkCopy = new OracleBulkCopy(_connStr, OracleBulkCopyOptions.UseInternalTransaction);
//oracleBulkCopy.BatchSize = dt.Rows.Count; //每一批次中的行数
try
{
conn.Open();
if ((dt != null) && (dt.Rows.Count != 0))
{
oracleBulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称
oracleBulkCopy.BatchSize = 100000; //每一批次中的行数
for (int i = 0; i < dt.Columns.Count; i++)
{
string col = dt.Columns[i].ColumnName;
oracleBulkCopy.ColumnMappings.Add(col, col);
}
oracleBulkCopy.WriteToServer(dt); //将提供的数据源中的所有行复制到目标表中
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.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);
}
/// <summary>
/// 使用OracleBulkCopy方式向表中批量添加数据。
/// </summary>
/// <param name="entities">记录集合</param>
/// <param name="columnNames">目标表列名集合</param>
/// <returns>0表示成功，其他失败</returns>
protected virtual int InsertByBulkCopy(List <T> entities, params string[] columnNames)
{
if (entities == null || entities.Count == 0)
{
throw new ArgumentNullException("entites");
}
MetaDataTable metaDataTable = new MetaDataTable(typeof(T), this._tableName);
DataTable dataTable = this.GetDataTable(entities, metaDataTable, columnNames);
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(this._connectionString))
{
bulkCopy.DestinationTableName = this._tableName;
bulkCopy.BatchSize = dataTable.Rows.Count;
this.SetColumnMappings(bulkCopy.ColumnMappings, metaDataTable, columnNames);
try
{
bulkCopy.WriteToServer(dataTable);
}
catch (Exception ex)
{
string message = string.Format("[SQL]:{0},[Exception]:{1}", "BulkCopy", ex.ToString());
throw new ApplicationException(message);
}
}
return(0);
}
/// <summary>
/// 导入到Oracle
/// </summary>
/// <param name="dtData">需要导入的datatable</param>
/// <returns></returns>
public static void BulkToOracle(DataTable dtData)
{
using (OracleConnection conn_bulkcopy = new OracleConnection(ConfigurationManager.AppSettings["OracleConnectionString"].ToString()))
{
//OracleTransaction myTransaction = conn_bulkcopy.BeginTransaction();
try
{
conn_bulkcopy.Open();
using (OracleBulkCopy bcp = new OracleBulkCopy(conn_bulkcopy))
{
bcp.DestinationTableName = dtData.TableName;
if (bcp.DestinationTableName != "WHwarehousearea1")
{
bcp.BatchSize = dtData.Rows.Count;
SetColumnMapping(dtData, bcp);
bcp.WriteToServer(dtData);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
public override int BulkInsert(string tableName, DbDataReader reader, int batchSize = 200000, int timeout = 60)
{
List <string> lstAllColName = this.Info[tableName];
OracleBulkCopy bulk = null;
bulk = new OracleBulkCopy(this.ConnectionString);
using (bulk)
{
int colCount = reader.FieldCount;
for (int j = 0; j < colCount; j++)
{
string currName = reader.GetName(j);
if (lstAllColName.Contains(currName, StringComparer.OrdinalIgnoreCase))
{
bulk.ColumnMappings.Add(new OracleBulkCopyColumnMapping(currName, currName));
}
}
bulk.DestinationTableName = tableName;
bulk.BulkCopyTimeout = timeout;
bulk.BatchSize = batchSize;
bulk.WriteToServer(reader);
reader.Close();
}
return(reader.RecordsAffected);
}
public bool SqlBulkCopyImport(System.Collections.Generic.IList <string> maplist, string TableName, DataTable dt)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connection))
{
bulkCopy.DestinationTableName = TableName;
foreach (string a in maplist)
{
bulkCopy.ColumnMappings.Add(a, a);
}
try
{
bulkCopy.WriteToServer(dt);
return(true);
}
catch (Exception e)
{
throw e;
}
}
}
}
/// <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 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);
}
}
public void WriteToOracle(string qualifiedTableName, string conString, DataTable dataTable)
{
try
{
//Oracle connection
OracleConnection con = new OracleConnection();
con.ConnectionString = conString;
con.Open();
// First truncate table
OracleCommand truncateTbl = con.CreateCommand();
truncateTbl.CommandText = "TRUNCATE table " + qualifiedTableName;
OracleDataReader dataReader = truncateTbl.ExecuteReader();
// them bulk data
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(con))
{
bulkCopy.DestinationTableName = qualifiedTableName;
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.WriteToServer(dataTable);
}
con.Close();
con.Dispose();
}
catch (Exception e)
{
Console.WriteLine("Error encountered during Bulk INSERT operation " + e.Message);
throw;
}
finally
{
Console.WriteLine("Volcado de datos en Oracle table " + qualifiedTableName + " satisfactorio");
}
}
public void Oracle_UseSqlBulkCopy(DataTable dt)
{
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(DBConfig.CmsSqlConString))
{
try
{
bulkCopy.DestinationTableName = dt.TableName;//Ҫ����ı�ı���
foreach (DataColumn col in dt.Columns)
{
bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
}
}
}
public Task <int> ExecuteBulkCopyAsync(DataTable dt)
{
var identityColumnInfo = this.entityInfo.Columns.FirstOrDefault(it => it.IsIdentity);
if (identityColumnInfo != null)
{
throw new Exception("Oracle bulkcopy no support identity");
}
OracleBulkCopy copy = GetBulkCopyInstance();
try
{
copy.DestinationTableName = dt.TableName;
copy.WriteToServer(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseDb();
}
return(Task.FromResult(dt.Rows.Count));
}
public override int BulkCopy(
DbConnection connection,
DataTable dataTable,
string destinationTableName = null,
int?bulkCopyTimeout = null,
int?batchSize = null)
{
if (!(connection is OracleConnection conn))
{
return(0);
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (var bulkCopy = new OracleBulkCopy(conn))
{
bulkCopy.BatchSize = dataTable.Rows.Count;
bulkCopy.DestinationTableName = destinationTableName ?? dataTable.TableName;
bulkCopy.BulkCopyTimeout = conn.ConnectionTimeout;
bulkCopy.WriteToServer(dataTable);
return(dataTable.Rows.Count);
}
}
/// <summary>
/// 批量复制
/// </summary>
/// <param name="dataSet">数据集</param>
/// <param name="dbConnection">数据库连接</param>
public void BulkCopy(DataSet dataSet, DbConnection dbConnection)
{
#region # 验证
if (dataSet == null || dataSet.Tables.Count == 0)
{
return;
}
#endregion
OracleConnection oracleConnection = (OracleConnection)dbConnection;
foreach (DataTable dataTable in dataSet.Tables)
{
if (dataTable.Rows.Count > 0)
{
//开启批量复制
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(oracleConnection, OracleBulkCopyOptions.UseInternalTransaction))
{
bulkCopy.BatchSize = dataTable.Rows.Count;
bulkCopy.DestinationTableName = dataTable.TableName;
//列映射
foreach (DataColumn dataColumn in dataTable.Columns)
{
bulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName.ToUpper());
}
//执行批量插入
bulkCopy.WriteToServer(dataTable);
}
}
}
}
/// <summary>
/// 批量复制
/// </summary>
/// <param name="dataTable">数据表</param>
public void BulkCopy(DataTable dataTable)
{
#region # 验证
if (dataTable == null || dataTable.Rows.Count == 0)
{
return;
}
#endregion
using (OracleConnection connection = this.CreateConnection())
{
connection.Open();
//开启批量复制
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connection, OracleBulkCopyOptions.UseInternalTransaction))
{
bulkCopy.BatchSize = dataTable.Rows.Count;
bulkCopy.DestinationTableName = dataTable.TableName;
//列映射
foreach (DataColumn dataColumn in dataTable.Columns)
{
bulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName.ToUpper());
}
//执行批量插入
bulkCopy.WriteToServer(dataTable);
}
}
}
static void Main()
{
string connectionString = GetConnectionString();
// Open a connection to the DB.
using (OracleConnection sourceConnection = new OracleConnection(connectionString))
{
sourceConnection.Open();
// Perform an initial count on the source/destination table.
OracleCommand commandRowCount = new OracleCommand("SELECT COUNT(*) FROM BLOGS", sourceConnection);
long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a OracleDataReader.
OracleCommand commandSourceData = new OracleCommand("SELECT ID, URL FROM BLOGS", sourceConnection);
OracleDataReader reader = commandSourceData.ExecuteReader();
// Open the destination connection. In the real world you would
// not use OracleBulkCopy to move data from one table to the other
// in the same database. This is for demonstration purposes only.
using (OracleConnection destinationConnection = new OracleConnection(connectionString))
{
destinationConnection.Open();
// Set up the bulk copy object.
// Note that the column positions in the source data reader match the column positions in
// the destination table so there is no need to map columns.
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName = "BLOGS";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the OracleDataReader. The OracleBulkCopy object is automatically
// closed at the end of the using block.
reader.Close();
}
}
// Perform a final count on the destination table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
}
protected void InsertDataToDbBulkMethod(DataTable table)
{
OracleBulkCopy bcopy = new OracleBulkCopy(_conn as OracleConnection);
bcopy.DestinationTableName = table.TableName;
bcopy.WriteToServer(table);
bcopy.Close();
}
public void WriteToServer(DataTable dataTable)
{
//dynamic bulkCopy = Activator.CreateInstance(BulkCopyType, new object[] { Connection });
OracleBulkCopy bulkCopy = new OracleBulkCopy((OracleConnection)Connection);
bulkCopy.DestinationTableName = TableName;
bulkCopy.WriteToServer(dataTable);
}
/// <summary>
/// Bulk Copy a Data Reader to an Oracle Database.
/// </summary>
/// <param name="oracleConnection">Oracle Database Connection.</param>
/// <param name="dataReader">Data Reader to consumer.</param>
public static void ConsumeDataReader(
OracleConnection oracleConnection,
IDataReader dataReader)
{
using (var bulkCopy = new OracleBulkCopy(oracleConnection))
{
bulkCopy.WriteToServer(dataReader);
}
}
/// <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);
}
public void BulkCopyDPTS(DataTable Dt, string UserID)
{
OracleConnection Oracleconn = new OracleConnection(WebConfigurationManager.ConnectionStrings["OracleConString"].ConnectionString);
Oracleconn.Open();
using (OracleBulkCopy bulkcopy = new OracleBulkCopy(Oracleconn))
{
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_RECEIPT_NO", "DPTS_RECEIPT_NO"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_POLICY_NO", "DPTS_POLICY_NO"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_PREM_RECEIPT_NO", "DPTS_PREM_RECEIPT_NO"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_RECEIPT_AMT", "DPTS_RECEIPT_AMT"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_TIME_SLAB", "DPTS_TIME_SLAB"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_AGENT", "DPTS_AGENT"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_TABLE", "DPTS_TABLE"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_TERM", "DPTS_TERM"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_MODE", "DPTS_MODE"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_TRANSFER_AMT", "DPTS_TRANSFER_AMT"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_VARIANCE", "DPTS_VARIANCE"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_COMM_MONTH", "DPTS_COMM_MONTH"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_COMM_YEAR", "DPTS_COMM_YEAR"));
//bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_TIME_SLAB_INDEX", "TFR_AMT1"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_PROCESS_IND", "DPTS_PROCESS_IND"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_UPLOAD_BY", "DPTS_UPLOAD_BY"));
////bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_UPLOAD_DATE", "DPTS_UPLOAD_DATE"));
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("DPTS_UPLOAD_TYPE", "DPTS_UPLOAD_TYPE"));
bulkcopy.DestinationTableName = "HCI_TBL_DPTS_TEMP_BEFORE";
try
{
bulkcopy.WriteToServer(Dt);
Oracleconn.Close();
//Transfer Data to HCI_TBL_DPTS_TEMP_BEFORE to HCI_TBL_DPTS_TEMP with auto ID
OracleConnection connection = new OracleConnection(ConnectionString);
connection.Open();
OracleCommand cmd = null;
cmd = new OracleCommand("HCI_SP_DPTS_DATA_MOVE");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = connection;
cmd.Parameters.Add("V_USER_ID", OracleDbType.Char).Value = UserID;
cmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// 快速导入dataTable映射的列至数据库表(目标列名区分大小写)
/// </summary>
/// <param name="dataTable">要导入的dataTable</param>
/// <param name="tableName">目标表名</param>
/// <param name="columnMapping">列映射关系</param>
/// <returns>成功返回success，否则为返回错误消息</returns>
public string FastImportDatatableToDB(DataTable dataTable, string tableName, DataRow[] columnMapping)
{
OracleBulkCopy bulkCopy = new OracleBulkCopy(this._strConnString, OracleBulkCopyOptions.UseInternalTransaction);
bulkCopy.DestinationTableName = tableName;
foreach (DataRow row in columnMapping)
{
bulkCopy.ColumnMappings.Add(row[0].ToString(), row[1].ToString());
}
OpenConn();
bulkCopy.WriteToServer(dataTable);
return("success");
}
/// <summary>
/// 将整表数据写入数据库，表名及栏位名需与数据库一致。
/// </summary>
/// <param name="table">数据表。</param>
public void WriteTable(DataTable table)
{
var bulkCopy = new OracleBulkCopy(ConnectionString, OracleBulkCopyOptions.UseInternalTransaction)
{
BulkCopyTimeout = 300,
DestinationTableName = table.TableName
};
foreach (DataColumn item in table.Columns)
{
bulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
bulkCopy.WriteToServer(table);
}
/// <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);
}
}
/// <summary>
/// Bulk Insert
/// </summary>
/// <param name="Ps_TableName">Destination Target Table</param>
/// <param name="P_Data">Data Table</param>
public void BulkInsert(string Ps_TableName, DataTable P_Data)
{
using (OracleBulkCopy BulkCopy = new OracleBulkCopy(Conn))
{
BulkCopy.DestinationTableName = Ps_TableName;
try
{
BulkCopy.WriteToServer(P_Data);
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// 批量执行
/// </summary>
/// <param name="batchSize"></param>
/// <param name="timeout"></param>
public override void Execute(int batchSize = 10000, int timeout = 10 * 1000)
{
OracleConnection newConnection = (OracleConnection)_database.CreateConnection();
try
{
_dataTable = ToDataTable(_list);
if (_dataTable == null || _dataTable.Rows.Count == 0)
{
return;
}
var sbc = new OracleBulkCopy(newConnection);
using (sbc)
{
sbc.BatchSize = batchSize;
sbc.DestinationTableName = _dataTable.TableName;
sbc.BulkCopyTimeout = timeout;
if (newConnection.State != ConnectionState.Open)
{
newConnection.Open();
}
sbc.WriteToServer(_dataTable);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (newConnection.State == ConnectionState.Open)
{
newConnection.Close();
}
_list.Clear();
}
}
/// <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();
}
}
protected internal void InsertDataOrcl <T>(List <T> list, string TableName, string ConnectionString)
{
using (OracleBulkCopy bulkcopy = new OracleBulkCopy(ConnectionString))
{
foreach (PropertyDescriptor prop in GetListroperties <T>())
{
bulkcopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping(prop.Name, prop.Name));
}
bulkcopy.BulkCopyTimeout = 660;
bulkcopy.DestinationTableName = TableName;
try
{
bulkcopy.WriteToServer(ConvertToDataTable(list));
}
catch
{
}
}
}
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 void InsertUsingOracleBulk(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt)
{
try
{
using (var bulkCopy = new OracleBulkCopy(ConnectionString, OracleBulkCopyOptions.Default))
{
foreach (DataColumn col in dt.Columns)
{
bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = "[" + schmapper.SchemaName + "].[" + schmapper.TableName + "]";
bulkCopy.WriteToServer(dt);
}
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <int> ExecuteBulkCopyAsync()
{
if (DbColumnInfoList == null || DbColumnInfoList.Count == 0)
{
return(0);
}
if (Inserts.First().GetType() == typeof(DataTable))
{
return(WriteToServer());
}
DataTable dt = GetCopyData();
OracleBulkCopy bulkCopy = GetBulkCopyInstance();
bulkCopy.DestinationTableName = InsertBuilder.GetTableNameString;
try
{
await Task.Run(() => bulkCopy.WriteToServer(dt));
}
catch (Exception ex)
{
CloseDb();
throw ex;
}
CloseDb();
return(DbColumnInfoList.Count);
}
public override void Load(string table, DataTable records, int bachSize, int timeout)
{
using (var oraBulkCopy = new OracleBulkCopy(ConnectionString))
{
// set bulk copy options
oraBulkCopy.BulkCopyOptions = OracleBulkCopyOptions.Default;
// mapping between data source and target database table
oraBulkCopy.DestinationTableName = table;
// batch and time options
oraBulkCopy.BatchSize = bachSize;
oraBulkCopy.BulkCopyTimeout = timeout; // in seconds; i've set my time out to 10 minutes
// do the actual copy
oraBulkCopy.WriteToServer(records);
}
}
/// <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);
}
}
//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>
/// Bulk copies a set of objects to the server.
/// </summary>
/// <param name="connection">The connection to use.</param>
/// <param name="tableName">The name of the table.</param>
/// <param name="reader">The reader to read objects from.</param>
/// <param name="configure">A callback method to configure the bulk copy object.</param>
/// <param name="options">Options for initializing the bulk copy object.</param>
/// <param name="transaction">An optional transaction to participate in.</param>
public override void BulkCopy(IDbConnection connection, string tableName, IDataReader reader, Action<InsightBulkCopy> configure, InsightBulkCopyOptions options, IDbTransaction transaction)
{
if (transaction != null)
throw new ArgumentException("OracleProvider does not support external transactions for bulk copy", "transaction");
OracleBulkCopyOptions oracleOptions = OracleBulkCopyOptions.Default;
if (options.HasFlag(InsightBulkCopyOptions.UseInternalTransaction))
oracleOptions |= OracleBulkCopyOptions.UseInternalTransaction;
using (var bulk = new OracleBulkCopy((OracleConnection)connection, oracleOptions))
using (var oracleBulk = new OracleInsightBulkCopy(bulk))
{
bulk.DestinationTableName = tableName;
if (configure != null)
configure(oracleBulk);
bulk.WriteToServer(reader);
}
}
public bool SqlBulkCopyImport(System.Collections.Generic.IList<string> maplist, string TableName, DataTable dt)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connection))
{
bulkCopy.DestinationTableName = TableName;
foreach (string a in maplist)
{
bulkCopy.ColumnMappings.Add(a, a);
}
try
{
bulkCopy.WriteToServer(dt);
return true;
}
catch (Exception e)
{
throw e;
}
}
}
}
/// <summary>
/// 快速导入dataTable映射的列至数据库表(目标列名区分大小写)
/// </summary>
/// <param name="dataTable">要导入的dataTable</param>
/// <param name="tableName">目标表名</param>
/// <param name="columnMapping">列映射关系</param>
/// <returns>成功返回success，否则为返回错误消息</returns>
public string FastImportDatatableToDB(DataTable dataTable, string tableName, DataRow[] columnMapping)
{
OracleBulkCopy bulkCopy = new OracleBulkCopy(this._strConnString, OracleBulkCopyOptions.UseInternalTransaction);
bulkCopy.DestinationTableName = tableName;
foreach (DataRow row in columnMapping)
{
bulkCopy.ColumnMappings.Add(row[0].ToString(), row[1].ToString());
}
OpenConn();
bulkCopy.WriteToServer(dataTable);
return "success";
}
public void InsertBulkEmail(List<Email> emailList)
{
string connectionString = OracleConnectionString;
try
{
DataTable smsTable = GetEmailTable(emailList);
OracleBulkCopy bulkCopy = new OracleBulkCopy(connectionString);
bulkCopy.ColumnMappings.Add("Id", "Id");
bulkCopy.ColumnMappings.Add("ToAddress", "ToAddress");
bulkCopy.ColumnMappings.Add("Subject", "Subject");
bulkCopy.ColumnMappings.Add("StatusId", "StatusId");
bulkCopy.ColumnMappings.Add("StatusDate", "StatusDate");
bulkCopy.ColumnMappings.Add("CampaignId", "CampaignId");
bulkCopy.ColumnMappings.Add("Body", "Body");
bulkCopy.DestinationTableName = "EMAIL";
bulkCopy.WriteToServer(smsTable);
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertBulkSMS(List<Sms> smsList)
{
string connectionString = OracleConnectionString;
try
{
DataTable smsTable = GetSmsTable(smsList);
OracleBulkCopy bulkCopy = new OracleBulkCopy(connectionString);
bulkCopy.ColumnMappings.Add("Id", "Id");
bulkCopy.ColumnMappings.Add("ToNumber", "ToNumber");
bulkCopy.ColumnMappings.Add("Content", "Content");
bulkCopy.ColumnMappings.Add("StatusId", "StatusId");
bulkCopy.ColumnMappings.Add("StatusDate", "StatusDate");
bulkCopy.ColumnMappings.Add("CampaignId", "CampaignId");
bulkCopy.DestinationTableName = "SMS";
bulkCopy.WriteToServer(smsTable);
}
catch (Exception ex)
{
throw ex;
}
}
protected void InsertDataToDbBulkMethod(DataTable table)
{
OracleBulkCopy bcopy = new OracleBulkCopy(_conn as OracleConnection);
bcopy.DestinationTableName = table.TableName;
bcopy.WriteToServer(table);
bcopy.Close();
}