public override void Insert(System.Data.DataTable data, string toTable)
{
DB2BulkCopy bulk;
if (this._s == DBStatus.Begin_Trans)
{
bulk = new DB2BulkCopy(conn, DB2BulkCopyOptions.Default);
}
else
{
bulk = new DB2BulkCopy(conn);
}
try
{
bulk.DestinationTableName = toTable;
bulk.WriteToServer(data);
}
catch (Exception ex)
{
throw ex;
}
finally
{
bulk.Close();
}
}
public Db2Bulk(string ConnectionString, int timeout = 1800,
BulkCopyOptions option = BulkCopyOptions.KeepIdentity)
{
this.Option = option;
this.ConnectionString = ConnectionString;
bulkCopy = CreatedBulkCopy(option);
bulkCopy.BulkCopyTimeout = timeout;
}
public DB2InsightBulkCopy(DB2BulkCopy bulkCopy)
{
if (bulkCopy == null)
{
throw new ArgumentNullException("bulkCopy");
}
_bulkCopy = bulkCopy;
_bulkCopy.DB2RowsCopied += OnRowsCopied;
}
public Db2Bulk(IDbConnection dbConnection, int timeout = 1800,
BulkCopyOptions option = BulkCopyOptions.KeepIdentity)
{
this.Option = option;
this.ConnectionString = ConnectionString;
DB2Connection oracleConnection = (DB2Connection)dbConnection;
bulkCopy = new DB2BulkCopy(oracleConnection, (DB2BulkCopyOptions)option);
bulkCopy.BulkCopyTimeout = timeout;
}
public void IBMBuldCopy()
{
string sql_bruv_1 = "Server=10.24.1.202:446;Database=BRUVDB4V;UID=ATWO;PWD=24rete31;Max Pool Size=100;Min Pool Size=10;";
string sql_frymek = "Server=10.27.5.197:50000;Database=BRUV;UID=db2admin;PWD=db2pass1234!@#$;Max Pool Size=100;Min Pool Size=10;";
try
{
using (DB2Connection myConn = new DB2Connection(sql_bruv_1))
{
using (DB2Connection conn = new DB2Connection(sql_frymek))
{
myConn.Open();
conn.Open();
log.InfoFormat("{0}: Time elapsed: {1}", Table, DateTime.Now);
string myInsertQuery = String.Format("SELECT * FROM RREV.{0}", Table);
using (DB2Command myDB2Command = new DB2Command(myInsertQuery, myConn))
{
using (DB2DataReader reader = myDB2Command.ExecuteReader())
{
using (DB2BulkCopy salesCopy = new DB2BulkCopy(conn))
{
salesCopy.DestinationTableName = String.Format("ATWO.{0}", Table);
salesCopy.WriteToServer(reader);
var errors = salesCopy.Errors;
if (errors.Count > 0)
{
log.ErrorFormat("table:{0}, errors:{1}", Table, errors.Count);
foreach (var er in errors)
{
log.ErrorFormat("table:{0}, msg:{1}", Table, er.ToString());
}
}
salesCopy.Close();
myConn.Close();
conn.Close();
}
}
}
}
}
log.InfoFormat("{0}: Time elapsed: {1}", Table, DateTime.Now);
}
catch (Exception ex)
{
log.Error(ex.ToString());
Console.WriteLine(String.Format("error: {0} with exception: {1}", Table, ex.Message));
}
}
public override bool Import(DataTable Data, ODAParameter[] Prms)
{
DB2BulkCopy bulkcopy = null;
IDbConnection conn = null;
DataTable ImportData = Data.Copy();
try
{
if (this.Transaction == null)
{
conn = this.GetConnection();
bulkcopy = new DB2BulkCopy((DB2Connection)conn);
}
else
{
bulkcopy = new DB2BulkCopy((DB2Connection)this.Transaction.Connection, DB2BulkCopyOptions.Default);
}
for (int i = 0; i < Prms.Length; i++)
{
if (ImportData.Columns.Contains(Prms[i].ParamsName))
{
DB2BulkCopyColumnMapping colMap = new DB2BulkCopyColumnMapping(Prms[i].ParamsName, Prms[i].ParamsName);
bulkcopy.ColumnMappings.Add(colMap);
}
}
bulkcopy.BulkCopyTimeout = 600000;
//需要操作的数据库表名
bulkcopy.DestinationTableName = ImportData.TableName;
//将内存表表写入
bulkcopy.WriteToServer(ImportData);
return(true);
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
if (bulkcopy != null)
{
bulkcopy.Close();
bulkcopy = null;
}
}
}
protected virtual void Dispose(bool disposing)
{
if (disposed)
{
return;
}
if (disposing)
{
if (bulkCopy != null)
{
bulkCopy.Close();
bulkCopy = null;
}
}
disposed = 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 (reader == null)
{
throw new ArgumentNullException("reader");
}
if (transaction != null)
{
throw new ArgumentException("DB2Provider does not support external transactions for bulk copy", "transaction");
}
DB2BulkCopyOptions db2Options = DB2BulkCopyOptions.Default;
if (options.HasFlag(InsightBulkCopyOptions.KeepIdentity))
{
db2Options |= DB2BulkCopyOptions.KeepIdentity;
}
if (options.HasFlag(InsightBulkCopyOptions.TableLock))
{
db2Options |= DB2BulkCopyOptions.TableLock;
}
if (options.HasFlag(InsightBulkCopyOptions.Truncate))
{
db2Options |= DB2BulkCopyOptions.Truncate;
}
using (var bulk = new DB2BulkCopy((DB2Connection)connection, db2Options))
using (var insightBulk = new DB2InsightBulkCopy(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);
}
}
public static void PushToDatabase(DataTable dt, DB2Connection conn)
{
try
{
using (DB2BulkCopy bulkCopy = new DB2BulkCopy(conn, DB2BulkCopyOptions.TableLock))
{
bulkCopy.BulkCopyTimeout = 100000000;
// bulkCopy.NotifyAfter = 1000;
//bulkCopy.DB2RowsCopied += callme;
// bulkCopy.DestinationTableName = "DASH11634.NLP_YELP_REVIEWS";
bulkCopy.DestinationTableName = "NLP_LANGUAGE_FILTERED_REVIEWS";
bulkCopy.WriteToServer(dt);
foreach (var error in bulkCopy.Errors)
{
Console.WriteLine(error);
}
}
}
catch (Exception ex)
{
}
}
public DB2InsightBulkCopy(DB2BulkCopy bulkCopy)
{
if (bulkCopy == null) throw new ArgumentNullException("bulkCopy");
_bulkCopy = bulkCopy;
_bulkCopy.DB2RowsCopied += OnRowsCopied;
}
/// <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");
DB2BulkCopyOptions db2Options = DB2BulkCopyOptions.Default;
if (options.HasFlag(InsightBulkCopyOptions.KeepIdentity))
db2Options |= DB2BulkCopyOptions.KeepIdentity;
if (options.HasFlag(InsightBulkCopyOptions.TableLock))
db2Options |= DB2BulkCopyOptions.TableLock;
if (options.HasFlag(InsightBulkCopyOptions.Truncate))
db2Options |= DB2BulkCopyOptions.Truncate;
using (var bulk = new DB2BulkCopy((DB2Connection)connection, db2Options))
using (var insightBulk = new DB2InsightBulkCopy(bulk))
{
bulk.DestinationTableName = tableName;
if (configure != null)
configure(insightBulk);
bulk.WriteToServer(reader);
}
}
/// <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");
DB2BulkCopyOptions db2Options = DB2BulkCopyOptions.Default;
if (options.HasFlag(InsightBulkCopyOptions.KeepIdentity))
db2Options |= DB2BulkCopyOptions.KeepIdentity;
if (options.HasFlag(InsightBulkCopyOptions.TableLock))
db2Options |= DB2BulkCopyOptions.TableLock;
if (options.HasFlag(InsightBulkCopyOptions.Truncate))
db2Options |= DB2BulkCopyOptions.Truncate;
using (var bulk = new DB2BulkCopy((DB2Connection)connection, db2Options))
using (var insightBulk = new DB2InsightBulkCopy(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);
}
}
