Oracle® Data Provider for .NET Developer's Guide 10g Release 2 (10.2) Part Number B14307-01 |
|
|
View PDF |
An OracleDataAdapter
object represents a data provider object that populates the DataSet
and updates changes in the DataSet
to the Oracle database.
Class Inheritance
Object
MarshalByRefObject
Component
DataAdapter
DbDataAdapter
OracleDataAdapter
Declaration
// C# public sealed class OracleDataAdapter : DbDataAdapter, IDbDataAdapter
Thread Safety
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
Example
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"); } }
Requirements
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
See Also:
OracleDataAdapter
members are listed in the following tables:
OracleDataAdapter Constructors
OracleDataAdapter
constructors are listed in Table 5-29.
Table 5-29 OracleDataAdapter Constructors
Constructor | Description |
---|---|
OracleDataAdapter Constructors | Instantiates a new instance of OracleDataAdapter class (Overloaded) |
OracleDataAdapter Static Methods
The OracleDataAdapter
static method is listed in Table 5-30.
Table 5-30 OracleDataAdapter Static Method
Method | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleDataAdapter Properties
OracleDataAdapter
properties are listed in Table 5-31.
Table 5-31 OracleDataAdapter Properties
Name | Description |
---|---|
AcceptChangesDuringFill |
Inherited from DataAdapter |
Container |
Inherited from Component |
ContinueUpdateOnError |
Inherited from DataAdapter |
DeleteCommand | A SQL statement or stored procedure to delete rows from an Oracle database |
InsertCommand | A SQL statement or stored procedure to insert new rows into an Oracle database |
MissingMappingAction |
Inherited from DataAdapter |
MissingSchemaAction |
Inherited from DataAdapter |
Requery | Determines whether or not the SelectCommand is reexecuted on the next call to Fill |
SafeMapping | Creates a mapping between column names in the result set to .NET types, to preserve the data |
SelectCommand | A SQL statement or stored procedure that returns a single or multiple result set |
Site |
Inherited from Component |
TableMappings |
Inherited from DataAdapter |
UpdateCommand | A SQL statement or stored procedure to update rows from the DataSet to an Oracle database |
OracleDataAdapter Public Methods
OracleDataAdapter
public methods are listed in Table 5-32.
Table 5-32 OracleDataAdapter Public Methods
Public Method | Description |
---|---|
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose |
Inherited from Component |
Equals |
Inherited from Object (Overloaded) |
Fill | Adds or refreshes rows in the DataSet to match the data in the Oracle database (Overloaded) |
FillSchema |
Inherited from DbDataAdapter |
GetFillParameters |
Inherited from DbDataAdapter |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
ToString |
Inherited from Object |
Update |
Inherited from DbDataAdapter |
OracleDataAdapter Events
OracleDataAdapter
events are listed in Table 5-33.
Table 5-33 OracleDataAdapter Events
Event Name | Description |
---|---|
Disposed |
Inherited from Component |
FillError |
Inherited from DbDataAdapter |
RowUpdated | This event is raised when row(s) have been updated by the Update() method |
RowUpdating | This event is raised when row data are about to be updated to the database |
OracleDataAdapter
constructors create new instances of an OracleDataAdapter
class.
Overload List:
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
.
This constructor creates an instance of an OracleDataAdapter
class with no arguments.
Declaration
// C# public OracleDataAdapter();
Remarks
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
.
Declaration
// C#
public OracleDataAdapter(OracleCommand selectCommand);
Parameters
selectCommand
The OracleCommand
that is to be set as the SelectCommand
property.
Remarks
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
.
Declaration
// C# public OracleDataAdapter(string selectCommandText, OracleConnection selectConnection);
Parameters
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.
Remarks
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
.
Declaration
// C# public OracleDataAdapter(string selectCommandText, string selectConnectionString);
Parameters
selectCommandText
The string that is set as the CommandText
of the SelectCommand
property of the OracleDataAdapter
.
selectConnectionString
The connection string.
Remarks
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 5-34.
Table 5-34 OracleDataAdapter Static Method
Method | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleDataAdapter
properties are listed in Table 5-35.
Table 5-35 OracleDataAdapter Properties
Name | Description |
---|---|
AcceptChangesDuringFill |
Inherited from DataAdapter |
Container |
Inherited from Component |
ContinueUpdateOnError |
Inherited from DataAdapter |
DeleteCommand | A SQL statement or stored procedure to delete rows from an Oracle database |
InsertCommand | A SQL statement or stored procedure to insert new rows into an Oracle database |
MissingMappingAction |
Inherited from DataAdapter |
MissingSchemaAction |
Inherited from DataAdapter |
Requery | Determines whether or not the SelectCommand is reexecuted on the next call to Fill |
SafeMapping | Creates a mapping between column names in the result set to .NET types, to preserve the data |
SelectCommand | A SQL statement or stored procedure that returns a single or multiple result set |
Site |
Inherited from Component |
TableMappings |
Inherited from DataAdapter |
UpdateCommand | A SQL statement or stored procedure to update rows from the DataSet to an Oracle database |
This property is a SQL statement or stored procedure to delete rows from an Oracle database.
Declaration
// C# public OracleCommand DeleteCommand {get; set;}
Property Value
An OracleCommand
used during the Update
call to delete rows from tables in the Oracle database, corresponding to the deleted rows in the DataSet
.
Remarks
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.
This property is a SQL statement or stored procedure to insert new rows into an Oracle database.
Declaration
// C# public OracleCommand InsertCommand {get; set;}
Property Value
An OracleCommand
used during the Update
call to insert rows into a table, corresponding to the inserted rows in the DataSet
.
Remarks
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
.
Declaration
// C# public Boolean Requery {get; set;}
Property Value
Returns true
if the SelectCommand
is reexecuted on the next call to Fill
; otherwise, returns false
.
See Also:
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.
Declaration
// C# public Hashtable SafeMapping {get; set;}
Property Value
A hash table.
Remarks
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
Example
See the example in "OracleDataAdapter Safe Type Mapping".
See Also:
This property is a SQL statement or stored procedure that returns single or multiple result sets.
Declaration
// C# public OracleCommand SelectCommand {get; set;}
Property Value
An OracleCommand
used during the Fill
call to populate the selected rows to the DataSet
.
Remarks
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.
See Also:
This property is a SQL statement or stored procedure to update rows from the DataSet
to an Oracle database.
Declaration
// C# public OracleCommand UpdateCommand {get; set;}
Property Value
An OracleCommand
used during the Update
call to update rows in the Oracle database, corresponding to the updated rows in the DataSet
.
Remarks
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.
See Also:
OracleDataAdapter
public methods are listed in Table 5-36.
Table 5-36 OracleDataAdapter Public Methods
Public Method | Description |
---|---|
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose |
Inherited from Component |
Equals |
Inherited from Object (Overloaded) |
Fill | Adds or refreshes rows in the DataSet to match the data in the Oracle database (Overloaded) |
FillSchema |
Inherited from DbDataAdapter |
GetFillParameters |
Inherited from DbDataAdapter |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
ToString |
Inherited from Object |
Update |
Inherited from DbDataAdapter |
Fill
populates or refreshes the specified DataTable
or DataSet
.
Overload List:
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.
Declaration
// C# public int Fill(DataTable dataTable, OracleRefCursor refCursor);
Parameters
dataTable
The DataTable
object being populated.
refCursor
The OracleRefCursor
that rows are being retrieved from.
Return Value
The number of rows added to or refreshed in the DataTable
.
Exceptions
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.
Remarks
No schema or key information is provided, even if the Fill
method is called with MissingSchemaAction
set to MissingSchemaAction.AddWithKey
.
See Also:
This method adds or refreshes rows in the DataSet
to match those in the provided OracleRefCursor
object.
Declaration
// C# public int Fill(DataSet dataSet, OracleRefCursor refCursor);
Parameters
dataSet
The DataSet
object being populated.
refCursor
The OracleRefCursor
that rows are being retrieved from.
Return Value
Returns the number of rows added or refreshed in the DataSet
.
Exceptions
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.
Remarks
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
.
See Also:
This method adds or refreshes rows in the specified source table of the DataSet
to match those in the provided OracleRefCursor
object.
Declaration
// C# public int Fill(DataSet dataSet, string srcTable, OracleRefCursor refCursor);
Parameters
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.
Return Value
Returns the number of rows added or refreshed into the DataSet
.
Exceptions
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.
Remarks
No schema or key information is provided, even if the Fill
method is called with MissingSchemaAction
set to MissingSchemaAction.AddWithKey
.
See Also:
This method adds or refreshes rows in a specified range in the DataSet
to match rows in the provided OracleRefCursor
object.
Declaration
// C# public int Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable, OracleRefCursor refCursor);
Parameters
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.
Return Value
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.
Exceptions
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.
Remarks
No schema or key information is provided, even if the Fill
method is called with MissingSchemaAction
set to MissingSchemaAction.AddWithKey
.
See Also:
OracleDataAdapter
events are listed in Table 5-37.
Table 5-37 OracleDataAdapter Events
Event Name | Description |
---|---|
Disposed |
Inherited from Component |
FillError |
Inherited from DbDataAdapter |
RowUpdated | This event is raised when row(s) have been updated by the Update() method |
RowUpdating | This event is raised when row data are about to be updated to the database |
This event is raised when row(s) have been updated by the Update()
method.
Declaration
// C# public event OracleRowUpdatedEventHandler RowUpdated;
Event Data
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
.
Example
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"); } }
See Also:
This event is raised when row data are about to be updated to the database.
Declaration
// C# public event OracleRowUpdatingEventHandler RowUpdating;
Event Data
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
.
Example
The example for the RowUpdated
event also shows how to use the RowUpdating
event. See RowUpdated
event "Example".