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# using System; using System.Data; using Oracle.DataAccess.Client; class OracleCommandSample { static void Main() { string constr = "User Id=scott;Password=tiger;Data Source=oracle"; 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)); } // Clean up reader.Dispose(); cmd.Dispose(); con.Dispose(); } }
Requirements
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleCommand
members are listed in the following tables:
OracleCommand Constructors
OracleCommand
constructors are listed in Table 5-1.
Table 5-1 OracleCommand Constructors
Constructor | Description |
---|---|
OracleCommand Constructors | Instantiates a new instance of OracleCommand class (Overloaded) |
OracleCommand Static Methods
The OracleCommand
static method is listed in Table 5-2.
OracleCommand Properties
OracleCommand
properties are listed in Table 5-3.
Table 5-3 OracleCommand Properties
Name | Description |
---|---|
AddRowid | Adds the ROWID as part of the select list |
AddToStatementCache | Causes executed statements to be cached, when the property is set to true and statement caching is enabled |
ArrayBindCount | Specifies if the array binding feature is to be used and also specifies the maximum number of array elements to be bound in the Value property |
BindByName | Specifies the binding method in the collection |
CommandText | 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 |
CommandTimeout | Specifies the number of seconds the command is allowed to execute before terminating the execution with an exception |
CommandType | Specifies the command type that indicates how the CommandText property is to be interpreted |
Connection | Specifies the OracleConnection object that is used to identify the connection to execute a command |
Container |
Inherited from Component |
FetchSize | Specifies the size of OracleDataReader 's internal cache to store result set data |
InitialLOBFetchSize | Specifies the amount of data that the OracleDataReader initially fetches for LOB columns |
InitialLONGFetchSize | Specifies the amount of data that the OracleDataReader initially fetches for LONG and LONG RAW columns |
Notification | Indicates that there is a notification request for the command |
NotificationAutoEnlist | Indicates whether or not to register for a database change notification with the database automatically when the command is executed |
Parameters | Specifies the parameters for the SQL statement or stored procedure |
RowSize | Specifies the amount of memory needed by the OracleDataReader internal cache to store one row of data |
Site | Inherited from Component |
Transaction | Specifies the OracleTransaction object in which the OracleCommand executes
Not supported in a .NET stored procedure |
UpdatedRowSource | Specifies how query command results are applied to the row being updated
Not supported in a .NET stored procedure |
XmlCommandType | Specifies the type of XML operation on the OracleCommand |
XmlQueryProperties | Specifies the properties that are used when an XML document is created from the result set of a SQL query statement |
XmlSaveProperties | Specifies the properties that are used when an XML document is used to save changes to the database |
OracleCommand Public Methods
OracleCommand
public methods are listed in Table 5-4.
Table 5-4 OracleCommand Public Methods
Public Method | Description |
---|---|
Cancel | Attempts to cancels a command that is currently executing on a particular connection |
Clone | Creates a copy of OracleCommand object |
CreateObjRef |
Inherited from MarshalByRefObject |
CreateParameter | Creates a new instance of OracleParameter class |
Dispose |
Inherited from Component |
Equals |
Inherited from Object (Overloaded) |
ExecuteNonQuery | Executes a SQL statement or a command using the XmlCommandType and CommandText properties and returns the number of rows affected |
ExecuteReader | Executes a command (Overloaded) |
ExecuteScalar | Returns the first column of the first row in the result set returned by the query |
ExecuteStream | Executes a command using the XmlCommandType and CommandText properties and returns the results in a new Stream object |
ExecuteToStream | Executes a command using the XmlCommandType and CommandText properties and appends the results as an XML document to the existing Stream |
ExecuteXmlReader | Executes a command using the XmlCommandType and CommandText properties and returns the result as an XML document in a .NET XmlTextReader object |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
Prepare |
This method is a no-op |
ToString |
Inherited from Object |
OracleCommand
constructors instantiate new instances of OracleCommand
class.
Overload List:
This constructor instantiates a new instance of OracleCommand
class.
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.
This constructor instantiates a new instance of OracleCommand
class.
Declaration
// C# public OracleCommand();
Remarks
Default constructor.
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.
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
The SQL command or stored procedure to be executed.
OracleConnection
The connection to the Oracle database.
The OracleCommand
static method is listed in Table 5-5.
OracleCommand
properties are listed in Table 5-6.
Table 5-6 OracleCommand Properties
Name | Description |
---|---|
AddRowid | Adds the ROWID as part of the select list |
AddToStatementCache | Causes executed statements to be cached, when the property is set to true and statement caching is enabled |
ArrayBindCount | Specifies if the array binding feature is to be used and also specifies the maximum number of array elements to be bound in the Value property |
BindByName | Specifies the binding method in the collection |
CommandText | 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 |
CommandTimeout | Specifies the number of seconds the command is allowed to execute before terminating the execution with an exception |
CommandType | Specifies the command type that indicates how the CommandText property is to be interpreted |
Connection | Specifies the OracleConnection object that is used to identify the connection to execute a command |
Container | Inherited from Component |
FetchSize | Specifies the size of OracleDataReader 's internal cache to store result set data |
InitialLOBFetchSize | Specifies the amount of data that the OracleDataReader initially fetches for LOB columns |
InitialLONGFetchSize | Specifies the amount that of data the OracleDataReader initially fetches for LONG and LONG RAW columns |
Notification | Indicates that there is a notification request for the command |
NotificationAutoEnlist | Indicates whether or not to register for a database change notification with the database automatically when the command is executed |
Parameters | Specifies the parameters for the SQL statement or stored procedure |
RowSize | Specifies the amount of memory needed by the OracleDataReader internal cache to store one row of data |
Site | Inherited from Component |
Transaction | Specifies the OracleTransaction object in which the OracleCommand executes
Not supported in a .NET stored procedure |
UpdatedRowSource | Specifies how query command results are applied to the row being updated
Not supported in a .NET stored procedure |
XmlCommandType | Specifies the type of XML operation on the OracleCommand |
XmlQueryProperties | Specifies the properties that are used when an XML document is created from the result set of a SQL query statement |
XmlSaveProperties | Specifies the properties that are used when an XML document is used to save changes to the database |
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 ROWID
s of a table, the ROWID
must explicitly be added to the select list without the use of this property.
This property causes executed statements to be cached when the property is set to true
and statement caching is enabled. If statement caching is disabled or if this property is set to false
, the executed statement is not cached.
Declaration
// C# public bool AddToStatementCache{get; set;}
Return Value
Returns bool
value. A value of true
indicates that statements are being added to the cache, false
indicates otherwise.
Property Value
A bool
value that indicates that the statements will be cached when they are executed, if statement caching is enabled.
Remarks
Default is true
.
AddToStatementCache
is ignored if statement caching is disabled. Statement caching is enabled by setting the Statement Cache Size connection string attribute to a value greater than 0
.
When statement caching is enabled, however, this property provides a way to selectively add statements to the cache.
Example
// C# using System; using System.Data; using Oracle.DataAccess.Client; class AddToStatementCacheSample { static void Main() { string constr = "User Id=scott;Password=tiger;Data Source=oracle;" + "statement cache size=10"; OracleConnection con = new OracleConnection(constr); con.Open(); OracleCommand cmd = new OracleCommand("select * from emp", con); if (cmd.AddToStatementCache) Console.WriteLine("Added to the statement cache:" + cmd.CommandText); else Console.WriteLine("Not added to the statement cache:" + cmd.CommandText); // The execution of "select * from emp" will be added to the statement cache // because statement cache size is greater than 0 and OracleCommand's // AddToStatementCache is true by default. OracleDataReader readerEmp = cmd.ExecuteReader(); // Do not add "select * from dept" to the statement cache cmd.CommandText = "select * from dept"; cmd.AddToStatementCache = false; if (cmd.AddToStatementCache) Console.WriteLine("Added to the statement cache:" + cmd.CommandText); else Console.WriteLine("Not added to the statement cache:" + cmd.CommandText); // The execution of "select * from dept" will not be added to the // statement cache because AddToStatementCache is set to false. OracleDataReader readerDept = cmd.ExecuteReader(); // Clean up con.Dispose(); } }
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.
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 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
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.
This property specifies the number of seconds that the command is allowed to execute before terminating with an exception.
Declaration
// C# public int CommandTimeout {get; set;}
Property Value
int
Implements
IDbCommand.CommandTimeout
Exceptions
InvalidArgument
- The specified value is less than 0.
Remarks
Default is 0
seconds, which enforces no time limit.
When the specified timeout value expires before a command execution finishes, the command attempts to cancel. If cancellation is successful, an exception is thrown with the message of ORA-01013:
user
requested
cancel
of
current
operation
. If the command executed in time without any errors, no exceptions are thrown.
In a situation where multiple OracleCommand
objects use the same connection, the timeout expiration on one of the OracleCommand
objects may terminate any of the executions on the single connection. To make the timeout expiration of a OracleCommand
cancel only its own command execution, simply use one OracleCommand
for each connection if that OracleCommand
sets the CommandTimeout
property to a value greater than 0
.
See Also:
|
This property specifies the command type that indicates how the CommandText
property is to be interpreted.
Declaration
// C# public System.Data.CommandType CommandType {get; 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.
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
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 database round-trip.
If the XmlCommandType
property is set to any value other than None
, this property is ignored.
See Also: |
This property specifies the amount of data that the OracleDataReader
initially fetches for LOB columns.
Declaration
// C# public int InitialLOBFetchSize {get; set;}
Property Value
An int
specifying the number of characters or bytes to fetch initially.
Exceptions
ArgumentException
- The InitialLOBFetchSize
value specified is invalid.
Remarks
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.
The InitialLOBFetchSize
value is used to determine the length of the LOB column data to fetch, if the LOB column is in the select list. If the select list does not contain a LOB column, the InitialLOBFetchSize
value is ignored.
When InitialLOBFetchSize
is set to -1
, the entire LOB data is prefetched and stored in the fetch array. Calls to GetString
, GetChars
or GetBytes
in OracleDataReader
allow retrieving the entire data. In this case, the following methods are disabled.
GetOracleBlob
GetOracleClob
GetOracleClobForUpdate
GetOracleBlobForUpdate
This feature works for retrieving data from Oracle Database 9i release 2 (9.2) and later
Default = 0.
For Oracle Database 10g release 2 (10.2) and later:
The maximum value supported for InitialLOBFetchSize
is 2 GB.
Prior to Oracle Database 10g release 2 (10.2), if the InitialLOBFetchSize
is set to a nonzero value, GetOracleBlob
and GetOracleClob
methods were disabled. BLOB
and CLOB
data was fetched by using GetBytes
and GetChars
methods, respectively. In Oracle Database 10g release 2 (10.2), this restriction no longer exists. GetOracleBlob
and GetOracleClob
methods can be used for any InitialLOBFetchSize
value zero or greater.
For releases prior to Oracle Database 10g release 2 (10.2):
The maximum value supported for InitialLOBFetchSize
is 32 K.
To fetch more than the specified InitialLOBFetchSize
value, 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)
If this property is set to 0
, none of the preceding is required
This property specifies the amount of data 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.
When InitialLONGFetchSize
is set to -1
, the entire LONG
or LONG
RAW
data is prefetched and stored in the fetch array. Calls to GetString
, GetChars
, or GetBytes
in OracleDataReader
allow retrieving the entire data.
Default = 0
.
Setting this property to 0
defers the LONG
and LONG
RAW
data retrieval entirely until the application specifically requests it.
See Also: |
This instance property indicates that there is a notification request for the command.
Declaration
// C# public OracleNotificationRequest Notification {set; get;}
Property Value
A notification request for the command.
Remarks
When a changed notification is first registered, the client listener is started in order to receive any database notification. The listener uses the port number defined in the OracleDependency.Port
static field. Subsequent change notification registrations use the same listener in the same client process and do not start another listener.
When Notification
is set to an OracleNotificationRequest
instance, a notification registration is created (if it has not already been created) when the command is executed. Once the registration is created, the properties of the OracleNotificationRequest
instance cannot be modified. If the notification registration has already been created, the result set that is associated with the command is added to the existing registration.
When Notification
is set to null
, subsequent command executions do not require a notification request. If a notification request is not required, set the Notification
property to null
, or set the NotificationAutoEnlist
property to false
.
For Database Change Notification, a notification request can be used for multiple command executions. In that case, any query result set associated with different commands can be invalidated within the same registration.
When the ROWID
column is explicitly included in the query (or when AddRowid
property is set to true
), then the row ID information is populated into the OracleNotificationArgs.Details
property when the OracleDependency.OnChange
event is fired.
This instance property indicates whether or not to register for a database change notification with the database automatically when the command is executed.
Declaration
// C# public bool NotificationAutoEnlist {set; get;}
Property Value
A bool
value indicating whether or not to make a database change notification request automatically, when the command is executed. If NotificationAutoEnlist
is set to true
, and the Notification
property is set appropriately, a database change notification request is registered automatically; otherwise, no database change notification registration is made.
Default value: true
Remarks
A notification request can be used for multiple command executions using the same OracleCommand
instance. In that case, set the NotificationAutoEnlist
property to true
.
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.
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 database round-trip, the OracleDataReader
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()
.
See Also: |
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.
Note: When this property is accessed through anIDbCommand reference, its set accessor method is not operational. |
Remarks (.NET Stored Procedure)
Always returns null
.
This property specifies how query command results are applied to the row to be updated.
Declaration
// C# public System.Data.UpdateRowSource UpdatedRowSource {get; set;}
Property Value
An UpdateRowSource
.
Implements
IDbCommand
Exceptions
ArgumentException
- The UpdateRowSource
value specified is invalid.
Remarks
Always returns UpdateRowSource,
Set accessor throws an ArgumentException
if the value is other than UpdateRowSource.None
.
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.
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.
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 are listed in Table 5-7.
Table 5-7 OracleCommand Public Methods
Public Method | Description |
---|---|
Cancel | Attempts to cancels a command that is currently executing on a particular connection |
Clone | Creates a copy of OracleCommand object |
CreateObjRef |
Inherited from MarshalByRefObject |
CreateParameter | Creates a new instance of OracleParameter class |
Dispose |
Inherited from Component |
Equals |
Inherited from Object (Overloaded) |
ExecuteNonQuery | Executes a SQL statement or a command using the XmlCommandType and CommandText properties and returns the number of rows affected |
ExecuteReader | Executes a command (Overloaded) |
ExecuteScalar | Returns the first column of the first row in the result set returned by the query |
ExecuteStream | Executes a command using the XmlCommandType and CommandText properties and returns the results in a new Stream object |
ExecuteToStream | Executes a command using the XmlCommandType and CommandText properties and appends the results as an XML document to the existing Stream |
ExecuteXmlReader | Executes a command using the XmlCommandType and CommandText properties and returns the result as an XML document in a .NET XmlTextReader object |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
Prepare |
This method is a no-op |
ToString |
Inherited from Object |
This method attempts to cancel a command that is currently executing on a particular connection.
Declaration
// C# public void Cancel();
Implements
IDbCommand.Cancel
Remarks
If cancellation of the command succeeds, an exception is thrown. If cancellation is not successful, no exception is thrown. If there is no command being executed at the time of the Cancel
invocation, Cancel
does nothing. Invoking the Cancel
method does not guarantee that the command executing at the time will always be cancelled. The execution may complete before it can be terminated. In such cases, no exception is thrown.
When multiple OracleCommand
objects share the same connection, only one command can be executed on that connection at any one time. When it is invoked, the Cancel
method attempts to cancel the statement currently running on the connection that the OracleCommand
object is using to execute the command. However, when multiple OracleCommand
objects execute statements on the same connection simultaneously, issuing a Cancel
method invocation may cancel any of the issued commands. This is because the command designated for cancellation may complete before the Cancel
invocation is effective. If this happens, a command executed by a different OracleCommand
could be cancelled instead.
There are several ways to avoid this non-deterministic situation that the Cancel
method can cause:
The application can create just one OracleCommand
object for each connection. Doing so assures that the Cancel
invocation only cancels commands executed by the OracleCommand
object using a particular connection.
Command executions in the application are synchronized between OracleCommand
objects that use the same connection.
These suggestions do not apply if Cancel
is not used in the application.
Because the termination on the currently running execution is non-deterministic, it is recommended that any non-atomic SQL or PL/SQL execution be started within a transaction. When the command execution successfully terminates with an exception of ORA-01013:
user
requested
cancel
of
current
operation
, the transaction can be rolled back for data integrity. Examples of non-atomic execution are collections of DML command executions that are executed one-by-one and multiple DML commands that are part of a PL/SQL stored procedure or function.
Example
// C# // This example shows how command executions can be cancelled in a // deterministic way even if multiple commands are executed on a single // connection. This is accomplished by synchronizing threads through events. // Since the Cancel method terminates the currently running operation on the // connection, threads must be serialized if multiple threads are using the // same connection to execute server round-trip incurring operations. // Furthermore, the example shows how the execution and cancel threads should // be synchronized so that nth iteration of the command execution does not // inappropriately cancel the (n+1)th command executed by the same thread. using System; using System.Data; using Oracle.DataAccess.Client; using System.Threading; class CancelSample { private OracleCommand cmd; Thread t1, t2; // threads signal following events when assigned operations are completed private AutoResetEvent ExecuteEvent = new AutoResetEvent(false); private AutoResetEvent CancelEvent = new AutoResetEvent(false); private AutoResetEvent FinishedEvent = new AutoResetEvent(false); AutoResetEvent[] ExecuteAndCancel = new AutoResetEvent[2]; // Default constructor CancelSample() { cmd = new OracleCommand("select * from all_objects", new OracleConnection("user id=scott;password=tiger;data source=oracle")); ExecuteAndCancel[0] = ExecuteEvent; ExecuteAndCancel[1] = CancelEvent; } // Constructor that takes a particular command and connection CancelSample(string command, OracleConnection con) { cmd = new OracleCommand(command, con); ExecuteAndCancel[0] = ExecuteEvent; ExecuteAndCancel[1] = CancelEvent; } // Execution of the command public void Execute() { OracleDataReader reader = null; try { Console.WriteLine("Execute."); reader = cmd.ExecuteReader(); Console.WriteLine("Execute Done."); reader.Close(); } catch(Exception e) { Console.WriteLine("The command has been cancelled.", e.Message); } Console.WriteLine("ExecuteEvent.Set()"); ExecuteEvent.Set(); } // Canceling of the command public void Cancel() { try { // cancel query if it takes longer than 100 ms to finish execution System.Threading.Thread.Sleep(100); Console.WriteLine("Cancel."); cmd.Cancel(); } catch (Exception e) { Console.WriteLine(e.ToString()); } Console.WriteLine("Cancel done."); Console.WriteLine("CancelEvent.Set()"); CancelEvent.Set(); } // Execution of the command with a potential of cancelling public void ExecuteWithinLimitedTime() { for (int i = 0; i < 5; i++) { Monitor.Enter(typeof(CancelSample)); try { Console.WriteLine("Executing " + this.cmd.CommandText); ExecuteEvent.Reset(); CancelEvent.Reset(); t1 = new Thread(new ThreadStart(this.Execute)); t2 = new Thread(new ThreadStart(this.Cancel)); t1.Start(); t2.Start(); } finally { WaitHandle.WaitAll(ExecuteAndCancel); Monitor.Exit(typeof(CancelSample)); } } FinishedEvent.Set(); } [MTAThread] static void Main() { try { AutoResetEvent[] ExecutionCompleteEvents = new AutoResetEvent[3]; // Create the connection that is to be used by three commands OracleConnection con = new OracleConnection("user id=scott;" + "password=tiger;data source=oracle"); con.Open(); // Create instances of CancelSample class CancelSample test1 = new CancelSample("select * from all_objects", con); CancelSample test2 = new CancelSample("select * from all_objects, emp", con); CancelSample test3 = new CancelSample("select * from all_objects, dept", con); // Create threads for each CancelSample object instance Thread t1 = new Thread(new ThreadStart(test1.ExecuteWithinLimitedTime)); Thread t2 = new Thread(new ThreadStart(test2.ExecuteWithinLimitedTime)); Thread t3 = new Thread(new ThreadStart(test3.ExecuteWithinLimitedTime)); // Obtain a handle to an event from each object ExecutionCompleteEvents[0] = test1.FinishedEvent; ExecutionCompleteEvents[1] = test2.FinishedEvent; ExecutionCompleteEvents[2] = test3.FinishedEvent; // Start all threads to execute three commands using a single connection t1.Start(); t2.Start(); t3.Start(); // Wait for all three commands to finish executing/canceling before //closing the connection WaitHandle.WaitAll(ExecutionCompleteEvents); con.Close(); } catch (Exception e) { Console.WriteLine(e.ToString()); } } }
See Also:
|
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.
This method creates a new instance of OracleParameter
class.
Declaration
// C# public OracleParameter CreateParameter();
Return Value
A new OracleParameter
with default values.
Implements
IDbCommand
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# using System; using System.Data; using Oracle.DataAccess.Client; class ExecuteNonQuerySample { static void Main() { string constr = "User Id=scott;Password=tiger;Data Source=oracle"; OracleConnection con = new OracleConnection(constr); con.Open(); OracleCommand cmd = new OracleCommand( "select sal from emp where empno=7934", con); object sal = cmd.ExecuteScalar(); Console.WriteLine("Employee sal before update: " + sal); cmd.CommandText = "update emp set sal = sal + .01 where empno=7934"; // Auto-commit changes int rowsUpdated = cmd.ExecuteNonQuery(); if (rowsUpdated > 0) { cmd.CommandText = "select sal from emp where empno=7934"; sal = cmd.ExecuteScalar(); Console.WriteLine("Employee sal after update: " + sal); } // Clean up cmd.Dispose(); con.Dispose(); } }
Requirements
For XML support, this method requires Oracle9i XML Developer's Kits (Oracle XDK) or later, to be installed in the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).
Overload List:
ExecuteReader
executes a command specified in the CommandText
.
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.
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 specified command executes this stored procedure when ExecuteReader
is called. If parameters for the stored procedure consist of REF
CURSOR
objects, behavior differs depending on whether ExecuteReader()
or ExecuteNonQuery()
is called. If ExecuteReader()
is invoked, REF
CURSOR
objects can be accessed through the OracleDataReader
that is returned.If more than one REF
CURSOR
is returned from a single execution, subsequent REF
CURSOR
objects can be accessed sequentially by the NextResult
method on the OracleDataReader
. If the ExecuteNonQuery
method is invoked, the output parameter value can be cast to a OracleRefCursor
type and the OracleRefCursor
object then can be used to either populate a DataSet
or create an OracleDataReader
object from it. This approach provides random access to all the REF
CURSOR
objects returned as output parameters.
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# using System; using System.Data; using Oracle.DataAccess.Client; class ExecuteReaderSample { static void Main() { string constr = "User Id=scott;Password=tiger;Data Source=oracle"; OracleConnection con = new OracleConnection(constr); con.Open(); OracleCommand cmd = new OracleCommand("select ename from emp", con); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine("Employee Name : " + reader.GetString(0)); } // Clean up reader.Dispose(); cmd.Dispose(); con.Dispose(); } }
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
The 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 command 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
CURSOR
s, read the section on OracleRefCursor
s 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
.
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# using System; using System.Data; using Oracle.DataAccess.Client; class ExecuteScalarSample { static void Main() { string constr = "User Id=scott;Password=tiger;Data Source=oracle"; OracleConnection con = new OracleConnection(constr); con.Open(); OracleCommand cmd = new OracleCommand("select count(*) from emp", con); object count = cmd.ExecuteScalar(); Console.WriteLine("There are {0} rows in table emp", count); // Clean up cmd.Dispose(); con.Dispose(); } }
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 later to be installed on the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).
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 later to be installed on the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).
See Also:
|
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 later to be installed on the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).