| Oracle® Data Provider for .NET Developer's Guide 11g Release 2 (11.2.0.1.2) Part Number E17357-04 |
|
|
View PDF |
| Oracle® Data Provider for .NET Developer's Guide 11g Release 2 (11.2.0.1.2) Part Number E17357-04 |
|
|
View PDF |
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
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
OracleBulkCopy constructors are listed in Table 17-1.
Table 17-1 OracleBulkCopy Constructors
| Constructor | Description |
|---|---|
|
|
OracleBulkCopy Properties
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
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
OracleBulkCopy events are listed in Table 17-4.
Table 17-4 OracleBulkCopy Events
| Event | Description |
|---|---|
|
Triggered every time the number of rows specified by the |
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.
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.
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.
See Also:
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.
See Also:
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.
See Also:
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.
See Also:
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.
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.
See Also:
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.
See Also:
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.
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.
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.
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.
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 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.
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.
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 copies rows to a destination table.
Overload List:
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.
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.
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.
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.
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.
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 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.
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.
|
 Copyright © 2002, 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|