/// <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; } }
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); }
public void Dispose() { if (bulkCopy != null) { bulkCopy.Close(); bulkCopy.Dispose(); bulkCopy = null; } }
/// <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); }
/// <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); } }
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); }
/// <summary>use to insert data using bulk method to the table</summary> /// <param name="destinationtablename">the table in which the data will be inserted</param> /// <param name="sourcedatatable"> the datatable contains the data to be inserted</param> /// <param name="columnmapping">column mapping used just in case the column structure between source and destination is different</param> public void DoBulkCopy(string destinationtablename, DataTable sourcedatatable, params OracleBulkCopyColumnMapping[] columnmapping) { try { if (oraCon.State == ConnectionState.Closed) { oraCon.Open(); } doClearOracleParameters(); OracleBulkCopy mybulkcopy = new OracleBulkCopy(oraCon); mybulkcopy.DestinationTableName = destinationtablename; if (columnmapping != null && columnmapping.Length > 0) { for (int i = 0; i < columnmapping.Length; i++) { mybulkcopy.ColumnMappings.Add(columnmapping[i]); } } mybulkcopy.WriteToServer(sourcedatatable); mybulkcopy.Close(); mybulkcopy.Dispose(); clearOracleParameter = true; doClearOracleParameters(); } catch (Exception) { throw; } finally { if (oraCon.State == ConnectionState.Open) { oraCon.Close(); } } }
static void Main(string[] args) { var txt = string.Empty; try { Console.WriteLine($"{DateTime.Now.ToString("HH:mm:ss")} 内存表开始循环组装数据..."); var data = new DataTable(); data.Columns.Add("ID", typeof(decimal)); data.Columns.Add("LOGID", typeof(decimal)); data.Columns.Add("PF", typeof(decimal)); data.Columns.Add("VAL", typeof(double)); data.Columns.Add("ITEMID", typeof(decimal)); data.Columns.Add("SITEID", typeof(decimal)); for (int i = 0; i < 1000000; i++) { var row = data.NewRow(); row["ID"] = i; row["LOGID"] = i; row["PF"] = i; row["VAL"] = i; row["ITEMID"] = i; row["SITEID"] = i; data.Rows.Add(row); } Console.WriteLine($"{DateTime.Now.ToString("HH:mm:ss")} 内存表组装数据完成..."); var st = Stopwatch.StartNew(); Console.WriteLine($"{DateTime.Now.ToString("HH:mm:ss")} 开始往数据库写入数据..."); var connString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= 192.168.0.106)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=HLINK;Password=123456"; var conn = new OracleConnection(connString); conn.Open(); var ocp = new OracleBulkCopy(conn); ocp.BatchSize = 1000000; ocp.BulkCopyOptions = OracleBulkCopyOptions.UseInternalTransaction; ocp.BulkCopyTimeout = 360000; ocp.DestinationTableName = "RESULT_ITEM"; foreach (DataColumn item in data.Columns) { ocp.ColumnMappings.Add(item.ColumnName, item.ColumnName); } ocp.WriteToServer(data); ocp.Close(); ocp.Dispose(); st.Stop(); Console.WriteLine($"{DateTime.Now.ToString("HH:mm:ss")} 数据库写入完成,数据量 {data.Rows.Count},耗时[{st.Elapsed.TotalSeconds}]..."); } catch (Exception ex) { Console.WriteLine(ex); } finally { do { txt = Console.ReadLine(); } while (txt != "EXIT"); } }
//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> /// 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); } }
private void button10_Click(object sender, EventArgs e) { Stopwatch _stopwatch = new Stopwatch(); String _Results; string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;"; try { OracleConnection _connObj = new OracleConnection(_connstring); _connObj.Open(); OracleCommand _cmdObj = _connObj.CreateCommand(); //Clear all products _cmdObj.CommandText = "DELETE FROM Products"; _cmdObj.ExecuteNonQuery(); _cmdObj.CommandText = "INSERT INTO Products(ID, Name, Price) VALUES(:ID, :Name, :Price)"; OracleParameter _IDParam = new OracleParameter("ID", OracleDbType.Varchar2); _cmdObj.Parameters.Add(_IDParam); OracleParameter _nameParam = new OracleParameter("Name", OracleDbType.Varchar2); _cmdObj.Parameters.Add(_nameParam); OracleParameter _priceParam = new OracleParameter("Price", OracleDbType.Decimal); _cmdObj.Parameters.Add(_priceParam); _stopwatch.Start(); for (int i = 1; i <= 50000; i++) { _IDParam.Value = "E" + Convert.ToString(i); _nameParam.Value = "Test Product" + Convert.ToString(i); _priceParam.Value = 100; _cmdObj.ExecuteNonQuery(); } _stopwatch.Stop(); _cmdObj.Dispose(); //Oracle Bulk Copy _Results = "Without Oracle Bulk Copy:\t" + _stopwatch.Elapsed.TotalSeconds.ToString() + " seconds\n"; _cmdObj = _connObj.CreateCommand(); _cmdObj.CommandText = "DELETE FROM Products"; _cmdObj.ExecuteNonQuery(); _cmdObj.Dispose(); DataTable _dataTbl = new DataTable("SourceTable"); _dataTbl.Columns.Add(new DataColumn("ID", System.Type.GetType("System.String"))); _dataTbl.Columns.Add(new DataColumn("Name", System.Type.GetType("System.String"))); _dataTbl.Columns.Add(new DataColumn("Price", System.Type.GetType("System.String"))); _stopwatch.Reset(); _stopwatch.Start(); for (int i = 1; i <= 50000; i++) { DataRow _newrow = _dataTbl.NewRow(); _newrow["ID"] = "E" + Convert.ToString(i); _newrow["Name"] = "Test Product" + Convert.ToString(i); _newrow["Price"] = 100; _dataTbl.Rows.Add(_newrow); } _stopwatch.Stop(); OracleBulkCopy _bulkCopy = new OracleBulkCopy(_connObj); _bulkCopy.DestinationTableName = "Products"; _stopwatch.Start(); _bulkCopy.WriteToServer(_dataTbl); _stopwatch.Stop(); _Results += "With Oracle Bulk Copy:\t" + _stopwatch.Elapsed.TotalSeconds.ToString() + " seconds\n"; _bulkCopy.Close(); _bulkCopy.Dispose(); _bulkCopy = null; MessageBox.Show(_Results); _connObj.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }