OracleBulkCopy Class

An OracleBulkCopy object efficiently bulk loads or copies data into an Oracle table from another data source.

Class Inheritance

System.Object

  System.OracleBulkCopy

Declaration

// C#
public sealed class OracleBulkCopy : IDisposable
 

Thread Safety

All public static methods are thread-safe, although instance methods do not guarantee thread safety.

Remarks

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.

Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

Microsoft .NET Framework Version: 1.0 or later

---

OracleBulkCopy Members

OracleBulkCopy members are listed in the following tables.

OracleBulkCopy Constructors

OracleBulkCopy constructors are listed in Table 15-1.

OracleBulkCopy Properties

OracleBulkCopy properties are listed in Table 15-2.

OracleBulkCopy Public Methods

OracleBulkCopy public methods are listed in Table 15-3.

OracleBulkCopy Events

OracleBulkCopy events are listed in Table 15-4.

---

OracleBulkCopy Constructors

OracleBulkCopy constructors create new instances of the OracleBulkCopy class.

Overload List:

• OracleBulkCopy(OracleConnection)

  This constructor instantiates a new instance of OracleBulkCopy class using the specified connection and default value for OracleBulkCopyOptions.

• OracleBulkCopy(string)

  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.

OracleBulkCopy(OracleConnection)

This constructor instantiates a new instance of OracleBulkCopy class using the specified connection and default OracleBulkCopyOptions enumeration values.

Declaration

// C#
public OracleBulkCopy(OracleConnection connection);

Parameters

• connection

  The open instance of OracleConnection that performs the bulk copy operation.

Exceptions

ArgumentNullException - The connection parameter is null.

InvalidOperationException - The connection is not in the open state.

Remarks

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.

OracleBulkCopy(string)

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.

Declaration

// C#
public OracleBulkCopy(string connectionString);

Parameters

• connectionString

  The connection information used to connect to the Oracle database and perform the bulk copy operation.

Exception

ArgumentNullException - The connectionString parameter is null.

ArgumentException - The connectionString parameter is empty.

Remarks

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.

OracleBulkCopy(OracleConnection, OracleBulkCopyOptions)

This constructor instantiates a new instance of OracleBulkCopy using the specified connection object and OracleBulkCopyOptions value.

Declaration

// C#
public OracleBulkCopy(OracleConnection connection, OracleBulkCopyOptions      copyOptions);

Parameters

• 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.

Exceptions

ArgumentNullException - The connection parameter is null.

InvalidOperationException - The connection is not in the open state.

Remarks

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.

OracleBulkCopy(string, OracleBulkCopyOptions)

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.

Declaration

// C#
public OracleBulkCopy(string connectionString, OracleBulkCopyOptions copyOptions);

Parameters

• 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.

Exceptions

ArgumentNullException - The connectionString is null.

ArgumentException - The connectionString parameter is empty.

Remarks

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

OracleBulkCopy properties are listed in Table 15-5.

BatchSize

This property specifies the number of rows to be sent as a batch to the database.

Declaration

// C#
public int BatchSize {get; set;}

Property Value

An integer value for the number of rows to be sent to the database as a batch.

Exceptions

ArgumentOutOfRangeException - The batch size is less than zero.

Remarks

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.

BulkCopyOptions

This property specifies the OracleBulkCopyOptions enumeration value that determines the behavior of the bulk copy option.

Declaration

// C#
public OracleBulkCopyOptions BulkCopyOptions {get; set;}

Property Value

The OracleBulkCopyOptions enumeration object that defines the behavior of the bulk copy operation.

Exceptions

ArgumentNullException - The bulk copy options set is null.

Remarks

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.

BulkCopyTimeout

This property specifies the number of seconds allowed for the bulk copy operation to complete before it is aborted.

Declaration

// C#
public int BulkCopyTimeout {get; set;}

Property Value

An integer value for the number of seconds after which the bulk copy operation times out.

Exceptions

ArgumentOutOfRangeException - The timeout value is set to less than zero.

Remarks

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.

ColumnMappings

This property specifies the column mappings between the data source and destination table.

Declaration

// C#
public OracleBulkCopyColumnMappingCollection ColumnMappings {get;}

Property Value

The OracleBulkCopyColumnMappingCollection object that defines the column mapping between the source and destination table.

Remarks

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.

Connection

This property specifies the OracleConnection object that the Oracle database uses to perform the bulk copy operation.

Declaration

// C#
public OracleConnection Connection {get; }

Property Value

The OracleConnection object used for the bulk copy operations.

Remarks

This property gets the connection constructed by the OracleBulkCopy, if the OracleBulkCopy object is initialized using a connection string.

DestinationTableName

This property specifies the database table that the data is loaded into.

Declaration

// C#
public string DestinationTableName {get; set;}

Property Value

A string value that identifies the destination table name.

Exceptions

ArgumentNullException - The destination table name set is null.

ArgumentException - The destination table name is empty.

Remarks

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.

NotifyAfter

This property defines the number of rows to be processed before a notification event is generated.

Declaration

// C#
public int NotifyAfter {get; set;}

Property Value

An integer value that specifies the number of rows to be processed before the notification event is raised.

Exceptions

ArgumentOutOfRangeException - The property value is set to a number less than zero.

Remarks

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 Public Methods

OracleBulkCopy methods are listed in Table 15-6.

Close

This method closes the OracleBulkCopy instance.

Declaration

// C#
public void Close();

Exceptions

InvalidOperationException - The Close method was called from a OracleRowsCopied event.

Remarks

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.

Dispose

This method releases any resources or memory allocated by the object.

Declaration

// C#
public void Dispose();

Implements

IDisposable

Remarks

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

WriteToServer copies rows to a destination table.

Overload List:

• WriteToServer(DataRow[])

  This method copies all rows from the supplied DataRow array to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.

• WriteToServer(DataTable)

  This method copies all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.

• WriteToServer(IDataReader)

  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.

WriteToServer(DataRow[])

This method copies all rows from the supplied DataRow array to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.

Declaration

// C#
public void WriteToServer(DataRow[] rows);

Parameters

• rows

  An array of DataRow objects to be copied to the destination table.

Exceptions

ArgumentNullException - The rows parameter is null.

InvalidOperationException - The connection is not in an open state.

Remarks

The ColumnMappings collection maps from the DataRow columns to the destination database table.

WriteToServer(DataTable)

This method copies all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.

Declaration

// C#
public void WriteToServer(DataTable table);

Parameters

• table

  The source DataTable containing rows to be copied to the destination table.

Exceptions

ArgumentNullException - The table parameter is null.

InvalidOperationException - The connection is not in an open state.

Remarks

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.

WriteToServer(IDataReader)

This method copies all rows in the supplied IDataReader to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.

Declaration

// C#
public void WriteToServer(IDataReader reader);

Parameters

• reader

  A IDataReader instance containing rows to be copied to the destination table.

Exceptions

ArgumentNullException - The reader parameter is null.

InvalidOperationException - The connection is not in an open state.

Remarks

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.

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.

Declaration

// C#
public void WriteToServer(DataTable table, DataRowState rowState);

Parameters

• 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.

Exceptions

ArgumentNullException - The table or rowState parameter is null.

InvalidOperationException - The connection is not in an open state.

Remarks

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.

WriteToServer(OracleRefCursor)

This method copies all rows from the specified OracleRefCursor to a destination table specified by the DestinationTableName property of the OracleBulkCopy object.

Declaration

// C#
public void WriteToServer(OracleRefCursor refCursor);

Parameters

• refCursor

  An OracleRefCursor object containing rows to be copied to the destination table.

Exceptions

ArgumentNullException - The refCursor parameter is null

InvalidOperationException - The connection is not in an open state.

Remarks

The ColumnMappings collection maps from the OracleRefCursor columns to the destination database table.

---

OracleBulkCopy Events

OracleBulkCopy events are listed in Table 15-7.

OracleRowsCopied

This event is triggered every time the number of rows specified by the OracleBulkCopy.NotifyAfter property has been processed.

Declaration

// C#
public event OracleRowsCopiedEventHandler OracleRowsCopied;

Exceptions

InvalidOperationException - The Close method is called inside this event.

Remarks

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.