Release 9.2.0.2
Part Number A96160-01
 Contents |
 Index |
| Oracle Data Provider for .NET Developer's Guide Release 9.2.0.2 Part Number A96160-01 |
|
Oracle.DataAccess.Client Namespace, 2 of 26
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 Microsoft .NET Stream, a scalar value, or as output parameters.
Object
MarshalByRefObject
Component
OracleCommand
// 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.
// C# ... string conStr = "User Id=scott;Password=tiger;Data Source=oracle"; // Create the OracleConnection OracleConnection con = new OracleConnection(conStr); con.Open(); string cmdQuery = "select ename, empno from emp"; // Create the OracleCommand OracleCommand cmd = new OracleCommand(cmdQuery); cmd.Connection = con; cmd.CommandType = CommandType.Text; // Execute command, create OracleDataReader object OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { // output Employee Name and Number Console.WriteLine("Employee Name : " + reader.GetString(0) + " , " + "Employee Number : " + reader.GetDecimal(1)); } // Dispose OracleDataReader object reader.Dispose(); // Dispose OracleCommand object cmd.Dispose(); // Close and Dispose OracleConnection object con.Close(); con.Dispose(); ...
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccesss.dll
OracleCommand members are listed in the following tables:
OracleCommand constructors are listed in Table 4-1.
| Constructor | Description |
|---|---|
|
Instantiates a new instance of |
OracleCommand static methods are listed in Table 4-2.
| Methods | Description |
|---|---|
|
|
Inherited from |
OracleCommand properties are listed in Table 4-3.
OracleCommand public methods are listed in Table 4-4.
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.
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);
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(stringcmdText, OracleConnectionOracleConnection);
cmdText
Specifies the SQL command or stored procedure to be executed.
OracleConnection
Specifies the connection to the Oracle database.
OracleCommand static methods are listed in Table 4-5.
| Methods | Description |
|---|---|
|
|
Inherited from |
OracleCommand properties are listed in Table 4-6.
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:
|
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.
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.
This property specifies the SQL statement or stored procedure to run against the Oracle database.
// 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.
This property specifies the command type that indicates how the CommandText property is to be interpreted.
// C# public System.Data.CommandType CommandType {final get; final set;}
A CommandType.
ArgumentException - The value is not a valid CommandType such as: CommandType.Text, CommandType.StoredProcedure, CommandType.TableDirect.
Default = CommandType.Text
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 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 per server round-trip.
This property specifies the amount 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 amount of the InitialLONGFetchSize is in number of characters for LONG data and in number of bytes for LONG RAW data.
To fetch more than the InitialLONGFetchSize amount, a primary key or a ROWID must be in the select-list. The InitialLONGFetchSize value is used to determine the LONG and LONG RAW data to fetch if one of the two is in the select-list. (This value is ignored if the select-list does not contain either a LONG or a LONG RAW.)
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 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 non-zero 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 per server round-trip, the OracleDataReader's FetchSize property can be set dynamically to RowSize * N. Note that for the FetchSize to take effect appropriately, it must be set after OracleCommand.ExecuteReader() but before OracleDataReader.Read().
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.
This property specifies how query command results are applied to the row to be updated.
// C# public System.Data.UpdateRowSource UpdatedRowSource {final get; final set;}
An UpdateRowSource.
IDbCommand
ArgumentException - The UpdateRowSource value specified is invalid.
Default = UpdateRowSource.None if the command is automatically generated. Default = UpdateRowSource.Both if the command is not automatically generated.
OracleCommand public methods are listed in Table 4-7.
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.
// C# ... //Need a proper casting for the return value when cloned OracleCommand cmd_cloned = (OracleCommand) cmd.Clone(); ...
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 and returns the number of rows affected.
// C# public int ExecuteNonQuery();
The number of rows affected.
IDbCommand
InvalidOperationException - The command cannot be executed.
If the command is UPDATE, INSERT, or DELETE SQL, it returns the number of rows affected. For all other types of statements, the return value is -1.
ExecuteNonQuery is used for either of the following:
DataSet, by executing UPDATE, INSERT, or DELETE statements.
Although ExecuteNonQuery does not return any rows, it populates any output parameters or return values mapped to parameters with data.
// C# ... OracleConnection con = new OracleConnection("User Id=scott;Password=tiger;"+ "Data Source=oracle"); OracleCommand cmd = new OracleCommand("update emp set sal = 3000" + "where empno=7934", con); cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Dispose(); ...
ExecuteReader executes a command specified in the CommandText.
This method executes a command specified in the CommandText and returns an OracleDataReader object.
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 command executes this stored procedure when you call ExecuteReader(). If parameters for the stored procedure consists of REF CURSORs, behavior differs depending on whether ExecuteReader() or ExecuteNonQuery() is called.
The value of 100 is used for the FetchSize. If 0 is specified, no rows are fetched. For further information, see "Obtaining LONG and LONG RAW Data".
// C# ... OracleConnection con = new OracleConnection("User Id=scott;Password=tiger;" + "Data Source=oracle"); OracleCommand cmd = new OracleCommand("select ename from emp", con); cmd.Connection.Open(); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine("Employee Name : " + reader.GetString(0)); } reader.Dispose(); cmd.Dispose(); ...
This method executes a command specified in the CommandText and returns an OracleDataReader object, using the specified behavior.
// C# public OracleDataReader ExecuteReader(CommandBehavior 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 expected behavior of each CommandBehavior enumerated type, read the .NET Framework documentation.
When the CommandType property is set to CommandType.StoredProcedure, the CommandText property should be set to the name of the stored procedure. The command executes this stored procedure when ExecuteReader() is called.
If the stored procedure returns stored REF CURSORs, read the section on OracleRefCursors for more details. See "OracleRefCursor Class".
The value of 100 is used for the FetchSize. If 0 is specified, no rows are fetched. For more information, see "Obtaining LONG and LONG RAW Data".
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.
// 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.
// C# ... CmdObj.CommandText = "select count(*) from emp"; decimal count = (decimal) CmdObj.ExecuteScalar(); ...
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|