An OracleDataAdapter object represents a data provider object that populates the DataSet and updates changes in the DataSet to the Oracle database.
System.Object
System.MarshalByRefObject
System.ComponentModel.Component
System.Data.Common.DataAdapter
System.Data.Common.DbDataAdapter
Oracle.DataAccess.Client.OracleDataAdapter
// C# public sealed class OracleDataAdapter : DbDataAdapter, IDbDataAdapter
| Provider | ODP.NET, Unmanaged Driver | ODP.NET, Managed Driver |
| Assembly | Oracle.DataAccess.dll |
Oracle.ManagedDataAccess.dll |
| Namespace | Oracle.DataAccess.Client |
Oracle.ManagedDataAccess.Client |
| .NET Framework | 3.5, 4.0, 4.5, 4.6 | 4.0, 4.5, 4.6 |
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
The following example uses the OracleDataAdapter and the dataset to update the EMP table:
// C#
using System;
using System.Data;
using Oracle.DataAccess.Client;
class OracleDataAdapterSample
{
static void Main()
{
string constr = "User Id=scott;Password=tiger;Data Source=oracle";
string cmdstr = "SELECT empno, sal from emp";
// Create the adapter with the selectCommand txt and the
// connection string
OracleDataAdapter adapter = new OracleDataAdapter(cmdstr, constr);
// Create the builder for the adapter to automatically generate
// the Command when needed
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
// Create and fill the DataSet using the EMP
DataSet dataset = new DataSet();
adapter.Fill(dataset, "EMP");
// Get the EMP table from the dataset
DataTable table = dataset.Tables["EMP"];
// Indicate DataColumn EMPNO is unique
// This is required by the OracleCommandBuilder to update the EMP table
table.Columns["EMPNO"].Unique = true;
// Get the first row from the EMP table
DataRow row = table.Rows[0];
// Update the salary
double sal = double.Parse(row["SAL"].ToString());
row["SAL"] = sal + .01;
// Now update the EMP using the adapter
// The OracleCommandBuilder will create the UpdateCommand for the
// adapter to update the EMP table
adapter.Update(dataset, "EMP");
Console.WriteLine("Row updated successfully");
}
}
See Also:
OracleDataAdapter members are listed in the following tables.
OracleDataAdapter Constructors
OracleDataAdapter constructors are listed in Table 6-41.
Table 6-41 OracleDataAdapter Constructors
| Constructor | Description |
|---|---|
|
Instantiates a new instance of |
OracleDataAdapter Static Methods
The OracleDataAdapter static method is listed in Table 6-42.
Table 6-42 OracleDataAdapter Static Method
| Method | Description |
|---|---|
|
|
Inherited from |
OracleDataAdapter properties are listed in Table 6-43.
Table 6-43 OracleDataAdapter Properties
| Property | Description |
|---|---|
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
A SQL statement or stored procedure to delete rows from an Oracle database |
|
|
Determines whether or not to insert identity column values in the Not available in the ODP.NET, Managed Driver |
|
|
Determines whether or not to update identity column values in the Not available in the ODP.NET, Managed Driver |
|
|
A SQL statement or stored procedure to insert new rows into an Oracle database |
|
|
|
Inherited from |
|
|
Inherited from |
|
Determines whether or not the |
|
|
Determines if the |
|
|
Creates a mapping between column names in the result set to .NET types, to preserve the data Not available in the ODP.NET, Managed Driver |
|
|
A SQL statement or stored procedure that returns a single or multiple result set |
|
|
|
Inherited from |
|
|
Inherited from |
|
Specifies a value that enables or disables batch processing support, and specifies the number of SQL statements that can be executed in a single round-trip to the database |
|
|
A SQL statement or stored procedure to update rows from the |
OracleDataAdapter Public Methods
OracleDataAdapter public methods are listed in Table 6-44.
Table 6-44 OracleDataAdapter Public Methods
| Public Method | Description |
|---|---|
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
Adds or refreshes rows in the |
|
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
OracleDataAdapter events are listed in Table 6-45.
Table 6-45 OracleDataAdapter Events
| Event Name | Description |
|---|---|
|
|
Inherited from |
|
|
Inherited from |
|
This event is raised when row(s) have been updated by the |
|
|
This event is raised when row data are about to be updated to the database |
OracleDataAdapter constructors create new instances of an OracleDataAdapter class.
This constructor creates an instance of an OracleDataAdapter class.
OracleDataAdapter(OracleCommand)
This constructor creates an instance of an OracleDataAdapter class with the provided OracleCommand as the SelectCommand.
OracleDataAdapter(string, OracleConnection)
This constructor creates an instance of an OracleDataAdapter class with the provided OracleConnection object and the command text for the SelectCommand.
OracleDataAdapter(string, string)
This constructor creates an instance of an OracleDataAdapter class with the provided connection string and the command text for the SelectCommand.
See Also:
This constructor creates an instance of an OracleDataAdapter class with no arguments.
// C# public OracleDataAdapter();
Initial values are set for the following OracleDataAdapter properties as indicated:
MissingMappingAction = MissingMappingAction.Passthrough
MissingSchemaAction = MissingSchemaAction.Add
This constructor creates an instance of an OracleDataAdapter class with the provided OracleCommand as the SelectCommand.
// C#
public OracleDataAdapter(OracleCommand selectCommand);
selectCommand
The OracleCommand that is to be set as the SelectCommand property.
Initial values are set for the following OracleDataAdapter properties as indicated:
MissingMappingAction = MissingMappingAction.Passthrough
MissingSchemaAction = MissingSchemaAction.Add
This constructor creates an instance of an OracleDataAdapter class with the provided OracleConnection object and the command text for the SelectCommand.
// C# public OracleDataAdapter(string selectCommandText, OracleConnection selectConnection);
selectCommandText
The string that is set as the CommandText of the SelectCommand property of the OracleDataAdapter.
selectConnection
The OracleConnection to connect to the Oracle database.
The OracleDataAdapter opens and closes the connection, if it is not already open. If the connection is open, it must be explicitly closed.
Initial values are set for the following OracleDataAdapter properties as indicated:
MissingMappingAction = MissingMappingAction.Passthrough
MissingSchemaAction = MissingSchemaAction.Add
This constructor creates an instance of an OracleDataAdapter class with the provided connection string and the command text for the SelectCommand.
// C# public OracleDataAdapter(string selectCommandText, string selectConnectionString);
selectCommandText
The string that is set as the CommandText of the SelectCommand property of the OracleDataAdapter.
selectConnectionString
The connection string.
Initial values are set for the following OracleDataAdapter properties as indicated:
MissingMappingAction = MissingMappingAction.Passthrough
MissingSchemaAction = MissingSchemaAction.Add
The OracleDataAdapter static method is listed in Table 6-46.
OracleDataAdapter properties are listed in Table 6-47.
Table 6-47 OracleDataAdapter Properties
| Property | Description |
|---|---|
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
A SQL statement or stored procedure to delete rows from an Oracle database |
|
|
Determines whether or not to insert identity column values in the Not available in the ODP.NET, Managed Driver |
|
|
Determines whether or not to update identity column values in the Not available in the ODP.NET, Managed Driver |
|
|
A SQL statement or stored procedure to insert new rows into an Oracle database |
|
|
|
Inherited from |
|
|
Inherited from |
|
Determines whether or not the |
|
|
Determines if the |
|
|
Creates a mapping between column names in the result set to .NET types, to preserve the data Not Available in ODP.NET, Managed Driver |
|
|
A SQL statement or stored procedure that returns a single or multiple result set |
|
|
|
Inherited from |
|
|
Inherited from |
|
Specifies a value that enables or disables batch processing support, and specifies the number of SQL statements that can be executed in a single round-trip to the database |
|
|
A SQL statement or stored procedure to update rows from the |
See Also:
This property is a SQL statement or stored procedure to delete rows from an Oracle database.
// C#
public OracleCommand DeleteCommand {get; set;}
An OracleCommand used during the Update call to delete rows from tables in the Oracle database, corresponding to the deleted rows in the DataSet.
Default = null
If there is primary key information in the DataSet, the DeleteCommand can be automatically generated using the OracleCommandBuilder, if no command is provided for this.
When inserting DataSet data into the database, this property indicates whether the database generates the inserted row's identity column value or DataSet supplies this value.
// C#
public bool IdentityInsert {get; set;}
When set to true, ODP.NET inserts DataSet identity column values into the database. When set to false, the database determines the inserted identity column values.
This property applies only to identity columns of type GENERATED BY DEFAULT and GENERATED BY DEFAULT ON NULL. Identity column of type GENERATED ALWAYS will ignore this property and will always use database generated values.
When set to false, the server will generate an identity value for the row. That generated identity value returns back to the client to update the DataSet value.
When this property is set to true for the GENERATED BY DEFAULT case and the application attempts to insert a NULL value into the database's identity column, the NOT NULL constraint is violated and an error occurs. ODP.NET will then allow the database to generate the identity column value and return the generated value to the DataSet.
The default value for this property is false.
When updating DataSet data into the database, this property indicates whether to replace the database's identity column values with values of the DataSet or leave the current values unchanged.
// C#
public bool IdentityUpdate {get; set;}
When set to true, ODP.NET updates the database identity column values with the values of the DataSet. When set to false, the database identity columns are left unchanged.
This property applies only to identity columns of type GENERATED BY DEFAULT and GENERATED BY DEFAULT ON NULL. In the case of type GENERATED ALWAYS, this property will be ignored and the database will always retain its current identity values.
When set to false, the existing identity column value in the server is returned to the DataSet.
When this property is set to true for the GENERATED BY DEFAULT and GENERATED BY DEFAULT ON NULL cases and the application attempts to update the database's identity column with a NULL value, the NOT NULL constraint is violated and an error occurs. ODP.NET then does not update the identity column value and instead returns the existing identity column value of the database to the DataSet.
The default value for this property is false.
This property is a SQL statement or stored procedure to insert new rows into an Oracle database.
// C#
public OracleCommand InsertCommand {get; set;}
An OracleCommand used during the Update call to insert rows into a table, corresponding to the inserted rows in the DataSet.
Default = null
If there is primary key information in the DataSet, the InsertCommand can be automatically generated using the OracleCommandBuilder, if no command is provided for this property.
This property determines whether or not the SelectCommand is reexecuted on the next call to Fill.
// C#
public Boolean Requery {get; set;}
Returns true if the SelectCommand is reexecuted on the next call to Fill; otherwise, returns false.
This property determines if the Fill method returns ODP.NET-specific values or .NET common language specification compliant values.
// C#
public Boolean ReturnProviderSpecificTypes {get; set;}
A value that indicates whether or not the Fill method returns ODP.NET-specific values.
Starting with ODP.NET 12.1.0.2, when set to true and LegacyEntireLOBFetch = 0 (default), BLOB and CLOB column values are represented in the DataTable as OracleBlob and OracleClob, respectively.
A value of false indicates that the Fill method returns .NET common language specification compliant values. The default is false.
This property creates a mapping between column names in the result set to .NET types that represent column values in the DataSet, to preserve the data.
// C#
public Hashtable SafeMapping {get; set;}
A hash table.
Default = null
The SafeMapping property is used, when necessary, to preserve data in the following types:
DATE
TimeStamp (refers to all TimeStamp objects)
INTERVAL DAY TO SECOND
NUMBER
See the example in "OracleDataAdapter Safe Type Mapping".
This property is a SQL statement or stored procedure that returns single or multiple result sets.
// C#
public OracleCommand SelectCommand {get; set;}
An OracleCommand used during the Fill call to populate the selected rows to the DataSet.
Default = null
If the SelectCommand does not return any rows, no tables are added to the dataset and no exception is raised.
If the SELECT statement selects from a VIEW, no key information is retrieved when a FillSchema() or a Fill() with MissingSchemaAction.AddWithKey is invoked.
This property specifies a value that enables or disables batch processing support, and specifies the number of SQL statements that can be executed in a single round-trip to the database.
// C#
public virtual int UpdateBatchSize {get; set;}
An integer that returns the batch size.
ArgumentOutOfRangeException - The value is set to a number < 0.
Update batches executed with large amounts of data may encounter an "PLS-00123: Program too large" error. To avoid this error, reduce the size of UpdateBatchSize to a smaller value.
For each row in the DataSet that has been modified, added, or deleted, one SQL statement will be executed on the database.
Values are as follows:
Value = 0
The data adapter executes all the SQL statements in a single database round-trip
Value = 1 - Default value
This value disables batch updating and SQL statements are executed one at a time.
Value = n where n > 1
The data adapter updates n rows of data per database round-trip.
This property is a SQL statement or stored procedure to update rows from the DataSet to an Oracle database.
// C#
public OracleCommand UpdateCommand {get; set;}
An OracleCommand used during the Update call to update rows in the Oracle database, corresponding to the updated rows in the DataSet.
Default = null
If there is primary key information in the DataSet, the UpdateCommand can be automatically generated using the OracleCommandBuilder, if no command is provided for this property.
OracleDataAdapter public methods are listed in Table 6-48.
Table 6-48 OracleDataAdapter Public Methods
| Public Method | Description |
|---|---|
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
Adds or refreshes rows in the |
|
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from System. |
|
|
Inherited from |
|
|
Inherited from |
See Also:
Fill populates or refreshes the specified DataTable or DataSet.
Fill(DataTable, OracleRefCursor)
This method adds or refreshes rows in the specified DataTable to match those in the provided OracleRefCursor object.
Fill(DataSet, OracleRefCursor)
This method adds or refreshes rows in the DataSet to match those in the provided OracleRefCursor object.
Fill(DataSet, string, OracleRefCursor)
This method adds or refreshes rows in the specified source table of the DataSet to match those in the provided OracleRefCursor object.
Fill(DataSet, int, int, string, OracleRefCursor)
This method adds or refreshes rows in a specified range in the DataSet to match rows in the provided OracleRefCursor object.
This method adds or refreshes rows in the specified DataTable to match those in the provided OracleRefCursor object.
// C# public int Fill(DataTable dataTable, OracleRefCursor refCursor);
dataTable
The DataTable object being populated.
refCursor
The OracleRefCursor that rows are being retrieved from.
The number of rows added to or refreshed in the DataTable.
ArgumentNullException - The dataTable or refCursor parameter is null.
InvalidOperationException - The OracleRefCursor is already being used to fetch data.
NotSupportedException - The SafeMapping type is not supported.
No schema or key information is provided, even if the Fill method is called with MissingSchemaAction set to MissingSchemaAction.AddWithKey.
This method adds or refreshes rows in the DataSet to match those in the provided OracleRefCursor object.
// C# public int Fill(DataSet dataSet, OracleRefCursor refCursor);
dataSet
The DataSet object being populated.
refCursor
The OracleRefCursor that rows are being retrieved from.
Returns the number of rows added or refreshed in the DataSet.
ArgumentNullException - The dataSet or refCursor parameter is null.
InvalidOperationException - The OracleRefCursor is already being used to fetch data.
InvalidOperationException - The OracleRefCursor is ready to fetch data.
NotSupportedException - The SafeMapping type is not supported.
If there is no DataTable to refresh, a new DataTable named Table is created and populated using the provided OracleRefCursor object.
No schema or key information is provided, even if the Fill method is called with MissingSchemaAction set to MissingSchemaAction.AddWithKey.
This method adds or refreshes rows in the specified source table of the DataSet to match those in the provided OracleRefCursor object.
// C# public int Fill(DataSet dataSet, string srcTable, OracleRefCursor refCursor);
dataSet
The DataSet object being populated.
srcTable
The name of the source table used in the table mapping.
refCursor
The OracleRefCursor that rows are being retrieved from.
Returns the number of rows added or refreshed into the DataSet.
ArgumentNullException - The dataSet or refCursor parameter is null.
InvalidOperationException - The OracleRefCursor is already being used to fetch data or the source table name is invalid.
NotSupportedException - The SafeMapping type is not supported.
No schema or key information is provided, even if the Fill method is called with MissingSchemaAction set to MissingSchemaAction.AddWithKey.
This method adds or refreshes rows in a specified range in the DataSet to match rows in the provided OracleRefCursor object.
// C# public int Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable, OracleRefCursor refCursor);
dataSet
The DataSet object being populated.
startRecord
The record number to start with.
maxRecords
The maximum number of records to obtain.
srcTable
The name of the source table used in the table mapping.
refCursor
The OracleRefCursor that rows are being retrieved from.
This method returns the number of rows added or refreshed in the DataSet. This does not include rows affected by statements that do not return rows.
ArgumentNullException - The dataSet or refCursor parameter is null.
InvalidOperationException - The OracleRefCursor is already being used to fetch data or the source table name is invalid.
NotSupportedException - The SafeMapping type is not supported.
No schema or key information is provided, even if the Fill method is called with MissingSchemaAction set to MissingSchemaAction.AddWithKey.
OracleDataAdapter events are listed in Table 6-49.
Table 6-49 OracleDataAdapter Events
| Event Name | Description |
|---|---|
|
|
Inherited from |
|
|
Inherited from |
|
This event is raised when row(s) have been updated by the |
|
|
This event is raised when row data are about to be updated to the database |
See Also:
This event is raised when row(s) have been updated by the Update() method.
// C# public event OracleRowUpdatedEventHandler RowUpdated;
The event handler receives an OracleRowUpdatedEventArgs object which exposes the following properties containing information about the event.
Command
The OracleCommand executed during the Update.
Errors (inherited from RowUpdatedEventArgs)
The exception, if any, is generated during the Update.
RecordsAffected (inherited from RowUpdatedEventArgs)
The number of rows modified, inserted, or deleted by the execution of the Command.
Row (inherited from RowUpdatedEventArgs)
The DataRow sent for Update.
StatementType (inherited from RowUpdatedEventArgs)
The type of SQL statement executed.
Status (inherited from RowUpdatedEventArgs)
The UpdateStatus of the Command.
TableMapping (inherited from RowUpdatedEventArgs)
The DataTableMapping used during the Update.
The following example shows how to use the RowUpdating and RowUpdated events.
// C#
using System;
using System.Data;
using Oracle.DataAccess.Client;
class RowUpdatedSample
{
// Event handler for RowUpdating event
protected static void OnRowUpdating(object sender,
OracleRowUpdatingEventArgs e)
{
Console.WriteLine("Row updating.....");
Console.WriteLine("Event arguments:");
Console.WriteLine("Command Text: " + e.Command.CommandText);
Console.WriteLine("Command Type: " + e.StatementType);
Console.WriteLine("Status: " + e.Status);
}
// Event handler for RowUpdated event
protected static void OnRowUpdated(object sender,
OracleRowUpdatedEventArgs e)
{
Console.WriteLine("Row updated.....");
Console.WriteLine("Event arguments:");
Console.WriteLine("Command Text: " + e.Command.CommandText);
Console.WriteLine("Command Type: " + e.StatementType);
Console.WriteLine("Status: " + e.Status);
}
static void Main()
{
string constr = "User Id=scott;Password=tiger;Data Source=oracle";
string cmdstr = "SELECT EMPNO, ENAME, SAL FROM EMP";
// Create the adapter with the selectCommand txt and the
// connection string
OracleDataAdapter adapter = new OracleDataAdapter(cmdstr, constr);
// Create the builder for the adapter to automatically generate
// the Command when needed
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
// Create and fill the DataSet using the EMP
DataSet dataset = new DataSet();
adapter.Fill(dataset, "EMP");
// Get the EMP table from the dataset
DataTable table = dataset.Tables["EMP"];
// Indicate DataColumn EMPNO is unique
// This is required by the OracleCommandBuilder to update the EMP table
table.Columns["EMPNO"].Unique = true;
// Get the first row from the EMP table
DataRow row = table.Rows[0];
// Update the salary
double sal = double.Parse(row["SAL"].ToString());
row["SAL"] = sal + .01;
// Set the event handlers for the RowUpdated and the RowUpdating event
// the OnRowUpdating() method will be triggered before the update, and
// the OnRowUpdated() method will be triggered after the update
adapter.RowUpdating += new OracleRowUpdatingEventHandler(OnRowUpdating);
adapter.RowUpdated += new OracleRowUpdatedEventHandler(OnRowUpdated);
// Now update the EMP using the adapter
// The OracleCommandBuilder will create the UpdateCommand for the
// adapter to update the EMP table
// The OnRowUpdating() and the OnRowUpdated() methods will be triggered
adapter.Update(dataset, "EMP");
}
}
This event is raised when row data are about to be updated to the database.
// C# public event OracleRowUpdatingEventHandler RowUpdating;
The event handler receives an OracleRowUpdatingEventArgs object which exposes the following properties containing information about the event.
Command
The OracleCommand executed during the Update.
Errors (inherited from RowUpdatingEventArgs)
The exception, if any, is generated during the Update.
Row (inherited from RowUpdatingEventArgs)
The DataRow sent for Update.
StatementType (inherited from RowUpdatingEventArgs)
The type of SQL statement executed.
Status (inherited from RowUpdatingEventArgs)
The UpdateStatus of the Command.
TableMapping (inherited from RowUpdatingEventArgs)
The DataTableMapping used during the Update.
The example for the RowUpdated event also shows how to use the RowUpdating event. See RowUpdated event "Example".