Release 9.2.0.4
Part Number B10961-01
 Contents |
 Index |
| Oracle® Data Provider for .NET Developer's Guide Release 9.2.0.4 Part Number B10961-01 |
|
Oracle.DataAccess.Client Namespace, 5 of 30
An OracleDataAdapter object represents a data provider object that populates the DataSet and updates changes in the DataSet to the Oracle database.
Object
MarshalByRefObject
Component
DataAdapter
DbDataAdapter
OracleDataAdapter
// C# public sealed class OracleDataAdapter : DbDataAdapter, IDbDataAdapter
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
The OracleDataAdapter examples in this section are based on the EMPINFO table which is defined as follows:
CREATE TABLE empInfo ( empno NUMBER(4) PRIMARY KEY, empName VARCHAR2(20) NOT NULL, hiredate DATE, salary NUMBER(7,2), jobDescription Clob, byteCodes BLOB );
The EMPINFO table has the following values:
EMPNO EMPNAME HIREDATE SALARY JOBDESCRIPTION BYTECODES (Hex Values) ===== ======= ======== ====== ============== ============ 1 KING 01-MAY-81 12345.67 SOFTWARE ENGR {0x12, 0x34} 2 SCOTT 01-SEP-75 34567.89 MANAGER {0x56, 0x78} 3 BLAKE 01-OCT-90 9999.12 TRANSPORT {0x23, 0x45} 4 SMITH NULL NULL NULL NULL
The following example uses the OracleDataAdapter and the dataset to update the EMPINFO table:
// C# public static void AdapterUpdate(string connStr) { string cmdStr = "SELECT EMPNO, EMPNAME, SALARY FROM EMPINFO"; //create the adapter with the selectCommand txt and the //connection string OracleDataAdapter adapter = new OracleDataAdapter(cmdStr, connStr); //get the connection from the adapter OracleConnection connection = adapter.SelectCommand.Connection; //create the UpdateCommand object for updating the EMPINFO table //from the dataset adapter.UpdateCommand = new OracleCommand("UPDATE EMPINFO SET SALARY = "+ " :iSALARY where EMPNO = :iEMPNO", connection); adapter.UpdateCommand.Parameters.Add(":iSALARY", OracleDbType.Double, 0, "SALARY"); adapter.UpdateCommand.Parameters.Add(":iEMPNO", OracleDbType.Int16, 0, "EMPNO"); //Create and fill the DataSet using the EMPINFO DataSet dataset = new DataSet(); adapter.Fill(dataset, "EMPINFO"); //Get the EMPINFO table from the dataset DataTable table = dataset.Tables["EMPINFO"]; //Get the first row from the EMPINFO table DataRow row0 = table.Rows[0]; //update the salary in the first row row0["SALARY"] = 99999.99; //Now update the EMPINFO using the adapter, the salary //of 'KING' is changed to 99999.99 adapter.Update(dataset, "EMPINFO"); }
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleDataAdapter members are listed in the following tables:
OracleDataAdapter constructors are listed in Table 4-31.
| Constructor | Description |
|---|---|
|
Instantiates a new instance of OracleDataAdapter class (Overloaded) |
OracleDataAdapter static methods are listed in Table 4-32.
| Methods | Description |
|---|---|
|
|
Inherited from |
OracleDataAdapter properties are listed in Table 4-33.
OracleDataAdapter public methods are listed in Table 4-34.
OracleDataAdapter events are listed in Table 4-35.
OracleDataAdapter event delegates are listed in Table 4-36.
OracleDataAdapter constructors create new instances of an OracleDataAdapter class.
This constructor creates an instance of an OracleDataAdapter class.
This constructor creates an instance of an OracleDataAdapter class with the provided OracleCommand as the SelectCommand.
This constructor creates an instance of an OracleDataAdapter class with the provided OracleConnection object and the command text for the SelectCommand.
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.
// 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);
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
OracleDataAdapter static methods are listed in Table 4-37.
| Methods | Description |
|---|---|
|
|
Inherited from |
OracleDataAdapter properties are listed in Table 4-38.
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.
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 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 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 hashtable.
Default = null
The SafeMapping property is used, when necessary, to preserve data in the following types:
// C# // The following example shows how to use the SafeMapping property to fill the // dataset public static void UseSafeMapping( string connStr) { //In this SELECT statement, EMPNO, HIREDATE and SALARY must be //preserved using safe type mapping. string cmdStr = "SELECT EMPNO, EMPNAME, HIREDATE, SALARY FROM EMPINFO"; //create the adapter with the selectCommand txt and the connection string OracleDataAdapter adapter = new OracleDataAdapter(cmdStr, connStr); //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 EMPINFO 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 EMPINFO table is case-sensitive, //the safe type mapping column name must also be case-sensitive. adapter.SafeMapping.Add("EMPNO", typeof(string)); //Create and fill the DataSet using the EMPINFO DataSet dataset = new DataSet(); adapter.Fill(dataset, "EMPINFO"); //Get the EMPINFO table from the dataset DataTable table = dataset.Tables["EMPINFO"]; //Get the first row from the EMPINFO table DataRow row0 = table.Rows[0]; //Print out the row info Console.WriteLine("EMPNO Column: type = " + row0["EMPNO"].GetType() + "; value = " + row0["EMPNO"]); Console.WriteLine("EMPNAME Column: type = " + row0["EMPNAME"].GetType() + "; value = " + row0["EMPNAME"]); Console.WriteLine("HIREDATE Column: type = " + row0["HIREDATE"].GetType()+ "; value = " + row0["HIREDATE"]); Console.WriteLine("SALARY Column: type = " + row0["SALARY"].GetType() + "; value = " + row0["SALARY"]); }
Output:
EMPNO Column: type = System.String; value = 1 EMPNAME Column: type = System.String; value = KING HIREDATE Column: type = System.String; value = 01-MAY-81 SALARY Column: type = System.Byte[]; value = System.Byte[]
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 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 4-39.
Fill populates or refreshes the specified DataTable or DataSet.
This method adds or refreshes rows in the specified DataTable to match those in the provided OracleRefCursor object.
This method adds or refreshes rows in the DataSet to match those in the provided OracleRefCursor object.
This method adds or refreshes rows in the specified source table of the DataSet to match those in the provided OracleRefCursor object.
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 4-40.
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# // create the 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); } // create the 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); } public static void AdapterEvents(string connStr) { string cmdStr = "SELECT EMPNO, EMPNAME, SALARY FROM EMPINFO"; //create the adapter with the selectCommand txt and the //connection string OracleDataAdapter adapter = new OracleDataAdapter(cmdStr, connStr); //get the connection from the adapter OracleConnection connection = adapter.SelectCommand.Connection; //create the UpdateCommand object for updating the EMPINFO table //from the dataset adapter.UpdateCommand = new OracleCommand("UPDATE EMPINFO SET SALARY = "+ " :iSALARY where EMPNO = :iEMPNO", connection); adapter.UpdateCommand.Parameters.Add(":iSALARY", OracleDbType.Double, 0, "SALARY"); adapter.UpdateCommand.Parameters.Add(":iEMPNO", OracleDbType.Int16, 0, "EMPNO"); //Create and fill the DataSet using the EMPINFO DataSet dataset = new DataSet(); adapter.Fill(dataset, "EMPINFO"); //Get the EMPINFO table from the dataset DataTable table = dataset.Tables["EMPINFO"]; //Get the first row from the EMPINFO table DataRow row0 = table.Rows[0]; //update the salary in the first row row0["SALARY"] = 99999.99; //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 EMPINFO using the adapter, the salary //of 'KING' is changed to 99999.99 //The OnRowUpdating() and the OnRowUpdated() methods will be triggered adapter.Update(dataset, "EMPINFO"); }
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".
OracleDataAdapter event delegates are listed in Table 4-41.
This event delegate handles the RowUpdated Event.
This event delegate handles the RowUpdating Event.
|
 Copyright © 2002, 2003 Oracle Corporation. All Rights Reserved. |
|