Oracle® Data Provider for .NET Developer's Guide 10g Release 1 (10.1) Part Number B10117-01 |
|
|
View PDF |
This chapter describes the classes and public methods Oracle Data Provider for .NET exposes for ADO.NET programmers. They are:
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.
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.
ExecuteXmlReader
, ExecuteStream
, and ExecuteToStream
methods are only supported for XML operations.
ExecuteReader
and ExecuteScalar
methods are not supported for XML operations.
// 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.DataAccess.dll
OracleCommand
members are listed in the following tables:
OracleCommand
constructors are listed in Table 4-1.
Table 4-1 OracleCommand Constructors
Constructor | Description |
---|---|
OracleCommand Constructors | Instantiates a new instance of OracleCommand class (Overloaded) |
OracleCommand
static methods are listed in Table 4-2.
Table 4-2 OracleCommand Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleCommand
properties are listed in Table 4-3.
Table 4-3 OracleCommand Properties
Name | Description |
---|---|
AddRowid | Adds the ROWID as part of the select list |
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 |
Not supported |
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 that the OracleDataReader initially fetches for LOB columns |
InitialLONGFetchSize | Specifies the amount that the OracleDataReader initially fetches for LONG and LONG RAW columns |
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 |
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 are listed in Table 4-4.
Table 4-4 OracleCommand Public Methods
Public Method | Description |
---|---|
Cancel |
Not Supported |
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.
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
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.
Table 4-5 OracleCommand Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleCommand
properties are listed in Table 4-6.
Table 4-6 OracleCommand Properties
Name | Description |
---|---|
AddRowid | Adds the ROWID as part of the select list |
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 | Not supported |
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 that the OracleDataReader initially fetches for LOB columns |
InitialLONGFetchSize | Specifies the amount that the OracleDataReader initially fetches for LONG and LONG RAW columns |
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 |
UpdatedRowSource | Specifies how query command results are applied to the row being updated |
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.
// 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 ROWID
s of a table, the ROWID
must explicitly be added to the select list without the use of this property.
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.
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 supported only for OracleCommand.CommandType = CommandType.Text
, not for OracleCommand.CommandType = CommandType.StoredProcedure
.
BindByName
is ignored under the following conditions:
The value of the XmlCommandType
property is Insert
, Update
, or Delete
.
The value of the XmlCommandType
property is Query
, but there are no parameters set on the OracleCommand
.
If the XmlCommandType
property is OracleXmlCommandType.Query
and any parameters are set on the OracleCommand
, the BindByName
property must be set to true
. Otherwise, the following OracleCommand
methods throw an InvalidOperationException
.
ExecuteNonQuery
ExecuteXmlReader
ExecuteStream
ExecuteToStream
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.
// 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 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
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 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 server 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 that the OracleDataReader initially fetches for LOB columns.
// C# public int InitialLOBFetchSize {get; set;}
An int
specifying the amount.
ArgumentException
- The InitialLOBFetchSize
value specified is invalid.
The maximum value supported for InitialLOBFetchSize
is 32767
. If this property is set to a higher value, the provider resets it to 32767
.
Default = 0.
The value of InitialLOBFetchSize
specifies the initial amount of LOB data that is immediately fetched by the OracleDataReader
. The property value specifies the number of characters for CLOB
and NCLOB
data and the number of bytes for BLOB
data. To fetch more than the specified InitialLOBFetchSize
amount, one of the following must be in the select list:
primary key
ROWID
unique columns - (defined as a set of columns on which a unique constraint has been defined or a unique index has been created, where at least one of the columns in the set has a NOT
NULL
constraint defined on it)
The InitialLOBFetchSize
value is used to determine the length of the LOB column data to fetch if LOB column is in the select list. If the select list does not contain a LOB column, the InitialLOBFetchSize
value is ignored.
A primary key, a ROWID
, or unique columns are not required if this property is set to 0.
If the InitialLOBFetchSize
is set to a nonzero value, GetOracleBlob()
and GetOracleClob()
methods are disabled. BLOB
and CLOB
data are fetched by using GetBytes()
and GetChars()
, respectively.
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 maximum value supported for InitialLONGFetchSize
is 32767
. If this property is set to a higher value, the provider resets it to 32767
.
The value of InitialLONGFetchSize
specifies the initial amount of LONG
or LONG
RAW
data that is immediately fetched by the OracleDataReader
. The property value specifies the number of characters for LONG data and the number of bytes for LONG
RAW
. To fetch more than the specified InitialLONGFetchSize
amount, one of the following must be in the select list:
primary key
ROWID
unique columns - (defined as a set of columns on which a unique constraint has been defined or a unique index has been created, where at least one of the columns in the set has a NOT
NULL
constraint defined on it)
The InitialLONGFetchSize
value is used to determine the length of the LONG
and LONG
RAW
column data to fetch if one of the two is in the select list. If the select list does not contain a LONG
or a LONG
RAW
column, the InitialLONGFetchSize
value is ignored.
Default = 0
.
Setting this property to 0
defers the LONG
and LONG
RAW
data retrieval entirely until the application specifically requests it.
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 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()
.
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. |
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.
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 4-7.
Table 4-7 OracleCommand Public Methods
Public Method | Description |
---|---|
Cancel |
Not Supported |
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 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 or a command using the XmlCommandType
and CommandText
properties and returns the number of rows affected.
// 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# ... 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(); ...
For XML support, this method requires Oracle9i XML Developer's Kits (Oracle XDK) or higher, to be installed in the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).
ExecuteReader
ExecuteReader
executes a command specified in the CommandText
.
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 command executes this stored procedure when you call ExecuteReader()
. If parameters for the stored procedure consists of REF
CURSOR
s, behavior differs depending on whether ExecuteReader()
or ExecuteNonQuery()
is called.
The value of 100
is used for the FetchSize
. If 0
is specified, no rows are fetched. For further information, see "Obtaining LONG and LONG RAW Data".
If the value of the XmlCommandType
property is set to OracleXmlCommandType.Insert
, OracleXmlCommandType.Update
, OracleXmlCommandType.Delete
, or OracleXmlCommandType.Query
then the ExecuteReader
method throws an InvalidOperationException
.
// 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);
behavior
Specifies 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 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
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.
// 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# ... CmdObj.CommandText = "select count(*) from emp"; decimal count = (decimal) CmdObj.ExecuteScalar(); ...
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.
For database releases 8.1.7 and 9.0.1 only: This method requires Oracle XML Developer's Kit (Oracle XDK) release 9.2 or higher to be installed on the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).
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
.
For database releases 8.1.7 and 9.0.1 only: This method requires Oracle XML Developer's Kit (Oracle XDK) release 9.2 or higher to be installed on the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).
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.
// 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.
For database releases 8.1.7 and 9.0.1 only: This method requires Oracle XML Developer's Kit (Oracle XDK) release 9.2 or higher to be installed on the database. Oracle XDK can be downloaded from Oracle Technology Network (OTN).
See Also:
|
An OracleCommandBuilder
object provides automatic SQL generation for the OracleDataAdapter
when updates are made to the database.
Object
MarshalByRefObject
Component
OracleCommandBuilder
// C# public sealed class OracleCommandBuilder : Component
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
OracleCommandBuilder
automatically generates SQL statements for single-table updates when the SelectCommand
property of the OracleDataAdapter
is set. An exception is thrown if the DataSet
contains multiple tables. The OracleCommandBuilder
registers itself as a listener for RowUpdating
events whenever its DataAdapter
property is set. Only one OracleDataAdapter
object and one OracleCommandBuilder
object can be associated with each other at one time.
To generate INSERT
, UPDATE
, or DELETE
statements, the OracleCommandBuilder
uses ExtendedProperties
within the DataSet
to retrieve a required set of metadata. If the SelectCommand
is changed after the metadata is retrieved (for example, after the first update), the RefreshSchema
method should be called to update the metadata.
OracleCommandBuilder
first looks for the metadata from the ExtendedProperties
of the DataSet
; if the metadata is not available, OracleCommandBuilder
uses the SelectCommand
property of the OracleDataAdapter
to retrieve the metadata.
The OracleCommandBuilder
examples in this section are based on the EMPINFO
table which is defined as follows:
CREATE TABLE empInfo ( empno NUMBER(4) PRIMARY KEY, empName VARCHAR2(20) NOT NULL, hiredate DATE, salary NUMBER(7,2), jobDescription Clob, byteCodes BLOB );
The EMPINFO
table has the following values:
EMPNO EMPNAME HIREDATE SALARY JOBDESCRIPTION BYTECODES (Hex Values) ===== ======= ======== ====== ============== ============ 1 KING 01-MAY-81 12345.67 SOFTWARE ENGR {0x12, 0x34} 2 SCOTT 01-SEP-75 34567.89 MANAGER {0x56, 0x78} 3 BLAKE 01-OCT-90 9999.12 TRANSPORT {0x23, 0x45} 4 SMITH NULL NULL NULL NULL
The following example uses the OracleCommandBuilder
object to create the UpdateCommand
for the OracleDataAdapter
object when OracleDataAdapter.Update()
is called.
// C# public static void BuilderUpdate(string connStr) { string cmdStr = "SELECT EMPNO, EMPNAME, JOBDESCRIPTION FROM EMPINFO"; //create the adapter with the selectCommand txt and the //connection string OracleDataAdapter adapter = new OracleDataAdapter(cmdStr, connStr); //get the connection from the adapter OracleConnection connection = adapter.SelectCommand.Connection; //create the builder for the adapter to automatically generate //the Command when needed OracleCommandBuilder builder = new OracleCommandBuilder(adapter); //Create and fill the DataSet using the EMPINFO DataSet dataset = new DataSet(); adapter.Fill(dataset, "EMPINFO"); //Get the EMPINFO table from the dataset DataTable table = dataset.Tables["EMPINFO"]; //Get the first row from the EMPINFO table DataRow row0 = table.Rows[0]; //update the job description in the first row row0["JOBDESCRIPTION"] = "MANAGER"; //Now update the EMPINFO using the adapter, the job description //of 'KING' is changed to 'MANAGER' //The OracleCommandBuilder will create the UpdateCommand for the //adapter to update the EMPINFO table adapter.Update(dataset, "EMPINFO"); }
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleCommandBuilder
members are listed in the following tables:
OracleCommandBuilder
constructors are listed in Table 4-8.
Table 4-8 OracleCommandBuilder Constructors
Constructor | Description |
---|---|
OracleCommandBuilder Constructors | Instantiates a new instance of OracleCommandBuilder class (Overloaded) |
OracleCommandBuilder
static methods are listed in Table 4-9.
Table 4-9 OracleCommandBuilder Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleCommandBuilder
properties are listed in Table 4-10.
Table 4-10 OracleCommandBuilder Properties
Name | Description |
---|---|
Container |
Inherited from Component |
DataAdapter | Indicates the OracleDataAdapter for which the SQL statements are generated |
CaseSensitive | Indicates whether or not double quotes are used around Oracle object names when generating SQL statements |
Site |
Inherited from Component |
OracleCommandBuilder
public methods are listed in Table 4-11.
Table 4-11 OracleCommandBuilder Public Methods
Public Method | Description |
---|---|
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose |
Inherited from Component |
Equals |
Inherited from Object (Overloaded) |
GetDeleteCommand | Gets the automatically generated OracleCommand object that has the SQL statement (CommandText ) perform deletions on the database |
GetHashCode |
Inherited from Object |
GetInsertCommand | Gets the automatically generated OracleCommand object that has the SQL statement (CommandText ) perform insertions on the database |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
GetUpdateCommand | Gets the automatically generated OracleCommand object that has the SQL statement (CommandText ) perform updates on the database |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
RefreshSchema | Refreshes the database schema information used to generate INSERT , UPDATE , or DELETE statements |
ToString |
Inherited from Object |
OracleCommandBuilder
events are listed in Table 4-12.
OracleCommandBuilder
event delegates are listed in Table 4-13.
Table 4-13 OracleCommandBuilder Event Delegates
Event Delegate Name | Description |
---|---|
EventHandler |
Inherited from Component |
OracleCommandBuilder
constructors create new instances of the OracleCommandBuilder
class.
This constructor creates an instance of the OracleCommandBuilder
class.
OracleCommandBuilder(OracleDataAdapter)
This constructor creates an instance of the OracleCommandBuilder
class and sets the DataAdapter
property to the provided OracleDataAdapter
object.
This constructor creates an instance of the OracleCommandBuilder
class.
// C# public OracleCommandBuilder();
Default constructor.
This constructor creates an instance of the OracleCommandBuilder
class and sets the DataAdapter
property to the provided OracleDataAdapter
object.
// C# public OracleCommandBuilder(OracleDataAdapter da);
da
The OracleDataAdapter
object provided.
OracleCommandBuilder
properties are listed in Table 4-14.
Table 4-14 OracleCommandBuilder Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleCommandBuilder
properties are listed in Table 4-15.
Table 4-15 OracleCommandBuilder Properties
Name | Description |
---|---|
Container |
Inherited from Component |
DataAdapter | Indicates the OracleDataAdapter for which the SQL statements are generated |
CaseSensitive | Indicates whether or not double quotes are used around Oracle object names when generating SQL statements |
Site |
Inherited from Component |
This property indicates the OracleDataAdapter
for which the SQL statements are generated.
// C# OracleDataAdapter DataAdapter{get; set;}
OracleDataAdapter
Default = null
This property indicates whether or not double quotes are used around Oracle object names (for example, tables or columns) when generating SQL statements.
// C# bool CaseSensitive {get; set;}
A bool
that indicates whether or not double quotes are used.
Default = false
OracleCommandBuilder
public methods are listed in Table 4-16.
Table 4-16 OracleCommandBuilder Public Methods
Public Method | Description |
---|---|
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose |
Inherited from Component |
Equals |
Inherited from Object (Overloaded) |
GetDeleteCommand | Gets the automatically generated OracleCommand object that has the SQL statement (CommandText ) perform deletions on the database |
GetHashCode |
Inherited from Object |
GetInsertCommand | Gets the automatically generated OracleCommand object that has the SQL statement (CommandText ) perform insertions on the database |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
GetUpdateCommand | Gets the automatically generated OracleCommand object that has the SQL statement (CommandText ) perform updates on the database |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
RefreshSchema | Refreshes the database schema information used to generate INSERT , UPDATE , or DELETE statements |
ToString |
Inherited from Object |
This method gets the automatically generated OracleCommand
object that has the SQL statement (CommandText
) perform deletions on the database when an application calls Update()
on the OracleDataAdapter
.
// C# public OracleCommand GetDeleteCommand();
An OracleCommand
.
ObjectDisposedException
- The OracleCommandBuilder
object is already disposed.
InvalidOperationException
- Either the SelectCommand
or the DataAdapter
property is null, or the primary key cannot be retrieved from the SelectCommand
property of the OracleDataAdapter
.
This method gets the automatically generated OracleCommand
object that has the SQL statement (CommandText
) perform insertions on the database when an application calls Update()
on the OracleDataAdapter
.
// C# public OracleCommand GetInsertCommand();
An OracleCommand
.
ObjectDisposedException
- The OracleCommandBuilder
object is already disposed.
InvalidOperationException
- Either the SelectCommand
or the DataAdapter
property is null, or the primary key cannot be retrieved from the SelectCommand
property of the OracleDataAdapter
.
This method gets the automatically generated OracleCommand
object that has the SQL statement (CommandText
) perform updates on the database when an application calls Update()
on the OracleDataAdapter
.
// C# public OracleCommand GetUpdateCommand();
An OracleCommand
.
ObjectDisposedException
- The OracleCommandBuilder
object is already disposed.
InvalidOperationException
- Either the SelectCommand
or the DataAdapter
property is null, or the primary key cannot be retrieved from the SelectCommand
property of the OracleDataAdapter
.
This method refreshes the database schema information used to generate INSERT
, UPDATE
, or DELETE
statements.
// C# public void RefreshSchema();
An application should call RefreshSchema
whenever the SelectCommand
value of the OracleDataAdapter
changes.
OracleCommandBuilder
events are listed in Table 4-17.
OracleCommandBuilder
event delegates are listed in Table 4-18.
Table 4-18 OracleCommandBuilder Event Delegates
Event Delegate Name | Description |
---|---|
EventHandler |
Inherited from Component |
An OracleConnection
object represents a connection to an Oracle database.
Object
MarshalByRefObject
Component
OracleConnection
// C# public sealed class OracleConnection : Component, IDbConnection, ICloneable
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
// C# // Uses connection to create and return an OracleCommand object. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); OracleCommand cmd = con.CreateCommand(); cmd.CommandText = "insert into mytable values (99, 'foo')"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); ...
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleConnection
members are listed in the following tables:
OracleConnection
constructors are listed in Table 4-19.
Table 4-19 OracleConnection Constructors
Constructor | Description |
---|---|
OracleConnection Constructors | Instantiates a new instance of the OracleConnection class (Overloaded) |
OracleConnection
static methods are listed in Table 4-20.
Table 4-20 OracleConnection Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleConnection
properties are listed in Table 4-21
Table 4-21 OracleConnection Properties
Name | Description |
---|---|
ConnectionString | Specifies connection information used to connect to an Oracle database |
ConnectionTimeout | Specifies the maximum amount of time that the Open() method can take to obtain a pooled connection before terminating the request |
Container |
Inherited from Component |
DataSource | Specifies the Oracle Net Service Name (also known as TNS alias) that identifies an Oracle database instance |
ServerVersion | Specifies the version number of the Oracle database to which the OracleConnection has established a connection |
Site |
Inherited from Component |
State | Specifies the current state of the connection |
OracleConnection
public methods are listed in Table 4-22.
Table 4-22 OracleConnection Public Methods
Public Method | Description |
---|---|
BeginTransaction | Begins a local transaction (Overloaded) |
ChangeDatabase |
Not Supported |
Clone | Creates a copy of an OracleConnection object |
Close | Closes the database connection |
CreateCommand | Creates and returns an OracleCommand object associated with the OracleConnection object |
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose |
Inherited from Component |
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetSessionInfo | Returns or refreshes the property values of the OracleGlobalization object that represents the globalization settings of the session (Overloaded) |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
Open | Opens a database connection with the property settings specified by the ConnectionString |
OpenWithNewPassword | Opens a new connection with the new password |
SetSessionInfo | Alters the session's globalization settings with the property values provided by the OracleGlobalization object |
ToString |
Inherited from Object |
OracleConnection
events are listed in Table 4-23.
Table 4-23 OracleConnection Events
Event Name | Description |
---|---|
Disposed |
Inherited from Component |
Failover | An event that is triggered when an Oracle failover occurs |
InfoMessage | An event that is triggered for any message or warning sent by the database |
StateChange | An event that is triggered when the connection state changes |
OracleConnection
event delegates are listed in Table 4-24.
Table 4-24 OracleConnection Event Delegates
Event Delegate Name | Description |
---|---|
OracleFailoverEventHandler | An event delegate that handles the Failover event |
OracleInfoMessageEventHandler | An event delegate that handles the InfoMessage event |
StateChangeEventHandler | An event delegate that handles the StateChange event |
OracleConnection
constructors instantiate new instances of the OracleConnection
class.
This constructor instantiates a new instance of the OracleConnection
class using default property values.
This constructor instantiates a new instance of the OracleConnection
class with the provided connection string.
This constructor instantiates a new instance of the OracleConnection
class using default property values.
// C# public OracleConnection();
The properties for OracleConnection
are set to the following default values:
ConnectionString
= empty string
ConnectionTimeout
= 15
DataSource
= empty string
ServerVersion
= empty string
This constructor instantiates a new instance of the OracleConnection
class with the provided connection string.
// C# public OracleConnection(String connectionString);
connectionString
The connection information used to connect to the Oracle database.
The ConnectionString
property is set to the supplied connectionString
. The ConnectionString
property is parsed and an exception is thrown if it contains invalid connection string attributes or attribute values.
The properties of the OracleConnection
object default to the following values unless they are set by the connection string:
ConnectionString
= empty string
ConnectionTimeout
= 15
DataSource
= empty string
ServerVersion
= empty string
OracleConnection
static methods are listed in Table 4-25.
Table 4-25 OracleConnection Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleConnection
properties are listed in Table 4-26
Table 4-26 OracleConnection Properties
Name | Description |
---|---|
ConnectionString | Specifies connection information used to connect to an Oracle database |
ConnectionTimeout | Specifies the maximum amount of time that the Open() method can take to obtain a pooled connection before terminating the request |
Container |
Inherited from Component |
DataSource | Specifies the Oracle Net Service Name (also known as TNS alias) that identifies an Oracle database instance |
ServerVersion | Specifies the version number of the Oracle database to which the OracleConnection has established a connection |
Site |
Inherited from Component |
State | Specifies the current state of the connection |
This property specifies connection information used to connect to an Oracle database.
// C# public string ConnectionString{get; set;}
If the connection string is supplied through the constructor, this property is set to that string.
IDbConnection
ArgumentException
- An invalid syntax is specified for the connection string.
InvalidOperationException
- ConnectionString
is being set while the connection is open.
The default value is an empty string.
ConnectionString
must be a string of attribute name and value pairings, separated by a semi-colon, for example:
// C# OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=MYSCHEMA;Password=MYPASSWORD;" + "Data Source=Oracle";
If the ConnectionString
is not in a proper format, an exception is thrown. All spaces are ignored unless they are within double quotes.
When the ConnectionString
property is set, the OracleConnection
object immediately parses the string for errors. An ArgumentException
is thrown if the ConnectionString
contains invalid attributes or invalid values. Attribute values for User
Id
, Password
, Proxy
User
Id
, Proxy
Password
, and Data
Source
(if provided) are not validated until the Open
method is called.
The connection must be closed to set the ConnectionString
property. When the ConnectionString
property is reset, all previously set values are reinitialized to their default values before the new values are applied.
The Oracle database supports case-sensitive user names. To connect as a user whose name is of mixed case, for example, "MySchema
", the User
Id
attribute value must be surrounded by double quotes, as follows:
// C# OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=\"MySchema\";Password=MYPASSWORD;" + "Data Source=Oracle";
However, if the Oracle user name is all upper case, the User
Id
connection string attribute can be set to that user name without the use of the double quotes since User
Id
s that are not doubled-quoted are converted to all upper case when connecting. Single quotes are not supported.
If a connection string attribute is set more than once, the last setting takes effect and no exceptions are thrown.
Boolean connection string attributes can be set to either true
, false
, yes
, or no
.
Supported connection string attributes:
Table 4-27 lists the supported connection string attributes.
Table 4-27 Supported Connection String Attributes
This code example shows that the case of the User
Id
attribute value is not preserved unless it is surrounded by double quotes. The example also demonstrates when connection pools are created and when connections are drawn from the connection pool.
// C# // Assume users "MYSCHEMA"and "MySchema" exist in the database ... OracleConnection con1 = new OracleConnection(); con1.ConnectionString = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; con1.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" // A new connection is created; A new Connection Pool X is created con1.Dispose(); // Connection is placed back into Pool X OracleConnection con2 = new OracleConnection(); con2.ConnectionString = "User Id=MySchema;Password=MyPassword;" + "Data Source=oracle;"; con2.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" A connection is // obtained from Pool X; A new connection is NOT created. con2.Dispose(); // Connection is placed back into Pool X OracleConnection con3 = new OracleConnection(); con3.ConnectionString = "User Id=\"MYSCHEMA\";Password=MYPASSWORD;" + "Data Source=oracle;"; con3.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" A connection is // obtained from Pool X; A new connection is NOT created. con3.Dispose(); // Connection is placed back into Pool X OracleConnection con4 = new OracleConnection(); con4.ConnectionString = "User Id=\"MySchema\";Password=mypassword;" + "Data Source=oracle;"; con4.Open(); // Attempts to connect as "MySchema/MYPASSWORD" // A new connection is created; A new Connection Pool Y is created con4.Dispose(); // Connection is placed back into Pool Y OracleConnection con5 = new OracleConnection(); con5.ConnectionString = "User Id=MySchema;Password=mypassword;" + "Data Source=oracle; "; con5.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" // A connection is obtained from Connection Pool X // Extra spaces in the connection string do not force creation // of a new pool con5.Dispose(); // Connection is placed back into Pool X OracleConnection con6 = new OracleConnection(); con6.ConnectionString = "User Id=MySchema;Password=mypassword;" + "Data Source=oracle;Pooling=true;"; con6.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" // A connection is obtained from Connection Pool X. "Pooling=true" // in the connection string does not force creation of a new pool // since the initial connection was established using the default // value of "Pooling=true". Note that even if the connection // string had "POOLING=Yes", a new connection pool will not be // created since they both enable pooling. The same rule applies // to other connection string attributes as well. con6.Dispose(); // Connection is placed back into Pool X ...
This property specifies the maximum amount of time that the Open()
method can take to obtain a pooled connection before terminating the request.
// C# public int ConnectionTimeout {get;}
The maximum time allowed for a pooled connection request, in seconds.
IDbConnection
The default value is 15
.
Setting this property to 0
allows the pooled connection request to wait for a free connection without a time limit. The timeout takes effect only for pooled connection requests and not for new connection requests.
This property specifies the Oracle Net Service Name (formerly known as TNS alias) that identifies an Oracle database instance.
// C# public string DataSource {get;}
The Oracle Net Service Name.
The default value of this property is an empty string
This property specifies the version number of the Oracle database to which the OracleConnection
has established a connection.
// C# public string ServerVersion {get;}
The version of the Oracle database, for example "9.2.0.1.0."
InvalidOperationException
- The connection is closed.
The default is an empty string.
This property specifies the current state of the connection.
// C# public ConnectionState State {get;}
The ConnectionState
of the connection.
IDbConnection
ODP.NET supports ConnectionState.Closed
and ConnectionState.Open
for this property. The default value is ConnectionState.Closed
.
OracleConnection
public methods are listed in Table 4-28.
Table 4-28 OracleConnection Public Methods
Public Method | Description |
---|---|
BeginTransaction | Begins a local transaction (Overloaded) |
ChangeDatabase |
Not Supported |
Clone | Creates a copy of an OracleConnection object |
Close | Closes the database connection |
CreateCommand | Creates and returns an OracleCommand object associated with the OracleConnection object |
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose |
Inherited from Component |
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetSessionInfo | Returns or refreshes the property values of the OracleGlobalization object that represents the globalization settings of the session (Overloaded) |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
Open | Opens a database connection with the property settings specified by the ConnectionString |
OpenWithNewPassword | Opens a new connection with the new password |
SetSessionInfo | Alters the session's globalization settings with the property values provided by the OracleGlobalization object |
ToString |
Inherited from Object |
BeginTransaction
BeginTransaction
methods begin local transactions.
This method begins a local transaction.
BeginTransaction(IsolationLevel)
This method begins a local transaction with the specified isolation level.
This method begins a local transaction.
// C# public OracleTransaction BeginTransaction();
An OracleTransaction
object representing the new transaction.
IDbConnection
InvalidOperationException
- A transaction has already been started.
The transaction is created with its isolation level set to its default value of IsolationLevel.ReadCommitted
. All further operations related to the transaction must be performed on the returned OracleTransaction
object.
This method begins a local transaction with the specified isolation level.
// C# public OracleTransaction BeginTransaction(IsolationLevel isolationLevel);
isolationLevel
The isolation level for the new transaction.
An OracleTransaction
object representing the new transaction.
IDbConnection
InvalidOperationException
- A transaction has already been started.
ArgumentException
- The isolationLevel
specified is invalid.
The following two isolation levels are supported:
IsolationLevel.ReadCommitted
IsolationLevel.Serializable
Requesting other isolation levels causes an exception.
// C# // Starts a transaction and inserts one record. If insert fails, rolls back // the transaction. Otherwise, commits the transaction. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); //Create an OracleCommand object using the connection object OracleCommand cmd = new OracleCommand("", con); // Start a transaction OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted); try { cmd.CommandText = "insert into mytable values (99, 'foo')"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); txn.Commit(); Console.WriteLine("Both records are inserted into the database table."); } catch(Exception e) { txn.Rollback(); Console.WriteLine("Neither record was inserted into the database table."); } ...
This method creates a copy of an OracleConnection
object.
// C# public object Clone();
An OracleConnection
object.
ICloneable
The cloned object has the same property values as that of the object being cloned.
// C# ... OracleConnection con = new OracleConnection(ConStr); con.Open(); ... //Need a proper casting for the return value when cloned OracleConnection con_cloned = (OracleConnection) con.Clone(); ...
This method closes the connection to the database.
// C# public void Close();
IDbConnection
Performs the following:
Rolls back any pending transactions.
Places the connection to the connection pool if connection pooling is enabled. Even if connection pooling is enabled, the connection can be closed if it exceeds the connection lifetime specified in the connection string. If connection pooling is disabled, the connection is closed.
Closes the connection to the database.
The connection can be reopened using Open()
.
This method creates and returns an OracleCommand
object associated with the OracleConnection
object.
// C# public OracleCommand CreateCommand();
The OracleCommand
object.
IDbConnection
// C# // Uses connection to create and return an OracleCommand object. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); OracleCommand cmd = Con.CreateCommand(); cmd.CommandText = "insert into mytable values (99, 'foo')"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); ...
GetSessionInfo
GetSessionInfo
returns or refreshes an OracleGlobalization
object that represents the globalization settings of the session.
This method returns a new instance of the OracleGlobalization
object that represents the globalization settings of the session.
GetSessionInfo(OracleGlobalization)
This method refreshes the provided OracleGlobalization
object with the globalization settings of the session.
This method returns a new instance of the OracleGlobalization
object that represents the globalization settings of the session.
// C# public OracleGlobalization GetSessionInfo();
The newly created OracleGlobalization
object.
// C# // Retrieves the session globalization info and prints the language name. // Then sets new territory, language, and timestamp format into the session // globalization info in the connection object. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); ... //Get session info from connection object OracleGlobalization ogi = con.GetSessionInfo(); //Print the language name Console.WriteLine(ogi.Language); //Update session info oraGlob.Territory = "JAPAN"; ogi.Language = "JAPANESE"; ogi.TimeStampFormat = "HH.MI.SSXFF AM YYYY-MM-DD"; //Set session info into connection object con.SetSessionInfo(ogi); ...
This method refreshes the provided OracleGlobalization
object with the globalization settings of the session.
// C# public void GetSessionInfo(OracleGlobalization oraGlob);
oraGlob
The OracleGlobalization
object to be updated.
This method opens a connection to an Oracle database.
// C# public void Open();
IDbConnection
ObjectDisposedException
- The object is already disposed.
InvalidOperationException
- The connection is already opened or the connection string is null or empty.
The connection is obtained from the pool if connection pooling is enabled. Otherwise, a new connection is established.
It is possible that the pool does not contain any unused connections when the Open()
method is invoked. In this case, a new connection is established.
If no connections are available within the specified connection timeout value, when the Max
Pool
Size
is reached, an OracleException
is thrown.
This method opens a new connection with the new password.
// C# public void OpenWithNewPassword(string newPassword);
newPassword
A string that contains the new password.
This method uses the ConnectionString
property settings to establish a new connection. The old password must be provided in the connection string as the Password
attribute value.
This method can only be called on an OracleConnection
in the closed state.
This method alters the session's globalization settings with all the property values specified in the provided OracleGlobalization
object.
// C# public void SetSessionInfo(OracleGlobalization oraGlob);
oraGlob
An OracleGlobalization
object.
Calling this method is equivalent to calling an ALTER
SESSION
SQL
on the session.
// C# // Retrieves the session globalization info and prints the language name. // Then sets new territory, language, and timestamp format into the session // globalization info in the connection object. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); //Create an OracleGlobalization object OracleGlobalization ogi; //Get session info using the second overloaded method con.GetSessionInfo(ogi); //Print the language name Console.WriteLine(ogi.Language); //Update session globalization info oraGlob.Territory = "JAPAN"; ogi.Language = "JAPANESE"; ogi.TimeStampFormat = "HH.MI.SSXFF AM YYYY-MM-DD"; //Set session globalization info into connection object con.SetSessionInfo(ogi);
OracleConnection
events are listed in Table 4-29.
Table 4-29 OracleConnection Events
Event Name | Description |
---|---|
Disposed |
Inherited from Component |
Failover | An event that is triggered when an Oracle failover occurs |
InfoMessage | An event that is triggered for any message or warning sent by the database |
StateChange | An event that is triggered when the connection state changes |
This event is triggered when an Oracle failover occurs.
// C# public event OracleFailoverEventHandler Failover;
The event handler receives an OracleFailoverEventArgs
object which exposes the following properties containing information about the event.
FailoverType
Indicates the type of the failover.
FailoverEvent
Indicates the state of the failover.
This event is triggered for any message or warning sent by the database.
// C# public event OracleInfoMessageEventHandler InfoMessage;
The event handler receives an OracleInfoMessageEventArgs
object which exposes the following properties containing information about the event.
Errors
The collection of errors generated by the data source.
Message
The error text generated by the data source.
Source
The name of the object that generated the error.
In order to respond to warnings and messages from the database, the client should create an OracleInfoMessageEventHandler
delegate to listen to this event.
This event is triggered when the connection state changes.
// C# public event StateChangeEventHandler StateChange;
The event handler receives a StateChangeEventArgs
object which exposes the following properties containing information about the event.
CurrentState
The new state of the connection.
OriginalState
The original state of the connection.
The StateChange
event is raised after a connection changes state, whenever an explicit call is made to Open
, Close
or Dispose
.
OracleConnection
event delegates are listed in Table 4-30.
Table 4-30 OracleConnection Event Delegates
Event Delegate Name | Description |
---|---|
OracleFailoverEventHandler | An event delegate that handles the Failover event |
OracleInfoMessageEventHandler | An event delegate that handles the InfoMessage event |
StateChangeEventHandler | An event delegate that handles the StateChange event |
This event delegate handles the Failover
event.
This event delegate handles the InfoMessage
event.
This event delegate handles the StateChange
event.
See Also:
|
An OracleDataAdapter
object represents a data provider object that populates the DataSet
and updates changes in the DataSet
to the Oracle database.
Object
MarshalByRefObject
Component
DataAdapter
DbDataAdapter
OracleDataAdapter
// C# public sealed class OracleDataAdapter : DbDataAdapter, IDbDataAdapter
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
The OracleDataAdapter
examples in this section are based on the EMPINFO
table which is defined as follows:
CREATE TABLE empInfo ( empno NUMBER(4) PRIMARY KEY, empName VARCHAR2(20) NOT NULL, hiredate DATE, salary NUMBER(7,2), jobDescription Clob, byteCodes BLOB );
The EMPINFO
table has the following values:
EMPNO EMPNAME HIREDATE SALARY JOBDESCRIPTION BYTECODES (Hex Values) ===== ======= ======== ====== ============== ============ 1 KING 01-MAY-81 12345.67 SOFTWARE ENGR {0x12, 0x34} 2 SCOTT 01-SEP-75 34567.89 MANAGER {0x56, 0x78} 3 BLAKE 01-OCT-90 9999.12 TRANSPORT {0x23, 0x45} 4 SMITH NULL NULL NULL NULL
The following example uses the OracleDataAdapter
and the dataset to update the EMPINFO
table:
// C# public static void AdapterUpdate(string connStr) { string cmdStr = "SELECT EMPNO, EMPNAME, SALARY FROM EMPINFO"; //create the adapter with the selectCommand txt and the //connection string OracleDataAdapter adapter = new OracleDataAdapter(cmdStr, connStr); //get the connection from the adapter OracleConnection connection = adapter.SelectCommand.Connection; //create the UpdateCommand object for updating the EMPINFO table //from the dataset adapter.UpdateCommand = new OracleCommand("UPDATE EMPINFO SET SALARY = "+ " :iSALARY where EMPNO = :iEMPNO", connection); adapter.UpdateCommand.Parameters.Add(":iSALARY", OracleDbType.Double, 0, "SALARY"); adapter.UpdateCommand.Parameters.Add(":iEMPNO", OracleDbType.Int16, 0, "EMPNO"); //Create and fill the DataSet using the EMPINFO DataSet dataset = new DataSet(); adapter.Fill(dataset, "EMPINFO"); //Get the EMPINFO table from the dataset DataTable table = dataset.Tables["EMPINFO"]; //Get the first row from the EMPINFO table DataRow row0 = table.Rows[0]; //update the salary in the first row row0["SALARY"] = 99999.99; //Now update the EMPINFO using the adapter, the salary //of 'KING' is changed to 99999.99 adapter.Update(dataset, "EMPINFO"); }
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleDataAdapter
members are listed in the following tables:
OracleDataAdapter
constructors are listed in Table 4-31.
Table 4-31 OracleDataAdapter Constructors
Constructor | Description |
---|---|
OracleDataAdapter Constructors | Instantiates a new instance of OracleDataAdapter class (Overloaded) |
OracleDataAdapter
static methods are listed in Table 4-32.
Table 4-32 OracleDataAdapter Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleDataAdapter
properties are listed in Table 4-33.
Table 4-33 OracleDataAdapter Properties
Name | Description |
---|---|
AcceptChangesDuringFill |
Inherited from DataAdapter |
Container |
Inherited from Component |
ContinueUpdateOnError |
Inherited from DataAdapter |
DeleteCommand | A SQL statement or stored procedure to delete rows from an Oracle database |
InsertCommand | A SQL statement or stored procedure to insert new rows into an Oracle database |
MissingMappingAction |
Inherited from DataAdapter |
MissingSchemaAction |
Inherited from DataAdapter |
Requery | Determines whether the SelectCommand is reexecuted on the next call to Fill |
SafeMapping | Creates a mapping between column names in the result set to .NET types, to preserve the data |
SelectCommand | A SQL statement or stored procedure that returns a single or multiple result set |
Site |
Inherited from Component |
TableMappings |
Inherited from DataAdapter |
UpdateCommand | A SQL statement or stored procedure to update rows from the DataSet to an Oracle database |
OracleDataAdapter
public methods are listed in Table 4-34.
Table 4-34 OracleDataAdapter Public Methods
Public Method | Description |
---|---|
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose |
Inherited from Component |
Equals |
Inherited from Object (Overloaded) |
Fill | Adds or refreshes rows in the DataSet to match the data in the Oracle database (Overloaded) |
FillSchema |
Inherited from DbDataAdapter |
GetFillParameters |
Inherited from DbDataAdapter |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
ToString |
Inherited from Object |
Update |
Inherited from DbDataAdapter |
OracleDataAdapter
events are listed in Table 4-35.
Table 4-35 OracleDataAdapter Events
Event Delegate Name | Description |
---|---|
EventHandler |
Inherited from Component |
FillErrorEventHandler |
Inherited from DbDataAdapter |
OracleRowUpdatedEventHandler | Event Delegate for the RowUpdated Event |
OracleRowUpdatingEventHandler | Event Delegate for the RowUpdating Event |
OracleDataAdapter
event delegates are listed in Table 4-36.
Table 4-36 OracleDataAdapter Event Delegates
Event Delegate Name | Description |
---|---|
EventHandler |
Inherited from Component |
FillErrorEventHandler |
Inherited from DbDataAdapter |
OracleRowUpdatedEventHandler | Event Delegate for the RowUpdated Event |
OracleRowUpdatingEventHandler | Event Delegate for the RowUpdating Event |
OracleDataAdapter
constructors create new instances of an OracleDataAdapter
class.
This constructor creates an instance of an OracleDataAdapter
class.
OracleDataAdapter(OracleCommand)
This constructor creates an instance of an OracleDataAdapter
class with the provided OracleCommand
as the SelectCommand
.
OracleDataAdapter(string, OracleConnection)
This constructor creates an instance of an OracleDataAdapter
class with the provided OracleConnection
object and the command text for the SelectCommand
.
OracleDataAdapter(string, string)
This constructor creates an instance of an OracleDataAdapter
class with the provided connection string and the command text for the SelectCommand
.
This constructor creates an instance of an OracleDataAdapter
class with no arguments.
// C# public OracleDataAdapter();
Initial values are set for the following OracleDataAdapter
properties as indicated:
MissingMappingAction = MissingMappingAction.Passthrough
MissingSchemaAction = MissingSchemaAction.Add
This constructor creates an instance of an OracleDataAdapter
class with the provided OracleCommand
as the SelectCommand
.
// C# public OracleDataAdapter(OracleCommand selectCommand);
selectCommand
The OracleCommand
that is to be set as the SelectCommand
property.
Initial values are set for the following OracleDataAdapter
properties as indicated:
MissingMappingAction = MissingMappingAction.Passthrough
MissingSchemaAction = MissingSchemaAction.Add
This constructor creates an instance of an OracleDataAdapter
class with the provided OracleConnection
object and the command text for the SelectCommand
.
// C# public OracleDataAdapter(string selectCommandText, OracleConnection selectConnection);
selectCommandText
The string that is set as the CommandText
of the SelectCommand
property of the OracleDataAdapter
.
selectConnection
The OracleConnection
to connect to the Oracle database.
The OracleDataAdapter
opens and closes the connection, if it is not already open. If the connection is open, it must be explicitly closed.
Initial values are set for the following OracleDataAdapter
properties as indicated:
MissingMappingAction = MissingMappingAction.Passthrough
MissingSchemaAction = MissingSchemaAction.Add
This constructor creates an instance of an OracleDataAdapter
class with the provided connection string and the command text for the SelectCommand
.
// C# public OracleDataAdapter(string selectCommandText, string selectConnectionString);
selectCommandText
The string that is set as the CommandText
of the SelectCommand
property of the OracleDataAdapter
.
selectConnectionString
The connection string.
Initial values are set for the following OracleDataAdapter
properties as indicated:
MissingMappingAction = MissingMappingAction.Passthrough
MissingSchemaAction = MissingSchemaAction.Add
OracleDataAdapter
static methods are listed in Table 4-37.
Table 4-37 OracleDataAdapter Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleDataAdapter
properties are listed in Table 4-38.
Table 4-38 OracleDataAdapter Properties
Name | Description |
---|---|
AcceptChangesDuringFill |
Inherited from DataAdapter |
Container |
Inherited from Component |
ContinueUpdateOnError |
Inherited from DataAdapter |
DeleteCommand | A SQL statement or stored procedure to delete rows from an Oracle database |
InsertCommand | A SQL statement or stored procedure to insert new rows into an Oracle database |
MissingMappingAction |
Inherited from DataAdapter |
MissingSchemaAction |
Inherited from DataAdapter |
Requery | Determines whether the SelectCommand is reexecuted on the next call to Fill |
SafeMapping | Creates a mapping between column names in the result set to .NET types, to preserve the data |
SelectCommand | A SQL statement or stored procedure that returns a single or multiple result set |
Site |
Inherited from Component |
TableMappings |
Inherited from DataAdapter |
UpdateCommand | A SQL statement or stored procedure to update rows from the DataSet to an Oracle database |
This property is a SQL statement or stored procedure to delete rows from an Oracle database.
// C# public OracleCommand DeleteCommand {get; set;}
An OracleCommand
used during the Update
call to delete rows from tables in the Oracle database, corresponding to the deleted rows in the DataSet
.
Default = null
If there is primary key information in the DataSet
, the DeleteCommand
can be automatically generated using the OracleCommandBuilder
, if no command is provided for this.
This property is a SQL statement or stored procedure to insert new rows into an Oracle database.
// C# public OracleCommand InsertCommand {get; set;}
An OracleCommand
used during the Update
call to insert rows into a table, corresponding to the inserted rows in the DataSet
.
Default = null
If there is primary key information in the DataSet
, the InsertCommand
can be automatically generated using the OracleCommandBuilder
, if no command is provided for this property.
This property determines whether the SelectCommand
is reexecuted on the next call to Fill
.
// C# public Boolean Requery {get; set;}
Returns true
if the SelectCommand
is reexecuted on the next call to Fill
; otherwise, returns false
.
This property creates a mapping between column names in the result set to .NET types that represent column values in the DataSet
, to preserve the data.
// C# public Hashtable SafeMapping {get; set;}
A hashtable.
Default = null
The SafeMapping
property is used, when necessary, to preserve data in the following types:
DATE
TimeStamp
(refers to all TimeStamp
objects)
INTERVAL
DAY
TO
SECOND
NUMBER
// C# // The following example shows how to use the SafeMapping property to fill the // dataset public static void UseSafeMapping( string connStr) {
//In this SELECT statement, EMPNO, HIREDATE and SALARY must be //preserved using safe type mapping. string cmdStr = "SELECT EMPNO, EMPNAME, HIREDATE, SALARY FROM EMPINFO"; //create the adapter with the selectCommand txt and the connection string OracleDataAdapter adapter = new OracleDataAdapter(cmdStr, connStr); //get the connection from the adapter OracleConnection connection = adapter.SelectCommand.Connection; //create the safe type mapping for the adapter //which can safely map column data to byte arrays, where // applicable. By executing the following statement, EMPNO, HIREDATE AND //SALARY columns will be mapped to byte[] adapter.SafeMapping.Add("*", typeof(byte[])); //Map HIREDATE to a string //If the column name in the EMPINFO table is case-sensitive, //the safe type mapping column name must be case-sensitive. adapter.SafeMapping.Add("HIREDATE", typeof(string)); //Map EMPNO to a string //If the column name in the EMPINFO table is case-sensitive, //the safe type mapping column name must also be case-sensitive. adapter.SafeMapping.Add("EMPNO", typeof(string)); //Create and fill the DataSet using the EMPINFO DataSet dataset = new DataSet(); adapter.Fill(dataset, "EMPINFO"); //Get the EMPINFO table from the dataset DataTable table = dataset.Tables["EMPINFO"]; //Get the first row from the EMPINFO table DataRow row0 = table.Rows[0]; //Print out the row info Console.WriteLine("EMPNO Column: type = " + row0["EMPNO"].GetType() + "; value = " + row0["EMPNO"]); Console.WriteLine("EMPNAME Column: type = " + row0["EMPNAME"].GetType() + "; value = " + row0["EMPNAME"]); Console.WriteLine("HIREDATE Column: type = " + row0["HIREDATE"].GetType()+ "; value = " + row0["HIREDATE"]); Console.WriteLine("SALARY Column: type = " + row0["SALARY"].GetType() + "; value = " + row0["SALARY"]); }
Output:
EMPNO Column: type = System.String; value = 1 EMPNAME Column: type = System.String; value = KING HIREDATE Column: type = System.String; value = 01-MAY-81 SALARY Column: type = System.Byte[]; value = System.Byte[]
This property is a SQL statement or stored procedure that returns single or multiple result sets.
// C# public OracleCommand SelectCommand {get; set;}
An OracleCommand
used during the Fill
call to populate the selected rows to the DataSet
.
Default = null
If the SelectCommand
does not return any rows, no tables are added to the dataset and no exception is raised.
If the SELECT
statement selects from a VIEW
, no key information is retrieved when a FillSchema()
or a Fill()
with MissingSchemaAction.AddWithKey
is invoked.
This property is a SQL statement or stored procedure to update rows from the DataSet
to an Oracle database.
// C# public OracleCommand UpdateCommand {get; set;}
An OracleCommand
used during the Update
call to update rows in the Oracle database, corresponding to the updated rows in the DataSet
.
Default = null
If there is primary key information in the DataSet
, the UpdateCommand
can be automatically generated using the OracleCommandBuilder
, if no command is provided for this property.
OracleDataAdapter
public methods are listed in Table 4-39.
Table 4-39 OracleDataAdapter Public Methods
Public Method | Description |
---|---|
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose |
Inherited from Component |
Equals |
Inherited from Object (Overloaded) |
Fill | Adds or refreshes rows in the DataSet to match the data in the Oracle database (Overloaded) |
FillSchema |
Inherited from DbDataAdapter |
GetFillParameters |
Inherited from DbDataAdapter |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
ToString |
Inherited from Object |
Update |
Inherited from DbDataAdapter |
Fill
Fill
populates or refreshes the specified DataTable
or DataSet
.
Fill(DataTable, OracleRefCursor)
This method adds or refreshes rows in the specified DataTable
to match those in the provided OracleRefCursor
object.
Fill(DataSet, OracleRefCursor)
This method adds or refreshes rows in the DataSet
to match those in the provided OracleRefCursor
object.
Fill(DataSet, string, OracleRefCursor)
This method adds or refreshes rows in the specified source table of the DataSet
to match those in the provided OracleRefCursor
object.
Fill(DataSet, int, int, string, OracleRefCursor)
This method adds or refreshes rows in a specified range in the DataSet
to match rows in the provided OracleRefCursor
object.
This method adds or refreshes rows in the specified DataTable
to match those in the provided OracleRefCursor
object.
// C# public int Fill(DataTable dataTable, OracleRefCursor refCursor);
dataTable
The DataTable
object being populated.
refCursor
The OracleRefCursor
that rows are being retrieved from.
The number of rows added to or refreshed in the DataTable
.
ArgumentNullException
- The dataTable
or refCursor
parameter is null.
InvalidOperationException
- The OracleRefCursor
is already being used to fetch data.
NotSupportedException
- The SafeMapping
type is not supported.
No schema or key information is provided, even if the Fill
method is called with MissingSchemaAction
set to MissingSchemaAction.AddWithKey
.
This method adds or refreshes rows in the DataSet
to match those in the provided OracleRefCursor
object.
// C# public int Fill(DataSet dataSet, OracleRefCursor refCursor);
dataSet
The DataSet
object being populated.
refCursor
The OracleRefCursor
that rows are being retrieved from.
Returns the number of rows added or refreshed in the DataSet
.
ArgumentNullException
- The dataSet
or refCursor
parameter is null.
InvalidOperationException
- The OracleRefCursor
is already being used to fetch data.
InvalidOperationException
- The OracleRefCursor
is ready to fetch data.
NotSupportedException
- The SafeMapping
type is not supported.
If there is no DataTable
to refresh, a new DataTable
named Table
is created and populated using the provided OracleRefCursor
object.
No schema or key information is provided, even if the Fill
method is called with MissingSchemaAction
set to MissingSchemaAction.AddWithKey
.
This method adds or refreshes rows in the specified source table of the DataSet
to match those in the provided OracleRefCursor
object.
// C# public int Fill(DataSet dataSet, string srcTable, OracleRefCursor refCursor);
dataSet
The DataSet
object being populated.
srcTable
The name of the source table used in the table mapping.
refCursor
The OracleRefCursor
that rows are being retrieved from.
Returns the number of rows added or refreshed into the DataSet
.
ArgumentNullException
- The dataSet
or refCursor
parameter is null.
InvalidOperationException
- The OracleRefCursor
is already being used to fetch data or the source table name is invalid.
NotSupportedException
- The SafeMapping
type is not supported.
No schema or key information is provided, even if the Fill
method is called with MissingSchemaAction
set to MissingSchemaAction.AddWithKey
.
This method adds or refreshes rows in a specified range in the DataSet
to match rows in the provided OracleRefCursor
object.
// C# public int Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable, OracleRefCursor refCursor);
dataSet
The DataSet
object being populated.
startRecord
The record number to start with.
maxRecords
The maximum number of records to obtain.
srcTable
The name of the source table used in the table mapping.
refCursor
The OracleRefCursor
that rows are being retrieved from.
This method returns the number of rows added or refreshed in the DataSet
. This does not include rows affected by statements that do not return rows.
ArgumentNullException
- The dataSet
or refCursor
parameter is null.
InvalidOperationException
- The OracleRefCursor
is already being used to fetch data or the source table name is invalid.
NotSupportedException
- The SafeMapping
type is not supported.
No schema or key information is provided, even if the Fill
method is called with MissingSchemaAction
set to MissingSchemaAction.AddWithKey
.
OracleDataAdapter
events are listed in Table 4-40.
Table 4-40 OracleDataAdapter Events
Event Name | Description |
---|---|
Disposed |
Inherited from Component |
FillError |
Inherited from DbDataAdapter |
RowUpdated | This event is raised when row(s) have been updated by the Update() method |
RowUpdating | This event is raised when row data are about to be updated to the database |
This event is raised when row(s) have been updated by the Update()
method.
// C# public event OracleRowUpdatedEventHandler RowUpdated;
The event handler receives an OracleRowUpdatedEventArgs
object which exposes the following properties containing information about the event.
Command
The OracleCommand
executed during the Update
.
Errors
(inherited from RowUpdatedEventArgs
)
The exception, if any, is generated during the Update
.
RecordsAffected
(inherited from RowUpdatedEventArgs
)
The number of rows modified, inserted, or deleted by the execution of the Command
.
Row
(inherited from RowUpdatedEventArgs
)
The DataRow
sent for Update
.
StatementType
(inherited from RowUpdatedEventArgs
)
The type of SQL statement executed.
Status
(inherited from RowUpdatedEventArgs
)
The UpdateStatus
of the Command
.
TableMapping
(inherited from RowUpdatedEventArgs
)
The DataTableMapping
used during the Update
.
The following example shows how to use the RowUpdating
and RowUpdated
events.
// C# // create the event handler for RowUpdating event protected static void OnRowUpdating(object sender, OracleRowUpdatingEventArgs e) { Console.WriteLine("Row updating....."); Console.WriteLine("Event arguments:"); Console.WriteLine("Command Text: " + e.Command.CommandText); Console.WriteLine("Command Type: " + e.StatementType); Console.WriteLine("Status: " + e.Status); } // create the event handler for RowUpdated event protected static void OnRowUpdated(object sender, OracleRowUpdatedEventArgs e) { Console.WriteLine("Row updated....."); Console.WriteLine("Event arguments:"); Console.WriteLine("Command Text: " + e.Command.CommandText); Console.WriteLine("Command Type: " + e.StatementType); Console.WriteLine("Status: " + e.Status); } public static void AdapterEvents(string connStr) { string cmdStr = "SELECT EMPNO, EMPNAME, SALARY FROM EMPINFO"; //create the adapter with the selectCommand txt and the //connection string OracleDataAdapter adapter = new OracleDataAdapter(cmdStr, connStr); //get the connection from the adapter OracleConnection connection = adapter.SelectCommand.Connection; //create the UpdateCommand object for updating the EMPINFO table //from the dataset adapter.UpdateCommand = new OracleCommand("UPDATE EMPINFO SET SALARY = "+ " :iSALARY where EMPNO = :iEMPNO", connection); adapter.UpdateCommand.Parameters.Add(":iSALARY", OracleDbType.Double, 0, "SALARY"); adapter.UpdateCommand.Parameters.Add(":iEMPNO", OracleDbType.Int16, 0, "EMPNO"); //Create and fill the DataSet using the EMPINFO DataSet dataset = new DataSet(); adapter.Fill(dataset, "EMPINFO"); //Get the EMPINFO table from the dataset DataTable table = dataset.Tables["EMPINFO"]; //Get the first row from the EMPINFO table DataRow row0 = table.Rows[0]; //update the salary in the first row row0["SALARY"] = 99999.99; //set the event handlers for the RowUpdated and the RowUpdating event //the OnRowUpdating() method will be triggered before the update, and //the OnRowUpdated() method will be triggered after the update adapter.RowUpdating += new OracleRowUpdatingEventHandler(OnRowUpdating); adapter.RowUpdated += new OracleRowUpdatedEventHandler(OnRowUpdated); //Now update the EMPINFO using the adapter, the salary //of 'KING' is changed to 99999.99 //The OnRowUpdating() and the OnRowUpdated() methods will be triggered adapter.Update(dataset, "EMPINFO"); }
This event is raised when row data are about to be updated to the database.
// C# public event OracleRowUpdatingEventHandler RowUpdating;
The event handler receives an OracleRowUpdatingEventArgs
object which exposes the following properties containing information about the event.
Command
The OracleCommand
executed during the Update
.
Errors
(inherited from RowUpdatingEventArgs
)
The exception, if any, is generated during the Update
.
Row
(inherited from RowUpdatingEventArgs
)
The DataRow
sent for Update
.
StatementType
(inherited from RowUpdatingEventArgs
)
The type of SQL statement executed.
Status
(inherited from RowUpdatingEventArgs
)
The UpdateStatus
of the Command
.
TableMapping
(inherited from RowUpdatingEventArgs
)
The DataTableMapping
used during the Update
.
The example for the RowUpdated
event also shows how to use the RowUpdating
event. See RowUpdated
event "Example".
OracleDataAdapter
event delegates are listed in Table 4-41.
Table 4-41 OracleDataAdapter Event Delegates
Event Delegate Name | Description |
---|---|
EventHandler |
Inherited from Component |
FillErrorEventHandler |
Inherited from DbDataAdapter |
OracleRowUpdatedEventHandler | Event Delegate for the RowUpdated Event |
OracleRowUpdatingEventHandler | Event Delegate for the RowUpdating Event |
This event delegate handles the RowUpdated
Event.
This event delegate handles the RowUpdating
Event.
An OracleDataReader
object represents a forward-only, read-only, in-memory result set.
Unlike the DataSet
, the OracleDataReader
stays connected and fetches one row at a time.
Object
MarshalByRefObject
OracleDataReader
// C# public sealed class OracleDataReader : MarshalByRefObject, IEnumerable, IDataReader, IDisposable, IDataRecord
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
An OracleDataReader
instance is constructed by a call to the ExecuteReader
method of the OracleCommand
object. The only properties that can be accessed after the DataReader
is closed or has been disposed, are IsClosed
and RecordsAffected
.
The OracleDataReader
examples in this section are based on the EMPINFO
table which is defined as follows:
CREATE TABLE empInfo ( empno NUMBER(4) PRIMARY KEY, empName VARCHAR2(20) NOT NULL, hiredate DATE, salary NUMBER(7,2), jobDescription Clob, byteCodes BLOB );
The EMPINFO
table has the following values:
EMPNO EMPNAME HIREDATE SALARY JOBDESCRIPTION BYTECODES (Hex Values) ===== ======= ======== ====== ============== ============ 1 KING 01-MAY-81 12345.67 SOFTWARE ENGR {0x12, 0x34} 2 SCOTT 01-SEP-75 34567.89 MANAGER {0x56, 0x78} 3 BLAKE 01-OCT-90 9999.12 TRANSPORT {0x23, 0x45} 4 SMITH NULL NULL NULL NULL
The following example retrieves the data from the EMPINFO
table:
//C # //This method retrieves all the data from EMPINFO table public void ReadEmpInfo(string connStr) { string cmdStr = "SELECT * FROM EMPINFO"; OracleConnection connection = new OracleConnection(connStr); OracleCommand cmd = new OracleCommand(cmdStr, connection); connection.Open(); OracleDataReader reader = cmd.ExecuteReader(); //declare the variables to retrieve the data in EmpInfo short empNo; string empName; DateTime hireDate; double salary; string jobDesc; byte[] byteCodes = new byte[10]; //read the next row until end of row while (reader.Read()) { empNo = reader.GetInt16(0); Console.WriteLine("Employee number: " + empNo); empName = reader.GetString(1); Console.WriteLine("Employee name: " + empName); //the following columns can have NULL value, so it //is important to call IsDBNull before getting the column data if (!reader.IsDBNull(2)) { hireDate = reader.GetDateTime(2); Console.WriteLine("Hire date: " + hireDate); } if (!reader.IsDBNull(3)) { salary = reader.GetDouble(3); Console.WriteLine("Salary: " + salary); } if (!reader.IsDBNull(4)) { jobDesc = reader.GetString(4); Console.WriteLine("Job Description: " + jobDesc); } if (!reader.IsDBNull(5)) { long len = reader.GetBytes(5, 0, byteCodes, 0, 10); Console.Write("Byte codes: " ); for (int i = 0; i < len; i++) Console.Write(byteCodes[i].ToString("x")); Console.WriteLine(); } Console.WriteLine(); //done reading one row } //Done Reading EMPINFO table //Close the reader reader.Close(); // Close the connection connection.Close(); }
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleDataReader
members are listed in the following tables:
OracleDataReader
static methods are listed in Table 4-42.
Table 4-42 OracleDataReader Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleDataReader
properties are listed in Table 4-43.
Table 4-43 OracleDataReader Properties
Property | Description |
---|---|
Depth | Gets a value indicating the depth of nesting for the current row |
FetchSize | Specifies the size of OracleDataReader 's internal cache |
FieldCount | Gets the number of columns in the result set |
IsClosed | Indicates whether the data reader is closed |
Item | Gets the value of the column (Overloaded) |
InitialLOBFetchSize |
Specifies the amount that the OracleDataReader initially fetches for LOB columns |
InitialLONGFetchSize | Specifies the amount that the OracleDataReader initially fetches for LONG and LONG RAW columns |
RecordsAffected | Gets the number of rows changed, inserted, or deleted by execution of the SQL statement |
OracleDataReader
public methods are listed in Table 4-44.
Table 4-44 OracleDataReader Public Methods
Public Method | Description |
---|---|
Close | Closes the OracleDataReader |
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose | Releases any resources or memory allocated by the object |
Equals |
Inherited from Object (Overloaded) |
GetBoolean |
Not Supported |
GetByte | Returns the byte value of the specified column |
GetBytes | Populates the provided byte array with up to the maximum number of bytes, from the specified offset (in bytes) of the column |
GetChar |
Not Supported |
GetChars | Populates the provided character array with up to the maximum number of characters, from the specified offset (in characters) of the column |
GetData |
Not Supported |
GetDataTypeName | Returns the ODP.NET type name of the specified column |
GetDateTime | Returns the DateTime value of the specified column |
GetDecimal | Returns the decimal value of the specified NUMBER column |
GetDouble | Returns the double value of the specified NUMBER column or BINARY_DOUBLE column |
GetFieldType | Returns the Type of the specified column |
GetFloat | Returns the float value of the specified NUMBER column or BINARY_FLOAT column |
GetGuid |
Not Supported |
GetHashCode |
Inherited from Object |
GetInt16 | Returns the Int16 value of the specified NUMBER column |
GetInt32 | Returns the Int32 value of the specified NUMBER column |
GetInt64 | Returns the Int64 value of the specified NUMBER column |
GetLifetimeService |
Inherited by MarshalByRefObject |
GetName | Returns the name of the specified column |
GetOracleBFile | Returns an OracleBFile object of the specified BFILE column |
GetOracleBinary | Returns an OracleBinary structure of the specified column |
GetOracleBlob | Returns an OracleBlob object of the specified BLOB column |
GetOracleBlobForUpdate | Returns an updatable OracleBlob object of the specified BLOB column |
GetOracleClob | Returns an OracleClob object of the specified CLOB column |
GetOracleClobForUpdate | Returns an updatable OracleClob object of the specified CLOB column |
GetOracleDate | Returns an OracleDate structure of the specified DATE column |
GetOracleDecimal | Returns an OracleDecimal structure of the specified NUMBER column |
GetOracleIntervalDS | Returns an OracleIntervalDS structure of the specified INTERVAL DAY TO SECOND column |
GetOracleIntervalYM | Returns an OracleIntervalYM structure of the specified INTERVAL YEAR TO MONTH column |
GetOracleString | Returns an OracleString structure of the specified column |
GetOracleTimeStamp | Returns an OracleTimeStamp structure of the Oracle TimeStamp column |
GetOracleTimeStampLTZ | Returns an OracleTimeStampLTZ structure of the specified Oracle TimeStamp WITH LOCAL TIME ZONE column |
GetOracleTimeStampTZ | Returns an OracleTimeStampTZ structure of the specified Oracle TimeStamp WITH TIME ZONE column |
GetOracleXmlType | Returns an OracleXmlType object of the specified XMLType column |
GetOracleValue | Returns the specified column value as a ODP.NET type |
GetOracleValues | Gets all the column values as ODP.NET types |
GetOrdinal | Returns the 0 -based ordinal (or index) of the specified column name |
GetSchemaTable | Returns a DataTable that describes the column metadata of the OracleDataReader |
GetString | Returns the string value of the specified column |
GetTimeSpan | Returns the TimeSpan value of the specified INTERVAL DAY TO SECOND column |
GetType |
Inherited from Object class |
GetValue | Returns the column value as a .NET type |
GetValues | Gets all the column values as .NET types |
GetXmlReader | Returns the contents of an XMLType column as an instance of an .NET XmlTextReader object |
IsDBNull | Indicates whether the column value is null |
NextResult | Advances the data reader to the next result set when reading the results |
Read | Reads the next row in the result set |
ToString |
Inherited from Object |
OracleDataReader
static methods are listed in Table 4-45.
Table 4-45 OracleDataReader Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleDataReader
properties are listed in Table 4-46.
Table 4-46 OracleDataReader Properties
Property | Description |
---|---|
Depth | Gets a value indicating the depth of nesting for the current row |
FetchSize | Specifies the size of OracleDataReader 's internal cache |
FieldCount | Gets the number of columns in the result set |
IsClosed | Indicates whether the data reader is closed |
Item | Gets the value of the column (Overloaded) |
InitialLOBFetchSize |
Specifies the amount that the OracleDataReader initially fetches for LOB columns |
InitialLONGFetchSize | Specifies the amount that the OracleDataReader initially fetches for LONG and LONG RAW columns |
RecordsAffected | Gets the number of rows changed, inserted, or deleted by execution of the SQL statement |
This property gets a value indicating the depth of nesting for the current row.
// C# public int Depth {get;}
The depth of nesting for the current row.
IDataReader
InvalidOperationException
- The reader is closed.
Default = 0
This property always returns zero because Oracle does not support nesting.
This property specifies the size of OracleDataReader
's internal cache.
// C# public long FetchSize {get; set;}
A long
that specifies the amount of memory (in bytes) that the OracleDataReader
uses for its internal cache.
ArgumentException
- The FetchSize
value specified is invalid.
Default = The OracleCommand
's FetchSize
property value.
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 fetched into its internal cache for each server round-trip.
This property gets the number of columns in the result set.
// C# public int FieldCount {get;}
The number of columns in the result set if one exists, otherwise 0
.
IDataRecord
InvalidOperationException
- The reader is closed.
Default = 0
This property has a value of 0 for queries that do not return result sets.
This property indicates whether the data reader is closed.
// C# public bool IsClosed {get;}
If the OracleDataReader
is in a closed state, returns true
; otherwise, returns false
.
IDataReader
Default = true
IsClosed
and RecordsAffected
are the only two properties that are accessible after the OracleDataReader
is closed.
Item
This property gets the value of the column in .NET datatype.
This property gets the .NET Value
of the column specified by the column index.
This property gets the .NET Value
of the column specified by the column name.
This property gets the .NET Value
of the column specified by the column index.
// C# public object this[int index] {get;}
index
The zero-based index of the column.
The .NET value of the specified column.
IDataRecord
Default = Not Applicable
In C#, this property is the indexer for this class.
This property gets the .NET Value
of the column specified by the column name.
// C# public object this[string columnName] {get;}
columnName
The name of the column.
The .NET Value
of the specified column.
IDataRecord
Default = Not Applicable
A case-sensitive search is made to locate the specified column by its name. If this fails, then a case-insensitive search is made.
In C#, this property is the indexer for this class.
This property specifies the amount that the OracleDataReader
initially fetches for LOB columns.
// C# public int InitialLOBFetchSize {get;}
The size of the chunk to retrieve.
InvalidOperationException
- The reader is closed.
The maximum value supported for InitialLOBFetchSize
is 32767
. If this property is set to a higher value, the provider resets it to 32767
.
Default is the OracleCommand.InitialLOBFetchSize
, from which this value is inherited.
This property specifies the amount that the OracleDataReader
initially fetches for LONG
and LONG
RAW
columns.
// C# public long InitialLONGFetchSize {get;}
The size of the chunk to retrieve. The default is 0
.
InvalidOperationException
- The reader is closed.
The maximum value supported for InitialLONGFetchSize
is 32767
. If this property is set to a higher value, the provider resets it to 32767
.
Default is OracleCommand.InitialLONGFetchSize
, from which this value is inherited.
This property is read-only for the OracleDataReader
.
This property gets the number of rows changed, inserted, or deleted by execution of the SQL statement.
// C# public int RecordsAffected {get;}
The number of rows affected by execution of the SQL statement.
IDataReader
Default = 0
The value of -1
is returned for SELECT
statements.
IsClosed
and RecordsAffected
are the only two properties that are accessible after the OracleDataReader
is closed.
OracleDataReader
public methods are listed in Table 4-47.
Table 4-47 OracleDataReader Public Methods
Public Method | Description |
---|---|
Close | Closes the OracleDataReader |
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose | Releases any resources or memory allocated by the object |
Equals |
Inherited from Object (Overloaded) |
GetBoolean |
Not Supported |
GetByte | Returns the byte value of the specified column |
GetBytes | Populates the provided byte array with up to the maximum number of bytes, from the specified offset (in bytes) of the column |
GetChar |
Not Supported |
GetChars | Populates the provided character array with up to the maximum number of characters, from the specified offset (in characters) of the column |
GetData |
Not Supported |
GetDataTypeName | Returns the ODP.NET type name of the specified column |
GetDateTime | Returns the DateTime value of the specified column |
GetDecimal | Returns the decimal value of the specified NUMBER column |
GetDouble | Returns the double value of the specified NUMBER column or BINARY_DOUBLE column |
GetFieldType | Returns the Type of the specified column |
GetFloat | Returns the float value of the specified NUMBER column or BINARY_FLOAT column |
GetGuid |
Not Supported |
GetHashCode |
Inherited from Object |
GetInt16 | Returns the Int16 value of the specified NUMBER column |
GetInt32 | Returns the Int32 value of the specified NUMBER column |
GetInt64 | Returns the Int64 value of the specified NUMBER column |
GetLifetimeService |
Inherited by MarshalByRefObject |
GetName | Returns the name of the specified column |
GetOracleBFile | Returns an OracleBFile object of the specified BFILE column |
GetOracleBinary | Returns an OracleBinary structure of the specified column |
GetOracleBlob | Returns an OracleBlob object of the specified BLOB column |
GetOracleBlobForUpdate | Returns an updatable OracleBlob object of the specified BLOB column |
GetOracleClob | Returns an OracleClob object of the specified CLOB column |
GetOracleClobForUpdate | Returns an updatable OracleClob object of the specified CLOB column |
GetOracleDate | Returns an OracleDate structure of the specified DATE column |
GetOracleDecimal | Returns an OracleDecimal structure of the specified NUMBER column |
GetOracleIntervalDS | Returns an OracleIntervalDS structure of the specified INTERVAL DAY TO SECOND column |
GetOracleIntervalYM | Returns an OracleIntervalYM structure of the specified INTERVAL YEAR TO MONTH column |
GetOracleString | Returns an OracleString structure of the specified column |
GetOracleTimeStamp | Returns an OracleTimeStamp structure of the Oracle TimeStamp column |
GetOracleTimeStampLTZ | Returns an OracleTimeStampLTZ structure of the specified Oracle TimeStamp WITH LOCAL TIME ZONE column |
GetOracleTimeStampTZ | Returns an OracleTimeStampTZ structure of the specified Oracle TimeStamp WITH TIME ZONE column |
GetOracleXmlType | Returns an OracleXmlType object of the specified XMLType column |
GetOracleValue | Returns the specified column value as a ODP.NET type |
GetOracleValues | Gets all the column values as ODP.NET types |
GetOrdinal | Returns the 0 -based ordinal (or index) of the specified column name |
GetSchemaTable | Returns a DataTable that describes the column metadata of the OracleDataReader |
GetString | Returns the string value of the specified column |
GetTimeSpan | Returns the TimeSpan value of the specified INTERVAL DAY TO SECOND column |
GetType |
Inherited from Object class |
GetValue | Returns the column value as a .NET type |
GetValues | Gets all the column values as .NET types |
GetXmlReader | Returns the value of an XMLType column as an instance of an .NET XmlTextReader |
IsDBNull | Indicates whether the column value is null |
NextResult | Advances the data reader to the next result set when reading the results |
Read | Reads the next row in the result set |
ToString |
Inherited from Object |
This method closes the OracleDataReader
.
// C# public void Close();
IDataReader
The Close
method frees all resources associated with the OracleDataReader
.
The code example for the OracleDataReader
class includes the Close
method. See OracleDataReader
Overview "Example".
This method releases any resources or memory allocated by the object.
// C# public void Dispose();
IDisposable
The Dispose
method also closes the OracleDataReader
.
This method returns the byte value of the specified column.
// C# public byte GetByte(int index);
index
The zero-based column index.
The value of the column as a byte.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method populates the provided byte array with up to the maximum number of bytes, from the specified offset (in bytes) of the column.
// C# public long GetBytes(int index, long fieldOffset, byte[] buffer, int bufferOffset, int length);
index
The zero-based column index.
fieldOffset
The offset within the column from which reading begins (in bytes).
buffer
The byte array that the data is read into.
bufferOffset
The offset within the buffer to begin reading data into (in bytes).
length
The maximum number of bytes to read (in bytes).
The number of bytes read.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
This method returns the number of bytes read into the buffer. This may be less than the actual length of the field if the method has been called previously for the same column.
If a null reference is passed for buffer, the length of the field in bytes is returned.
IsDBNull
should be called to check for NULL
values before calling this method.
This method populates the provided character array with up to the maximum number of characters, from the specified offset (in characters) of the column.
// C# public long GetChars(int index, long fieldOffset, char[] buffer, int bufferOffset, int length);
index
The zero based column index.
fieldOffset
The index within the column from which to begin reading (in characters).
buffer
The character array that the data is read into.
bufferOffset
The index within the buffer to begin reading data into (in characters).
length
The maximum number of characters to read (in characters).
The number of characters read.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
This method returns the number of characters read into the buffer. This may be less than the actual length of the field, if the method has been called previously for the same column.
If a null reference is passed for buffer, the length of the field in characters is returned.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns the ODP.NET type name of the specified column.
// C# public string GetDataTypeName(int index);
index
The zero-based column index.
The name of the ODP.NET type of the column.
IDataRecord
InvalidOperationException
- The reader is closed.
IndexOutOfRangeException
- The column index is invalid.
This method returns the DateTime
value of the specified column.
// C# public DateTime GetDateTime(int index);
index
The zero-based column index.
The DateTime
value of the column.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns the decimal
value of the specified NUMBER
column.
// C# public decimal GetDecimal(int index);
index
The zero-based column index.
The decimal
value of the column.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns the double
value of the specified NUMBER
column or BINARY_DOUBLE
column.
// C# public double GetDouble(int index);
index
The zero-based column index.
The double
value of the column.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
Starting with Oracle Database 10g, GetDouble
now supports retrieval of data from BINARY_DOUBLE
columns.
This method returns the Type
of the specified column.
// C# public Type GetFieldType(int index);
index
The zero-based column index.
The Type
of the default .NET type of the column.
IDataRecord
InvalidOperationException
- The reader is closed.
IndexOutOfRangeException
- The column index is invalid.
This method returns the float
value of the specified NUMBER
column or BINARY_FLOAT
column.
// C# public float GetFloat(int index);
index
The zero-based column index.
The float
value of the column.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
Starting with Oracle Database 10g, GetFloat
now supports retrieval of data from BINARY_FLOAT
columns.
This method returns the Int16
value of the specified NUMBER
column.
Note: short is equivalent to Int16 . |
// C# public short GetInt16(int index);
index
The zero-based column index.
The Int16
value of the column.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns the Int32
value of the specified NUMBER
column.
Note: int is equivalent to Int32 . |
// C# public int GetInt32(int index);
index
The zero-based column index.
The Int32
value of the column.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns the Int64
value of the specified NUMBER
column.
Note: long is equivalent to Int64 . |
// C# public long GetInt64(int index);
index
The zero-based column index.
The Int64
value of the column.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns the name of the specified column.
// C# public string GetName(int index);
index
The zero-based column index.
The name of the column.
IDataRecord
InvalidOperationException
- The reader is closed.
IndexOutOfRangeException
- The column index is invalid.
This method returns an OracleBFile
object of the specified BFILE
column.
// C# public OracleBFile GetOracleBFile(int index);
index
The zero-based column index.
The OracleBFile
value of the column.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns an OracleBinary
structure of the specified column.
// C# public OracleBinary GetOracleBinary(int index);
index
The zero-based column index.
The OracleBinary
value of the column.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
GetOracleBinary
is used on the following Oracle types:
BFILE
BLOB
LONG
RAW
RAW
This method returns an OracleBlob
object of the specified BLOB
column.
// C# public OracleBlob GetOracleBlob(int index);
index
The zero-based column index.
The OracleBlob
value of the column.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
GetOracleBlobForUpdate
GetOracleBlobForUpdate
returns an updatable OracleBlob
object of the specified BLOB
column.
This method returns an updatable OracleBlob
object of the specified BLOB
column.
GetOracleBlobForUpdate(int, int)
This method returns an updatable OracleBlob
object of the specified BLOB
column using a WAIT
clause.
This method returns an updatable OracleBlob
object of the specified BLOB
column.
// C# public OracleBlob GetOracleBlobForUpdate(int index);
index
The zero-based column index.
An updatable OracleBlob
object.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
When the OracleCommand
's ExecuteReader()
method is invoked, all the data fetched by the OracleDataReader
is from a particular snapshot. Therefore, calling an accessor method on the same column always returns the same value. However, the GetOracleBlobForUpdate()
method incurs a server round-trip to obtain a reference to the current BLOB
data while also locking the row using the FOR
UPDATE
clause. This means that the OracleBlob
obtained from GetOracleBlob()
can have a different value than the OracleBlob
obtained from GetOracleBlobForUpdate()
since it is not obtained from the original snapshot.
The returned OracleBlob
object can be used to safely update the BLOB
because the BLOB
column has been locked after a call to this method.
Invoking this method internally executes a SELECT..FOR UPDATE
statement without a WAIT
clause. Therefore, the statement can wait indefinitely until a lock is acquired for that row.
IsDBNull
should be called to check for NULL
values before calling this method.
The following example gets the OracleBlob
object for update from the reader, updates the OracleBlob
object, and then commits the transaction.
// C# public static void ReadOracleBlobForUpdate(string connStr) { //get the job description for empno = 1 string cmdStr = "SELECT BYTECODES, EMPNO FROM EMPINFO where EMPNO = 1"; OracleConnection connection = new OracleConnection(connStr); OracleCommand cmd = new OracleCommand(cmdStr, connection); connection.Open(); //Since we are going to update the OracleBlob object, we will //have to create a transaction OracleTransaction txn = connection.BeginTransaction(); //get the reader OracleDataReader reader = cmd.ExecuteReader(); //declare the variables to retrieve the data in EmpInfo OracleBlob byteCodesBlob; //read the first row reader.Read(); if (!reader.IsDBNull(0)) { byteCodesBlob = reader.GetOracleBlobForUpdate(0); //Close the reader reader.Close(); //Update the job description Clob object byte[] addedBytes = new byte[2] {0, 0}; byteCodesBlob.Append(addedBytes, 0, addedBytes.Length); //Now commit the transaction txn.Commit(); } else reader.Close(); // Close the connection connection.Close(); }
This method returns an updatable OracleBlob
object of the specified BLOB
column using a WAIT
clause.
// C# public OracleBlob GetOracleBlobForUpdate(int index, int wait);
index
The zero-based column index.
wait
The number of seconds the method waits to acquire a lock.
An updatable OracleBlob
object.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
When the OracleCommand
's ExecuteReader()
method is invoked, all the data fetched by the OracleDataReader
is from a particular snapshot. Therefore, calling an accessor method on the same column always returns the same value. However, the GetOracleBlobForUpdate()
method incurs a server round-trip to obtain a reference to the current BLOB
data while also locking the row using the FOR
UPDATE
clause. This means that the OracleBlob
obtained from GetOracleBlob()
can have a different value than the OracleBlob
obtained from GetOracleBlobForUpdate()
since it is not obtained from the original snapshot.
IsDBNull
should be called to check for NULL
values before calling this method.
The returned OracleBlob
object can be used to safely update the BLOB
because the BLOB
column has been locked after a call to this method.
Invoking this method internally executes a SELECT..FOR UPDATE
statement which locks the row.
Different WAIT
clauses are appended to the statement, depending on the wait
value. If the wait
value is:
0
"NOWAIT
" is appended at the end of a SELECT..FOR
UPDATE
statement. The statement executes immediately whether the lock is acquired or not. If the lock is not acquired, an exception is thrown.
n
"WAIT
n
" is appended at the end of a SELECT..FOR
UPDATE
statement. The statement executes as soon as the lock is acquired. However, if the lock cannot be acquired by n
seconds, this method call throws an exception.
The WAIT
n
" feature is only available for Oracle9i or later. For any version lower than Oracle9i, n
is implicitly treated as -1
and nothing is appended at the end of a SELECT..FOR
UPDATE
statement.
-1
Nothing is appended at the end of the SELECT..FOR
UPDATE
. The statement execution waits indefinitely until a lock can be acquired.
The GetOracleBlobForUpdate methods are comparable. See "Example" for a code example demonstrating usage.
This method returns an OracleClob object of the specified CLOB column.
// C# public OracleClob GetOracleClob(int index);
index
The zero-based column index.
The OracleClob
value of the column.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
GetOracleClobForUpdate
GetOracleClobForUpdate
returns an updatable OracleClob
object of the specified CLOB
column.
This method returns an updatable OracleClob
object of the specified CLOB
column.
GetOracleClobForUpdate(int, int)
This method returns an updatable OracleClob
object of the specified CLOB
column using a WAIT
clause.
This method returns an updatable OracleClob
object of the specified CLOB
column.
// C# public OracleClob GetOracleClobForUpdate(int index);
index
The zero-based column index.
An updatable OracleClob
.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
When the OracleCommand
's ExecuteReader()
method is invoked, all the data fetched by the OracleDataReader
is from a particular snapshot. Therefore, calling an accessor method on the same column always returns the same value. However, the GetOracleClobForUpdate()
method incurs a server round-trip to obtain a reference to the current CLOB
data while also locking the row using the FOR
UPDATE
clause. This means that the OracleClob
obtained from GetOracleClob()
can have a different value than the OracleClob
obtained from GetOracleClobForUpdate()
since it is not obtained from the original snapshot.
The returned OracleClob
object can be used to safely update the CLOB
because the CLOB
column is locked after a call to this method.
Invoking this method internally executes a SELECT..FOR
UPDATE
statement without a WAIT
clause. Therefore, the statement can wait indefinitely until a lock is acquired for that row.
IsDBNull
should be called to check for NULL
values before calling this method.
The following example gets the OracleClob
object for update from the reader, updates the OracleClob
object, and then commits the transaction.
// C# public static void ReadOracleClobForUpdate(string connStr) { //get the job description for empno = 1 string cmdStr = "SELECT JOBDESCRIPTION, EMPNO FROM EMPINFO where EMPNO = 1"; OracleConnection connection = new OracleConnection(connStr); OracleCommand cmd = new OracleCommand(cmdStr, connection); connection.Open(); //Since we are going to update the OracleClob object, we will //have to create a transaction OracleTransaction txn = connection.BeginTransaction(); //get the reader OracleDataReader reader = cmd.ExecuteReader(); //declare the variables to retrieve the data in EmpInfo OracleClob jobDescClob; //read the first row reader.Read(); if (!reader.IsDBNull(0)) { jobDescClob = reader.GetOracleClobForUpdate(0); //Close the reader reader.Close(); //Update the job description Clob object char[] jobDesc = "-SALES".ToCharArray(); jobDescClob.Append(jobDesc, 0, jobDesc.Length); //Now commit the transaction txn.Commit(); } else reader.Close(); // Close the connection connection.Close(); }
This method returns an updatable OracleClob
object of the specified CLOB
column using a WAIT
clause.
// C# public OracleClob GetOracleClobForUpdate(int index, int wait);
index
The zero-based column index.
wait
The number of seconds the method waits to acquire a lock.
An updatable OracleClob
.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
When the OracleCommand
's ExecuteReader()
method is invoked, all the data fetched by the OracleDataReader
is from a particular snapshot. Therefore, calling an accessor method on the same column always returns the same value. However, the GetOracleClobForUpdate()
method incurs a server round-trip to obtain a reference to the current CLOB
data while also locking the row using the FOR
UPDATE
clause. This means that the OracleClob
obtained from GetOracleClob()
can have a different value than the OracleClob
obtained from GetOracleClobForUpdate()
since it is not obtained from the original snapshot.
Invoking this method internally executes a SELECT..FOR UPDATE
statement which locks the row.
The returned OracleClob
object can be used to safely update the CLOB
because the CLOB
column is locked after a call to this method.
Different WAIT
clauses are appended to the statement, depending on the wait
value. If the wait
value is:
0
"NOWAIT
" is appended at the end of a SELECT..FOR UPDATE
statement. The statement executes immediately whether the lock is acquired or not. If the lock is not acquired, an exception is thrown.
n
"WAIT
n
" is appended at the end of a SELECT..FOR UPDATE
statement. The statement executes as soon as the lock is acquired. However, if the lock cannot be acquired by n
seconds, this method call throws an exception.
The WAIT
n
" feature is only available for Oracle9i or later. For any version lower than Oracle9i, n
is implicitly treated as -1
and nothing is appended at the end of a SELECT..FOR
UPDATE
statement.
-1
Nothing is appended at the end of the SELECT..FOR UPDATE
. The statement execution waits indefinitely until a lock can be acquired.
IsDBNull
should be called to check for NULL
values before calling this method.
The GetOracleClobForUpdate methods are comparable. See "Example" for a code example demonstrating usage.
This method returns an OracleDate structure of the specified DATE column.
// C# public OracleDate GetOracleDate(int index);
index
The zero-based column index.
The OracleDate
value of the column.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns an OracleDecimal
structure of the specified NUMBER
column.
// C# public OracleDecimal GetOracleDecimal(int index);
index
The zero-based column index.
The OracleDecimal
value of the column.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns an OracleIntervalDS
structure of the specified INTERVAL
DAY
TO
SECOND
column.
// C# public OracleIntervalDS GetOracleIntervalDS(int index);
index
The zero-based column index.
The OracleIntervalDS
value of the column.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns an OracleIntervalYM
structure of the specified INTERVAL
YEAR
TO
MONTH
column.
// C# public OracleIntervalYM GetOracleIntervalYM(int index);
index
The zero-based column index.
The OracleIntervalYM
value of the column.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns an OracleString
structure of the specified column. The string is stored as a Unicode string.
// C# public OracleString GetOracleString(int index);
index
The zero-based column index.
The OracleString
value of the column.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
GetOracleString
is used on the following Oracle column types:
CHAR
CLOB
LONG
NCLOB
NCHAR
NVARCHAR2
ROWID
UROWID
VARCHAR2
XMLType
This method returns an OracleTimeStamp
structure of the Oracle TimeStamp
column.
// C# public OracleTimeStamp GetOracleTimeStamp(int index);
index
The zero-based column index.
The OracleTimeStamp
value of the column.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
GetOracleTimeStamp
is used with the Oracle Type TimeStamp
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns an OracleTimeStampLTZ
structure of the specified Oracle TimeStamp
WITH
LOCAL
TIME
ZONE
column.
// C# public OracleTimeStampLTZ GetOracleTimeStampLTZ(int index);
index
The zero-based column index.
The OracleTimeStampLTZ
value of the column.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
GetOracleTimeStampLTZ
is used with the Oracle Type TimeStamp
with Local Time Zone columns.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns an OracleTimeStampTZ
structure of the specified Oracle TimeStamp
WITH
TIME
ZONE
column.
// C# public OracleTimeStampTZ GetOracleTimeStampTZ(int index);
index
The zero-based column index.
The OracleTimeStampTZ
value of the column.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
Used with the Oracle Type TimeStamp
with Local Time Zone columns
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns an OracleXmlType
object of the specified XMLType
column.
// C# public OracleXmlType GetOracleXmlType(int index);
index
The zero-based column index.
The OracleXmlType
value of the column.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This property can only be used with Oracle9i Release 2 (9.2) or higher.
This method returns the specified column value as an ODP.NET type.
// C# public object GetOracleValue(int index);
index
The zero-based column index.
The value of the column as an ODP.NET type.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
This method gets all the column values as ODP.NET types.
// C# public int GetOracleValues(object[] values);
values
An array of objects to hold the ODP.NET types as the column values.
The number of ODP.NET types in the values
array.
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
This method provides a way to retrieve all column values rather than retrieving each column value individually.
The number of column values retrieved is the minimum of the length of the values
array and the number of columns in the result set.
This method returns the 0
-based ordinal (or index) of the specified column name.
// C# public int GetOrdinal(string name);
name
The specified column name.
The index of the column.
IDataRecord
InvalidOperationException
- The reader is closed.
IndexOutOfRangeException
- The column index is invalid.
A case-sensitive search is made to locate the specified column by its name. If this fails, then a case-insensitive search is made.
This method returns a DataTable
that describes the column metadata of the OracleDataReader
.
// C# public DataTable GetSchemaTable();
A DataTable
that contains the metadata of the result set.
IDataReader
InvalidOperationException
- The connection is closed or the reader is closed.
OracleDataReader.GetSchemaTable()
returns the SchemaTable
.
The OracleDataReader
SchemaTable
is a DataTable
that describes the column metadata of the OracleDataReader
.
The columns of the SchemaTable
are in the order shown.
Table 4-48 OracleDataReader SchemaTable
Name | Name Type | Description |
---|---|---|
ColumnNameTB |
System.String |
The name of the column. |
ColumnOrdinal |
System.Int32 |
The 0 -based ordinal of the column. |
ColumnSize |
System.Int64 |
The maximum possible length of a value in the column. ColumnSize value is determined as follows:
See " |
NumericPrecision |
System.Int16 |
The maximum precision of the column, if the column is a numeric datatype.
This column has valid values for Oracle |
NumericScale |
System.Int16 |
The scale of the column.
This column has valid values for Oracle |
IsUnique |
System.Boolean |
Indicates whether the column is unique.
The default is The value of this property is the same for each occurrence of the base table column in the select list. |
IsKey |
System.Boolean |
Indicates whether the column is a key column.
This set of columns can be generated from one of the following in descending order of priority:
An explicitly selected |
IsRowID |
System.Boolean |
true if the column is a ROWID , otherwise false . |
BaseColumnName |
System.String |
The name of the column in the database if an alias is used for the column. |
BaseSchemaName |
System.String |
The name of the schema in the database that contains the column. |
BaseTableName |
System.String |
The name of the table or view in the database that contains the column. |
DataType |
System.RuntimeType |
Maps to the common language runtime type. |
ProviderType |
Oracle.DataAccess. Client.OracleDbType |
The database column type (OracleDbType ) of the column. |
AllowDBNull |
System.Boolean |
true if null values are allowed, otherwise false . |
IsAliased |
System.Boolean |
true if the column is an alias; otherwise false . |
IsByteSemantic |
System.Boolean |
IsByteSemantic is:
This value is always |
IsExpression |
System.Boolean |
true if the column is an expression; otherwise false . |
IsHidden |
System.Boolean |
true if the column is hidden; otherwise false . |
IsReadOnly |
System.Boolean |
true if the column is read-only; otherwise false . |
IsLong |
System.Boolean |
true if the column is a LONG , LONG RAW , BLOB , CLOB , or BFILE ; otherwise false . |
This example creates and uses the SchemaTable
from the reader.
// C# public static void ReadSchemaTable(string connStr) { ..... //get the reader OracleDataReader reader = cmd.ExecuteReader(); //get the schema table DataTable schemaTable = reader.GetSchemaTable(); //retrieve the first column info. DataRow col0 = schemaTable.Rows[0]; //print out the column info Console.WriteLine("Column name: " + col0["COLUMNNAME"]); Console.WriteLine("Precision: " + col0["NUMERICPRECISION"]); Console.WriteLine("Scale: " + col0["NUMERICSCALE"]); ..... }
This method returns the string
value of the specified column.
// C# public string GetString(int index);
index
The zero-based column index.
The string
value of the column.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns the TimeSpan
value of the specified INTERVAL
DAY
TO
SECOND
column.
// C# public TimeSpan GetTimeSpan(int index);
index
The zero-based column index.
The TimeSpan
value of the column.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns the column value as a .NET type.
// C# public object GetValue(int index);
index
The zero-based column index.
The value of the column as a .NET type.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
When this method is invoked for a NUMBER
column, the .NET type returned depends on the precision and scale of the column. For example, if a column is defined as NUMBER
(4,0)
then values in this column are retrieved as a System.Int16
.If the precision and scale is such that no .NET type can represent all the possible values that could exist in that column, the value is returned as a System.Decimal
, if possible. If the value cannot be represented by a System.Decimal
, an exception is raised. For example, if a column is defined as NUMBER
(20,10)
then a value in this column is retrieved as a System.Decimal
.
This method gets all the column values as .NET types.
// C# public int GetValues(object[ ] values);
values
An array of objects to hold the .NET types as the column values.
The number of objects in the values
array.
IDataRecord
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
This method provides a way to retrieve all column values rather than retrieving each column value individually.
The number of column values retrieved is the minimum of the length of the values array and the number of columns in the result set.
This method returns the contents of an XMLType
column as an instance of an .NET XmlTextReader
object.
// C# public XmlReader GetXmlReader(int index);
index
The zero-based column index.
A .NET XmlTextReader
.
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
IsDBNull
should be called to check for NULL
values before calling this method.
This method indicates whether the column value is NULL
.
// C# public bool IsDBNull(int index);
index
The zero-based column index.
Returns true
if the column is a NULL
value; otherwise, returns false
.
IDataRecord
InvalidOperationException
- The reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
This method should be called to check for NULL
values before calling the other accessor methods.
The code example for the OracleDataReader
class includes the IsDBNull
method. See "Example".
This method advances the data reader to the next result set.
// C# public bool NextResult();
Returns true
if another result set exists; otherwise, returns false
.
IDataReader
InvalidOperationException
- The connection is closed or the reader is closed.
NextResult
is used when reading results from stored procedure execution that return more than one result set.
This method reads the next row in the result set.
// C# public bool Read();
Returns true
if another row exists; otherwise, returns false
.
IDataReader
InvalidOperationException
- The connection is closed or the reader is closed.
The initial position of the data reader is before the first row. Therefore, the Read
method must be called to fetch the first row. The row that was just read is considered the current row. If the OracleDataReader
has no more rows to read, it returns false
.
The code example for the OracleDataReader
class includes the Read
method. See "Example".
The OracleError
class represents an error reported by Oracle.
Object
OracleError
// C# public sealed class OracleError
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
The OracleError
class represents a warning or an error reported by Oracle.
// C# ... try { cmd.ExecuteNonQuery() } catch ( OracleException e ){ OracleError err1 = e.Errors[0]; OracleError err2 = e.Errors[1]; Console.WriteLine("Error 1 Message:", err1.Message); Console.WriteLine("Error 2 Source:", err2.Source); }
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleError
members are listed in the following tables:
OracleError
static methods are listed in Table 4-49.
OracleError
properties are listed in Table 4-50.
Table 4-50 OracleError Properties
Properties | Description |
---|---|
ArrayBindIndex |
Specifies the row number of errors that occurred during the Array Bind execution |
DataSource | Specifies the Oracle service name (TNS name) that identifies the Oracle database |
Message | Specifies the message describing the error |
Number | Specifies the Oracle error number |
Procedure | Specifies the stored procedure that causes the error |
Source | Specifies the name of the data provider that generates the error |
OracleError
methods are listed in Table 4-51.
Table 4-51 OracleError Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString | Returns a string representation of the OracleError |
OracleError
static methods are listed in Table 4-52.
OracleError
properties are listed in Table 4-53.
Table 4-53 OracleError Properties
Properties | Description |
---|---|
ArrayBindIndex |
Specifies the row number of errors that occurred during the Array Bind execution |
DataSource | Specifies the Oracle service name (TNS name) that identifies the Oracle database |
Message | Specifies the message describing the error |
Number | Specifies the Oracle error number |
Procedure | Specifies the stored procedure that causes the error |
Source | Specifies the name of the data provider that generates the error |
This property specifies the row number of errors that occurred during the Array Bind execution.
// C# public int ArrayBindIndex {get;}
An int
value that specifies the row number for errors that occurred during the Array Bind execution.
Default = 0.
This property is used for Array Bind operations only.
ArrayBindIndex
represents the zero-based row number at which the error occurred during an Array Bind operation. For example, if an array bind execution causes two errors on the 2nd and 4th operations, two OracleError
objects appear in the OracleErrorCollection
with the ArrayBindIndex
property values 2 and 4 respectively.
This property specifies the Oracle service name (TNS name) that identifies the Oracle database.
// C# public string DataSource {get;}
A string
.
This property specifies the message
describing the error
.
// C# public string Message {get;}
A string
.
This property specifies the Oracle error
number.
// C# public int Number {get;}
An int
.
This property specifies the stored procedure that causes the error
.
// C# public string Procedure {get;}
The stored procedure name.
Represents the stored procedure which creates this OracleError
object.
This property specifies the name of the data provider that generates the error
.
// C# public string Source {get;}
A string
.
OracleError
methods are listed in Table 4-54.
Table 4-54 OracleError Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString | Returns a string representation of the OracleError |
Overrides Object
This method returns a string representation of the OracleError
.
// C# public override string ToString();
Returns a string with the format Ora- error number: Class.Method name error message stack trace information.
ORA-24333: zero iteration count
An OracleErrorCollection
class represents a collection of all errors that are thrown by the Oracle Data Provider for .NET.
Object
ArrayList
OracleErrorCollection
// C# public sealed class OracleErrorCollection : ArrayList
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
A simple ArrayList
that holds a list of OracleError
s.
// C# // The following example demonstrates how to access an individual OracleError // from an OracleException ... public void DisplayErrors(OracleException myException) { for (int i=0; i < myException.Errors.Count; i++;) { Console.WriteLine("Index #" + i + "\n" + "Error: " + myException.Errors[i].ToString() + "\n"); } } ...
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleErrorCollection
members are listed in the following tables:
OracleErrorCollection
static methods are listed in Table 4-55.
Table 4-55 OracleErrorCollection Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleErrorCollection
properties are listed in Table 4-56.
Table 4-56 OracleErrorCollection Properties
Name | Description |
---|---|
Capacity |
Inherited from ArrayList |
Count |
Inherited from ArrayList |
IsReadOnly |
Inherited from ArrayList |
IsSynchronized |
Inherited from ArrayList |
Item |
Inherited from ArrayList |
OracleErrorCollection
public methods are listed in Table 4-57.
Table 4-57 OracleErrorCollection Public Methods
Public Method | Description |
---|---|
CopyTo |
Inherited from ArrayList |
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString |
Inherited from Object |
OracleErrorCollection
static methods are listed in Table 4-58.
Table 4-58 OracleErrorCollection Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleErrorCollection
properties are listed in Table 4-59.
Table 4-59 OracleErrorCollection Properties
Name | Description |
---|---|
Capacity |
Inherited from ArrayList |
Count |
Inherited from ArrayList |
IsReadOnly |
Inherited from ArrayList |
IsSynchronized |
Inherited from ArrayList |
Item |
Inherited from ArrayList |
OracleErrorCollection
public methods are listed in Table 4-60.
Table 4-60 OracleErrorCollection Public Methods
Public Method | Description |
---|---|
CopyTo |
Inherited from ArrayList |
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString |
Inherited from Object |
The OracleException
class represents an exception that is thrown when the Oracle Data Provider for .NET encounters an error. Each OracleException
object contains at least one OracleError
object in the Error
property that describes the error or warning.
Object
Exception
SystemException
OracleException
// C# public sealed class OracleException : SystemException
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
// C# // The following example generates an OracleException due to bad SQL syntax, // (that is the missing keyword "from") and then displays the exception message // and source property. .. try { ... // select * emp will cause ORA-00923 OracleCommand cmd = new OracleCommand("select * emp", con); } catch ( OracleException e ) { Console.WriteLine("{0} throws {1}",e.Source, e.Message); } ..
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleException
members are listed in the following tables:
OracleException
static methods are listed in Table 4-61.
Table 4-61 OracleException Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleException
properties are listed in Table 4-62.
Table 4-62 OracleException Properties
Properties | Description |
---|---|
DataSource | Specifies the TNS name that contains the information for connecting to an Oracle instance |
Errors | Specifies a collection of one or more OracleError objects that contain information about exceptions generated by the Oracle database |
HelpLink |
Inherited from Exception |
InnerException |
Inherited from Exception |
Message | Specifies the error messages that occur in the exception |
Number | Specifies the Oracle error number |
Procedure | Specifies the stored procedure that cause the exception |
Source | Specifies the name of the data provider that generates the error |
StackTrace |
Inherited from Exception |
TargetSite |
Inherited from Exception |
OracleException
methods are listed in Table 4-63.
Table 4-63 OracleException Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
GetBaseException |
Inherited from Exception |
GetHashCode |
Inherited from Object |
GetObjectData | Sets the serializable info object with information about the exception |
GetType |
Inherited from Object |
ToString | Returns the fully qualified name of this exception |
OracleException
static methods are listed in Table 4-64.
Table 4-64 OracleException Static Methods
Method | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleException
properties are listed in Table 4-65.
Table 4-65 OracleException Properties
Properties | Description |
---|---|
DataSource | Specifies the TNS name that contains the information for connecting to an Oracle instance |
Errors | Specifies a collection of one or more OracleError objects that contain information about exceptions generated by the Oracle database |
HelpLink |
Inherited from Exception |
InnerException |
Inherited from Exception |
Message | Specifies the error messages that occur in the exception |
Number | Specifies the Oracle error number |
Procedure | Specifies the stored procedure that cause the exception |
Source | Specifies the name of the data provider that generates the error |
StackTrace |
Inherited from Exception |
TargetSite |
Inherited from Exception |
This property specifies the TNS name that contains the information for connecting to an Oracle instance.
// C# public string DataSource {get;}
The TNS name containing the connect information.
This property specifies a collection of one or more OracleError
objects that contain information about exceptions generated by the Oracle database.
// C# public OracleErrorCollection Errors {get;}
An OracleErrorCollection
.
The Errors
property contains at least one instance of OracleError
objects.
Overrides Exception
This property specifies the error messages that occur in the exception.
// C# public override string Message {get;}
A string
.
Message
is a concatenation of all errors in the Errors
collection. Each error message is concatenated and is followed by a carriage return, except the last one.
This property specifies the Oracle error number.
// C# public int Number {get;}
The error number.
This error number can be the topmost level of error generated by Oracle and can be a provider-specific error number.
This property specifies the stored procedure that caused the exception.
// C# public string Procedure {get;}
The stored procedure name.
Overrides Exception
This property specifies the name of the data provider that generates the error.
// C# public override string Source {get;}
The name of the data provider.
OracleException
methods are listed in Table 4-66.
Table 4-66 OracleException Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
GetBaseException |
Inherited from Exception |
GetHashCode |
Inherited from Object |
GetObjectData | Sets the serializable info object with information about the exception |
GetType |
Inherited from Object |
ToString | Returns the fully qualified name of this exception |
Overrides Exception
This method sets the serializable info
object with information about the exception.
// C# public override void GetObjectData(SerializationInfo info, StreamingContext context);
info
A SerializationInfo
object.
context
A StreamingContext
object.
The information includes DataSource
, Message
, Number
, Procedure
, Source
, and StackTrace
.
Overrides Exception
This method returns the fully qualified name of this exception, the error
message in the Message
property, the InnerException.ToString()
message, and the stack trace.
// C# public override string ToString();
The string representation of the exception.
// C# ... try { ... // select * from emp will cause ORA-00923 OracleCommand cmd = new OracleCommand("select * from emp", con); } catch ( OracleException e ) { Console.WriteLine("{0}",e.ToString()); } ...
The OracleFailoverEventArgs
class provides event data for the OracleConnection.Failover
event. When database failover occurs, the OracleConnection.Failover
event is triggered along with the OracleFailoverEventArgs
object that stores the event data.
Object
EventArgs
OracleFailoverEventArgs
// C# public sealed class OracleFailoverEventArgs
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
// C# // Receiving Failover notifications switch (eventArgs.FailoverEvent) { case FailoverEvent.Begin: { Console.WriteLine(" \nFailover Begin - Failing Over ..." + "Please stand by \n"); Console.WriteLine(" \nFailover type was found to be " + eventArgs.FailoverType); break; } case FailoverEvent.End: { Console.WriteLine(" \nFailover ended ...resuming services\n"); break; } case FailoverEvent.Error: { Console.WriteLine(" Failover error gotten. Sleeping...\n"); Thread.Sleep(3000); return FailoverReturnCode.Retry; } default: { Console.WriteLine("\nBad Failover Event: " + eventArgs.FailoverEvent); break; } }
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleFailoverEventArgs
members are listed in the following tables:
The OracleFailoverEventArgs
static methods are listed in Table 4-67.
Table 4-67 OracleFailoverEventArgs Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
The OracleFailoverEventArgs
properties are listed in Table 4-68.
Table 4-68 OracleFailoverEventArgs Properties
Name | Description |
---|---|
FailoverType | Specifies the type of failover the client has requested |
FailoverEvent | Indicates the state of the failover |
The OracleFailoverEventArgs
public methods are listed in Table 4-69.
Table 4-69 OracleFailoverEventArgs Public Methods
Name | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString |
Inherited from Object |
The OracleFailoverEventArgs
static methods are listed in Table 4-70.
Table 4-70 OracleFailoverEventArgs Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
The OracleFailoverEventArgs
properties are listed in Table 4-71.
Table 4-71 OracleFailoverEventArgs Properties
Name | Description |
---|---|
FailoverType | Specifies the type of failover the client has requested |
FailoverEvent | Indicates the state of the failover |
This property indicates the state of the failover.
// C# public FailoverType FailoverType {get;}
A FailoverType
enumeration value.
This property indicates the state of the failover.
// C# public FailoverEvent FailoverEvent {get;}
A FailoverEvent
enumerated value.
The OracleFailoverEventArgs
public methods are listed in Table 4-72.
Table 4-72 OracleFailoverEventArgs Public Methods
Name | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString |
Inherited from Object |
The OracleFailoverEventHandler
represents the signature of the method that handles the OracleConnection.Failover
event.
// C# public delegate FailoverReturnCode OracleFailoverEventHandler(object sender, OracleFailoverEventArgs eventArgs);
sender
The source of the event.
eventArgs
The OracleFailoverEventArgs
object that contains the event data.
An int
.
To receive failover notifications, a callback function can be registered as follows:
ConObj.Failover += new OracleFailoverEventHandler(OnFailover);
The definition of the callback function OnFailover
can be as follows:
public FailoverReturnCode OnFailover(object sender, OracleFailoverEventArgs eventArgs)
void Main(string[] args) { ... // register callback function OnFailOver ConObj.Failover += new OracleFailoverEventHandler(OnFailOver); ... } //Failover Callback Function public FailoverReturnCode OnFailOver(object sender, OracleFailoverEventArgs eventArgs) { switch (eventArgs.FailoverEvent) { case FailoverEvent.Begin: { Console.WriteLine(" \nFailover Begin - Failing Over ... Please stand by \n"); Console.WriteLine(" Failover type was found to be " + eventArgs.FailoverType); break; } case FailoverEvent.Abort: { Console.WriteLine(" Failover aborted. Failover will not take place.\n"); break; } case FailoverEvent.End: { Console.WriteLine(" Failover ended ...resuming services\n"); break; } case FailoverEvent.Reauth: { Console.WriteLine(" Failed over user. Resuming services\n"); break; } case FailoverEvent.Error: { Console.WriteLine(" Failover error gotten. Sleeping...\n"); Thread.Sleep(3000); return FailoverReturnCode.Retry; } default: { Console.WriteLine("Bad Failover Event: %d.\n", eventArgs.FailoverEvent); break; } } return FailoverReturnCode.Success; } /* OnFailover */
The OracleGlobalization
class is used to obtain and set the Oracle globalization settings of the session, thread, and local computer (read-only).
Class Inheritance
Object
OracleGlobalization
public sealed class OracleGlobalization : ICloneable, IDisposable
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
An exception is thrown for invalid property values. All newly set property values are validated, except the TimeZone
property.
Changing the OracleGlobalization
object properties does not change the globalization settings of the session or the thread. Either the SetSessionInfo
method of the OracleConnection
object or the SetThreadInfo
method of the OracleGlobalization
object must be called to alter the session's and thread's globalization settings, respectively.
// C# // Sets thread globalization info. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); //Retrieves thread globalization info OracleGlobalization ogi = OracleGlobalization.GetThreadInfo(); //Print the language name in thread globalization info Console.WriteLine("Thread language: " + ogi.Language); //Set thread's language ogi.Language = "FRENCH"; OracleGlobalization.SetThreadInfo(ogi); OracleGlobalization ogi2; OracleGlobalization.GetThreadInfo(ogi2); //Print the language name in thread globalization info Console.WriteLine("Thread language: " + ogi2.Language);
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleGlobalization
members are listed in the following tables:
The OracleGlobalization
static methods are listed in Table 4-73.
Table 4-73 OracleGlobalization Static Methods
Name | Description |
---|---|
GetClientInfo | Returns an OracleGlobalization object that represents the Oracle globalization settings of the local computer (Overloaded) |
GetThreadInfo | Returns or refreshes an OracleGlobalization instance that represents Oracle globalization settings of the current thread (Overloaded) |
SetThreadInfo | Sets Oracle globalization parameters to the current thread |
The OracleGlobalization
properties are listed in Table 4-74.
Table 4-74 OracleGlobalization Properties
Name | Description |
---|---|
Calendar | Specifies the calendar system |
ClientCharacterSet | Specifies a client character set |
Comparison | Specifies a method of comparison for WHERE clauses and comparison in PL/SQL blocks |
Currency | Specifies the string to use as a local currency symbol for the L number format element |
DateFormat | Specifies the date format for Oracle Date type as a string |
DateLanguage | Specifies the language used to spell day and month names and date abbreviations |
DualCurrency | Specifies the dual currency symbol, such as Euro, for the U number format element |
ISOCurrency | Specifies the string to use as an international currency symbol for the C number format element |
Language | Specifies the default language of the database |
LengthSemantics | Enables creation of CHAR and VARCHAR2 columns using either byte or character (default) length semantics |
NCharConversionException | Determines whether data loss during an implicit or explicit character type conversion reports an error |
NumericCharacters | Specifies the characters used for the decimal character and the group separator character for numeric values in strings |
Sort | Specifies the collating sequence for ORDER by clause |
Territory | Specifies the name of the territory |
TimeStampFormat | Specifies the string format for TimeStamp types |
TimeStampTZFormat | Specifies the string format for TimeStampTZ types |
TimeZone | Specifies the time zone region name |
OracleGlobalization
public methods are listed in Table 4-75.
Table 4-75 OracleGlobalization Public Methods
Public Method | Description |
---|---|
Clone | Creates a copy of an OracleGlobalization object |
Dispose |
Inherited from Component |
The OracleGlobalization
static methods are listed in Table 4-76.
Table 4-76 OracleGlobalization Static Methods
Name | Description |
---|---|
GetClientInfo | Returns an OracleGlobalization object that represents the Oracle globalization settings of the local computer (Overloaded) |
GetThreadInfo | Returns or refreshes an OracleGlobalization instance that represents Oracle globalization settings of the current thread (Overloaded) |
SetThreadInfo | Sets Oracle globalization parameters to the current thread |
GetClientInfo
GetClientInfo
returns an OracleGlobalization
object instance that represents the Oracle globalization settings of the local computer.
This method returns an OracleGlobalization
instance that represents the globalization settings of the local computer.
GetClientInfo(OracleGlobalization)
This method refreshes the provided OracleGlobalization
object with the globalization settings of the local computer.
This method returns an OracleGlobalization
instance that represents the globalization settings of the local computer.
// C# public static OracleGlobalization GetClientInfo();
An OracleGlobalization
instance.
// C# // Retrieves the client globalization info. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); //Retrieves the client globalization info OracleGlobalization ogi = OracleGlobalization.GetClientInfo(); //Retrieves the client globalization info using overloaded method OracleGlobalization ogi2; OracleGlobalization.GetClientInfo(ogi2); //Print the language name in client globalization info Console.WriteLine("Client machine language: " + ogi.Language); Console.WriteLine("Client machine language: " + ogi2.Language);
This method refreshes the provided OracleGlobalization
object with the globalization settings of the local computer.
// C# public static void GetClientInfo(OracleGlobalization oraGlob);
oraGlob
The OracleGlobalization
object being updated.
// C# // Retrieves the client globalization info. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); //Retrieves the client globalization info OracleGlobalization ogi = OracleGlobalization.GetClientInfo(); //Retrieves the client globalization info using overloaded method OracleGlobalization ogi2; OracleGlobalization.GetClientInfo(ogi2); //Print the language name in client globalization info Console.WriteLine("Client machine language: " + ogi.Language); Console.WriteLine("Client machine language: " + ogi2.Language);
GetThreadInfo
GetThreadInfo
returns or refreshes an OracleGlobalization
instance.
This method returns an OracleGlobalization
object instance of the current thread.
GetThreadInfo(OracleGlobalization)
This method refreshes the OracleGlobalization
object instance with the globalization settings of the current thread.
This method returns an OracleGlobalization
instance of the current thread.
// C# public static OracleGlobalization GetThreadInfo();
An OracleGlobalization
instance.
Initially, GetThreadInfo()
returns an OracleGlobalization
object that has the same property values as that returned by GetClientInfo()
, unless the application changes it by invoking SetThreadInfo()
.
// C# Retrieves the thread globalization info. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); //Retrieves the thread globalization info OracleGlobalization ogi = OracleGlobalization.GetThreadInfo(); //Retrieves the thread globalization info using overloaded method OracleGlobalization ogi2; OracleGlobalization.GetThreadInfo(ogi2); //Print the language name in thread globalization info Console.WriteLine("Thread language: " + ogi.Language); Console.WriteLine("Thread language: " + ogi2.Language);
This method refreshes the OracleGlobalization
object with the globalization settings of the current thread.
// C# public static void GetThreadInfo(OracleGlobalization oraGlob);
oraGlob
The OracleGlobalization
object being updated.
Initially GetThreadInfo()
returns an OracleGlobalization
object that has the same property values as that returned by GetClientInfo()
, unless the application changes it by invoking SetThreadInfo()
.
// C# Retrieves the thread globalization info. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); //Retrieves the thread globalization info OracleGlobalization ogi = OracleGlobalization.GetThreadInfo(); //Retrieves the thread globalization info using overloaded method OracleGlobalization ogi2; OracleGlobalization.GetThreadInfo(ogi2); //Print the language name in thread globalization info Console.WriteLine("Thread language: " + ogi.Language); Console.WriteLine("Thread language: " + ogi2.Language);
This method sets Oracle globalization parameters to the current thread.
// C# public static void SetThreadInfo(OracleGlobalization oraGlob);
oraGlob
An OracleGlobalization
object.
Any .NET string conversions to and from ODP.NET Types, as well as ODP.NET Type constructors, use the globalization property values where applicable. For example, when constructing an OracleDate
structure from a .NET string, that string is expected to be in the format specified by the OracleGlobalization
.DateFormat
property of the thread.
// C# // Sets thread globalization info. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); //Retrieves thread globalization info OracleGlobalization ogi = OracleGlobalization.GetThreadInfo(); //Print the language name in thread globalization info Console.WriteLine("Thread language: " + ogi.Language); //Set thread's language ogi.Language = "FRENCH"; OracleGlobalization.SetThreadInfo(ogi); OracleGlobalization ogi2; OracleGlobalization.GetThreadInfo(ogi2); //Print the language name in thread globalization info Console.WriteLine("Thread language: " + ogi2.Language);
The OracleGlobalization
properties are listed in Table 4-77.
Table 4-77 OracleGlobalization Properties
Name | Description |
---|---|
Calendar | Specifies the calendar system |
ClientCharacterSet | Specifies a client character set |
Comparison | Specifies a method of comparison for WHERE clauses and comparison in PL/SQL blocks |
Currency | Specifies the string to use as a local currency symbol for the L number format element |
DateFormat | Specifies the date format for Oracle Date type as a string |
DateLanguage | Specifies the language used to spell day and month names and date abbreviations |
DualCurrency | Specifies the dual currency symbol, such as Euro, for the U number format element |
ISOCurrency | Specifies the string to use as an international currency symbol for the C number format element |
Language | Specifies the default language of the database |
LengthSemantics | Enables creation of CHAR and VARCHAR2 columns using either byte or character (default) length semantics |
NCharConversionException | Determines whether data loss during an implicit or explicit character type conversion reports an error |
NumericCharacters | Specifies the characters used for the decimal character and the group separator character for numeric values in strings |
Sort | Specifies the collating sequence for ORDER by clause |
Territory | Specifies the name of the territory |
TimeStampFormat | Specifies the string format for TimeStamp types |
TimeStampTZFormat | Specifies the string format for TimeStampTZ types |
TimeZone | Specifies the time zone region name |
This property specifies the calendar system.
// C# public string Calendar {get; set;}
A string representing the Calendar
.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_CALENDAR
setting of the local computer. This value is the same regardless of whether the OracleGlobalization
object represents the settings of the client, thread, or session.
This property specifies a client character set.
// C# public string ClientCharacterSet {get;}
A string that the provides the name of the character set of the local computer.
The default value is the character set of the local computer.
This property represents a method of comparison for WHERE
clauses and comparison in PL/SQL blocks.
// C# public string Comparison {get; set;}
A string that provides the name of the method of comparison.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_COMP
setting of the local computer.
This property specifies the string to use as a local currency symbol for the L number format element.
// C# public string Currency {get; set;}
The string to use as a local currency symbol for the L number format element.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_CURRENCY
setting of the local computer.
This property specifies the date format for Oracle Date
type as a string.
// C# public string DateFormat {get; set;}
The date format for Oracle Date
type as a string
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_DATE_FORMAT
setting of the local computer.
This property specifies the language used to spell names of days and months, and date abbreviations (for example: a.m., p.m., AD, BC).
// C# public string DateLanguage {get; set;}
A string specifying the language.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_DATE_LANGUAGE
setting of the local computer.
This property specifies the dual currency symbol, such as Euro, for the U number format element.
// C# public string DualCurrency {get; set;}
A string that provides the dual currency symbol.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_DUAL_CURRENCY
setting of the local computer.
This property specifies the string to use as an international currency symbol for the C number format element.
// C# public string ISOCurrency {get; set;}
The string used as an international currency symbol.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_ISO_CURRENCY
setting of the local computer.
This property specifies the default language of the database.
// C# public string Language {get; set;}
The default language of the database.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_LANGUAGE
setting of the local computer.
Language
is used for messages, day and month names, and sorting algorithms. It also determines NLS_DATE_LANGUAGE
and NLS_SORT
parameter values.
This property indicates whether CHAR
and VARCHAR2
columns use byte or character (default) length semantics.
// C# public string LengthSemantics {get; set;}
A string that indicates either byte or character length semantics.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_LENGTH_SEMANTICS
setting of the local computer.
This property determines whether data loss during an implicit or explicit character type conversion reports an error.
// C# public bool NCharConversionException {get; set;}
A string that indicates whether or not a character type conversion causes an error message.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_NCHAR_CONV_EXCP
setting of the local computer.
This property specifies the characters used for the decimal character and the group separator character for numeric values in strings.
// C# public string NumericCharacters {get; set;}
A string that represents the characters used.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_NUMERIC_CHARACTERS
setting of the local computer.
This property specifies the collating sequence for ORDER
by clause.
// C# public string Sort {get; set;}
A string that indicates the collating sequence.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_SORT
setting of the local computer.
This property specifies the name of the territory.
// C# public string Territory {get; set;}
A string that provides the name of the territory.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_TERRITORY
setting of the local computer.
Changing this property changes other globalization properties.
This property specifies the string format for TimeStamp
types.
// C# public string TimeStampFormat {get; set;}
The string format for TimeStamp
types.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_TIMESTAMP_FORMAT
setting of the local computer.
This property specifies the string format for TimeStampTZ
types.
// C# public string TimeStampTZFormat {get; set;}
The string format for TimeStampTZ
types.
ObjectDisposedException
- The object is already disposed.
The default value is the NLS_TIMESTAMP_TZ_FORMAT
setting of the local computer.
This property specifies the time zone region name or hour offset.
// C# public string TimeZone {get; set;}
The string represents the time zone region name or the time zone offset.
ObjectDisposedException
- The object is already disposed.
The default value is the time zone region name of the local computer
TimeZone
is only used when the thread constructs one of the TimeStamp
structures. TimeZone
has no effect on the session.
TimeZone
can be either an hour offset, for example, 7:00, or a valid time zone region name that is provided in V$TIMEZONE_NAMES
, such as US/Pacific. Time zone abbreviations are not supported.
Note: PST is a time zone region name as well as a time zone abbreviation; therefore it is accepted byOracleGlobalization . |
This property returns an empty string if the OracleGlobalization
object is obtained using GetSessionInfo()
or GetSessionInfo(OracleGlobalization)
. Initially, by default, the time zone of the session is identical to the time zone of the thread. Therefore, given that the session time zone is not changed by invoking ALTER
SESSION
calls, the session time zone can be fetched from the client's globalization settings.
OracleGlobalization
public methods are listed in Table 4-78.
Table 4-78 OracleGlobalization Public Methods
Public Method | Description |
---|---|
Clone | Creates a copy of an OracleGlobalization object |
Dispose |
Inherited from Component |
This method creates a copy of an OracleGlobalization
object.
// C# public object Clone();
An OracleGlobalization
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 OracleGlobalization ogi_cloned = (OracleGlobalization) ogi.Clone(); ...
The OracleInfoMessageEventArgs
class provides event data for the OracleConnection.InfoMessage
event. When any warning occurs in the database, the OracleConnection.InfoMessage
event is triggered along with the OracleInfoMessageEventArgs
object that stores the event data.
Object
EventArgs
OracleInfoMessageEventArgs
// C# public sealed class OracleInfoMessageEventArgs
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
// C# public void WarningHandler(object src, OracleInfoMessageEventArgs args) { LogOutput("Source object is: " + src.GetType().Name); LogOutput("InfoMessageArgs.Message is " + args.Message); LogOutput("InfoMessageArgs.Errors is " + args.Errors); LogOutput("InfoMessageArgs.Source is " + args.Source); } public bool MyFunc() { ... con.Open(); OracleCommand cmd = Con.CreateCommand(); //Register to the InfoMessageHandler cmd.Connection.InfoMessage += new OracleInfoMessageEventHandler(WarningHandler); cmd.CommandText = CmdStr; cmd.CommandType = CommandType.Text; //If CmdStr causes warning(s), it will be handled. cmd.ExecuteNonQuery(); ... }
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleInfoMessageEventArgs
members are listed in the following tables:
The OracleInfoMessageEventArgs
static methods are listed in Table 4-79.
Table 4-79 OracleInfoMessageEventArgs Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
The OracleInfoMessageEventArgs
properties are listed in Table 4-80.
Table 4-80 OracleInfoMessageEventArgs Properties
Name | Description |
---|---|
Errors | Specifies the collection of errors generated by the data source |
Message | Specifies the error text generated by the data source |
Source | Specifies the name of the object that generated the error |
The OracleInfoMessageEventArgs
methods are listed in Table 4-81.
Table 4-81 OracleInfoMessageEventArgs Public Methods
Name | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString |
Inherited from Object |
The OracleInfoMessageEventArgs
static methods are listed in Table 4-82.
Table 4-82 OracleInfoMessageEventArgs Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
The OracleInfoMessageEventArgs
properties are listed in Table 4-83.
Table 4-83 OracleInfoMessageEventArgs Properties
Name | Description |
---|---|
Errors | Specifies the collection of errors generated by the data source |
Message | Specifies the error text generated by the data source |
Source | Specifies the name of the object that generated the error |
This property specifies the collection of errors generated by the data source.
// C# public OracleErrorCollection Errors {get;}
The collection of errors.
This property specifies the error text generated by the data source.
// C# public string Message {get;}
The error text.
This property specifies the name of the object that generated the error.
// C# public string Source {get;}
The object that generated the error.
The OracleInfoMessageEventArgs
methods are listed in Table 4-84.
Table 4-84 OracleInfoMessageEventArgs Public Methods
Name | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString |
Inherited from Object |
The OracleInfoMessageEventHandler
represents the signature of the method that handles the OracleConnection.InfoMessage
event.
// C# public delegate void OracleInfoMessageEventHandler(object sender, OracleInfoMessageEventArgs eventArgs);
sender
The source of the event.
eventArgs
The OracleInfoMessageEventArgs
object that contains the event data.
An OracleParameter
object represents a parameter for an OracleCommand
or a DataSet
column.
Object
MarshalByRefObject
OracleParameter
// C# public sealed class OracleParameter : MarshalByRefObject, IDBDataParameter, IDataParameter, IDisposable, ICloneable
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
ArgumentException
- The type binding is invalid.
// C# ... OracleParameter [] prm = new OracleParameter[3]; // Create OracleParameter objects through OracleParameterCollection prm[0] = cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal, 1234, ParameterDirection.Input); prm[1] = cmd.Parameters.Add("paramEname", OracleDbType.Varchar2, "Client", ParameterDirection.Input); prm[2] = cmd.Parameters.Add("paramDeptNo", OracleDbType.Decimal, 10, ParameterDirection.Input); cmd.CommandText = "insert into emp(empno, ename, deptno) values(:1, :2, :3)"; cmd.CommandType = CommandType.CommandText; cmd.ExecuteNonQuery(); ...
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleParameter
members are listed in the following tables:
OracleParameter
constructors are listed in Table 4-85.
Table 4-85 OracleParameter Constructors
Constructor | Description |
---|---|
OracleParameter Constructors | Instantiates a new instance of OracleParameter class (Overloaded) |
OracleParameter
static methods are listed in Table 4-86.
Table 4-86 OracleParameter Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleParameter
properties are listed in Table 4-87.
Table 4-87 OracleParameter Properties
Name | Description |
---|---|
ArrayBindSize | Specifies the input or output size of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution |
ArrayBindStatus | Specifies the input or output status of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution |
CollectionType |
Specifies whether the OracleParameter represents a collection, and if so, specifies the collection type |
DbType | Specifies the datatype of the parameter using the Data.DbType enumeration type |
Direction | Specifies whether the parameter is input-only, output-only, bi-directional, or a stored function return value parameter |
IsNullable |
This method is a no-op |
Offset | Specifies the offset to the Value property or offset to the elements in the Value property |
OracleDbType | Specifies the Oracle datatype |
ParameterName | Specifies the name of the parameter |
Precision | Specifies the maximum number of digits used to represent the Value property |
Scale | Specifies the number of decimal places to which Value property is resolved |
Size | Specifies the maximum size, in bytes or characters, of the data transmitted to or from the server. For PL/SQL Associative Array Bind, Size specifies the maximum number of elements in PL/SQL Associative Array. |
SourceColumn | Specifies the name of the DataTable Column of the DataSet |
SourceVersion | Specifies the DataRowVersion value to use when loading the Value property of the parameter |
Status | Indicates the status of the execution related to the data in the Value property |
Value | Specifies the value of the Parameter |
OracleParameter
public methods are listed in Table 4-88.
Table 4-88 OracleParameter Public Methods
Public Method | Description |
---|---|
Clone | Creates a shallow copy of an OracleParameter object |
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose | Releases allocated resources |
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
ToString |
Inherited from Object (Overloaded) |
OracleParameter
constructors instantiate new instances of the OracleParameter
class.
This constructor instantiates a new instance of OracleParameter
class.
OracleParameter (string, OracleDbType)
This constructor instantiates a new instance of OracleParameter
class using the supplied parameter name and Oracle datatype.
OracleParameter(string, object)
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name and parameter value.
OracleParameter(string, OracleDbType, ParameterDirection)
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name, datatype, and parameter direction.
OracleParameter(string, OracleDbType, object, ParameterDirection)
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name, datatype, value, and direction.
OracleParameter(string, OracleDbType, int)
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name, datatype, and size.
OracleParameter(string, OracleDbType, int, string)
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name, datatype, size, and source column.
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name, datatype, size, direction, null indicator, precision, scale, source column, source version and parameter value.
OracleParameter(string, OracleDbType, int, object, ParameterDirection)
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name, datatype, size, value, and direction.
This constructor instantiates a new instance of OracleParameter
class.
// C# public OracleParameter();
Default Values:
DbType
- String
ParameterDirection
- Input
isNullable
- true
offset
- 0
OracleDbType
- Varchar2
ParameterAlias
- Empty string
ParameterName
- Empty string
Precision
- 0
Size
- 0
SourceColumn
- Empty string
SourceVersion
- Current
ArrayBindStatus
- Success
Value
- null
This constructor instantiates a new instance of OracleParameter
class using the supplied parameter name and Oracle datatype.
// C# public OracleParameter(string parameterName, OracleDbType oraType);
parameterName
Specifies the parameter name.
oraType
Specifies the datatype of the OracleParameter
.
Changing the DbType
implicitly changes the OracleDbType
.
Unless explicitly set in the constructor, all the properties have the default values.
Default Values:
DbType
- String
ParameterDirection
- Input
isNullable
- true
offset
- 0
OracleDbType
- Varchar2
ParameterAlias
- Empty string
ParameterName
- Empty string
Precision
- 0
Size
- 0
SourceColumn
- Empty string
SourceVersion
- Current
ArrayBindStatus
- Success
Value
- null
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name and parameter value.
// C# public OracleParameter(string parameterName, object obj);
parameterName
Specifies parameter name.
obj
Specifies value of the OracleParameter
.
Unless explicitly set in the constructor, all the properties have the default values.
Default Values:
DbType
- String
ParameterDirection
- Input
isNullable
- true
offset
- 0
OracleDbType
- Varchar2
ParameterAlias
- Empty string
ParameterName
- Empty string
Precision
- 0
Size
- 0
SourceColumn
- Empty string
SourceVersion
- Current
ArrayBindStatus
- Success
Value
- null
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name, datatype, and parameter direction.
// C# public OracleParameter(string parameterName, OracleDbType type, ParameterDirection direction);
parameterName
Specifies the parameter name.
type
Specifies the datatype of the OracleParameter
.
direction
Specifies the direction of the OracleParameter
.
Unless explicitly set in the constructor, all the properties have the default values.
Default Values:
DbType
- String
ParameterDirection
- Input
isNullable
- true
offset
- 0
OracleDbType
- Varchar2
ParameterAlias
- Empty string
ParameterName
- Empty string
Precision
- 0
Size
- 0
SourceColumn
- Empty string
SourceVersion
- Current
ArrayBindStatus
- Success
Value
- null
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name, datatype, value, and direction.
// C# public OracleParameter(string parameterName, OracleDbType type, object obj, ParameterDirection direction);
parameterName
Specifies the parameter name.
type
Specifies the datatype of the OracleParameter
.
obj
Specifies the value of the OracleParameter
.
direction
Specifies one of the ParameterDirection
values.
Changing the DbType
implicitly changes the OracleDbType
.
Unless explicitly set in the constructor, all the properties have the default values.
Default Values:
DbType
- String
ParameterDirection
- Input
isNullable
- true
offset
- 0
OracleDbType
- Varchar2
ParameterAlias
- Empty string
ParameterName
- Empty string
Precision
- 0
Size
- 0
SourceColumn
- Empty string
SourceVersion
- Current
ArrayBindStatus
- Success
Value
- null
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name, datatype, and size.
// C# public OracleParameter(string parameterName, OracleDbType type, int size);
parameterName
Specifies the parameter name.
type
Specifies the datatype of the OracleParameter
.
size
Specifies the size of the OracleParameter
value.
Unless explicitly set in the constructor, all the properties have the default values.
Default Values:
DbType
- String
ParameterDirection
- Input
isNullable
- true
offset
- 0
OracleDbType
- Varchar2
ParameterAlias
- Empty string
ParameterName
- Empty string
Precision
- 0
Size
- 0
SourceColumn
- Empty string
SourceVersion
- Current
ArrayBindStatus
- Success
Value
- null
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name, datatype, size, and source column.
// C# public OracleParameter(string parameterName, OracleDbType type, int size, string srcColumn);
parameterName
Specifies the parameter name.
type
Specifies the datatype of the OracleParameter
.
size
Specifies the size of the OracleParameter
value.
srcColumn
Specifies the name of the source column.
Unless explicitly set in the constructor, all the properties have the default values.
Default Values:
DbType
- String
ParameterDirection
- Input
isNullable
- true
offset
- 0
OracleDbType
- Varchar2
ParameterAlias
- Empty string
ParameterName
- Empty string
Precision
- 0
Size
- 0
SourceColumn
- Empty string
SourceVersion
- Current
ArrayBindStatus
- Success
Value
- null
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name, datatype, size, direction, null indicator, precision, scale, source column, source version and parameter value.
// C# public OracleParameter(string parameterName, OracleDbType oraType, int size, ParameterDirection direction, bool isNullable, byte precision, byte scale, string srcColumn, DataRowVersion srcVersion, object obj);
parameterName
Specifies the parameter name.
oraType
Specifies the datatype of the OracleParameter
.
size
Specifies the size of the OracleParameter
value.
direction
Specifies ParameterDirection
value.
isNullable
Specifies if the parameter value can be null
.
precision
Specifies the precision of the parameter value.
scale
Specifies the scale of the parameter value.
srcColumn
Specifies the name of the source column.
srcVersion
Specifies one of the DataRowVersion
values.
obj
Specifies the parameter value.
ArgumentException
- The supplied value does not belong to the type of Value
property in any of the OracleType
s.
Unless explicitly set in the constructor, all the properties have the default values.
Default Values:
DbType
- String
ParameterDirection
- Input
isNullable
- true
offset
- 0
OracleDbType
- Varchar2
ParameterAlias
- Empty string
ParameterName
- Empty string
Precision
- 0
Size
- 0
SourceColumn
- Empty string
SourceVersion
- Current
ArrayBindStatus
- Success
Value
- null
This constructor instantiates a new instance of the OracleParameter
class using the supplied parameter name, datatype, size, value, and direction.
// C# public OracleParameter(string parameterName, OracleDbType type, int size, object obj, ParameterDirection direction);
parameterName
Specifies the parameter name.
type
Specifies the datatype of the OracleParameter
.
size
Specifies the size of the OracleParameter
value.
obj
Specifies the value of the OracleParameter
.
direction
Specifies one of the ParameterDirection
values.
Changing the DbType
implicitly changes the OracleDbType
.
Unless explicitly set in the constructor, all the properties have the default values.
Default Values:
DbType
- String
ParameterDirection
- Input
isNullable
- true
offset
- 0
OracleDbType
- Varchar2
ParameterAlias
- Empty string
ParameterName
- Empty string
Precision
- 0
Size
- 0
SourceColumn
- Empty string
SourceVersion
- Current
ArrayBindStatus
- Success
Value
- null
OracleParameter
static methods are listed in Table 4-89.
Table 4-89 OracleParameter Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleParameter
properties are listed in Table 4-90.
Table 4-90 OracleParameter Properties
Name | Description |
---|---|
ArrayBindSize | Specifies the input or output size of elements in Value property of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution |
ArrayBindStatus | Specifies the input or output status of elements in Value property of a parameter before or after an Array Bind or PL/SQL Associative Array Bind execution |
DbType | Specifies the datatype of the parameter using the Data.DbType enumeration type |
Direction | Specifies whether the parameter is input-only, output-only, bi-directional, or a stored function return value parameter |
IsNullable |
This method is a no-op |
Offset | Specifies the offset to the Value property or offset to the elements in the Value property |
OracleDbType | Specifies the Oracle datatype |
ParameterName | Specifies the name of the parameter |
Precision | Specifies the maximum number of digits used to represent the Value property |
Scale | Specifies the number of decimal places to which Value property is resolved |
Size | Specifies the maximum size, in bytes or characters, of the data transmitted to or from the server. For PL/SQL Associative Array Bind, Size specifies the maximum number of elements in PL/SQL Associative Array |
SourceColumn | Specifies the name of the DataTable Column of the DataSet |
SourceVersion | Specifies the DataRowVersion value to use when loading the Value property of the parameter |
Status | Indicates the status of the execution related to the data in the Value property |
Value | Specifies the value of the Parameter |
This property specifies the input or output size of elements of Value
property before or after an Array Bind or PL/SQL Associative Array execution.
// C# public int[] ArrayBindSize {get; set; }
An array of int
values specifying the size.
Default = null
.
This property is only used for variable size element types for an Array Bind or PL/SQL Associative Array. For fixed size element types, this property is ignored.
Each element in the ArrayBindSize
corresponds to the bind size of an element in the Value
property. Before execution, ArrayBindSize
specifies the maximum size of each element to be bound in the Value
property. After execution, it contains the size of each element returned in the Value
property.
For binding a PL/SQL Associative Array, whose elements are of a variable-length element type, as an InputOutput
, Out
, or ReturnValue
parameter, this property must be set, and the number of elements in ArrayBindSize
must be equal to or greater than the number of elements in Value
property.
// C# ... OracleParameter Param = new OracleParameter("name", OracleDbType.Varchar2); Param.ArrayBindSize = new Int32[3]; // These sizes indicate the maximum size of the elements in parameter Value // property. Param.ArrayBindSize[0] = 100; Param.ArrayBindSize[1] = 300; Param.ArrayBindSize[2] = 200; ...
This property specifies the input or output status of each element in the Value
property before or after an Array Bind or PL/SQL Associative Array execution.
// C# public OracleParameterStatus[] ArrayBindStatus { get; set; }
An array of OracleParameterStatus
enumerated values.
ArgumentOutofRangeException
- The Status
value specified is invalid.
Default = null
.
ArrayBindStatus
is used for Array Bind and PL/SQL Associative Array execution only.
Before execution, ArrayBindStatus
indicates the bind status of each element in the Value
property. After execution, it contains the execution status of each element in the Value
property.
This property specifies whether the OracleParameter
represents a collection, and if so, specifies the collection type.
// C# public OracleCollectionType CollectionType { get; set; }
An OracleCollectionType
enumerated value.
ArgumentException
- The OracleCollectionType
value specified is invalid.
Default = OracleCollectionType.None
. If OracleParameter
is used to bind a PL/SQL Associative Array, then CollectionType
must be set to OracleCollectionType.PLSQLAssociativeArray
.
This property specifies the datatype of the parameter using the Data.DbType
enumeration type.
// C# public DbType DbType {get; set; }
A DbType
enumerated value.
IDataParameter
ArgumentException
- The DbType
value specified is invalid.
Default = DbType.String
DbType
is the datatype of each element in the array if the OracleParameter
object is used for Array Bind or PL/SQL Associative Array Bind execution.
Due to the link between DbType
and OracleDbType
properties, if the DbType
property is set, the OracleDbType
property is inferred from DbType
.
This property specifies whether the parameter is input-only, output-only, bi-directional, or a stored function return value parameter.
// C# public ParameterDirection Direction { get; set; }
A ParameterDirection
enumerated value.
IDataParameter
ArgumentOutOfRangeException
- The ParameterDirection
value specified is invalid.
Default = ParameterDirection.Input
Possible values: Input
, InputOutput
, Output
, and ReturnValue
.
This property specifies the offset to the Value
property.
// C# public int Offset { get; set; }
An int
that specifies the offset.
ArgumentOutOfRangeException
- The Offset
value specified is invalid.
Default = 0
For Array Bind and PL/SQL Associative Array Bind, Offset
applies to every element in the Value
property.
The Offset
property is used for binary and string data types. The Offset
property represents the number of bytes for binary types and the number of characters for strings. The count for strings does not include the terminating character if a null
is referenced. The Offset
property is used by parameters of the following types:
OracleDbType.BFile
OracleDbType.Blob
OracleDbType.LongRaw
OracleDbType.Raw
OracleDbType.Char
OracleDbType.Clob
OracleDbType.NClob
OracleDbType.NChar
OracleDbType.NVarchar2
OracleDbType.Varchar2
This property specifies the Oracle datatype.
// C# public OracleDbType OracleDbType { get; set; }
An OracleDbType
enumerated value.
Default = OracleDbType.Varchar2
If the OracleParameter
object is used for Array Bind or PL/SQL Associative Array Bind execution, OracleDbType
is the datatype of each element in the array.
The OracleDbType
property and DbType
property are linked. Therefore, setting the OracleDbType
property changes the DbType
property to a supporting DbType
.
This property specifies the name of the parameter.
// C# public string ParameterName { get; set; }
String
IDataParameter
Default = null
Oracle supports ParameterName
up to 30 characters.
This property specifies the maximum number of digits used to represent the Value
property.
// C# Public byte Precision { get; set; }
byte
Default = 0
The Precision
property is used by parameters of type OracleDbType.Decimal
.
Oracle supports Precision
range from 0
to 38
.
For Array Bind and PL/SQL Associative Array Bind, Precision
applies to each element in the Value
property.
This property specifies the number of decimal places to which Value
property is resolved.
// C# public byte Scale { get; set; }
byte
Default = 0
.
Scale
is used by parameters of type OracleDbType.Decimal
.
Oracle supports Scale
between -84
and 127
.
For Array Bind and PL/SQL Associative Array Bind, Scale
applies to each element in the Value
property.
This property specifies the maximum size, in bytes or characters, of the data transmitted to or from the server.
For PL/SQL Associative Array Bind, Size
specifies the maximum number of elements in PL/SQL Associative Array.
// C# public int Size { get; set;}
int
ArgumentOutOfRangeException
- The Size
value specified is invalid.
InvalidOperationException
- The Size
= 0 when the OracleParameter
object is used to bind a PL/SQL Associative Array.
The default value is 0
.
Before execution, this property specifies the maximum size to be bound in the Value
property. After execution, it contains the size of the type in the Value
property.
Size
is used for parameters of the following types:
OracleDbType.Blob
OracleDbType.Char
OracleDbType.Clob
OracleDbType.LongRaw
OracleDbType.NChar
OracleDbType.NClob
OracleDbType.NVarchar2
OracleDbType.Raw
OracleDbType.Varchar2
The value of Size
is handled as follows:
Fixed length datatypes: ignored
Variable length datatypes: describes the maximum amount of data transmitted to or from the server. For character data, Size
is in number of characters and for binary data, it is in number of bytes.
If the Size
is not explicitly set, it is inferred from the actual size of the specified parameter value when binding.
Note: Size does not include the null terminating character for the string data. |
If the OracleParameter
object is used to bind a PL/SQL Associative Array, Size
specifies the maximum number of elements in the PL/SQL Associative Array. Before the execution, this property specifies the maximum number of elements in the PL/SQL Associative Array. After the execution, it specifies the current number of elements returned in the PL/SQL Associative Array. For Output
and InputOutput
parameters and return values, Size
specifies the maximum number of elements in the PL/SQL Associative Array.
ODP.NET does not support binding an empty PL/SQL Associative Array. Therefore, Size
cannot be set to 0
when the OracleParameter
object is used to bind a PL/SQL Associative Array.
This property specifies the name of the DataTable
Column of the DataSet
.
// C# public string SourceColumn { get; set; }
A string
.
IDataParameter
Default = empty string
This property specifies the DataRowVersion
value to use when loading the Value
property of the parameter.
// C# public DataRowVersion SourceVersion { get; set; }
DataRowVersion
IDataParameter
ArgumentOutOfRangeException
- The DataRowVersion
value specified is invalid.
Default = DataRowVersion.Current
SourceVersion
is used by the OracleDataAdapter.UpdateCommand()
during the OracleDataAdapter.Update
to determine whether the original or current value is used for a parameter value. This allows primary keys to be updated. This property is ignored by the OracleDataAdapter.InsertCommand()
and the OracleDataAdapter.DeleteCommand()
.
This property indicates the status of the execution related to the data in the Value
property.
// C# public OracleParameterStatus Status { get; set; }
An OracleParameterStatus
enumerated value.
ArgumentOutOfRangeException
- The Status
value specified is invalid.
Default = OracleParameterStatus.Success
Before execution, this property indicates the bind status related to the Value
property. After execution, it returns the status of the execution.
Status
indicates whether:
A NULL
is fetched from a column.
Truncation has occurred during the fetch; then Value
was not big enough to hold the data.
A NULL
is to be inserted into a database column; then Value
is ignored, and a NULL
is inserted into a database column.
This property is ignored for Array Bind and PL/SQL Associative Array Bind. Instead, ArrayBindStatus
property is used.
This property specifies the value of the Parameter
.
// C# public object Value { get; set; }
An object
.
IDataParameter
ArgumentException
- The Value
property specified is invalid.
InvalidArgumentException
- The Value
property specified is invalid.
Default = null
If the OracleParameter
object is used for Array Bind or PL/SQL Associative Array, Value
is an array of parameter values.
The Value
property can be overwritten by OracleDataAdapter.Update()
.
The provider attempts to convert any type of value if it supports the IConvertible
interface. Conversion errors occur if the specified type is not compatible with the value.
When sending a null
parameter value to the database, the user must specify DBNull
, not null
. The null
value in the system is an empty object that has no value. DBNull
is used to represent null
values. The user can also specify a null
value by setting Status
to OracleParameterStatus.NullValue
. In this case, the provider sends a null
value to the database.
If neither OracleDbType
nor DbType
are set, their values can be inferred by Value
.
See Also: |
For input parameters the value is:
Bound to the OracleCommand
that is sent to the server.
Converted to the datatype specified in OracleDbType
or DbType
when the provider sends the data to the server.
For output parameters the value is:
Set on completion of the OracleCommand
(true for return value parameters also).
Set to the data from the server, to the datatype specified in OracleDbType
or DbType
.
When array binding is used with:
Input parameter - Value
should be set to an array of values. OracleCommand.ArrayBindCount
should be set to a value that is greater than zero to indicate the number of elements to be bound.
The number of elements in the array should be equal to the OracleCommand.ArrayBindCount
property; otherwise, their minimum value is used to bind the elements in the array.
Output parameter - OracleCommand.ArrayBindCount
should be set to a value that is greater than zero to indicate the number of elements to be retrieved (for SELECT
statements).
When PL/SQL Associative Array binding is used with:
Input parameter – Value should be set to an array of values. CollectionType
should be set to OracleCollection.PLSQLAssociativeArray.Size
should be set to specify the possible maximum number of array elements in the PL/SQL Associative Array. If Size
is smaller than the number of elements in Value
, then Size
specifies the number of elements in the Value property to be bound.
Output parameter - CollectionType
should be set to OracleCollection.PLSQLAssociativeArray
. Size
should be set to specify the maximum number of array elements in PL/SQL Associative Array.
Each parameter should have a value. To bind a parameter with a null
value, set Value
to DBNull.Value
, or set Status
to OracleParameterStatus.
NullInsert
.
OracleParameter
public methods are listed in Table 4-91.
Table 4-91 OracleParameter Public Methods
Public Method | Description |
---|---|
Clone | Creates a shallow copy of an OracleParameter object |
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose | Releases allocated resources |
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
ToString |
Inherited from Object (Overloaded) |
This method creates a shallow copy of an OracleParameter
object.
// C# public object Clone();
An OracleParameter
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 OracleParameter param_cloned = (OracleParameter) param.Clone(); ...
This method releases resources allocated for an OracleParameter
object.
// C# public void Dispose();
IDisposable
An OracleParameterCollection
class represents a collection of all parameters relevant to an OracleCommand
object and their mappings to DataSet
columns.
Object
MarshalByRefObject
OracleParameterCollection
// C# public sealed class OracleParameterCollection : MarshalByRefObject, IDataParameterCollection, IList, ICollection, IEnumerable
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
The position of an OracleParameter
added into the OracleParameterCollection
is the binding position in the SQL statement. Position is 0
-based and is used only for positional binding. If named binding is used, the position of an OracleParameter
in the OracleParameterCollection
is ignored.
// C# string conStr = "User Id=scott;Password=tiger;Data Source=oracle"; // Create the OracleConnection OracleConnection con = new OracleConnection(conStr); con.Open(); // Create the OracleCommand OracleCommand cmd = new OracleCommand(); cmd.Connection = con; // Create OracleParameter OracleParameter [] prm = new OracleParameter[3]; // Bind parameters prm[0] = cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal, 1234, ParameterDirection.Input); prm[1] = cmd.Parameters.Add("paramEname", OracleDbType.Varchar2, "Client", ParameterDirection.Input); prm[2] = cmd.Parameters.Add("paramDeptNo", OracleDbType.Decimal, 10, ParameterDirection.Input); cmd.CommandText = "insert into emp(empno, ename, deptno) values(:1, :2, :3)"; cmd.ExecuteNonQuery(); // Remove OracleParameter objects from the collection cmd.Parameters.Clear(); // Dispose OracleCommand object cmd.Dispose(); // Close and Dispose OracleConnection object con.Close(); con.Dispose();
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleParameterCollection
members are listed in the following tables:
OracleParameterCollection
static methods are listed in Table 4-92.
Table 4-92 OracleParameterCollection Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleParameterCollection
properties are listed in Table 4-93.
Table 4-93 OracleParameterCollection Properties
Name | Description |
---|---|
Count | Specifies the number of OracleParameter s in the collection |
Item | Gets and sets the OracleParameter object (Overloaded) |
OracleParameterCollection
public methods are listed in Table 4-94.
Table 4-94 OracleParameterCollection Public Methods
Public Method | Description |
---|---|
Add | Adds objects to the collection (Overloaded) |
Clear | Removes all the OracleParameter objects from the collection |
Contains | Indicates whether objects exist in the collection (Overloaded) |
CopyTo | Copies OracleParameter objects from the collection, starting with the supplied index to the supplied array |
CreateObjRef |
Inherited from MarshalByRefObject |
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
IndexOf | Returns the index of the objects in the collection (Overloaded) |
Insert | Inserts the supplied OracleParameter to the collection at the specified index |
Remove | Removes objects from the collection |
RemoveAt | Removes objects from the collection by location (Overloaded) |
ToString |
Inherited from Object |
OracleParameterCollection
static methods are listed in Table 4-95.
Table 4-95 OracleParameterCollection Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleParameterCollection
properties are listed in Table 4-96.
Table 4-96 OracleParameterCollection Properties
Name | Description |
---|---|
Count | Specifies the number of OracleParameter s in the collection |
Item | Gets and sets the OracleParameter object (Overloaded) |
This property specifies the number of OracleParameter
objects in the collection.
// C# public int Count {get;}
The number of OracleParameter
objects.
ICollection
Default = 0
Item
Item
gets and sets the OracleParameter
object.
This property gets and sets the OracleParameter
object at the index specified by the supplied parameterIndex
.
This property gets and sets the OracleParameter
object using the parameter name specified by the supplied parameterName
.
This property gets and sets the OracleParameter
object at the index specified by the supplied parameterIndex
.
// C# public object Item[int parameterIndex] {get; set;}
An object.
IList
IndexOutOfRangeException
- The supplied index does not exist.
The OracleParameterCollection
class is a zero-based index.
This property gets and sets the OracleParameter
object using the parameter name specified by the supplied parameterName
.
// C# public OracleParameter Item[string parameterName] {get; set;};
An OracleParameter
.
IDataParameterCollection
IndexOutOfRangeException
- The supplied parameter name does not exist.
OracleParameterCollection
public methods are listed in Table 4-97.
Table 4-97 OracleParameterCollection Public Methods
Public Method | Description |
---|---|
Add | Adds objects to the collection (Overloaded) |
Clear | Removes all the OracleParameter objects from the collection |
Contains | Indicates whether objects exist in the collection (Overloaded) |
CopyTo | Copies OracleParameter objects from the collection, starting with the supplied index to the supplied array |
CreateObjRef |
Inherited from MarshalByRefObject |
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
IndexOf | Returns the index of the objects in the collection (Overloaded) |
Insert | Inserts the supplied OracleParameter to the collection at the specified index |
Remove | Removes objects from the collection |
RemoveAt | Removes objects from the collection by location (Overloaded) |
ToString |
Inherited from Object |
Add
Add
adds objects to the collection.
This method adds the supplied object to the collection.
This method adds the supplied OracleParameter
object to the collection.
This method adds an OracleParameter
object to the collection using the supplied name and object value.
This method adds an OracleParameter
object to the collection using the supplied name and database type.
Add(string, OracleDbType, ParameterDirection)
This method adds an OracleParameter
object to the collection using the supplied name, database type, and direction.
Add(string, OracleDbType, object, ParameterDirection)
This method adds an OracleParameter
object to the collection using the supplied name, database type, parameter value, and direction.
Add(string, OracleDbType, int, object, ParameterDirection)
This method adds an OracleParameter
object to the collection using the supplied name, database type, size, parameter value, and direction.
Add(string, OracleDbType, int)
This method adds an OracleParameter
object to the collection using the supplied name, database type, and size.
Add (string, OracleDbType, int, string)
This method adds an OracleParameter
object to the collection using the supplied name, database type, size, and source column.
Add(string, OracleDbType, int, ParameterDirection, bool, byte, byte, string, DataRowVersion, object)
This method adds an OracleParameter
object to the collection using the supplied name, database type, size, direction, null indicator, precision, scale, source column, source version, and parameter value.
This method adds the supplied object to the collection.
// C# public int Add(object obj);
obj
Specifies the supplied object.
The index at which the new OracleParameter
is added.
IList
InvalidCastException
- The supplied obj
cannot be cast to an OracleParameter
object.
This method adds the supplied OracleParameter
object to the collection.
// C# public OracleParameter Add(OracleParameter paramObj);
paramObj
Specifies the supplied OracleParameter
object.
The newly created OracleParameter
object which was added to the collection.
This method adds an OracleParameter
object to the collection using the supplied name and object value
// C# public OracleParameter Add(string name, object val);
name
Specifies the parameter name.
val
Specifies the OracleParameter
value.
The newly created OracleParameter
object which was added to the collection.
This method adds an OracleParameter
object to the collection using the supplied name and database type.
// C# public OracleParameter Add(string name, OracleDbType dbType);
name
Specifies the parameter name.
dbType
Specifies the datatype of the OracleParameter
.
The newly created OracleParameter
object which was added to the collection.
This method adds an OracleParameter
object to the collection using the supplied name, database type, and direction.
// C# public OracleParameter Add(string name, OracleDbType dbType, ParameterDirection direction);
name
Specifies the parameter name.
dbType
Specifies the datatype of the OracleParameter
.
direction
Specifies the OracleParameter
direction.
The newly created OracleParameter
object which was added to the collection.
This method adds an OracleParameter
object to the collection using the supplied name, database type, parameter value, and direction.
// C# public OracleParameter Add(string name, OracleDbType dbType, object val, ParameterDirection dir);
name
Specifies the parameter name.
dbType
Specifies the datatype of the OracleParameter
.
val
Specifies the OracleParameter
value.
dir
Specifies one of the ParameterDirection
values.
The newly created OracleParameter
object which was added to the collection.
// C# ... OracleParameter prm = new OracleParameter(); prm = cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal, 1234, ParameterDirection.Input); cmd.CommandText = "insert into NumTable(numcol) values(:1)"; cmd.ExecuteNonQuery(); ...
This method adds an OracleParameter
object to the collection using the supplied name, database type, size, parameter value, and direction.
// C# public OracleParameter Add(string name, OracleDbType dbType, int size, object val, ParameterDirection dir;
name
Specifies the parameter name.
dbType
Specifies the datatype of the OracleParameter
.
size
Specifies the size of OracleParameter
.
val
Specifies the OracleParameter
value.
dir
Specifies one of the ParameterDirection
values.
The newly created OracleParameter
object which was added to the collection.
This method adds an OracleParameter
object to the collection using the supplied name, database type, and size.
// C# public OracleParameter Add(string name, OracleDbType dbType, int size);
name
Specifies the parameter name.
dbType
Specifies the datatype of the OracleParameter
.
size
Specifies the size of OracleParameter
.
The newly created OracleParameter
object which was added to the collection.
// C# ... OracleParameter prm = new OracleParameter(); prm = cmd.Parameters.Add("param1", OracleDbType.Decimal, 10); prm.Direction = ParameterDirection.Input; prm.Value = 1111; cmd.CommandText = "insert into NumTable(numcol) values(:1)"; cmd.ExecuteNonQuery(); ...
This method adds an OracleParameter
object to the collection using the supplied name, database type, size, and source column.
// C# public OracleParameter Add(string name, OracleDbType dbType, int size, string srcColumn);
name
Specifies the parameter name.
dbType
Specifies the datatype of the OracleParameter
.
size
Specifies the size of OracleParameter
.
srcColumn
Specifies the name of the source column.
An OracleParameter
.
This method adds an OracleParameter
object to the collection using the supplied name, database type, size, direction, null indicator, precision, scale, source column, source version, and parameter value.
// C# public OracleParameter Add(string name, OracleDbType dbType, int size, ParameterDirection dir, bool isNullable, byte precision, byte scale, string srcColumn, DataRowVersion version, object val);
name
Specifies the parameter name.
dbType
Specifies the datatype of the OracleParameter
.
size
Specifies the size of OracleParameter
.
dir
Specifies one of the ParameterDirection
values.
isNullable
Specifies if the parameter
value can be null
.
precision
Specifies the precision of the parameter
value.
scale
Specifies the scale of the parameter
value.
srcColumn
Specifies the name of the source column.
version
Specifies one of the DataRowVersion
values.
val
Specifies the parameter
value.
The newly created OracleParameter
object which was added to the collection.
ArgumentException
- The type of supplied val
does not belong to the type of Value
property in any of the ODP.NET Types.
This method removes all the OracleParameter
objects from the collection.
// C# public void Clear();
IList
// C# ... OracleParameter [] prm = new OracleParameter[3]; prm[0] = cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal, 1234, ParameterDirection.Input); prm[1] = cmd.Parameters.Add("paramEname", OracleDbType.Varchar2, "Client", ParameterDirection.Input); prm[2] = cmd.Parameters.Add("paramDeptNo", OracleDbType.Decimal, 10, ParameterDirection.Input); cmd.CommandText = "insert into emp(empno, ename, deptno) values(:1,:2, :3)"; cmd.ExecuteNonQuery(); // This method removes all the parameters from the parameter collection. cmd.Parameters.Clear(); ...
Contains
Contains
indicates whether the supplied object exists in the collection.
This method indicates whether the supplied object exists in the collection.
This method indicates whether an OracleParameter
object exists in the collection using the supplied string.
This method indicates whether the supplied object exists in the collection.
// C# public bool Contains(object obj)
obj
Specifies the object.
A bool
that indicates whether or not the OracleParameter
specified is inside the collection.
IList
InvalidCastException
- The supplied obj
is not an OracleParameter
object.
Returns true
if the collection contains the OracleParameter
object; otherwise, returns false
.
... prm = cmd.Parameters.Add("param1", OracleDbType.Decimal, 1234, ParameterDirection.Input); if (cmd.Parameters.Contains((Object)prm)) // This method removes a particular parameter from the parameter collection. cmd.Parameters.Remove((Object) prm); ...
This method indicates whether an OracleParameter
object exists in the collection using the supplied string.
// C# public bool Contains(string name);
name
Specifies the name of OracleParameter
object.
Returns true
if the collection contains the OracleParameter
object with the specified parameter name; otherwise, returns false
.
IDataParameterCollection
... prm = cmd.Parameters.Add("param1", OracleDbType.Decimal, 1234, + ParameterDirection.Input); if (cmd.Parameters.Contains((Object)prm)) // This method removes a particular parameter from the parameter collection. cmd.Parameters.Remove((Object) prm); ...
This method copies OracleParameter
objects from the collection, starting with the supplied index
to the supplied array
.
// C# public void CopyTo(Array array, int index);
array
Specifies the array.
index
Specific the index to array.
ICollection
IndexOf
IndexOf
returns the index of the OracleParameter
object in the collection.
This method returns the index of the OracleParameter
object in the collection.
This method returns the index
of the OracleParameter
object with the specified name in the collection.
This method returns the index of the OracleParameter
object in the collection.
// C# public int IndexOf(object obj);
obj
Specifies the object.
Returns the index of the OracleParameter
object in the collection.
IList
InvalidCastException
- The supplied obj
cannot be cast to an OracleParameter
object.
Returns the index
of the supplied OracleParameter
obj
in the collection.
This method returns the index
of the OracleParameter
object with the specified name in the collection.
// C# public int IndexOf(String name);
name
Specifies the name of parameter.
Returns the index
of the supplied OracleParameter
in the collection.
IDataParameterCollection
This method inserts the supplied OracleParameter
object to the collection at the specified index
.
// C# public void Insert(int index, object obj);
index
Specifies the index.
obj
Specifies the OracleParameter
object.
IList
An InvalidCastException
is thrown if the supplied obj
cannot be cast to an OracleParameter
object.
This method removes the supplied OracleParameter
from the collection.
// C# public void Remove(object obj);
obj
Specifies the object to remove.
IList
InvalidCastException
- The supplied obj
cannot be cast to an OracleParameter
object.
... prm = cmd.Parameters.Add("param1", OracleDbType.Decimal, 1234, ParameterDirection.Input); if (cmd.Parameters.Contains((Object)prm)) // This method removes a particular parameter from the parameter collection. cmd.Parameters.Remove((Object) prm); ...
RemoveAt
RemoveAt
removes the OracleParameter
object from the collection by location.
This method removes from the collection the OracleParameter
object located at the index specified by the supplied index.
This method removes from the collection the OracleParameter
object specified by the supplied name.
This method removes from the collection the OracleParameter
object located at the index specified by the supplied index.
// C# public void RemoveAt(int index);
index
Specifies the index from which the OracleParameter
is to be removed.
IList
This method removes from the collection the OracleParameter
object specified by the supplied name.
// C# public void RemoveAt(String name);
name
The name of the OracleParameter
object to be removed from the collection.
IDataParameterCollection
The OracleRowUpdatedEventHandler
delegate represents the signature of the method that handles the OracleDataAdapter.RowUpdated
event.
// C# public delegate void OracleRowUpdatedEventHandler(object sender, OracleRowUpdatedEventArgs eventArgs);
sender
The source of the event.
eventArgs
The OracleRowUpdatedEventArgs
object that contains the event data.
Event callbacks can be registered through this event delegate for applications that wish to be notified after a row is updated.
In the .NET framework, the convention of an event delegate requires two parameters: the object that raises the event and the event data.
The OracleRowUpdatedEventArgs
class provides event data for the OracleDataAdapter.RowUpdated
event.
Object
EventArgs
RowUpdatedEventArgs
OracleRowUpdatedEventArgs
// C# public sealed class OracleRowUpdatedEventArgs : RowUpdatedEventArgs
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
The example for the RowUpdated
event shows how to use OracleRowUpdatedEventArgs
. See "Example".
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleRowUpdatedEventArgs
members are listed in the following tables:
OracleRowUpdatedEventArgs
constructors are listed in Table 4-98.
Table 4-98 OracleRowUpdatedEventArgs Constructors
Constructor | Description |
---|---|
OracleRowUpdatedEventArgs Constructor | Instantiates a new instance of OracleRowUpdatedEventArgs class |
The OracleRowUpdatedEventArgs
static methods are listed in Table 4-99.
Table 4-99 OracleRowUpdatedEventArgs Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
The OracleRowUpdatedEventArgs
properties are listed in Table 4-100.
Table 4-100 OracleRowUpdatedEventArgs Properties
Name | Description |
---|---|
Command | Specifies the OracleCommand that is used when OracleDataAdapter.Update() is called |
Errors |
Inherited from RowUpdatedEventArgs |
RecordsAffected |
Inherited from RowUpdatedEventArgs |
Row |
Inherited from RowUpdatedEventArgs |
StatementType |
Inherited from RowUpdatedEventArgs |
Status |
Inherited from RowUpdatedEventArgs |
TableMapping |
Inherited from RowUpdatedEventArgs |
The OracleRowUpdatedEventArgs
properties are listed in Table 4-101.
Table 4-101 OracleRowUpdatedEventArgs Public Methods
Name | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString |
Inherited from Object |
The OracleRowUpdatedEventArgs
constructor creates a new OracleRowUpdatedEventArgs
instance.
// C# public OracleRowUpdatedEventArgs(DataRow row,IDbCommand command, StatementType statementType, DataTableMapping tableMapping);
row
The DataRow
sent for Update
.
command
The IDbCommand
executed during the Update
.
statementType
The StatementType
Enumeration value indicating the type of SQL statement executed.
tableMapping
The DataTableMapping
used for the Update
.
The OracleRowUpdatedEventArgs
static methods are listed in Table 4-102.
Table 4-102 OracleRowUpdatedEventArgs Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
The OracleRowUpdatedEventArgs
properties are listed in Table 4-103.
Table 4-103 OracleRowUpdatedEventArgs Properties
Name | Description |
---|---|
Command | Specifies the OracleCommand that is used when OracleDataAdapter.Update() is called |
Errors |
Inherited from RowUpdatedEventArgs |
RecordsAffected |
Inherited from RowUpdatedEventArgs |
Row |
Inherited from RowUpdatedEventArgs |
StatementType |
Inherited from RowUpdatedEventArgs |
Status |
Inherited from RowUpdatedEventArgs |
TableMapping |
Inherited from RowUpdatedEventArgs |
This property specifies the OracleCommand
that is used when OracleDataAdapter.Update()
is called.
// C# public new OracleCommand Command {get;}
The OracleCommand
executed when Update
is called.
The OracleRowUpdatedEventArgs
properties are listed in Table 4-104.
Table 4-104 OracleRowUpdatedEventArgs Public Methods
Name | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString |
Inherited from Object |
The OracleRowUpdatingEventArgs
class provides event data for the OracleDataAdapter.RowUpdating
event.
Object
EventArgs
RowUpdatingEventArgs
OracleRowUpdatingEventArgs
// C# public sealed class OracleRowUpdatingEventArgs : RowUpdatingEventArgs
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
The example for the RowUpdated
event shows how to use OracleRowUpdatingEventArgs
. See "Example".
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleRowUpdatingEventArgs
members are listed in the following tables:
OracleRowUpdatingEventArgs
constructors are listed in Table 4-105.
Table 4-105 OracleRowUpdatingEventArgs Constructors
Constructor | Description |
---|---|
OracleRowUpdatingEventArgs Constructor | Instantiates a new instance of OracleRowUpdatingEventArgs class (Overloaded) |
The OracleRowUpdatingEventArgs
static methods are listed in Table 4-106.
Table 4-106 OracleRowUpdatingEventArgs Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
The OracleRowUpdatingEventArgs
properties are listed in Table 4-107.
Table 4-107 OracleRowUpdatingEventArgs Properties
Name | Description |
---|---|
Command |
Specifies the OracleCommand that is used when the OracleDataAdapter.Update() is called |
Errors |
Inherited from RowUpdatingEventArgs |
Row |
Inherited from RowUpdatingEventArgs |
StatementType |
Inherited from RowUpdatingEventArgs |
Status |
Inherited from RowUpdatingEventArgs |
TableMapping |
Inherited from RowUpdatingEventArgs |
The OracleRowUpdatingEventArgs
public methods are listed in Table 4-108.
Table 4-108 OracleRowUpdatingEventArgs Public Methods
Name | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString |
Inherited from Object |
The OracleRowUpdatingEventArgs
constructor creates a new instance of the OracleRowUpdatingEventArgs
class using the supplied data row, IDbCommand
, type of SQL statement, and table mapping.
// C# public OracleRowUpdatingEventArgs(DataRow row, IDbCommand command, StatementType statementType, DataTableMapping tableMapping);
row
The DataRow
sent for Update
.
command
The IDbCommand
executed during the Update
.
statementType
The StatementType
enumeration value indicating the type of SQL statement executed.
tableMapping
The DataTableMapping
used for the Update
.
The OracleRowUpdatingEventArgs
static methods are listed in Table 4-109.
Table 4-109 OracleRowUpdatingEventArgs Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
The OracleRowUpdatingEventArgs
properties are listed in Table 4-110.
Table 4-110 OracleRowUpdatingEventArgs Properties
Name | Description |
---|---|
Command |
Specifies the OracleCommand that is used when the OracleDataAdapter.Update() is called |
Errors |
Inherited from RowUpdatingEventArgs |
Row |
Inherited from RowUpdatingEventArgs |
StatementType |
Inherited from RowUpdatingEventArgs |
Status |
Inherited from RowUpdatingEventArgs |
TableMapping |
Inherited from RowUpdatingEventArgs |
This property specifies the OracleCommand
that is used when the OracleDataAdapter.Update()
is called.
// C# public new OracleCommand Command {get; set;}
The OracleCommand
executed when Update
is called.
The OracleRowUpdatingEventArgs
public methods are listed in Table 4-111.
Table 4-111 OracleRowUpdatingEventArgs Public Methods
Name | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString |
Inherited from Object |
The OracleRowUpdatingEventHandler
delegate represents the signature of the method that handles the OracleDataAdapter.RowUpdating
event.
// C# public delegate void OracleRowUpdatingEventHandler (object sender, OracleRowUpdatingEventArgs eventArgs);
sender
The source of the event.
eventArgs
The OracleRowUpdatingEventArgs
object that contains the event data.
Event callbacks can be registered through this event delegate for applications that wish to be notified after a row is updated.
In the .NET framework, the convention of an event delegate requires two parameters: the object that raises the event and the event data.
An OracleTransaction
object represents a local transaction.
Object
MarshalByRefObject
OracleTransaction
// C# public sealed class OracleTransaction : MarshalByRefObject, IDbTransaction, IDisposable
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
The application calls BeginTransaction
on the OracleConnection
object to create an OracleTransaction
object. The OracleTransaction
object can be created in one of the following two modes:
Read Committed (default)
Serializable
Any other mode results in an exception.
The execution of a DDL statement in the context of a transaction is not recommended since it results in an implicit commit that is not reflected in the state of the OracleTransaction
object.
All operations related to savepoints pertain to the current local transaction. Operations like commit and rollback performed on the transaction have no effect on data in any existing DataSet
.
// C# // Starts a transaction and inserts one record. If insert fails, rolls back // the transaction. Otherwise, commits the transaction. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); //Create an OracleCommand object using the connection object OracleCommand cmd = new OracleCommand("", con); // Start a transaction OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted); try { cmd.CommandText = "insert into mytable values (99, 'foo')"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); txn.Commit(); Console.WriteLine("One record is inserted into the database table."); } catch(Exception e) { txn.Rollback(); Console.WriteLine("No record was inserted into the database table."); } ...
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleTransaction
members are listed in the following tables:
OracleTransaction
static methods are listed in Table 4-112.
Table 4-112 OracleTransaction Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleTransaction
properties are listed in Table 4-113.
Table 4-113 OracleTransaction Properties
Name | Description |
---|---|
IsolationLevel |
Specifies the isolation level for the transaction |
Connection |
Specifies the connection that is associated with the transaction |
OracleTransaction
public methods are listed in Table 4-114.
Table 4-114 OracleTransaction Public Methods
Public Method | Description |
---|---|
Commit |
Commits the database transaction |
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose |
Frees the resources used by the OracleTransaction object |
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
Rollback |
Rolls back a database transaction (Overloaded) |
Save | Creates a savepoint within the current transaction |
ToString |
Inherited from Object |
OracleTransaction
static methods are listed in Table 4-115.
Table 4-115 OracleTransaction Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleTransaction
properties are listed in Table 4-116.
Table 4-116 OracleTransaction Properties
Name | Description |
---|---|
IsolationLevel |
Specifies the isolation level for the transaction |
Connection |
Specifies the connection that is associated with the transaction |
This property specifies the isolation level for the transaction.
// C# public IsolationLevel IsolationLevel {get;}
IsolationLevel
IDbTransaction
InvalidOperationException
- The transaction has already completed.
Default = IsolationLevel.ReadCommitted
This property specifies the connection that is associated with the transaction.
// C# public OracleConnection Connection {get;}
Connection
IDbTransaction
This property indicates the OracleConnection
object that is associated with the transaction.
OracleTransaction
public methods are listed in Table 4-117.
Table 4-117 OracleTransaction Public Methods
Public Method | Description |
---|---|
Commit |
Commits the database transaction |
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose |
Frees the resources used by the OracleTransaction object |
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
Rollback |
Rolls back a database transaction (Overloaded) |
Save | Creates a savepoint within the current transaction |
ToString |
Inherited from Object |
This method commits the database transaction.
// C# public void Commit();
IDbTransaction
InvalidOperationException
- The transaction has already been completed successfully, has been rolled back, or the associated connection is closed.
Upon a successful commit, the transaction enters a completed state.
// C# // Starts a transaction and inserts one record. If insert fails, rolls back // the transaction. Otherwise, commits the transaction. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); //Create an OracleCommand object using the connection object OracleCommand cmd = new OracleCommand("", con); // Start a transaction OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted); try { cmd.CommandText = "insert into mytable values (99, 'foo')"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); txn.Commit(); Console.WriteLine("One record was inserted into the database table."); } catch(Exception e) { txn.Rollback(); Console.WriteLine("No record was inserted into the database table."); } ...
This method frees the resources used by the OracleTransaction
object.
// C# public void Dispose();
IDisposable
This method releases both the managed and unmanaged resources held by the OracleTransaction
object. If the transaction is not in a completed state, an attempt to rollback the transaction is made.
Rollback
Rollback
rolls back a database transaction.
This method rolls back a database transaction.
This method rolls back a database transaction to a savepoint within the current transaction.
This method rolls back a database transaction.
// C# public void Rollback();
IDbTransaction
InvalidOperationException
- The transaction has already been completed successfully, has been rolled back, or the associated connection is closed.
After a Rollback()
, the OracleTransaction
object can no longer be used because the Rollback
ends the transaction.
// C# // Starts a transaction and inserts one record. Then rolls back the // transaction. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); OracleCommand cmd = Con.CreateCommand(); // Start a transaction OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted); cmd.CommandText = "insert into mytable values (99, 'foo')"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); txn.Rollback(); Console.WriteLine("Nothing was inserted into the database table."); ...
This method rolls back a database transaction to a savepoint within the current transaction.
// C# public void Rollback(string savepointName);
savepointName
The name of the savepoint to rollback to, in the current transaction.
InvalidOperationException
- The transaction has already been completed successfully, has been rolled back, or the associated connection is closed.
After a rollback to a savepoint, the current transaction remains active and can be used for further operations.
The savepointName
specified does not have to match the case of the savepointName
created using the Save
method, since savepoints are created in the database in a case-insensitive manner.
This method creates a savepoint within the current transaction.
// C# public void Save(string savepointName);
savepointName
The name of the savepoint being created in the current transaction.
InvalidOperationException
- The transaction has already been completed.
After creating a savepoint, the transaction does not enter a completed state and can be used for further operations.
The savepointName
specified is created in the database in a case-insensitive manner. Calling the Rollback
method rolls back to savepointName
. This allows portions of a transaction to be rolled back, instead of the entire transaction.
// C# // Starts a transaction and inserts two records. Creates a savepoint // within the current transaction for the first insert. Then rolls back to // the savepoint to commit the first record. ... string ConStr = "User Id=myschema;Password=mypassword;" + "Data Source=oracle;"; OracleConnection con = new OracleConnection(ConStr); con.Open(); OracleCommand cmd = Con.CreateCommand(); // Start a transaction OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted); cmd.CommandText = "insert into mytable values (99, 'foo')"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); //Create a savepoint txn.Save("MySavePoint"); cmd.CommandText = "insert into mytable values (100, 'bar')"; cmd.ExecuteNonQuery(); //Rollback to the savepoint txn.Rollback("MySavePoint"); //Commit the first insert txn.Commit();
An OracleXmlQueryProperties
object represents the XML properties used by the OracleCommand
class when the XmlCommandType
property is Query
.
Object
OracleXmlQueryProperties
public sealed class OracleXmlQueryProperties : ICloneable
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
OracleXmlQueryProperties
can be accessed, and modified using the XmlQueryProperties
property of the OracleCommand
class. Each OracleCommand
object has its own instance of the OracleXmlQueryProperties
class in the XmlQueryProperties
property.
Use the default constructor to get a new instance of the OracleXmlQueryProperties
. Use the OracleXmlQueryProperties
.Clone()
method to get a copy of an OracleXmlQueryProperties
instance.
This example retrieves relational data as XML.
// C# StreamReader sr = null; // Create the connection. string constr = "User Id=hr;Password=hr;Data Source=orcl"; OracleConnection conn = new OracleConnection(constr); conn.Open(); // Create the command. OracleCommand cmd = new OracleCommand("", conn); // Set the XML command type to query. cmd.XmlCommandType = OracleXmlCommandType.Query; // Set the SQL query. cmd.CommandText = "select * from employees e where e.employee_id = :empno"; // Set command properties that affect XML query behaviour. cmd.BindByName = true; cmd.AddRowid = true; // Bind values to the parameters in the SQL query. Int32 empNum = 205; cmd.Parameters.Add(":empno", OracleDbType.Int32, empNum, ParameterDirection.Input); // Set the XML query properties. cmd.XmlQueryProperties.MaxRows = -1; cmd.XmlQueryProperties.RootTag = "MYROWSET"; cmd.XmlQueryProperties.RowTag = "MYROW"; cmd.XmlQueryProperties.Xslt = null; cmd.XmlQueryProperties.XsltParams = null; // Test query execution without returning a result. int rows = cmd.ExecuteNonQuery(); Console.WriteLine("rows: " + rows); // Get the XML document as an XmlReader. XmlReader xmlReader = cmd.ExecuteXmlReader(); XmlDocument xmlDocument = new XmlDocument(); xmlDocument.PreserveWhitespace = true; xmlDocument.Load(xmlReader); Console.WriteLine(xmlDocument.OuterXml); // Change the SQL query, and set the maximum number of rows to 2. cmd.CommandText = "select * from employees e"; cmd.Parameters.Clear(); cmd.XmlQueryProperties.MaxRows = 2; // Get the XML document as a Stream. Stream stream = cmd.ExecuteStream(); sr = new StreamReader(stream, Encoding.Unicode); Console.WriteLine(sr.ReadToEnd()); // Get all the rows. cmd.XmlQueryProperties.MaxRows = -1; // Append the XML document to an existing Stream. MemoryStream mstream = new MemoryStream(32); cmd.ExecuteToStream(mstream); mstream.Seek(0, SeekOrigin.Begin); sr = new StreamReader(mstream, Encoding.Unicode); Console.WriteLine(sr.ReadToEnd()); // Clean up. cmd.Dispose(); conn.Close(); conn.Dispose();
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleXmlQueryProperties
members are listed in the following tables:
The OracleXmlQueryProperties
constructors are listed in Table 4-118.
Table 4-118 OracleXmlQueryProperties Constructors
Constructor | Description |
---|---|
OracleXmlQueryProperties Constructor | Instantiates a new instance of the OracleXmlQueryProperties class |
The OracleXmlQueryProperties
properties are listed in Table 4-119.
Table 4-119 OracleXmlQueryProperties Properties
Name | Description |
---|---|
MaxRows | Specifies the maximum number of rows from the result set of the query that can be represented in the result XML document |
RootTag | Specifies the root element of the result XML document |
RowTag | Specifies the value of the XML element which identifies a row of data from the result set in an XML document |
Xslt | Specifies the XSL document used for XML transformation using XSLT |
XsltParams | Specifies parameters for the XSL document |
The OracleXmlQueryProperties
public methods are listed in Table 4-120.
Table 4-120 OracleXmlQueryProperties Public Methods
Name | Description |
---|---|
Clone | Creates a copy of an OracleXmlQueryProperties object |
The OracleXmlQueryProperties
constructor instantiates a new instance of the OracleXmlQueryProperties
class.
// C# public OracleXmlQueryProperties();
The OracleXmlQueryProperties
properties are listed in Table 4-121.
Table 4-121 OracleXmlQueryProperties Properties
Name | Description |
---|---|
MaxRows | Specifies the maximum number of rows from the result set of the query that can be represented in the result XML document |
RootTag | Specifies the root element of the result XML document |
RowTag | Specifies the value of the XML element which identifies a row of data from the result set in an XML document |
Xslt | Specifies the XSL document used for XML transformation using XSLT |
XsltParams | Specifies parameters for the XSL document |
This property specifies the maximum number of rows from the result set of the query that can be represented in the result XML document.
// C# public int MaxRows {get; set;}
The maximum number of rows.
ArgumentException
- The new value for MaxRows
is not valid.
Default value is -1
.
Possible values are:
-1
(all rows).
A number greater than or equal to 0
.
This property specifies the root element of the result XML document.
// C# public string RootTag {get; set;}
The root element of the result XML document.
The default root tag is ROWSET
.
To indicate that no root tag is be used in the result XML document, set this property to null
or ""
or String.Empty
.
If both RootTag
and RowTag
are set to null
, an XML document is returned only if the result set returns one row and one column.
This property specifies the value of the XML element which identifies a row of data from the result set in an XML document.
// C# public string RowTag {get; set;}
The value of the XML element.
The default is ROW
.
To indicate that no row tag is be used in the result XML document, set this property to null
or ""
or String.Empty
.
If both RootTag
and RowTag
are set to null
, an XML document is returned only if the result set returns one row and one column.
This property specifies the XSL document used for XML transformation using XSLT.
// C# public string Xslt {get; set;}
The XSL document used for XML transformation.
Default value is null
.
The XSL document is used for XML transformation of the XML document generated from the result set of the query.
This property specifies parameters for the XSL document.
// C# public string XsltParams {get; set;}
The parameters for the XSL document.
Default value is null
.
The parameters are specified as a string of "name=value
" pairs of the form "param1=value1; param2=value2; …
" delimited by semicolons.
The OracleXmlQueryProperties
public methods are listed in Table 4-122.
Table 4-122 OracleXmlQueryProperties Public Methods
Name | Description |
---|---|
Clone | Creates a copy of an OracleXmlQueryProperties object |
This method creates a copy of an OracleXmlQueryProperties
object.
// C# public object Clone();
An OracleXmlQueryProperties
object
ICloneable
An OracleXmlSaveProperties
object represents the XML properties used by the OracleCommand
class when the XmlCommandType
property is Insert
, Update
, or Delete
.
Object
OracleXmlSaveProperties
public sealed class OracleXmlSaveProperties : ICloneable
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
OracleXmlSaveProperties
can be accessed and modified using the XmlSaveProperties
property of the OracleCommand
class. Each OracleCommand
object has its own instance of the OracleXmlSaveProperties
class in the XmlSaveProperties
property.
Use the default constructor to get a new instance of OracleXmlSaveProperties
. Use the OracleXmlSaveProperties
.Clone()
method to get a copy of an OracleXmlSaveProperties
instance.
This sample demonstrates how to do inserts, updates, and deletes to a relational table or view using an XML document.
// C# string[] KeyColumnsList = null; string[] UpdateColumnsList = null; int rows = 0; // Create the connection. string constr = "User Id=hr;Password=hr;Data Source=orcl"; OracleConnection conn = new OracleConnection(constr); conn.Open(); // Create the command. OracleCommand cmd = new OracleCommand("", conn); // Set the XML command type to insert. cmd.XmlCommandType = OracleXmlCommandType.Insert; // Set the XML document. cmd.CommandText = "<?xml version=\"1.0\"?>\n" + "<ROWSET>\n" + " <MYROW num = \"1\">\n" + " <EMPLOYEE_ID>1234</EMPLOYEE_ID>\n" + " <LAST_NAME>Smith</LAST_NAME>\n" + " <EMAIL>Smith@Oracle.com</EMAIL>\n" + " <HIRE_DATE>1/1/2003 0:0:0</HIRE_DATE>\n" + " <JOB_ID>IT_PROG</JOB_ID>\n" + " </MYROW>\n" + " <MYROW num = \"2\">\n" + " <EMPLOYEE_ID>1235</EMPLOYEE_ID>\n" + " <LAST_NAME>Barney</LAST_NAME>\n" + " <EMAIL>Barney@Oracle.com</EMAIL>\n" + " <HIRE_DATE>1/1/2003 0:0:0</HIRE_DATE>\n" + " <JOB_ID>IT_PROG</JOB_ID>\n" + " </MYROW>\n" + "</ROWSET>\n"; // Set the XML save properties. KeyColumnsList = new string[1]; KeyColumnsList[0] = "EMPLOYEE_ID"; UpdateColumnsList = new string[5]; UpdateColumnsList[0] = "EMPLOYEE_ID"; UpdateColumnsList[1] = "LAST_NAME"; UpdateColumnsList[2] = "EMAIL"; UpdateColumnsList[3] = "HIRE_DATE"; UpdateColumnsList[4] = "JOB_ID"; cmd.XmlSaveProperties.KeyColumnsList = KeyColumnsList; cmd.XmlSaveProperties.RowTag = "MYROW"; cmd.XmlSaveProperties.Table = "employees"; cmd.XmlSaveProperties.UpdateColumnsList = UpdateColumnsList; cmd.XmlSaveProperties.Xslt = null; cmd.XmlSaveProperties.XsltParams = null; // Do the inserts. rows = cmd.ExecuteNonQuery(); Console.WriteLine("rows: " + rows); // Set the XML command type to update. cmd.XmlCommandType = OracleXmlCommandType.Update; // Set the XML document. cmd.CommandText = "<?xml version=\"1.0\"?>\n" + "<ROWSET>\n" + " <MYROW num = \"1\">\n" + " <EMPLOYEE_ID>1234</EMPLOYEE_ID>\n" + " <LAST_NAME>Adams</LAST_NAME>\n" + " </MYROW>\n" + "</ROWSET>\n"; // Set the XML save properties. KeyColumnsList = new string[1]; KeyColumnsList[0] = "EMPLOYEE_ID"; UpdateColumnsList = new string[1]; UpdateColumnsList[0] = "LAST_NAME"; cmd.XmlSaveProperties.KeyColumnsList = KeyColumnsList; cmd.XmlSaveProperties.UpdateColumnsList = UpdateColumnsList; // Do the updates. rows = cmd.ExecuteNonQuery(); Console.WriteLine("rows: " + rows); // Set the XML command type to delete. cmd.XmlCommandType = OracleXmlCommandType.Delete; // Set the XML document. cmd.CommandText = "<?xml version=\"1.0\"?>\n" + "<ROWSET>\n" + " <MYROW num = \"1\">\n" + " <EMPLOYEE_ID>1234</EMPLOYEE_ID>\n" + " </MYROW>\n" + " <MYROW num = \"2\">\n" + " <EMPLOYEE_ID>1235</EMPLOYEE_ID>\n" + " </MYROW>\n" + "</ROWSET>\n"; // Set the XML save properties. KeyColumnsList = new string[1]; KeyColumnsList[0] = "EMPLOYEE_ID"; cmd.XmlSaveProperties.KeyColumnsList = KeyColumnsList; cmd.XmlSaveProperties.UpdateColumnsList = null; // Do the deletes. rows = cmd.ExecuteNonQuery(); Console.WriteLine("rows: " + rows); // Clean up. cmd.Dispose(); conn.Close(); conn.Dispose();
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleXmlSaveProperties
members are listed in the following tables:
OracleXmlSaveProperties
constructors are listed in Table 4-123
Table 4-123 OracleXmlSaveProperties Constructor
Constructor | Description |
---|---|
OracleXmlSaveProperties Constructor | Instantiates a new instance of the OracleXmlSaveProperties class |
The OracleXmlSaveProperties
properties are listed in Table 4-124.
Table 4-124 OracleXmlSaveProperties Properties
Name | Description |
---|---|
KeyColumnsList | Specifies the list of columns used as a key to locate existing rows for update or delete using an XML document |
RowTag | Specifies the value for the XML element that identifies a row of data in an XML document |
Table | Specifies the name of the table or view to which changes are saved |
UpdateColumnsList | Specifies the list of columns to update or insert |
Xslt | Specifies the XSL document used for XML transformation using XSLT |
XsltParams | Specifies the parameters for the XSLT document specified in the Xslt property |
The OracleXmlSaveProperties
public methods are listed in Table 4-125.
Table 4-125 OracleXmlSaveProperties Public Methods
Name | Description |
---|---|
Clone | Creates a copy of an OracleXmlSaveProperties object |
The OracleXmlSaveProperties
constructor instantiates a new instance of OracleXmlSaveProperties
class.
// C# public OracleXmlSaveProperties;
The OracleXmlSaveProperties
properties are listed in Table 4-126.
Table 4-126 OracleXmlSaveProperties Properties
Name | Description |
---|---|
KeyColumnsList | Specifies the list of columns used as a key to locate existing rows for update or delete using an XML document |
RowTag | Specifies the value for the XML element that identifies a row of data in an XML document |
Table | Specifies the name of the table or view to which changes are saved |
UpdateColumnsList | Specifies the list of columns to update or insert |
Xslt | Specifies the XSL document used for XML transformation using XSLT |
XsltParams | Specifies the parameters for the XSLT document specified in the Xslt property |
This property specifies the list of columns used as a key to locate existing rows for update or delete using an XML document.
// C# public string[] KeyColumnsList {get; set;}
The list of columns.
Default value is null.
The first null value (if any) terminates the list.
KeyColumnsList
usage with XMLCommandType
property values:
Insert
- KeyColumnsList
is ignored and can be null.
Update
- KeyColumnsList
must be specified; it identifies the columns to use to find the rows to be updated.
Delete
- If KeyColumnsList
is null, all the column values in each row element in the XML document are used to locate the rows to delete. Otherwise, KeyColumnsList
specifies the columns used to identify the rows to delete.
This property specifies the value for the XML element that identifies a row of data in an XML document.
// C# public string RowTag {get; set;}
An XML element name.
The default value is ROW
.
Each element in the XML document identifies one row in a table or view.
If RowTag
is set to ""
or null
, no row tag is used in the XML document. In this case, the XML document is assumed to contain only one row.
This property specifies the name of the table or view to which changes are saved.
// C# public string Table {get; set;}
A table name.
Default value is null
.
The property must be set to a valid table or view name.
This property specifies the list of columns to update or insert.
// C# public string[] UpdateColumnsList {get; set;}
A list of columns.
Default value is null.
The first null value (if any) terminates the list.
UpdateColumnList
usage with XMLCommandType
property values:
Insert
- UpdateColumnList
indicates which columns are assigned values when a new row is created. If UpdateColumnList
is null, then all columns are assigned values. If a column is on the UpdateColumnList
, but no value is specified for the row in the XML file, then NULL
is used. If a column is not on the UpdateColumnList
, then the default value for that column is used.
Update
- UpdateColumnList
specifies columns to modify for each row of data in the XML document. If UpdateColumnList
is null, all the values in each XML element in the XML document are used to modify the columns.
Delete
- UpdateColumnsList
is ignored and can be null.
This property specifies the XSL document used for XML transformation using XSLT.
// C# public string Xslt {get; set;}
The XSL document used for XML transformation.
Default = null
.
The XSL document is used for XSLT transformation of a given XML document. The transformed XML document is used to save changes to the table or view.
This property specifies the parameters for the XSLT document specified in the Xslt
property.
// C# public string XsltParams {get; set;}
The parameters for the XSLT document .
Default is null
.
This property is a string delimited by semicolons in "name=value
" pairs of the form "param1=value1; param2=value2; …
".
The OracleXmlSaveProperties
public methods are listed in Table 4-127.
Table 4-127 OracleXmlSaveProperties Public Methods
Name | Description |
---|---|
Clone | Creates a copy of an OracleXmlSaveProperties object |
This method creates a copy of an OracleXmlSaveProperties
object.
// C# public object Clone();
An OracleXmlSaveProperties
object
ICloneable