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.
System.Object
System.MarshalByRefObject
System.ComponentModel.Component
System.Data.Common.DbCommand (ADO.NET 2.0 only)
Oracle.DataAccess.Client.OracleCommand
// ADO.NET 2.0: C# public sealed class OracleCommand : DbCommand, ICloneable
// ADO.NET 1.x: C#
public sealed class OracleCommand : Component, IDbCommand, ICloneable
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
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.
To minimize the number of open server cursors, OracleCommand objects should be explicitly disposed.
// 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();
}
}
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
Microsoft .NET Framework Version: 1.0 or later
See Also:
OracleCommand members are listed in the following tables.
OracleCommand constructors are listed in Table 5-1.
Table 5-1 OracleCommand Constructors
| Constructor | Description |
|---|---|
|
Instantiates a new instance of |
The OracleCommand static method is listed in Table 5-2.
Table 5-2 OracleCommand Static Method
| Method | Description |
|---|---|
|
|
Inherited from |
OracleCommand properties are listed in Table 5-3.
Table 5-3 OracleCommand Properties
| Property | Description |
|---|---|
|
Adds the |
|
|
Causes executed statements to be cached, when the property is set to |
|
|
Specifies if the array binding feature is to be used and also specifies the maximum number of array elements to be bound in the |
|
|
Specifies the binding method in the collection |
|
|
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 |
|
|
Specifies the number of seconds the command is allowed to execute before terminating the execution with an exception |
|
|
Specifies the command type that indicates how the |
|
|
Specifies the |
|
|
|
Inherited from |
|
Specifies whether or not the |
|
|
Specifies the size of |
|
|
Specifies the amount of data that the |
|
|
Specifies the amount of data that the |
|
|
Indicates that there is a notification request for the command |
|
|
Indicates whether or not to register for a database change notification with the database automatically when the command is executed |
|
|
Specifies the parameters for the SQL statement or stored procedure |
|
|
Specifies the amount of memory needed by the |
|
|
Site |
Inherited from |
|
Specifies the Not supported in a .NET stored procedure |
|
|
Specifies how query command results are applied to the row being updated Not supported in a .NET stored procedure |
|
|
Specifies the type of XML operation on the |
|
|
Specifies the properties that are used when an XML document is created from the result set of a SQL query statement |
|
|
Specifies the properties that are used when an XML document is used to save changes to the database |
OracleCommand public methods are listed in Table 5-4.
Table 5-4 OracleCommand Public Methods
| Public Method | Description |
|---|---|
|
Attempts to cancels a command that is currently executing on a particular connection |
|
|
Creates a copy of |
|
|
|
Inherited from |
|
Creates a new instance of |
|
|
|
Inherited from |
|
|
Inherited from |
|
Executes a SQL statement or a command using the |
|
|
Executes a command (Overloaded) |
|
|
Returns the first column of the first row in the result set returned by the query |
|
|
Executes a command using the |
|
|
Executes a command using the |
|
|
Executes a command using the |
|
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
|
|
This method is a no-op |
|
|
Inherited from |
OracleCommand constructors instantiate new instances of OracleCommand class.
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.
// C# public OracleCommand();
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.
// C#
public OracleCommand(string cmdText);
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.
// C# public OracleCommand(string cmdText, OracleConnection OracleConnection);
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.
Table 5-5 OracleCommand Static Method
| Method | Description |
|---|---|
|
|
Inherited from |
OracleCommand properties are listed in Table 5-6.
Table 5-6 OracleCommand Properties
| Property | Description |
|---|---|
|
Adds the |
|
|
Causes executed statements to be cached, when the property is set to |
|
|
Specifies if the array binding feature is to be used and also specifies the maximum number of array elements to be bound in the |
|
|
Specifies the binding method in the collection |
|
|
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 |
|
|
Specifies the number of seconds the command is allowed to execute before terminating the execution with an exception |
|
|
Specifies the command type that indicates how the |
|
|
Specifies the |
|
|
Container |
Inherited from |
|
Specifies whether or not the |
|
|
Specifies the size of |
|
|
Specifies the amount of data that the |
|
|
Specifies the amount that of data the |
|
|
Indicates that there is a notification request for the command |
|
|
Indicates whether or not to register for a database change notification with the database automatically when the command is executed |
|
|
Specifies the parameters for the SQL statement or stored procedure |
|
|
Specifies the amount of memory needed by the |
|
|
Site |
Inherited from |
|
Specifies the Not supported in a .NET stored procedure |
|
|
Specifies how query command results are applied to the row being updated Not supported in a .NET stored procedure |
|
|
Specifies the type of XML operation on the |
|
|
Specifies the properties that are used when an XML document is created from the result set of a SQL query statement |
|
|
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.
// C#
public bool AddRowid {get; set;}
bool
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.
See Also:
"LOB Support" for further information on how this property used with LOBs
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.
// C#
public bool AddToStatementCache{get; set;}
Returns bool value. A value of true indicates that statements are being added to the cache, false indicates otherwise.
A bool value that indicates that the statements will be cached when they are executed, if statement caching is enabled.
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.
// 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();
}
}
See Also:
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.
// C#
public int ArrayBindCount {get; set;}
An int value that specifies number of array elements to be bound in the OracleParameter Value property.
ArgumentException - The ArrayBindCount value specified is invalid.
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.
See Also:
This property specifies the binding method in the collection.
// C#
public bool BindByName {get; set;}
Returns true if the parameters are bound by name; returns false if the parameters are bound by position.
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
See Also:
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.
// ADO.NET 2.0: C#
public override string CommandText {get; set;}
// ADO.NET 1.x: C#
public string CommandText {get; set;}
A string.
IDbCommand
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.
// ADO.NET 2.0: C#
public override int CommandTimeout {get; set;}
// ADO.NET 1.x: C#
public int CommandTimeout {get; set;}
int
IDbCommand.CommandTimeout
InvalidArgument - The specified value is less than 0.
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:
http://msdn.microsoft.com/library for detailed information about this Microsoft .NET Framework 1.1 feature
This property specifies the command type that indicates how the CommandText property is to be interpreted.
// ADO.NET 2.0: C#
public override CommandType CommandType {get; set;}
// ADO.NET 1.x: C#
public CommandType {get; set;}
A CommandType.
ArgumentException - The value is not a valid CommandType such as: CommandType.Text, CommandType.StoredProcedure, CommandType.TableDirect.
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.
// C#
public OracleConnection Connection {get; set;}
An OracleConnection object.
IDbCommand
Default = null
This property specifies whether or not the OracleCommand object is visible on designer controls.
// C#
public override bool DesignTimeVisible { get; set; }
A value that indicate whether or not OracleCommand object is visible in a control. The default is true.
This property is used by developers to indicate whether or not OracleCommand object is visible in a control.
This property specifies the size of OracleDataReader's internal cache to store result set data.
// C#
public long FetchSize {get; set;}
A long that specifies the size (in bytes) of the OracleDataReader's internal cache.
ArgumentException - The FetchSize value specified is invalid.
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.
The RowSize and FetchSize properties handle UDT and XMLType data differently than other scalar data types. Because only a reference to the UDT and XMLType data is stored in the ODP.NET's internal cache, the RowSize property accounts for only the memory needed for the reference (which is very small) and not the actual size of the UDT and XMLType data. Thus, applications can inadvertently fetch massive amount s of UDT or XMLType instances from the database in a single database round-trip because the actual size of UDT and XMLType data do not count against the FetchSize, and it would require numerous UDT and XMLType references to fill up the default cache size of 64K. Therefore, when fetching UDT or XMLType data, the FetchSize property must be appropriately configured to control the number of UDT and XMLType instances that are to be fetched, rather than the amount of the actual UDT and XMLType data to be fetched
NOTE: For LOB and LONG data types, only the sizes specified in the InitialLOBFetchSize and InitialLONGFetchSize properties are accounted for by the RowSize property in addition to the metadata and reference information that is maintained by the cache for each LOB in the select list.
See Also:
This property specifies the amount of data that the OracleDataReader initially fetches for LOB columns.
// C#
public int InitialLOBFetchSize {get; set;}
An int specifying the number of characters or bytes to fetch initially.
ArgumentException - The InitialLOBFetchSize value specified is invalid.
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
See Also:
This property specifies the amount of data that the OracleDataReader initially fetches for LONG and LONG RAW columns.
// C#
public int InitialLONGFetchSize {get; set;}
An int specifying the amount.
ArgumentException - The InitialLONGFetchSize value specified is invalid.
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.
// C#
public OracleNotificationRequest Notification {set; get;}
A notification request for the command.
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 Continuous Query 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 OracleDependency.OnChange event is fired, if the ROWID column is explicitly included in the query (or AddRowid property is set to true), then the Rowid column contains ROWID values in the DataTable referenced by the OracleNotificationEventArgs.Details property. This behavior can be overridden by explicitly requesting for an inclusion and exclusion of ROWID values in the OracleNotificationEventArgs by setting the OracleDependency.RowidInfo to OracleRowidInfo.Include or OracleRowidInfo.Exclude, respectively.
See Also:
This instance property indicates whether or not to register for a database change notification with the database automatically when the command is executed.
// C#
public bool NotificationAutoEnlist {set; get;}
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
A notification request can be used for multiple command executions using the same OracleCommand instance. In that case, set the NotificationAutoEnlist property to true.
See Also:
This property specifies the parameters for the SQL statement or stored procedure.
// C#
public OracleParameterCollection Parameters {get;}
OracleParameterCollection
IDbCommand
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.
// C#
public long RowSize {get;}
A long that indicates the amount of memory (in bytes) that an OracleDataReader needs to store one row of data for the executed query.
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.
// C#
public OracleTransaction Transaction {get;}
OracleTransaction
IDbCommand
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.
// ADO.NET 2.0: C#
public override UpdateRowSource UpdatedRowSource {get; set;}
// ADO.NET 1.x: C#
public UpdateRowSource UpdatedRowSource {get; set;}
An UpdateRowSource.
IDbCommand
ArgumentException - The UpdateRowSource value specified is invalid.
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.
// C#
public OracleXmlCommandType XmlCommandType {get; set;}
An OracleXmlCommandType.
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.
// C#
public OracleXmlQueryProperties XmlQueryProperties {get; set;}
OracleXmlQueryProperties.
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.
// C#
public OracleXmlSaveProperties XmlSaveProperties {get; set;}
OracleXmlSaveProperties.
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 |
|---|---|
|
Attempts to cancels a command that is currently executing on a particular connection |
|
|
Creates a copy of |
|
|
|
Inherited from |
|
Creates a new instance of |
|
|
|
Inherited from |
|
|
Inherited from |
|
Executes a SQL statement or a command using the |
|
|
Executes a command (Overloaded) |
|
|
Returns the first column of the first row in the result set returned by the query |
|
|
Executes a command using the |
|
|
Executes a command using the |
|
|
Executes a command using the |
|
|
|
Inherited from |
|
|
Inherited from System. |
|
|
Inherited from |
|
|
Inherited from |
|
|
This method is a no-op |
|
|
Inherited from |
This method attempts to cancel a command that is currently executing on a particular connection.
// ADO.NET 2.0: C# public override void Cancel();
// ADO.NET 1.x: C#
public void Cancel();
IDbCommand.Cancel
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.
// 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:
http://msdn.microsoft.com/library for detailed information about this Microsoft .NET Framework 1.1 feature
This method creates a copy of an OracleCommand object.
// C# public object Clone();
An OracleCommand object.
ICloneable
The cloned object has the same property values as that of the object being cloned.
This method creates a new instance of OracleParameter class.
// C# public OracleParameter CreateParameter();
A new OracleParameter with default values.
IDbCommand
This method executes a SQL statement or a command using the XmlCommandType and CommandText properties and returns the number of rows affected.
// ADO.NET 2.0: C# public override int ExecuteNonQuery();
// ADO.NET 1.x: C#
public int ExecuteNonQuery();
The number of rows affected.
IDbCommand
InvalidOperationException - The command cannot be executed.
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.
// 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();
}
}
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).
See Also:
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.
// C# public OracleDataReader ExecuteReader();
An OracleDataReader.
IDbCommand
InvalidOperationException - The command cannot be executed.
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.
// 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();
}
}
See Also:
This method executes a command specified in the CommandText and returns an OracleDataReader object, using the specified behavior.
// C#
public OracleDataReader ExecuteReader(CommandBehavior behavior);
behavior
The expected behavior.
An OracleDataReader.
IDbCommand
InvalidOperationException - The command cannot be executed.
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 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.
See Also:
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.
// ADO.NET 2.0: C# public override object ExecuteScalar();
// ADO.NET 1.x: C#
public object ExecuteScalar();
An object which represents the value of the first row, first column.
IDbCommand
InvalidOperationException - The command cannot be executed.
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.
// 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.
// C# public Stream ExecuteStream();
A Stream.
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.
See Also:
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.
// C#
public void ExecuteToStream(Stream outputStream);
outputStream
A Stream.
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.
See Also:
Oracle XML DB Developer's Guide
This method executes the command using the XmlCommandType and CommandText properties and returns the result as an XML document in a .NET XmlTextReader object.
// C# public XmlReader ExecuteXmlReader();
An XmlReader.
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.
See Also:
Oracle XML DB Developer's Guide