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); } }