public void WriteToServer(DataTable dt, int batchSize = 10240)
{
InitBulkCopy(dt, batchSize);
bulkCopy.WriteToServer(dt);
if (bulkCopy.Errors.Count > 0)
{
throw new Exception(string.Format("入库失败条数:{0}信息;{1}", bulkCopy.Errors.Count, bulkCopy.Errors[0].Message));
}
}
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));
}
}
/// <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)
{
}
}
/// <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);
}
}
