An OracleBulkCopy object efficiently bulk loads or copies data into an Oracle table from another data source.
System.Object
System.OracleBulkCopy
// C# public sealed class OracleBulkCopy : IDisposable
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
The OracleBulkCopy class can be used to write data to Oracle database tables only. However, the data source is not limited to Oracle databases; any data source can be used, as long as the data can be loaded to a DataTable instance or read with an IDataReader instance.
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
ODP.NET Version: ODP.NET for .NET Framework 2.0 or ODP.NET for .NET Framework 4
See Also:
OracleBulkCopy members are listed in the following tables.
OracleBulkCopy constructors are listed in Table 17-1.
Table 17-1 OracleBulkCopy Constructors
| Constructor | Description |
|---|---|
|
|
OracleBulkCopy properties are listed in Table 17-2.
Table 17-2 OracleBulkCopy Properties
| Property | Description |
|---|---|
|
Specifies the number of rows to be sent as a batch to the database |
|
|
Specifies the |
|
|
Specifies the number of seconds allowed for the bulk copy operation to complete before it is aborted |
|
|
Specifies the column mappings between the data source and destination table |
|
|
Specifies the |
|
|
Specifies the database table that the data is loaded in |
|
|
Defines the number of rows to be processed before a notification event is generated |
OracleBulkCopy public methods are listed in Table 17-3.
Table 17-3 OracleBulkCopy Public Methods
| Method | Description |
|---|---|
|
Closes the |
|
|
Releases any resources or memory allocated by the object |
|
|
Copies rows to a destination table |
OracleBulkCopy events are listed in Table 17-4.
OracleBulkCopy constructors create new instances of the OracleBulkCopy class.
OracleBulkCopy(OracleConnection)
This constructor instantiates a new instance of OracleBulkCopy class using the specified connection and default value for OracleBulkCopyOptions.
This constructor instantiates a new instance of OracleBulkCopy based on the supplied connectionString and default value for OracleBulkCopyOptions.
OracleBulkCopy(OracleConnection, OracleBulkCopyOptions)
This constructor instantiates a new instance of OracleBulkCopy using the specified connection object and OracleBulkCopyOptions value.
OracleBulkCopy(string, OracleBulkCopyOptions)
This constructor instantiates a new instance of OracleConnection based on the supplied connectionString and OracleBulkCopyOptions value.
This constructor instantiates a new instance of OracleBulkCopy class using the specified connection and default OracleBulkCopyOptions enumeration values.
// C#
public OracleBulkCopy(OracleConnection connection);
connection
The open instance of OracleConnection that performs the bulk copy operation.
ArgumentNullException - The connection parameter is null.
InvalidOperationException - The connection is not in the open state.
The connection object passed to this constructor must be open. It remains open after the OracleBulkCopy instance is closed.
This constructor uses the default enumeration value OracleBulkCopyOptions.Default.
The Connection property is set to the supplied connection.
This constructor instantiates a new instance of the OracleBulkCopy class by first creating an OracleConnection object based on the supplied connectionString , then initializing the new OracleBulkCopy object with the OracleConnection object and OracleBulkCopyOptions default value.
// C#
public OracleBulkCopy(string connectionString);
connectionString
The connection information used to connect to the Oracle database and perform the bulk copy operation.
ArgumentNullException - The connectionString parameter is null.
ArgumentException - The connectionString parameter is empty.
The WriteToServer method opens the connection, if it is not already opened. The connection is automatically closed when the OracleBulkCopy instance is closed.
This constructor uses the default enumeration value OracleBulkCopyOptions.Default.
The Connection property is set to the OracleConnection object initialized using the supplied connectionString.
This constructor instantiates a new instance of OracleBulkCopy using the specified connection object and OracleBulkCopyOptions value.
// C# public OracleBulkCopy(OracleConnection connection, OracleBulkCopyOptions copyOptions);
connection
The open instance of an OracleConnection object that performs the bulk copy operation.
copyOptions
The combination of OracleBulkCopyOptions enumeration values that determine the behavior of the OracleBulkCopy object.
ArgumentNullException - The connection parameter is null.
InvalidOperationException - The connection is not in the open state.
The connection passed to this constructor must be open. It remains open after the OracleBulkCopy instance is closed.
The Connection property is set to the supplied connection.
This constructor instantiates a new instance of the OracleBulkCopy class by first creating an OracleConnection object based on the supplied connectionString, then initializing the new OracleBulkCopy object with the OracleConnection object and the supplied OracleBulkCopyOptions enumeration values.
// C# public OracleBulkCopy(string connectionString, OracleBulkCopyOptions copyOptions);
connectionString
The connection information used to connect to the Oracle database to perform the bulk copy operation.
copyOptions
The combination of OracleBulkCopyOptions enumeration values that determine the behavior of the bulk copy operation.
ArgumentNullException - The connectionString is null.
ArgumentException - The connectionString parameter is empty.
The constructor uses the new instance of the OracleConnection class to initialize a new instance of the OracleBulkCopy class. The OracleBulkCopy instance behaves according to options supplied in the copyOptions parameter.
The connection is automatically closed when the OracleBulkCopy instance is closed.
The Connection property is set to an OracleConnection object initialized using the supplied connectionString.
OracleBulkCopy properties are listed in Table 17-5.
Table 17-5 OracleBulkCopy Properties
| Property | Description |
|---|---|
|
Specifies the number of rows to be sent as a batch to the database |
|
|
Specifies the |
|
|
Specifies the number of seconds allowed for the bulk copy operation to complete before it is aborted |
|
|
Specifies the column mappings between the data source and destination table |
|
|
Specifies the |
|
|
Specifies the database table that the data is loaded in |
|
|
Defines the number of rows to be processed before a notification event is generated |
This property specifies the number of rows to be sent as a batch to the database.
// C#
public int BatchSize {get; set;}
An integer value for the number of rows to be sent to the database as a batch.
ArgumentOutOfRangeException - The batch size is less than zero.
The default value is zero, indicating that the rows are not sent to the database in batches. The entire set of rows are sent in one single batch.
A batch is complete when BatchSize number of rows have been processed or there are no more rows to send to the database.
If BatchSize > 0 and the UseInternalTransaction bulk copy option is specified, each batch of the bulk copy operation occurs within a transaction. If the connection used to perform the bulk copy operation is already part of a transaction, an InvalidOperationException exception is raised.
If BatchSize > 0 and the UseInternalTransaction option is not specified, rows are sent to the database in batches of size BatchSize, but no transaction-related action is taken.
The BatchSize property can be set at any time. If a bulk copy is already in progress, the current batch size is determined by the previous batch size. Subsequent batches use the new batch size.
If the BatchSize property is initially zero and changes while a WriteToServer operation is in progress, that operation loads the data as a single batch. Any subsequent WriteToServer operations on the same OracleBulkCopy instance use the new BatchSize.
This property specifies the OracleBulkCopyOptions enumeration value that determines the behavior of the bulk copy option.
// C#
public OracleBulkCopyOptions BulkCopyOptions {get; set;}
The OracleBulkCopyOptions enumeration object that defines the behavior of the bulk copy operation.
ArgumentNullException - The bulk copy options set is null.
The default value of this property is OracleBulkCopyOptions.Default value. This property can be used to change the bulk copy options between the batches of a bulk copy operation.
This property specifies the number of seconds allowed for the bulk copy operation to complete before it is aborted.
// C#
public int BulkCopyTimeout {get; set;}
An integer value for the number of seconds after which the bulk copy operation times out.
ArgumentOutOfRangeException - The timeout value is set to less than zero.
The default value is 30 seconds.
If BatchSize>0, rows that were sent to the database in the previous batches remain committed. The rows that are processed in the current batch are not sent to the database. If BatchSize=0, no rows are sent to the database.
This property specifies the column mappings between the data source and destination table.
// C#
public OracleBulkCopyColumnMappingCollection ColumnMappings {get;}
The OracleBulkCopyColumnMappingCollection object that defines the column mapping between the source and destination table.
The ColumnMappings collection is unnecessary if the data source and the destination table have the same number of columns, and the ordinal position of each source column matches the ordinal position of the corresponding destination column. However, if the column counts differ, or the ordinal positions are not consistent, the ColumnMappings collection must be used to ensure that data is copied into the correct columns.
During the execution of a bulk copy operation, this collection can be accessed, but it cannot be changed.
By default, this property specifies an empty collection of column mappings.
This property specifies the OracleConnection object that the Oracle database uses to perform the bulk copy operation.
// C#
public OracleConnection Connection {get; }
The OracleConnection object used for the bulk copy operations.
This property gets the connection constructed by the OracleBulkCopy, if the OracleBulkCopy object is initialized using a connection string.
This property specifies the database table that the data is loaded into.
// C#
public string DestinationTableName {get; set;}
A string value that identifies the destination table name.
ArgumentNullException - The destination table name set is null.
ArgumentException - The destination table name is empty.
If DestinationTableName is modified while a WriteToServer operation is running, the change does not affect the current operation. The new DestinationTableName value is used the next time a WriteToServer method is called.
This property defines the number of rows to be processed before a notification event is generated.
// C#
public int NotifyAfter {get; set;}
An integer value that specifies the number of rows to be processed before the notification event is raised.
ArgumentOutOfRangeException - The property value is set to a number less than zero.
The default value for this property is zero, to specify that no notifications events are to be generated.
This property can be retrieved in user interface components to display the progress of a bulk copy operation. The NotifyAfter property can be set at anytime, even during a bulk copy operation. The changes take effect for the next notification and any subsequent operations on the same instance.
OracleBulkCopy methods are listed in Table 17-6.
Table 17-6 OracleBulkCopy Public Methods
| Method | Description |
|---|---|
|
Closes the |
|
|
Releases any resources or memory allocated by the object |
|
|
Copies rows to a destination table |
This method closes the OracleBulkCopy instance.
// C# public void Close();
InvalidOperationException - The Close method was called from a OracleRowsCopied event.
After the Close method is called on a OracleBulkCopy object, no other operation can succeed. Calls to the WriteToServer method throw an InvalidOperationException. The Close method closes the connection if the connection was opened by the OracleBulkCopy object, that is, if the OracleBulkCopy object was created by a constructor that takes a connection string.
This method releases any resources or memory allocated by the object.
// C# public void Dispose();
IDisposable
After the Dispose method is called on the OracleBulkCopy object, no other operation can succeed. The connection is closed if the connection was opened by the OracleBulkCopy object, that is, if a constructor that takes a connection string created the OracleBulkCopy object.
WriteToServer copies rows to a destination table.
This method copies all rows from the supplied DataRow array to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.
This method copies all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.
This method copies all rows in the supplied IDataReader to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.
WriteToServer(DataTable, DataRowState)
This method copies rows that match the supplied row state in the supplied DataTable to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.
WriteToServer(OracleRefCursor)
This method copies all rows from the specified OracleRefCursor to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.
This method copies all rows from the supplied DataRow array to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.
// C#
public void WriteToServer(DataRow[] rows);
rows
An array of DataRow objects to be copied to the destination table.
ArgumentNullException - The rows parameter is null.
InvalidOperationException - The connection is not in an open state.
The ColumnMappings collection maps from the DataRow columns to the destination database table.
This method copies all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.
// C#
public void WriteToServer(DataTable table);
table
The source DataTable containing rows to be copied to the destination table.
ArgumentNullException - The table parameter is null.
InvalidOperationException - The connection is not in an open state.
All rows in the DataTable are copied to the destination table except those that have been deleted.
The ColumnMappings collection maps from the DataTable columns to the destination database table.
This method copies all rows in the supplied IDataReader to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.
// C#
public void WriteToServer(IDataReader reader);
reader
A IDataReader instance containing rows to be copied to the destination table.
ArgumentNullException - The reader parameter is null.
InvalidOperationException - The connection is not in an open state.
The bulk copy operation starts with the next available row of the data reader. Typically, the reader returned by a call to the ExecuteReader method is passed to the WriteToServer method so that the next row becomes the first row. To copy multiple result sets, the application must call NextResult on the reader and then call the WriteToServer method again.
This WriteToServer method changes the state of the reader as it calls reader.Read internally to get the source rows. Thus, at the end of the WriteToServer operation, the reader is at the end of the result set.
The ColumnMappings collection maps from the data reader columns to the destination database table.
This method copies rows that match the supplied row state in the supplied DataTable to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.
// C# public void WriteToServer(DataTable table, DataRowState rowState);
table
A DataTable containing rows to be copied to the destination table.
rowState
The DataRowState enumeration value. Only rows matching the row state are copied to the destination.
ArgumentNullException - The table or rowState parameter is null.
InvalidOperationException - The connection is not in an open state.
Only rows in the DataTable that are in the state indicated in the rowState argument and have not been deleted are copied to the destination table.
The ColumnMappings collection maps from the DataTable columns to the destination database table.
This method copies all rows from the specified OracleRefCursor to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.
// C#
public void WriteToServer(OracleRefCursor refCursor);
refCursor
An OracleRefCursor object containing rows to be copied to the destination table.
ArgumentNullException - The refCursor parameter is null
InvalidOperationException - The connection is not in an open state.
The ColumnMappings collection maps from the OracleRefCursor columns to the destination database table.
OracleBulkCopy events are listed in Table 17-7.
Table 17-7 OracleBulkCopy Events
| Event | Description |
|---|---|
|
Triggered every time the number of rows specified by the |
This event is triggered every time the number of rows specified by the OracleBulkCopy.NotifyAfter property has been processed.
// C# public event OracleRowsCopiedEventHandler OracleRowsCopied;
InvalidOperationException - The Close method is called inside this event.
This event is raised when the number of rows specified by the NotifyAfter property has been processed. It does not imply that the rows have been sent to the database or committed.
To cancel the operation from this event, use the Abort property of OracleRowsCopiedEventArgs class.