Oracle Data Provider Classes

This chapter describes the classes and public methods Oracle Data Provider for .NET exposes for ADO.NET programmers. They are:

• OracleCommand Class

• OracleCommandBuilder Class

• OracleConnection Class

• OracleDataAdapter Class

• OracleDataReader Class

• OracleError Class

• OracleErrorCollection Class

• OracleException Class

• OracleFailoverEventArgs Class

• OracleFailoverEventHandler Delegate

• OracleGlobalization Class

• OracleInfoMessageEventArgs Class

• OracleInfoMessageEventHandler Delegate

• OracleParameter Class

• OracleParameterCollection Class

• OracleRowUpdatedEventArgs Class

• OracleRowUpdatedEventHandler Delegate

• OracleRowUpdatingEventArgs Class

• OracleRowUpdatingEventHandler Delegate

• OracleTransaction Class

• OracleXmlQueryProperties Class

• OracleXmlSaveProperties Class

---

OracleCommand Class

An OracleCommand object represents a SQL command, a stored procedure, or a table name. The OracleCommand object is responsible for formulating the request and passing it to the database. If results are returned, OracleCommand is responsible for returning results as an OracleDataReader, a .NET XmlReader, a .NET Stream, a scalar value, or as output parameters.

Class Inheritance

Object

  MarshalByRefObject

    Component

      OracleCommand

Declaration

// C#
public sealed class OracleCommand : Component, IDbCommand, ICloneable

Thread Safety

All public static methods are thread-safe, although instance methods do not guarantee thread safety.

Remarks

The execution of any transaction-related statements from an OracleCommand is not recommended because it is not reflected in the state of the OracleTransaction object represents the current local transaction, if one exists.

ExecuteXmlReader, ExecuteStream, and ExecuteToStream methods are only supported for XML operations.

ExecuteReader and ExecuteScalar methods are not supported for XML operations.

Example

// C#
...
string conStr = "User Id=scott;Password=tiger;Data Source=oracle";

// Create the OracleConnection
OracleConnection con = new OracleConnection(conStr);
con.Open();

string cmdQuery = "select ename, empno from emp";

// Create the OracleCommand
OracleCommand cmd = new OracleCommand(cmdQuery);
cmd.Connection = con;
cmd.CommandType = CommandType.Text;

// Execute command, create OracleDataReader object
OracleDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    // output Employee Name and Number
    Console.WriteLine("Employee Name : " + reader.GetString(0) + " , " +
                      "Employee Number : " + reader.GetDecimal(1));
}

// Dispose OracleDataReader object
reader.Dispose();

// Dispose OracleCommand object
cmd.Dispose();

// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
...

Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleCommand Members

OracleCommand members are listed in the following tables:

OracleCommand Constructors

OracleCommand constructors are listed in Table 4-1.

OracleCommand Static Methods

OracleCommand static methods are listed in Table 4-2.

OracleCommand Properties

OracleCommand properties are listed in Table 4-3.

OracleCommand Public Methods

OracleCommand public methods are listed in Table 4-4.

OracleCommand Constructors

OracleCommand constructors instantiate new instances of OracleCommand class.

Overload List:

• OracleCommand()

  This constructor instantiates a new instance of OracleCommand class.

• OracleCommand(string)

  This constructor instantiates a new instance of OracleCommand class using the supplied SQL command or stored procedure, and connection to the Oracle database.

• OracleCommand(string, OracleConnection)

  This constructor instantiates a new instance of OracleCommand class using the supplied SQL command or stored procedure, and connection to the Oracle database.

  
  

  See Also: Oracle.DataAccess.Client Namespace OracleCommand Class OracleCommand Members

  
  

OracleCommand()

This constructor instantiates a new instance of OracleCommand class.

Declaration

// C#
public OracleCommand();

Remarks

Default constructor.

OracleCommand(string)

This constructor instantiates a new instance of OracleCommand class using the supplied SQL command or stored procedure, and connection to the Oracle database.

Declaration

// C#
public OracleCommand(string cmdText);

Parameters

• cmdText

  The SQL command or stored procedure to be executed.

  
  

  See Also: Oracle.DataAccess.Client Namespace OracleCommand Class OracleCommand Members

  
  

OracleCommand(string, OracleConnection)

This constructor instantiates a new instance of OracleCommand class using the supplied SQL command or stored procedure, and connection to the Oracle database.

Declaration

// C#
public OracleCommand(string cmdText, OracleConnection OracleConnection);

Parameters

• cmdText

  Specifies the SQL command or stored procedure to be executed.

• OracleConnection

  Specifies the connection to the Oracle database.

  
  

  See Also: Oracle.DataAccess.Client Namespace OracleCommand Class OracleCommand Members

  
  

OracleCommand Static Methods

OracleCommand static methods are listed in Table 4-5.

OracleCommand Properties

OracleCommand properties are listed in Table 4-6.

AddRowid

This property adds the ROWID as part of the select list.

Declaration

// C#
public bool AddRowid {get; set;}

Property Value

bool

Remarks

Default is false.

This ROWID column is hidden and is not accessible by the application. To gain access to the ROWIDs of a table, the ROWID must explicitly be added to the select list without the use of this property.

ArrayBindCount

This property specifies if the array binding feature is to be used and also specifies the number of array elements to be bound in the OracleParameter Value property.

Declaration

// C#
public int ArrayBindCount {get; set;}

Property Value

An int value that specifies number of array elements to be bound in the OracleParameter Value property.

Exceptions

ArgumentException - The ArrayBindCount value specified is invalid.

Remarks

Default = 0.

If ArrayBindCount is equal to 0, array binding is not used; otherwise, array binding is used and OracleParameter Value property is interpreted as an array of values. The value of ArrayBindCount must be specified to use the array binding feature.

If neither DbType nor OracleDbType is set, it is strongly recommended that you set ArrayBindCount before setting the OracleParameter Value property so that inference of DbType and OracleDbType from Value can be correctly done.

Array binding is not used by default.

If the XmlCommandType property is set to any value other than None, this property is ignored.

BindByName

This property specifies the binding method in the collection.

Declaration

// C#
public bool BindByName {get; set;}

Property Value

Returns true if the parameters are bound by name; returns false if the parameters are bound by position.

Remarks

Default = false.

BindByName is supported only for OracleCommand.CommandType = CommandType.Text, not for OracleCommand.CommandType = CommandType.StoredProcedure.

BindByName is ignored under the following conditions:

• The value of the XmlCommandType property is Insert, Update, or Delete.

• The value of the XmlCommandType property is Query, but there are no parameters set on the OracleCommand.

If the XmlCommandType property is OracleXmlCommandType.Query and any parameters are set on the OracleCommand, the BindByName property must be set to true. Otherwise, the following OracleCommand methods throw an InvalidOperationException.

• ExecuteNonQuery

• ExecuteXmlReader

• ExecuteStream

• ExecuteToStream

  
  

  See Also: Oracle.DataAccess.Client Namespace OracleCommand Class OracleCommand Members "Array Binding" "Value "

  
  

CommandText

This property specifies the SQL statement or stored procedure to run against the Oracle database or the XML data used to store changes to the Oracle database.

Declaration

// C#
public string CommandText {get; set;}

Property Value

A string.

Implements

IDbCommand

Remarks

The default is an empty string.

When the CommandType property is set to StoredProcedure, the CommandText property is set to the name of the stored procedure. The command calls this stored procedure when an Execute method is called.

The effects of XmlCommandType values on CommandText are:

• XmlCommandType = None.

  CommandType property determines the contents of CommandText.

• XmlCommandType = Query.

  CommandText must be a SQL query. The SQL query should be a select statement. CommandType property is ignored.

• XmlCommandType property is Insert, Update, or Delete.

  CommandText must be an XML document. CommandType property is ignored.

  
  

  See Also: Oracle.DataAccess.Client Namespace OracleCommand Class OracleCommand Members

  
  

CommandType

This property specifies the command type that indicates how the CommandText property is to be interpreted.

Declaration

// C#
public System.Data.CommandType CommandType {final get; final set;}

Property Value

A CommandType.

Exceptions

ArgumentException - The value is not a valid CommandType such as: CommandType.Text, CommandType.StoredProcedure, CommandType.TableDirect.

Remarks

Default = CommandType.Text

If the value of the XmlCommandType property is not None, then the CommandType property is ignored.

Connection

This property specifies the OracleConnection object that is used to identify the connection to execute a command.

Declaration

// C#
public OracleConnection Connection {get; set;}

Property Value

An OracleConnection object.

Implements

IDbCommand

Remarks

Default = null

FetchSize

This property specifies the size of OracleDataReader's internal cache to store result set data.

Declaration

// C#
public long FetchSize {get; set;}

Property Value

A long that specifies the size (in bytes) of the OracleDataReader's internal cache.

Exceptions

ArgumentException - The FetchSize value specified is invalid.

Remarks

Default = 65536.

The FetchSize property is inherited by the OracleDataReader that is created by a command execution returning a result set. The FetchSize property on the OracleDataReader object determines the amount of data the OracleDataReader fetches into its internal cache for each server round-trip.

If the XmlCommandType property is set to any value other than None, this property is ignored.

InitialLOBFetchSize

This property specifies the amount that the OracleDataReader initially fetches for LOB columns.

Declaration

// C#
public int InitialLOBFetchSize  {get; set;}

Property Value

An int specifying the amount.

Exceptions

ArgumentException - The InitialLOBFetchSize value specified is invalid.

Remarks

The maximum value supported for InitialLOBFetchSize is 32767. If this property is set to a higher value, the provider resets it to 32767.

Default = 0.

The value of InitialLOBFetchSize specifies the initial amount of LOB data that is immediately fetched by the OracleDataReader. The property value specifies the number of characters for CLOB and NCLOB data and the number of bytes for BLOB data. To fetch more than the specified InitialLOBFetchSize amount, one of the following must be in the select list:

• primary key

• ROWID

• unique columns - (defined as a set of columns on which a unique constraint has been defined or a unique index has been created, where at least one of the columns in the set has a NOT NULL constraint defined on it)

The InitialLOBFetchSize value is used to determine the length of the LOB column data to fetch if LOB column is in the select list. If the select list does not contain a LOB column, the InitialLOBFetchSize value is ignored.

A primary key, a ROWID, or unique columns are not required if this property is set to 0.

If the InitialLOBFetchSize is set to a nonzero value, GetOracleBlob() and GetOracleClob() methods are disabled. BLOB and CLOB data are fetched by using GetBytes() and GetChars(), respectively.

InitialLONGFetchSize

This property specifies the amount that the OracleDataReader initially fetches for LONG and LONG RAW columns.

Declaration

// C#
public int InitialLONGFetchSize  {get; set;}

Property Value

An int specifying the amount.

Exceptions

ArgumentException - The InitialLONGFetchSize value specified is invalid.

Remarks

The maximum value supported for InitialLONGFetchSize is 32767. If this property is set to a higher value, the provider resets it to 32767.

The value of InitialLONGFetchSize specifies the initial amount of LONG or LONG RAW data that is immediately fetched by the OracleDataReader. The property value specifies the number of characters for LONG data and the number of bytes for LONG RAW. To fetch more than the specified InitialLONGFetchSize amount, one of the following must be in the select list:

• primary key

• ROWID

• unique columns - (defined as a set of columns on which a unique constraint has been defined or a unique index has been created, where at least one of the columns in the set has a NOT NULL constraint defined on it)

The InitialLONGFetchSize value is used to determine the length of the LONG and LONG RAW column data to fetch if one of the two is in the select list. If the select list does not contain a LONG or a LONG RAW column, the InitialLONGFetchSize value is ignored.

Default = 0.

Setting this property to 0 defers the LONG and LONG RAW data retrieval entirely until the application specifically requests it.

Parameters

This property specifies the parameters for the SQL statement or stored procedure.

Declaration

// C#
public OracleParameterCollection Parameters {get;}

Property Value

OracleParameterCollection

Implements

IDbCommand

Remarks

Default value = an empty collection

The number of the parameters in the collection must be equal to the number of parameter placeholders within the command text, or an error is raised.

If the command text does not contain any parameter tokens (such as,:1,:2), the values in the Parameters property are ignored.

RowSize

This property specifies the amount of memory needed by the OracleDataReader internal cache to store one row of data.

Declaration

// C#
public long RowSize {get;}

Property Value

A long that indicates the amount of memory (in bytes) that an OracleDataReader needs to store one row of data for the executed query.

Remarks

Default value = 0

The RowSize property is set to a nonzero value after the execution of a command that returns a result set. This property can be used at design time or dynamically during run-time, to set the FetchSize, based on number of rows. For example, to enable the OracleDataReader to fetch N rows for each server round-trip, the OracleDataReader's FetchSize property can be set dynamically to RowSize * N. Note that for the FetchSize to take effect appropriately, it must be set after OracleCommand.ExecuteReader() but before OracleDataReader.Read().

Transaction

This property specifies the OracleTransaction object in which the OracleCommand executes.

Declaration

// C#
public OracleTransaction Transaction {get;}

Property Value

OracleTransaction

Implements

IDbCommand

Remarks

Default value = null

Transaction returns a reference to the transaction object associated with the OracleCommand connection object. Thus the command is executed in whatever transaction context its connection is currently in.

UpdatedRowSource

This property specifies how query command results are applied to the row to be updated.

Declaration

// C#
public System.Data.UpdateRowSource UpdatedRowSource {final get; final set;}

Property Value

An UpdateRowSource.

Implements

IDbCommand

Exceptions

ArgumentException - The UpdateRowSource value specified is invalid.

Remarks

Default = UpdateRowSource.None if the command is automatically generated. Default = UpdateRowSource.Both if the command is not automatically generated.

XmlCommandType

This property specifies the type of XML operation on the OracleCommand.

Declaration

// C#
public OracleXmlCommandType XmlCommandType {get; set;}

Property Value

An OracleXmlCommandType.

Remarks

Default value is None.

XmlCommandType values and usage:

• None - The CommandType property specifies the type of operation.

• Query - CommandText property must be set to a SQL select statement. The query is executed, and the results are returned as an XML document. The SQL select statement in the CommandText and the properties specified by the XmlQueryProperties property are used to perform the operation. The CommandType property is ignored.

• Insert, Update, or Delete - CommandText property is an XML document containing the changes to be made. The XML document in the CommandText and the properties specified by the XmlSaveProperties property are used to perform the operation. The CommandType property is ignored.

  
  

  See Also: Oracle.DataAccess.Client Namespace OracleCommand Class OracleCommand Members

  
  

XmlQueryProperties

This property specifies the properties that are used when an XML document is created from the result set of a SQL query statement.

Declaration

// C#
public OracleXmlQueryProperties XmlQueryProperties {get; set;}

Property Value

OracleXmlQueryProperties.

Remarks

When a new instance of OracleCommand is created, an instance of OracleXmlQueryProperties is automatically available on the OracleCommand instance through the OracleCommand.XmlQueryProperties property.

A new instance of OracleXmlQueryProperties can be assigned to an OracleCommand instance. Assigning an instance of OracleXmlQueryProperties to the XmlQueryProperties of an OracleCommand instance creates a new instance of the given OracleXmlQueryProperties instance for the OracleCommand. This way each OracleCommand instance has its own OracleXmlQueryProperties instance.

Use the default constructor to get a new instance of OracleXmlQueryProperties.

Use the OracleXmlQueryProperties.Clone() method to get a copy of an OracleXmlQueryProperties instance.

XmlSaveProperties

This property specifies the properties that are used when an XML document is used to save changes to the database.

Declaration

// C#
public OracleXmlSaveProperties XmlSaveProperties {get; set;}

Property Value

OracleXmlSaveProperties.

Remarks

When a new instance of OracleCommand is created, an instance of OracleXmlSaveProperties is automatically available on the OracleCommand instance through the OracleCommand.XmlSaveProperties property.

A new instance of OracleXmlSaveProperties can be assigned to an OracleCommand instance. Assigning an instance of OracleXmlSaveProperties to the XmlSaveProperties of an OracleCommand instance creates a new instance of the given OracleXmlSaveProperties instance for the OracleCommand. This way each OracleCommand instance has its own OracleXmlSaveProperties instance.

Use the default constructor to get a new instance of OracleXmlSaveProperties.

Use the OracleXmlSaveProperties.Clone() method to get a copy of an OracleXmlSaveProperties instance.

OracleCommand Public Methods

OracleCommand public methods are listed in Table 4-7.

Clone

This method creates a copy of an OracleCommand object.

Declaration

// C#
public object Clone();

Return Value

An OracleCommand object.

Implements

ICloneable

Remarks

The cloned object has the same property values as that of the object being cloned.

Example

// C#
...
//Need a proper casting for the return value when cloned
OracleCommand cmd_cloned = (OracleCommand) cmd.Clone();
...

CreateParameter

This method creates a new instance of OracleParameter class.

Declaration

// C#
public OracleParameter CreateParameter();

Return Value

A new OracleParameter with default values.

Implements

IDbCommand

ExecuteNonQuery

This method executes a SQL statement or a command using the XmlCommandType and CommandText properties and returns the number of rows affected.

Declaration

// C#
public int ExecuteNonQuery();

Return Value

The number of rows affected.

Implements

IDbCommand

Exceptions

InvalidOperationException - The command cannot be executed.

Remarks

ExecuteNonQuery returns the number of rows affected, for the following:

• If the command is UPDATE, INSERT, or DELETE and the XmlCommandType property is set to OracleXmlCommandType.None.

• If the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, OracleXmlCommandType.Delete.

For all other types of statements, the return value is -1.

ExecuteNonQuery is used for either of the following:

• catalog operations (for example, querying the structure of a database or creating database objects such as tables).

• changing the data in a database without using a DataSet, by executing UPDATE, INSERT, or DELETE statements.

• changing the data in a database using an XML document.

Although ExecuteNonQuery does not return any rows, it populates any output parameters or return values mapped to parameters with data.

If the XmlCommandType property is set to OracleXmlCommandType.Query then ExecuteNonQuery executes the select statement in the CommandText property, and if successful, returns -1. The XML document that is generated is discarded. This is useful for determining if the operation completes successfully without getting the XML document back as a result.

If the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, or OracleXmlCommandType.Delete, then the value of the CommandText property is an XML document. ExecuteNonQuery saves the changes in that XML document to the table or view that is specified in the XmlSaveProperties property. The return value is the number of rows that are processed in the XML document. Also, each row in the XML document could affect multiple rows in the database, but the return value is still the number of rows in the XML document.

Example

// C#
...
OracleConnection con = new OracleConnection("User Id=scott;Password=tiger;"+
         "Data Source=oracle");
OracleCommand cmd = new OracleCommand("update emp set sal = 3000" +
         "where empno=7934", con);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
...

Requirements

For XML support, this method requires Oracle9i XML Developer's Kits (Oracle XDK) or higher, to be installed in the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).

ExecuteReader

ExecuteReader executes a command specified in the CommandText.

Overload List:

• ExecuteReader()

  This method executes a command specified in the CommandText and returns an OracleDataReader object.

• ExecuteReader(CommandBehavior)

  This method executes a command specified in the CommandText and returns an OracleDataReader object, using the specified CommandBehavior value.

  
  

  See Also: Oracle.DataAccess.Client Namespace OracleCommand Class OracleCommand Members

  
  

ExecuteReader()

This method executes a command specified in the CommandText and returns an OracleDataReader object.

Declaration

// C#
public OracleDataReader ExecuteReader();

Return Value

An OracleDataReader.

Implements

IDbCommand

Exceptions

InvalidOperationException - The command cannot be executed.

Remarks

When the CommandType property is set to CommandType.StoredProcedure, the CommandText property should be set to the name of the stored procedure.

The command executes this stored procedure when you call ExecuteReader(). If parameters for the stored procedure consists of REF CURSORs, behavior differs depending on whether ExecuteReader() or ExecuteNonQuery() is called.

The value of 100 is used for the FetchSize. If 0 is specified, no rows are fetched. For further information, see "Obtaining LONG and LONG RAW Data".

If the value of the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, OracleXmlCommandType.Delete, or OracleXmlCommandType.Query then the ExecuteReader method throws an InvalidOperationException.

Example

// C#
...
OracleConnection con = new OracleConnection("User Id=scott;Password=tiger;" 
       + "Data Source=oracle");
OracleCommand cmd = new OracleCommand("select ename from emp", con);
cmd.Connection.Open();
OracleDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    Console.WriteLine("Employee Name : " + reader.GetString(0));
}
      
reader.Dispose();
cmd.Dispose();
...

ExecuteReader(CommandBehavior)

This method executes a command specified in the CommandText and returns an OracleDataReader object, using the specified behavior.

Declaration

// C#
public OracleDataReader ExecuteReader(CommandBehavior behavior);

Parameters

• behavior

  Specifies expected behavior.

Return Value

An OracleDataReader.

Implements

IDbCommand

Exceptions

InvalidOperationException - The command cannot be executed.

Remarks

A description of the results and the effect on the database of the query command is indicated by the supplied behavior that specifies command behavior.

For valid CommandBehavior values and for the expected behavior of each CommandBehavior enumerated type, read the .NET Framework documentation.

When the CommandType property is set to CommandType.StoredProcedure, the CommandText property should be set to the name of the stored procedure. The command executes this stored procedure when ExecuteReader() is called.

If the stored procedure returns stored REF CURSORs, read the section on OracleRefCursors for more details. See "OracleRefCursor Class".

The value of 100 is used for the FetchSize. If 0 is specified, no rows are fetched. For more information, see "Obtaining LONG and LONG RAW Data".

If the value of the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, OracleXmlCommandType.Delete, or OracleXmlCommandType.Query then the ExecuteReader method throws an InvalidOperationException.

ExecuteScalar

This method executes the query using the connection, and returns the first column of the first row in the result set returned by the query.

Declaration

// C#
public object ExecuteScalar();

Return Value

An object which represents the value of the first row, first column.

Implements

IDbCommand

Exceptions

InvalidOperationException - The command cannot be executed.

Remarks

Extra columns or rows are ignored. ExecuteScalar retrieves a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader() method, and then performing the operations necessary to generate the single value using the data returned by an OracleDataReader.

If the query does not return any row, it returns null.

The ExecuteScalar method throws an InvalidOperationException, if the value of the XmlCommandType property is set to one of the following OracleXmlCommandType values: Insert, Update, Delete, Query.

Example

// C#
...
CmdObj.CommandText = "select count(*) from emp";
decimal count = (decimal) CmdObj.ExecuteScalar();
...

ExecuteStream

This method executes a command using the XmlCommandType and CommandText properties and returns the result as an XML document in a new Stream object.

Declaration

// C#
public Stream ExecuteStream();

Return Value

A Stream.

Remarks

The behavior of ExecuteStream varies depending on the XmlCommandType property value:

• XmlCommandType = OracleXmlCommandType.None

  ExecuteStream throws an InvalidOperationException.

• XmlCommandType = OracleXmlCommandType.Query

  ExecuteStream executes the select statement in the CommandText property, and if successful, returns an OracleClob object containing the XML document that was generated. OracleClob contains Unicode characters.

  If the SQL query does not return any rows, then ExcecuteStream returns an OracleClob object containing an empty XML document.

• XmlCommandType = OracleXmlCommandType.Insert, OracleXmlCommandType.Update, or OracleXmlCommandType.Delete.

  The value of the CommandText property is an XML document. ExecuteStream saves the data in that XML document to the table or view that is specified in the XmlSaveProperties property and an empty OracleClob is returned.

Requirements

For database releases 8.1.7 and 9.0.1 only: This method requires Oracle XML Developer's Kit (Oracle XDK) release 9.2 or higher to be installed on the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).

ExecuteToStream

This method executes a command using the XmlCommandType and CommandText properties and appends the result as an XML document to the existing Stream provided by the application.

Declaration

// C#
public void ExecuteToStream(Stream outputStream);

Parameters

• outputStream

  A Stream.

Remarks

The behavior of ExecuteToStream varies depending on the XmlCommandType property value:

• XmlCommandType = OracleXmlCommandType.None

  ExecuteToStream throws an InvalidOperationException.

• XmlCommandType = OracleXmlCommandType.Query

  ExecuteToStream executes the select statement in the CommandText property, and if successful, appends the XML document that was generated to the given Stream.

  If the SQL query does not return any rows, then nothing is appended to the given Stream. The character set of the appended data is Unicode.

• XmlCommandType = OracleXmlCommandType.Insert, OracleXmlCommandType.Update, or OracleXmlCommandType.Delete

  The value of the CommandText property is an XML document. ExecuteToStream saves the changes in that XML document to the table or view that is specified in the XmlSaveProperties property. Nothing is appended to the given Stream.

Requirements

For database releases 8.1.7 and 9.0.1 only: This method requires Oracle XML Developer's Kit (Oracle XDK) release 9.2 or higher to be installed on the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).

ExecuteXmlReader

This method executes the command using the XmlCommandType and CommandText properties and returns the result as an XML document in a .NET XmlTextReader object.

Declaration

// C#
public XmlReader ExecuteXmlReader();

Return Value

An XmlReader.

Remarks

The behavior of ExecuteXmlReader varies depending on the XmlCommandType property value:

• XmlCommandType = OracleXmlCommandType.None

  ExecuteStream throws an InvalidOperationException.

• XmlCommandType = OracleXmlCommandType.Query

  ExecuteXmlReader executes the select statement in the CommandText property, and if successful, returns a .NET XmlTextReader object containing the XML document that was generated.

  If the XML document is empty, which can happen if the SQL query does not return any rows, then an empty .NET XmlTextReader object is returned.

• XmlCommandType = OracleXmlCommandType.Insert, OracleXmlCommandType.Update, or OracleXmlCommandType.Delete.

  The value of the CommandText property is an XML document, and ExecuteXmlReader saves the changes in that XML document to the table or view that is specified in the XmlSaveProperties property. An empty .NET XmlTextReader object is returned.

Requirements

For database releases 8.1.7 and 9.0.1 only: This method requires Oracle XML Developer's Kit (Oracle XDK) release 9.2 or higher to be installed on the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).

---

OracleCommandBuilder Class

An OracleCommandBuilder object provides automatic SQL generation for the OracleDataAdapter when updates are made to the database.

Class Inheritance

Object

  MarshalByRefObject

    Component

      OracleCommandBuilder

Declaration

// C#
public sealed class OracleCommandBuilder : Component

Thread Safety

All public static methods are thread-safe, although instance methods do not guarantee thread safety.

Remarks

OracleCommandBuilder automatically generates SQL statements for single-table updates when the SelectCommand property of the OracleDataAdapter is set. An exception is thrown if the DataSet contains multiple tables. The OracleCommandBuilder registers itself as a listener for RowUpdating events whenever its DataAdapter property is set. Only one OracleDataAdapter object and one OracleCommandBuilder object can be associated with each other at one time.

To generate INSERT, UPDATE, or DELETE statements, the OracleCommandBuilder uses ExtendedProperties within the DataSet to retrieve a required set of metadata. If the SelectCommand is changed after the metadata is retrieved (for example, after the first update), the RefreshSchema method should be called to update the metadata.

OracleCommandBuilder first looks for the metadata from the ExtendedProperties of the DataSet; if the metadata is not available, OracleCommandBuilder uses the SelectCommand property of the OracleDataAdapter to retrieve the metadata.

Example

The OracleCommandBuilder 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 OracleCommandBuilder object to create the UpdateCommand for the OracleDataAdapter object when OracleDataAdapter.Update() is called.

// C#
public static void BuilderUpdate(string connStr)
{
  string cmdStr = "SELECT EMPNO, EMPNAME, JOBDESCRIPTION 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 builder for the adapter to automatically generate
  //the Command when needed
  OracleCommandBuilder builder = new OracleCommandBuilder(adapter);

  //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 job description in the first row
  row0["JOBDESCRIPTION"] = "MANAGER";

  //Now update the EMPINFO using the adapter, the job description
  //of 'KING' is changed to 'MANAGER'
  //The OracleCommandBuilder will create the UpdateCommand for the
  //adapter to update the EMPINFO table
  adapter.Update(dataset, "EMPINFO");

}

Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

OracleCommandBuilder Members

OracleCommandBuilder members are listed in the following tables:

OracleCommandBuilder Constructors

OracleCommandBuilder constructors are listed in Table 4-8.

OracleCommandBuilder Static Methods

OracleCommandBuilder static methods are listed in Table 4-9.

OracleCommandBuilder Properties

OracleCommandBuilder properties are listed in Table 4-10.

OracleCommandBuilder Public Methods

OracleCommandBuilder public methods are listed in Table 4-11.

OracleCommandBuilder Events

OracleCommandBuilder events are listed in Table 4-12.

OracleCommandBuilder Event Delegates

OracleCommandBuilder event delegates are listed in Table 4-13.

OracleCommandB