Skip Headers

Oracle® Data Provider for .NET Developer's Guide
10g Release 1 (10.1.0.3)
Part No. B14164-01
  Go To Table Of Contents
Contents
Go To Index
Index

Previous Next  

OracleDataAdapter Class

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


OracleDataAdapter Members

OracleDataAdapter members are listed in the following tables:

OracleDataAdapter Constructors

OracleDataAdapter constructors are listed in Table 4-31.

Table 4-31 OracleDataAdapter Constructors

Constructor Description
OracleDataAdapter Constructors Instantiates a new instance of OracleDataAdapter class (Overloaded)

OracleDataAdapter Static Methods

OracleDataAdapter static methods are listed in Table 4-32.

Table 4-32 OracleDataAdapter Static Methods

Methods Description
Equals Inherited from Object (Overloaded)

OracleDataAdapter Properties

OracleDataAdapter properties are listed in Table 4-33.

Table 4-33 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 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 4-34.

Table 4-34 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 4-35.

Table 4-35 OracleDataAdapter Events

Event Delegate Name Description
EventHandler Inherited from Component
FillErrorEventHandler Inherited from DbDataAdapter
OracleRowUpdatedEventHandler Event Delegate for the RowUpdated Event
OracleRowUpdatingEventHandler Event Delegate for the RowUpdating Event

OracleDataAdapter Event Delegates

OracleDataAdapter event delegates are listed in Table 4-36.

Table 4-36 OracleDataAdapter Event Delegates

Event Delegate Name Description
EventHandler Inherited from Component
FillErrorEventHandler Inherited from DbDataAdapter
OracleRowUpdatedEventHandler Event Delegate for the RowUpdated Event
OracleRowUpdatingEventHandler Event Delegate for the RowUpdating Event


OracleDataAdapter Constructors

OracleDataAdapter constructors create new instances of an OracleDataAdapter class.

Overload List:

OracleDataAdapter()

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:

OracleDataAdapter(OracleCommand)

This constructor creates an instance of an OracleDataAdapter class with the provided OracleCommand as the SelectCommand.

Declaration

// C#
public OracleDataAdapter(OracleCommand selectCommand);

Parameters

Remarks

Initial values are set for the following OracleDataAdapter properties as indicated:

OracleDataAdapter(string, OracleConnection)

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

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:

OracleDataAdapter(string, string)

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

Remarks

Initial values are set for the following OracleDataAdapter properties as indicated:


OracleDataAdapter Static Methods

OracleDataAdapter static methods are listed in Table 4-37.

Table 4-37 OracleDataAdapter Static Methods

Methods Description
Equals Inherited from Object (Overloaded)


OracleDataAdapter Properties

OracleDataAdapter properties are listed in Table 4-38.

Table 4-38 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 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

DeleteCommand

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.

InsertCommand

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.

Requery

This property determines whether 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.

SafeMapping

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

Remarks

Default = null

The SafeMapping property is used, when necessary, to preserve data in the following types:

Example

// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class SafeMappingSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
 
    // In this SELECT statement, EMPNO, HIREDATE and SALARY must be
    // preserved using safe type mapping.
    string cmdstr = "SELECT EMPNO, ENAME, HIREDATE, SAL FROM EMP";
 
    // Create the adapter with the selectCommand txt and the connection string
    OracleDataAdapter adapter = new OracleDataAdapter(cmdstr, constr);
 
    // Get the connection from the adapter
    OracleConnection connection = adapter.SelectCommand.Connection;
 
    // Create the safe type mapping for the adapter
    // which can safely map column data to byte arrays, where
    // applicable. By executing the following statement, EMPNO, HIREDATE AND
    // SALARY columns will be mapped to byte[]
    adapter.SafeMapping.Add("*", typeof(byte[]));
 
    // Map HIREDATE to a string
    // If the column name in the EMP table is case-sensitive,
    // the safe type mapping column name must be case-sensitive.
    adapter.SafeMapping.Add("HIREDATE", typeof(string));
 
    // Map EMPNO to a string
    // If the column name in the EMP table is case-sensitive,
    // the safe type mapping column name must also be case-sensitive.
    adapter.SafeMapping.Add("EMPNO", typeof(string));
    adapter.SafeMapping.Add("SAL", typeof(string));
 
    // 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"];
 
    // Get the first row from the EMP table
    DataRow row = table.Rows[0];
 
    // Print out the row info
    Console.WriteLine("EMPNO Column: type = " + row["EMPNO"].GetType() +
      "; value = " + row["EMPNO"]);
    Console.WriteLine("ENAME Column: type = " + row["ENAME"].GetType() +
      "; value = " + row["ENAME"]);
    Console.WriteLine("HIREDATE Column: type = " + row["HIREDATE"].GetType()+
      "; value = " + row["HIREDATE"]);
    Console.WriteLine("SAL Column: type = " + row["SAL"].GetType() +
      "; value = " + row["SAL"]);
  }
}

SelectCommand

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.

UpdateCommand

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.


OracleDataAdapter Public Methods

OracleDataAdapter public methods are listed in Table 4-39.

Table 4-39 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

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.

Declaration

// C#
public int Fill(DataTable dataTable, OracleRefCursor refCursor); 

Parameters

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.

Fill(DataSet, OracleRefCursor)

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

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.

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.

Declaration

// C#
public int Fill(DataSet dataSet, string srcTable, OracleRefCursor
   refCursor); 

Parameters

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.

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.

Declaration

// C#
public int Fill(DataSet dataSet, int startRecord, int maxRecords, 
   string srcTable, OracleRefCursor refCursor);

Parameters

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.


OracleDataAdapter Events

OracleDataAdapter events are listed in Table 4-40.

Table 4-40 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

RowUpdated

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.

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");
  }
}

RowUpdating

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.

Example

The example for the RowUpdated event also shows how to use the RowUpdating event. See RowUpdated event "Example".


OracleDataAdapter Event Delegates

OracleDataAdapter event delegates are listed in Table 4-41.

Table 4-41 OracleDataAdapter Event Delegates

Event Delegate Name Description
EventHandler Inherited from Component
FillErrorEventHandler Inherited from DbDataAdapter
OracleRowUpdatedEventHandler Event Delegate for the RowUpdated Event
OracleRowUpdatingEventHandler Event Delegate for the RowUpdating Event

OracleRowUpdatedEventHandler

This event delegate handles the RowUpdated Event.

OracleRowUpdatingEventHandler

This event delegate handles the RowUpdating Event.