public override void WriteToServer(string destinationTableName, DataTable table, int batchSize = 0, params BulkCopyColumnMapping[] columnMappings) { OracleBulkCopy bulkCopy = null; try { if (DbTransaction != null) { bulkCopy = new OracleBulkCopy((OracleConnection)DbTransaction.Connection, OracleBulkCopyOptions.Default); } else { bulkCopy = new OracleBulkCopy(_connectionString); } bulkCopy.DestinationTableName = destinationTableName; if (batchSize != 0) { bulkCopy.BatchSize = batchSize; } if (BulkCopyTimeout != (int)CommandTimeoutValue.None) { bulkCopy.BulkCopyTimeout = BulkCopyTimeout; } if (columnMappings != null) { foreach (var item in columnMappings) { var mapping = new OracleBulkCopyColumnMapping(); if (item.DestinationColumn != null) mapping.DestinationColumn = item.DestinationColumn; if (item.DestinationOrdinal != -1) mapping.DestinationOrdinal = item.DestinationOrdinal; if (item.SourceColumn != null) mapping.SourceColumn = item.SourceColumn; if (item.SourceOrdinal != -1) mapping.SourceOrdinal = item.SourceOrdinal; bulkCopy.ColumnMappings.Add(mapping); } } bulkCopy.WriteToServer(table); } finally { if (bulkCopy != null) { bulkCopy.Close(); } } }
/// <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); } }
void CopyTo(DbConnection con) { using (OracleBulkCopy obc = new OracleBulkCopy(con as OracleConnection)) { } }
/*sysno, stocksysno, deliveryusersysno, paidamount, codamount, status, isenforceallow, deliverytypesysno, * createdby, createddate, lastupdateby, lastupdatedate, stamp*/ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("stocksysno", "stocksysno"); bcp.ColumnMappings.Add("deliveryusersysno", "deliveryusersysno"); bcp.ColumnMappings.Add("paidamount", "paidamount"); bcp.ColumnMappings.Add("codamount", "codamount"); bcp.ColumnMappings.Add("status", "status"); bcp.ColumnMappings.Add("isenforceallow", "isenforceallow"); bcp.ColumnMappings.Add("deliverytypesysno", "deliverytypesysno"); bcp.ColumnMappings.Add("createdby", "createdby"); bcp.ColumnMappings.Add("createddate", "createddate"); bcp.ColumnMappings.Add("lastupdateby", "lastupdateby"); bcp.ColumnMappings.Add("lastupdatedate", "lastupdatedate"); bcp.ColumnMappings.Add("stamp", "stamp"); }
/* SysNo, AreaSysNo, CustomerSysNo, Title, Name, Gender, PhoneNumber, * MobilePhoneNumber, FaxNumber, EmailAddress, StreetAddress, ZipCode, IsDefault */ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("SysNo", "SysNo"); bcp.ColumnMappings.Add("AreaSysNo", "AreaSysNo"); bcp.ColumnMappings.Add("CustomerSysNo", "CustomerSysNo"); bcp.ColumnMappings.Add("Title", "Title"); bcp.ColumnMappings.Add("Name", "Name"); bcp.ColumnMappings.Add("Gender", "Gender"); bcp.ColumnMappings.Add("PhoneNumber", "PhoneNumber"); bcp.ColumnMappings.Add("MobilePhoneNumber", "MobilePhoneNumber"); bcp.ColumnMappings.Add("FaxNumber", "FaxNumber"); bcp.ColumnMappings.Add("EmailAddress", "EmailAddress"); bcp.ColumnMappings.Add("StreetAddress", "StreetAddress"); bcp.ColumnMappings.Add("ZipCode", "ZipCode"); bcp.ColumnMappings.Add("IsDefault", "IsDefault"); }
/*SysNo, InvoiceTypeSysNo, InvoiceCode, InvoiceNo, InvoiceAmount, InvoiceRemarks, * InvoiceTitle, Status, CreatedDate, CreatedBy, LastUpdateBy, * LastUpdateDate, TransactionSysNo*/ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("SysNo", "SysNo"); bcp.ColumnMappings.Add("InvoiceTypeSysNo", "InvoiceTypeSysNo"); bcp.ColumnMappings.Add("InvoiceCode", "InvoiceCode"); bcp.ColumnMappings.Add("InvoiceNo", "InvoiceNo"); bcp.ColumnMappings.Add("InvoiceAmount", "InvoiceAmount"); bcp.ColumnMappings.Add("InvoiceRemarks", "InvoiceRemarks"); bcp.ColumnMappings.Add("InvoiceTitle", "InvoiceTitle"); bcp.ColumnMappings.Add("Status", "Status"); bcp.ColumnMappings.Add("createddate", "createddate"); bcp.ColumnMappings.Add("CreatedBy", "CreatedBy"); bcp.ColumnMappings.Add("lastupdateby", "lastupdateby"); bcp.ColumnMappings.Add("lastupdatedate", "lastupdatedate"); bcp.ColumnMappings.Add("TransactionSysNo", "TransactionSysNo"); }
/*SysNo, Source, SourceSysNo, Amount, PaymentTypeSysNo, VoucherNo, Status, CreatedBy, CreatedDate, Operator, OperatedDate, LastUpdateBy, LastUpdateDate */ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("Source", "Source"); bcp.ColumnMappings.Add("SourceSysNo", "SourceSysNo"); bcp.ColumnMappings.Add("Amount", "Amount"); bcp.ColumnMappings.Add("PaymentTypeSysNo", "PaymentTypeSysNo"); bcp.ColumnMappings.Add("VoucherNo", "VoucherNo"); bcp.ColumnMappings.Add("Status", "Status"); bcp.ColumnMappings.Add("CreatedBy", "CreatedBy"); bcp.ColumnMappings.Add("CreatedDate", "CreatedDate"); bcp.ColumnMappings.Add("Operator", "Operator"); bcp.ColumnMappings.Add("OperatedDate", "OperatedDate"); bcp.ColumnMappings.Add("LastUpdateBy", "LastUpdateBy"); bcp.ColumnMappings.Add("LastUpdateDate", "LastUpdateDate"); }
/*sysno,transactionsysno, warehousesysno, sourcetype, sourcesysno, deliverytype, remarks, status, * createdby, createddate, lastupdateby, lastupdatedate, isprinted, stamp*/ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("transactionsysno", "transactionsysno"); bcp.ColumnMappings.Add("warehousesysno", "warehousesysno"); bcp.ColumnMappings.Add("sourcetype", "sourcetype"); bcp.ColumnMappings.Add("sourcesysno", "sourcesysno"); bcp.ColumnMappings.Add("deliverytype", "deliverytype"); bcp.ColumnMappings.Add("remarks", "remarks"); bcp.ColumnMappings.Add("status", "status"); bcp.ColumnMappings.Add("createdby", "createdby"); bcp.ColumnMappings.Add("createddate", "createddate"); bcp.ColumnMappings.Add("lastupdateby", "lastupdateby"); bcp.ColumnMappings.Add("lastupdatedate", "lastupdatedate"); bcp.ColumnMappings.Add("isprinted", "isprinted"); bcp.ColumnMappings.Add("stamp", "stamp"); }
protected virtual void Dispose(bool disposing) { if (disposed) { return; } if (disposing) { if (bulkCopy != null) { bulkCopy.Close(); bulkCopy = null; } } disposed = true; }
/*sysno, promotionsysno, title, starttime, endtime, comboquantity, salequantity, status, auditorsysno, * auditdate, createdby, createddate, lastupdateby, lastupdatedate*/ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("promotionsysno", "promotionsysno"); bcp.ColumnMappings.Add("title", "title"); bcp.ColumnMappings.Add("starttime", "starttime"); bcp.ColumnMappings.Add("endtime", "endtime"); bcp.ColumnMappings.Add("comboquantity", "comboquantity"); bcp.ColumnMappings.Add("salequantity", "salequantity"); bcp.ColumnMappings.Add("status", "status"); bcp.ColumnMappings.Add("auditorsysno", "auditorsysno"); bcp.ColumnMappings.Add("auditdate", "auditdate"); bcp.ColumnMappings.Add("createdby", "createdby"); bcp.ColumnMappings.Add("createddate", "createddate"); bcp.ColumnMappings.Add("lastupdateby", "lastupdateby"); bcp.ColumnMappings.Add("lastupdatedate", "lastupdatedate"); }
/*SysNo, TransactionSysNo, IncomeType, Source, SourceSysNo, IncomeAmount, ReceivedAmount, Status, Remark, CreatedBy, CreatedDate, ConfirmedBy, ConfirmedDate, * LastUpdateBy, LastUpdateDate*/ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("TransactionSysNo", "TransactionSysNo"); bcp.ColumnMappings.Add("IncomeType", "IncomeType"); bcp.ColumnMappings.Add("Source", "Source"); bcp.ColumnMappings.Add("SourceSysNo", "SourceSysNo"); bcp.ColumnMappings.Add("IncomeAmount", "IncomeAmount"); bcp.ColumnMappings.Add("ReceivedAmount", "ReceivedAmount"); bcp.ColumnMappings.Add("Status", "Status"); bcp.ColumnMappings.Add("Remark", "Remark"); bcp.ColumnMappings.Add("CreatedBy", "CreatedBy"); bcp.ColumnMappings.Add("CreatedDate", "CreatedDate"); bcp.ColumnMappings.Add("ConfirmedBy", "ConfirmedBy"); bcp.ColumnMappings.Add("ConfirmedDate", "ConfirmedDate"); bcp.ColumnMappings.Add("LastUpdateBy", "LastUpdateBy"); bcp.ColumnMappings.Add("LastUpdateDate", "LastUpdateDate"); }
/*sysno, attributesysno, productsysno,attributename, AttributeGroupSysNo, attributeoptionsysno, attributetext, * attributeimage, attributetype, displayorder, status, createdby, createddate, lastupdateby, lastupdatedate*/ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("attributesysno", "attributesysno"); bcp.ColumnMappings.Add("productsysno", "productsysno"); bcp.ColumnMappings.Add("attributename", "attributename"); bcp.ColumnMappings.Add("AttributeGroupSysNo", "AttributeGroupSysNo"); bcp.ColumnMappings.Add("attributeoptionsysno", "attributeoptionsysno"); bcp.ColumnMappings.Add("attributetext", "attributetext"); bcp.ColumnMappings.Add("attributeimage", "attributeimage"); bcp.ColumnMappings.Add("attributetype", "attributetype"); bcp.ColumnMappings.Add("displayorder", "displayorder"); bcp.ColumnMappings.Add("status", "status"); bcp.ColumnMappings.Add("createdby", "createdby"); bcp.ColumnMappings.Add("createddate", "createddate"); bcp.ColumnMappings.Add("lastupdateby", "lastupdateby"); bcp.ColumnMappings.Add("lastupdatedate", "lastupdatedate"); }
/**/ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("WarehouseSysNo", "WarehouseSysNo"); bcp.ColumnMappings.Add("deliveryusersysno", "deliveryusersysno"); bcp.ColumnMappings.Add("totalamount", "totalamount"); bcp.ColumnMappings.Add("paidamount", "paidamount"); bcp.ColumnMappings.Add("codamount", "codamount"); bcp.ColumnMappings.Add("status", "status"); bcp.ColumnMappings.Add("remarks", "remarks"); bcp.ColumnMappings.Add("auditorsysno", "auditorsysno"); bcp.ColumnMappings.Add("createdby", "createdby"); bcp.ColumnMappings.Add("createddate", "createddate"); bcp.ColumnMappings.Add("lastupdateby", "lastupdateby"); bcp.ColumnMappings.Add("lastupdatedate", "lastupdatedate"); bcp.ColumnMappings.Add("stamp", "stamp"); bcp.ColumnMappings.Add("auditdate", "auditdate"); }
/// <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(); } }
/* SysNo, ReceiptVoucherSysNo, TransactionSysNo, PaymentTypeSysNo, Amount, * VoucherNo, CreditCardNumber, ReceivablesSideType, ReceivablesSideSysNo, EasReceiptCode, * Status, CreatedBy, CreatedDate, LastUpdateBy, LastUpdateDate*/ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("SysNo", "SysNo"); bcp.ColumnMappings.Add("ReceiptVoucherSysNo", "ReceiptVoucherSysNo"); bcp.ColumnMappings.Add("TransactionSysNo", "TransactionSysNo"); bcp.ColumnMappings.Add("PaymentTypeSysNo", "PaymentTypeSysNo"); bcp.ColumnMappings.Add("Amount", "Amount"); bcp.ColumnMappings.Add("VoucherNo", "VoucherNo"); bcp.ColumnMappings.Add("CreditCardNumber", "CreditCardNumber"); bcp.ColumnMappings.Add("ReceivablesSideType", "ReceivablesSideType"); bcp.ColumnMappings.Add("ReceivablesSideSysNo", "ReceivablesSideSysNo"); bcp.ColumnMappings.Add("EasReceiptCode", "EasReceiptCode"); bcp.ColumnMappings.Add("Status", "Status"); bcp.ColumnMappings.Add("CreatedBy", "CreatedBy"); bcp.ColumnMappings.Add("CreatedDate", "CreatedDate"); bcp.ColumnMappings.Add("LastUpdateBy", "LastUpdateBy"); bcp.ColumnMappings.Add("LastUpdateDate", "LastUpdateDate"); }
/* SysNo, GroupSysNo, Name, Content, ImageUrl, LinkUrl, LinkTitle, OpenType, BeginDate, EndDate, DisplayOrder, Status, CreatedBy, CreatedDate, LastUpdateBy, LastUpdateDate */ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("SysNo", "SysNo"); bcp.ColumnMappings.Add("GroupSysNo", "GroupSysNo"); bcp.ColumnMappings.Add("Name", "Name"); bcp.ColumnMappings.Add("Content", "Content"); bcp.ColumnMappings.Add("ImageUrl", "ImageUrl"); bcp.ColumnMappings.Add("LinkUrl", "LinkUrl"); bcp.ColumnMappings.Add("LinkTitle", "LinkTitle"); bcp.ColumnMappings.Add("OpenType", "OpenType"); bcp.ColumnMappings.Add("BeginDate", "BeginDate"); bcp.ColumnMappings.Add("EndDate", "EndDate"); bcp.ColumnMappings.Add("DisplayOrder", "DisplayOrder"); bcp.ColumnMappings.Add("Status", "Status"); bcp.ColumnMappings.Add("CreatedBy", "CreatedBy"); bcp.ColumnMappings.Add("CreatedDate", "CreatedDate"); bcp.ColumnMappings.Add("LastUpdateBy", "LastUpdateBy"); bcp.ColumnMappings.Add("LastUpdateDate", "LastUpdateDate"); }
//sysno,parentsysno, pickuptypename, pickuptypedescription, pickuplevel, pickuptime, //traceurl,displayorder, provider, isonlinevisible, freight, status, //createdby, createddate, lastupdateby, lastupdatedate public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("parentsysno", "parentsysno"); bcp.ColumnMappings.Add("pickuptypename", "pickuptypename"); bcp.ColumnMappings.Add("pickuptypedescription", "pickuptypedescription"); bcp.ColumnMappings.Add("pickuplevel", "pickuplevel"); bcp.ColumnMappings.Add("pickuptime", "pickuptime"); bcp.ColumnMappings.Add("traceurl", "traceurl"); bcp.ColumnMappings.Add("displayorder", "displayorder"); bcp.ColumnMappings.Add("provider", "provider"); bcp.ColumnMappings.Add("isonlinevisible", "isonlinevisible"); bcp.ColumnMappings.Add("freight", "freight"); bcp.ColumnMappings.Add("status", "status"); bcp.ColumnMappings.Add("createdby", "createdby"); bcp.ColumnMappings.Add("createddate", "createddate"); bcp.ColumnMappings.Add("lastupdateby", "lastupdateby"); bcp.ColumnMappings.Add("lastupdatedate", "lastupdatedate"); }
/// <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 SetColumnMapping(OracleBulkCopy bcp) { //sysno,levelname,leveldescription,lowerlimit,upperlimit,canpayforproduct,productpaymentupperlimit,productpaymentpercentage, //canpayforservice,servicepaymentupperlimit, servicepaymentpercentage, createdby,createddate, lastupdateby, lastupdatedate bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("levelname", "levelname"); bcp.ColumnMappings.Add("leveldescription", "leveldescription"); bcp.ColumnMappings.Add("lowerlimit", "lowerlimit"); bcp.ColumnMappings.Add("upperlimit", "upperlimit"); bcp.ColumnMappings.Add("canpayforproduct", "canpayforproduct"); bcp.ColumnMappings.Add("productpaymentupperlimit", "productpaymentupperlimit"); bcp.ColumnMappings.Add("productpaymentpercentage", "productpaymentpercentage"); bcp.ColumnMappings.Add("canpayforservice", "canpayforservice"); bcp.ColumnMappings.Add("servicepaymentupperlimit", "servicepaymentupperlimit"); bcp.ColumnMappings.Add("servicepaymentpercentage", "servicepaymentpercentage"); bcp.ColumnMappings.Add("createdby", "createdby"); bcp.ColumnMappings.Add("createddate", "createddate"); bcp.ColumnMappings.Add("LastUpdateBy", "LastUpdateBy"); bcp.ColumnMappings.Add("LastUpdateDate", "LastUpdateDate"); }
private OracleBulkCopy GetBulkCopyInstance() { if (this.Context.Ado.Connection.State == ConnectionState.Closed) { this.Context.Ado.Connection.Open(); } OracleBulkCopy copy; if (this.Context.Ado.Transaction == null) { copy = new OracleBulkCopy((OracleConnection)this.Context.Ado.Connection, Oracle.ManagedDataAccess.Client.OracleBulkCopyOptions.Default); } else { copy = new OracleBulkCopy((OracleConnection)this.Context.Ado.Connection, OracleBulkCopyOptions.UseInternalTransaction); } return(copy); }
/*sysno, erpcode, erprmacode, warehousename, areasysno, streetaddress, contact, phone, status, * warehousetype, latitude, longitude, imgurl, createdby, createddate, lastupdateby, lastupdatedate*/ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("erpcode", "erpcode"); bcp.ColumnMappings.Add("erprmacode", "erprmacode"); bcp.ColumnMappings.Add("warehousename", "warehousename"); bcp.ColumnMappings.Add("areasysno", "areasysno"); bcp.ColumnMappings.Add("streetaddress", "streetaddress"); bcp.ColumnMappings.Add("contact", "contact"); bcp.ColumnMappings.Add("phone", "phone"); bcp.ColumnMappings.Add("status", "status"); bcp.ColumnMappings.Add("warehousetype", "warehousetype"); bcp.ColumnMappings.Add("latitude", "latitude"); bcp.ColumnMappings.Add("longitude", "longitude"); bcp.ColumnMappings.Add("imgurl", "imgurl"); bcp.ColumnMappings.Add("createdby", "createdby"); bcp.ColumnMappings.Add("createddate", "createddate"); bcp.ColumnMappings.Add("lastupdateby", "lastupdateby"); bcp.ColumnMappings.Add("lastupdatedate", "lastupdatedate"); }
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); }
/*SysNo, TransactionSysNo, Source, SourceSysNo, PayableAmount, PaidAmount, CustomerSysNo, RefundBank, RefundAccountName, RefundAccount, Status, Remarks, CreatedDate, CreatedBy, PayerSysNo, PayDate, LastUpdateBy, LastUpdateDate */ /*SysNo, TransactionSysNo, Source, SourceSysNo, PayableAmount, * PaidAmount, CustomerSysNo, RefundBank, RefundAccountName, RefundAccount, * Status, Remarks, CreatedDate, CreatedBy, PayerSysNo, PayDate, LastUpdateBy, LastUpdateDate */ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("TransactionSysNo", "TransactionSysNo"); bcp.ColumnMappings.Add("Source", "Source"); bcp.ColumnMappings.Add("SourceSysNo", "SourceSysNo"); bcp.ColumnMappings.Add("PayableAmount", "PayableAmount"); bcp.ColumnMappings.Add("PaidAmount", "PaidAmount"); bcp.ColumnMappings.Add("CustomerSysNo", "CustomerSysNo"); bcp.ColumnMappings.Add("RefundBank", "RefundBank"); bcp.ColumnMappings.Add("RefundAccountName", "RefundAccountName"); bcp.ColumnMappings.Add("RefundAccount", "RefundAccount"); bcp.ColumnMappings.Add("Status", "Status"); bcp.ColumnMappings.Add("Remarks", "Remarks"); bcp.ColumnMappings.Add("CreatedDate", "CreatedDate"); bcp.ColumnMappings.Add("CreatedBy", "CreatedBy"); bcp.ColumnMappings.Add("PayerSysNo", "PayerSysNo"); bcp.ColumnMappings.Add("PayDate", "PayDate"); bcp.ColumnMappings.Add("LastUpdateBy", "LastUpdateBy"); bcp.ColumnMappings.Add("LastUpdateDate", "LastUpdateDate"); }
/*SysNo, DeliverySysNo, TransactionSysNo, NoteType, NoteSysNo, IsCOD, * StockOutAmount, Receivable, PaymentType, PayNo, AddressSysNo, ExpressNo, Status, Remarks, CreatedBy, CreatedDate, LastUpdateBy, LastUpdateDate*/ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("SysNo", "SysNo"); bcp.ColumnMappings.Add("DeliverySysNo", "DeliverySysNo"); bcp.ColumnMappings.Add("TransactionSysNo", "TransactionSysNo"); bcp.ColumnMappings.Add("NoteType", "NoteType"); bcp.ColumnMappings.Add("NoteSysNo", "NoteSysNo"); bcp.ColumnMappings.Add("IsCOD", "IsCOD"); bcp.ColumnMappings.Add("StockOutAmount", "StockOutAmount"); bcp.ColumnMappings.Add("Receivable", "Receivable"); bcp.ColumnMappings.Add("PaymentType", "PaymentType"); bcp.ColumnMappings.Add("PayNo", "PayNo"); bcp.ColumnMappings.Add("AddressSysNo", "AddressSysNo"); bcp.ColumnMappings.Add("ExpressNo", "ExpressNo"); bcp.ColumnMappings.Add("Status", "Status"); bcp.ColumnMappings.Add("Remarks", "Remarks"); bcp.ColumnMappings.Add("CreatedBy", "CreatedBy"); bcp.ColumnMappings.Add("CreatedDate", "CreatedDate"); bcp.ColumnMappings.Add("LastUpdateBy", "LastUpdateBy"); bcp.ColumnMappings.Add("LastUpdateDate", "LastUpdateDate"); }
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 override void SetColumnMapping(OracleBulkCopy bcp) { //SYSNO, PARENTSYSNO, CATEGORYNAME, CODE, SEOTITLE, SEOKEYWORD, SEODESCRIPTION, TEMPLATESYSNO, ISONLINE, DISPLAYORDER, REMARKS, CREATEDBY, CREATEDDATE, LASTUPDATEBY, LASTUPDATEDATE, STATUS bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("ParentSysNo", "ParentSysNo"); bcp.ColumnMappings.Add("CategoryName", "CategoryName"); bcp.ColumnMappings.Add("Code", "Code"); bcp.ColumnMappings.Add("CategoryImage", "CategoryImage"); bcp.ColumnMappings.Add("SeoTitle", "SeoTitle"); bcp.ColumnMappings.Add("SeoKeyword", "SeoKeyword"); bcp.ColumnMappings.Add("SeoDescription", "SeoDescription"); bcp.ColumnMappings.Add("templatesysno", "templatesysno"); bcp.ColumnMappings.Add("ISONLINE", "ISONLINE"); bcp.ColumnMappings.Add("DisplayOrder", "DisplayOrder"); bcp.ColumnMappings.Add("remarks", "remarks"); bcp.ColumnMappings.Add("createdby", "createdby"); bcp.ColumnMappings.Add("CreatedDate", "CreatedDate"); bcp.ColumnMappings.Add("LastUpdateBy", "LastUpdateBy"); bcp.ColumnMappings.Add("LastUpdateDate", "LastUpdateDate"); bcp.ColumnMappings.Add("status", "status"); }
public override void SetColumnMapping(OracleBulkCopy bcp) { //sysno,paymentname, paymentdescription, isonlinepay, isonlinevisible, //paymenttype, displayorder, createdby, createddate, lastupdateby, lastupdatedate, //status, requiredcardnumber bcp.ColumnMappings.Add("sysno", "sysno"); bcp.ColumnMappings.Add("paymentname", "paymentname"); bcp.ColumnMappings.Add("paymentdescription", "paymentdescription"); bcp.ColumnMappings.Add("isonlinepay", "isonlinepay"); bcp.ColumnMappings.Add("isonlinevisible", "isonlinevisible"); bcp.ColumnMappings.Add("paymenttype", "paymenttype"); bcp.ColumnMappings.Add("displayorder", "displayorder"); bcp.ColumnMappings.Add("createdby", "createdby"); bcp.ColumnMappings.Add("createddate", "createddate"); bcp.ColumnMappings.Add("lastupdateby", "lastupdateby"); bcp.ColumnMappings.Add("lastupdatedate", "lastupdatedate"); bcp.ColumnMappings.Add("status", "status"); bcp.ColumnMappings.Add("requiredcardnumber", "requiredcardnumber"); }
public async Task PopulateAsync(DbSchema[] schemas) { using (var conn = new OracleConnection(Connection.ConnectionString)) { await conn.OpenAsync(); this.Logger.Information($"Opened connection to {conn.Database} on {conn.DataSource}"); try { foreach (var schema in schemas) { var cleanCmd = new OracleCommand($"TRUNCATE TABLE {schema.TableName}", conn); await cleanCmd.ExecuteNonQueryAsync(); this.Logger.Information($"table {schema.TableName} truncated"); using (var bulkCopy = new OracleBulkCopy(conn, OracleBulkCopyOptions.Default)) { bulkCopy.DestinationTableName = $"{schema.TableName}"; foreach (var col in schema.Columns) { bulkCopy.ColumnMappings.Add(col.Name, col.Name); } bulkCopy.WriteToServer(schema.Records); } this.Logger.Information($"{schema.Records.Rows.Count} records have been imported into {schema.TableName}"); } this.Logger.Information($"All transactions have been committed."); } catch (Exception ex) { this.Logger.Error(ex, $"An error occured while doing operations."); throw; } await conn.CloseAsync(); } }
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 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; } }
/*SysNo, StockOutSysNo, TransactionSysNo, OrderSysNo, OrderItemSysNo, * ProductSysNo, ProductName, ProductQuantity, ReturnQuantity, Weight, * Measurement, OriginalPrice, RealSalesAmount, Remarks, Status, * CreatedBy, CreatedDate, LastUpdateBy, LastUpdateDate*/ public override void SetColumnMapping(OracleBulkCopy bcp) { bcp.ColumnMappings.Add("SysNo", "SysNo"); bcp.ColumnMappings.Add("StockOutSysNo", "StockOutSysNo"); bcp.ColumnMappings.Add("TransactionSysNo", "TransactionSysNo"); bcp.ColumnMappings.Add("OrderSysNo", "OrderSysNo"); bcp.ColumnMappings.Add("OrderItemSysNo", "OrderItemSysNo"); bcp.ColumnMappings.Add("ProductSysNo", "ProductSysNo"); bcp.ColumnMappings.Add("ProductName", "ProductName"); bcp.ColumnMappings.Add("ProductQuantity", "ProductQuantity"); bcp.ColumnMappings.Add("ReturnQuantity", "ReturnQuantity"); bcp.ColumnMappings.Add("Weight", "Weight"); bcp.ColumnMappings.Add("Measurement", "Measurement"); bcp.ColumnMappings.Add("OriginalPrice", "OriginalPrice"); bcp.ColumnMappings.Add("RealSalesAmount", "RealSalesAmount"); bcp.ColumnMappings.Add("Remarks", "Remarks"); bcp.ColumnMappings.Add("Status", "Status"); bcp.ColumnMappings.Add("CreatedBy", "CreatedBy"); bcp.ColumnMappings.Add("CreatedDate", "CreatedDate"); bcp.ColumnMappings.Add("LastUpdateBy", "LastUpdateBy"); bcp.ColumnMappings.Add("LastUpdateDate", "LastUpdateDate"); }
public bool GuardarMasivo(DataTable dt, string dtName, OracleConnection db) { bool seGuardo = false; try { db.Open(); using (OracleBulkCopy bulkCopy = new OracleBulkCopy(db)) { bulkCopy.DestinationTableName = dtName; bulkCopy.WriteToServer(dt); } db.Close(); seGuardo = true; } catch (Exception ex) { Log.Error(ex); } return(seGuardo); }
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 OracleBulkCopyWrapper(DbConnection conn) { OracleConnection sqlConn = conn as OracleConnection; bulkCopy = new OracleBulkCopy(sqlConn); this.ColumnMappings = new Dictionary<string, string>(); }
//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 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); } }
public OracleInsightBulkCopy(OracleBulkCopy bulkCopy) { if (bulkCopy == null) throw new ArgumentNullException("bulkCopy"); _bulkCopy = bulkCopy; _bulkCopy.OracleRowsCopied += OnRowsCopied; }
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; } }
protected void InsertDataToDbBulkMethod(DataTable table) { OracleBulkCopy bcopy = new OracleBulkCopy(_conn as OracleConnection); bcopy.DestinationTableName = table.TableName; bcopy.WriteToServer(table); bcopy.Close(); }
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; } }