| Oracle® Data Provider for .NET Developer's Guide 10g Release 1 (10.1) Part Number B10117-01 |
|
|
View PDF |
| 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 ROWIDs 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 CURSORs, behavior differs depending on whether ExecuteReader() or ExecuteNonQuery() is called.
The value of 100 is used for the FetchSize. If 0 is specified, no rows are fetched. For further information, see "Obtaining LONG and LONG RAW Data".
If the value of the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, OracleXmlCommandType.Delete, or OracleXmlCommandType.Query then the ExecuteReader method throws an InvalidOperationException.
// 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 CURSORs, read the section on OracleRefCursors for more details. See "OracleRefCursor Class".
The value of 100 is used for the FetchSize. If 0 is specified, no rows are fetched. For more information, see "Obtaining LONG and LONG RAW Data".
If the value of the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, OracleXmlCommandType.Delete, or OracleXmlCommandType.Query then the ExecuteReader method throws an InvalidOperationException.
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 Ids 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.