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.
The following section contain related information:
Class Inheritance
Object
MarshalByRefObject
OracleDataReader
Declaration
// C# public sealed class OracleDataReader : MarshalByRefObject, IEnumerable, IDataReader, IDisposable, IDataRecord
Thread Safety
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
Remarks
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 following OracleDataReader
example retrieves the data from the EMP
table:
/* Database Setup, if you have not done so yet. connect scott/tiger@oracle CREATE TABLE empInfo ( empno NUMBER(4) PRIMARY KEY, empName VARCHAR2(20) NOT NULL, hiredate DATE, salary NUMBER(7,2), jobDescription Clob, byteCodes BLOB ); Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values (1,'KING','SOFTWARE ENGR', '5657'); Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values (2,'SCOTT','MANAGER', '5960'); commit; */ // C# using System; using System.Data; using Oracle.DataAccess.Client; class OracleDataReaderSample { static void Main() { string constr = "User Id=scott;Password=tiger;Data Source=oracle"; OracleConnection con = new OracleConnection(constr); con.Open(); string cmdstr = "SELECT * FROM EMPINFO"; OracleConnection connection = new OracleConnection(constr); OracleCommand cmd = new OracleCommand(cmdstr, con); 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(); } // Clean up reader.Dispose(); con.Dispose(); } }
Requirements
Namespace: Oracle.DataAccess.Client
Assembly: Oracle.DataAccess.dll
OracleDataReader
members are listed in the following tables:
OracleDataReader Static Methods
The OracleDataReader
static method is listed in Table 5-38.
Table 5-38 OracleDataReader Static Method
Method | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleDataReader Properties
OracleDataReader
properties are listed in Table 5-39.
Table 5-39 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 |
HasRows | Indicates whether the OracleDataReader has one or more rows |
IsClosed | Indicates whether or not 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
OracleDataReader
public methods are listed in Table 5-40.
Table 5-40 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 or not 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 |
The OracleDataReader
static method is listed in Table 5-41.
Table 5-41 OracleDataReader Static Method
Method | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleDataReader
properties are listed in Table 5-42.
Table 5-42 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 |
HasRows | Indicates whether the OracleDataReader has one or more rows |
IsClosed | Indicates whether or not 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.
Declaration
// C# public int Depth {get;}
Property Value
The depth of nesting for the current row.
Implements
IDataReader
Exceptions
InvalidOperationException
- The reader is closed.
Remarks
Default = 0
This property always returns zero because Oracle does not support nesting.
This property specifies the size of OracleDataReader
's internal cache.
Declaration
// C# public long FetchSize {get; set;}
Property Value
A long
that specifies the amount of memory (in bytes) that the OracleDataReader
uses for its internal cache.
Exceptions
ArgumentException
- The FetchSize
value specified is invalid.
Remarks
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 database round-trip.
This property gets the number of columns in the result set.
Declaration
// C# public int FieldCount {get;}
Property Value
The number of columns in the result set if one exists, otherwise 0
.
Implements
IDataRecord
Exceptions
InvalidOperationException
- The reader is closed.
Remarks
Default = 0
This property has a value of 0 for queries that do not return result sets.
This property indicates whether the OracleDataReader
has one or more rows.
Declaration
// C# public bool HasRows (get;);
Return Value
bool
Remarks
HasRows
indicates whether or not the OracleDataReader
has any rows.
The value of HasRows
does not change based on the row position. For example, even if the application has read all the rows from the result set and the next Read method invocation will return false, the HasRows
property still returns true since the result set was not empty to begin with.
Rows are fetched to determine the emptiness of the OracleDataReader
when HasRows
property is accessed for the first time after the creation of the OracleDataReader
object.
Example
// C# using System; using Oracle.DataAccess.Client; class HasRowsSample { static void Main() { string constr = "User Id=scott;Password=tiger;Data Source=oracle"; OracleConnection con = new OracleConnection(constr); con.Open(); OracleCommand cmd = new OracleCommand( "select * from emp where empno = 9999", con); OracleDataReader reader = cmd.ExecuteReader(); if (!reader.HasRows) Console.WriteLine("The result set is empty."); else Console.WriteLine("The result set is not empty."); con.Dispose(); } }
See Also:
|
This property indicates whether or not the data reader is closed.
Declaration
// C# public bool IsClosed {get;}
Property Value
If the OracleDataReader
is in a closed state, returns true
; otherwise, returns false
.
Implements
IDataReader
Remarks
Default = true
IsClosed
and RecordsAffected
are the only two properties that are accessible after the OracleDataReader
is closed.
This property gets the value of the column in .NET datatype.
Overload List:
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.
Declaration
// C# public object this[int index] {get;}
Parameters
index
The zero-based index of the column.
Property Value
The .NET value of the specified column.
Implements
IDataRecord
Remarks
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.
Declaration
// C#
public object this[string columnName] {get;}
Parameters
columnName
The name of the column.
Property Value
The .NET Value
of the specified column.
Implements
IDataRecord
Remarks
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.
Declaration
// C# public int InitialLOBFetchSize {get;}
Property Value
The size of the chunk to retrieve.
Exceptions
InvalidOperationException
- The reader is closed.
Remarks
For Oracle Database 10g release 2 (10.2) and later, the maximum value supported for InitialLOBFetchSize
is 2 GB.
For releases prior to Oracle Database 10g release 2 (10.2), the maximum value supported for InitialLOBFetchSize
is 32K.
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.
Declaration
// C# public long InitialLONGFetchSize {get;}
Property Value
The size of the chunk to retrieve. The default is 0
.
Exceptions
InvalidOperationException
- The reader is closed.
Remarks
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.
Declaration
// C# public int RecordsAffected {get;}
Property Value
The number of rows affected by execution of the SQL statement.
Implements
IDataReader
Remarks
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 5-43.
Table 5-43 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 or not 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
.
Declaration
// C# public void Close();
Implements
IDataReader
Remarks
The Close
method frees all resources associated with the OracleDataReader
.
Example
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.
Declaration
// C# public void Dispose();
Implements
IDisposable
Remarks
The Dispose
method also closes the OracleDataReader
.
This method returns the byte value of the specified column.
Declaration
// C#
public byte GetByte(int index);
Parameters
index
The zero-based column index.
Return Value
The value of the column as a byte.
Implements
IDataRecord
Exceptions
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
.
Remarks
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.
Declaration
// C# public long GetBytes(int index, long fieldOffset, byte[] buffer, int bufferOffset, int length);
Parameters
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).
Return Value
The number of bytes read.
Implements
IDataRecord
Exceptions
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
.
Remarks
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.
Declaration
// C# public long GetChars(int index, long fieldOffset, char[] buffer, int bufferOffset, int length);
Parameters
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).
Return Value
The number of characters read.
Implements
IDataRecord
Exceptions
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
.
Remarks
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.
Declaration
// C#
public string GetDataTypeName(int index);
Parameters
index
The zero-based column index.
Return Value
The name of the ODP.NET type of the column.
Implements
IDataRecord
Exceptions
InvalidOperationException
- The reader is closed.
IndexOutOfRangeException
- The column index is invalid.
This method returns the DateTime
value of the specified column.
Declaration
// C#
public DateTime GetDateTime(int index);
Parameters
index
The zero-based column index.
Return Value
The DateTime
value of the column.
Implements
IDataRecord
Exceptions
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
.
Remarks
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns the decimal
value of the specified NUMBER
column.
Declaration
// C#
public decimal GetDecimal(int index);
Parameters
index
The zero-based column index.
Return Value
The decimal
value of the column.
Implements
IDataRecord
Exceptions
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
.
Remarks
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.
Declaration
// C#
public double GetDouble(int index);
Parameters
index
The zero-based column index.
Return Value
The double
value of the column.
Implements
IDataRecord
Exceptions
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
.
Remarks
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.
Declaration
// C#
public Type GetFieldType(int index);
Parameters
index
The zero-based column index.
Return Value
The Type
of the default .NET type of the column.
Implements
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.
Declaration
// C#
public float GetFloat(int index);
Parameters
index
The zero-based column index.
Return Value
The float
value of the column.
Implements
IDataRecord
Exceptions
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
.
Remarks
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 . |
Declaration
// C#
public short GetInt16(int index);
Parameters
index
The zero-based column index.
Return Value
The Int16
value of the column.
Implements
IDataRecord
Exceptions
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
.
Remarks
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 . |
Declaration
// C#
public int GetInt32(int index);
Parameters
index
The zero-based column index.
Return Value
The Int32
value of the column.
Implements
IDataRecord
Exceptions
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
.
Remarks
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 . |
Declaration
// C#
public long GetInt64(int index);
Parameters
index
The zero-based column index.
Return Value
The Int64
value of the column.
Implements
IDataRecord
Exceptions
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
.
Remarks
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns the name of the specified column.
Declaration
// C#
public string GetName(int index);
Parameters
index
The zero-based column index.
Return Value
The name of the column.
Implements
IDataRecord
Exceptions
InvalidOperationException
- The reader is closed.
IndexOutOfRangeException
- The column index is invalid.
This method returns an OracleBFile
object of the specified BFILE
column.
Declaration
// C#
public OracleBFile GetOracleBFile(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleBFile
value of the column.
Exceptions
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
.
Remarks
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns an OracleBinary
structure of the specified column.
Declaration
// C#
public OracleBinary GetOracleBinary(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleBinary
value of the column.
Exceptions
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
.
Remarks
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.
Declaration
// C#
public OracleBlob GetOracleBlob(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleBlob
value of the column.
Exceptions
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
.
Remarks
IsDBNull
should be called to check for NULL
values before calling this method.
GetOracleBlobForUpdate
returns an updatable OracleBlob
object of the specified BLOB
column.
Overload List:
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.
Declaration
// C#
public OracleBlob GetOracleBlobForUpdate(int index);
Parameters
index
The zero-based column index.
Return Value
An updatable OracleBlob
object.
Exceptions
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
.
Remarks
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 database 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.
/* Database Setup, if you have not done so yet. connect scott/tiger@oracle CREATE TABLE empInfo ( empno NUMBER(4) PRIMARY KEY, empName VARCHAR2(20) NOT NULL, hiredate DATE, salary NUMBER(7,2), jobDescription Clob, byteCodes BLOB ); Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values (1,'KING','SOFTWARE ENGR', '5657'); Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values (2,'SCOTT','MANAGER', '5960'); commit; */ // C# using System; using System.Data; using Oracle.DataAccess.Client; using Oracle.DataAccess.Types; class GetOracleBlobForUpdateSample { static void Main() { string constr = "User Id=scott;Password=tiger;Data Source=oracle"; OracleConnection con = new OracleConnection(constr); con.Open(); // Get the ByteCodes for empno = 1 string cmdstr = "SELECT BYTECODES, EMPNO FROM EMPINFO where EMPNO = 1"; OracleCommand cmd = new OracleCommand(cmdstr, con); // Since we are going to update the OracleBlob object, we will //have to create a transaction OracleTransaction txn = con.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 ByteCodes object byte[] addedBytes = new byte[2] {0, 0}; byteCodesBlob.Append(addedBytes, 0, addedBytes.Length); // Now commit the transaction txn.Commit(); Console.WriteLine("Blob Column successfully updated"); } else reader.Dispose(); // Close the connection con.Dispose(); } }
This method returns an updatable OracleBlob
object of the specified BLOB
column using a WAIT
clause.
Declaration
// C# public OracleBlob GetOracleBlobForUpdate(int index, int wait);
Parameters
index
The zero-based column index.
wait
The number of seconds the method waits to acquire a lock.
Return Value
An updatable OracleBlob
object.
Exceptions
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
.
Remarks
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 database 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.
Example
The GetOracleBlobForUpdate
methods are comparable. See "Example" for a code example demonstrating usage.
This method returns an OracleClob object of the specified CLOB column.
Declaration
// C#
public OracleClob GetOracleClob(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleClob
value of the column.
Exceptions
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
.
Remarks
IsDBNull
should be called to check for NULL
values before calling this method.
GetOracleClobForUpdate
returns an updatable OracleClob
object of the specified CLOB
column.
Overload List:
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.
Declaration
// C#
public OracleClob GetOracleClobForUpdate(int index);
Parameters
index
The zero-based column index.
Return Value
An updatable OracleClob
.
Exceptions
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
.
Remarks
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 database 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.
/* Database Setup, if you have not done so yet. connect scott/tiger@oracle CREATE TABLE empInfo ( empno NUMBER(4) PRIMARY KEY, empName VARCHAR2(20) NOT NULL, hiredate DATE, salary NUMBER(7,2), jobDescription Clob, byteCodes BLOB ); Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values (1,'KING','SOFTWARE ENGR', '5657'); Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values (2,'SCOTT','MANAGER', '5960'); commit; */ // C# using System; using System.Data; using Oracle.DataAccess.Client; using Oracle.DataAccess.Types; class GetOracleClobForUpdateSample { static void Main() { string constr = "User Id=scott;Password=tiger;Data Source=oracle"; OracleConnection con = new OracleConnection(constr); con.Open(); // Get the job description for empno = 1 string cmdStr = "SELECT JOBDESCRIPTION, EMPNO FROM EMPINFO where EMPNO = 1"; OracleCommand cmd = new OracleCommand(cmdStr, con); // Since we are going to update the OracleClob object, we will // have to create a transaction OracleTransaction txn = con.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(); Console.WriteLine("Clob Column successfully updated"); } else reader.Close(); // Close the connection con.Close(); } }
This method returns an updatable OracleClob
object of the specified CLOB
column using a WAIT
clause.
Declaration
// C# public OracleClob GetOracleClobForUpdate(int index, int wait);
Parameters
index
The zero-based column index.
wait
The number of seconds the method waits to acquire a lock.
Return Value
An updatable OracleClob
.
Exceptions
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
.
Remarks
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 database 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.
Example
The GetOracleClobForUpdate methods are comparable. See "Example" for a code example demonstrating usage.
This method returns an OracleDate structure of the specified DATE column.
Declaration
// C#
public OracleDate GetOracleDate(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleDate
value of the column.
Exceptions
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
.
Remarks
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns an OracleDecimal
structure of the specified NUMBER
column.
Declaration
// C#
public OracleDecimal GetOracleDecimal(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleDecimal
value of the column.
Exceptions
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
.
Remarks
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.
Declaration
// C#
public OracleIntervalDS GetOracleIntervalDS(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleIntervalDS
value of the column.
Exceptions
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
.
Remarks
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.
Declaration
// C#
public OracleIntervalYM GetOracleIntervalYM(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleIntervalYM
value of the column.
Exceptions
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
.
Remarks
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.
Declaration
// C#
public OracleString GetOracleString(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleString
value of the column.
Exceptions
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
.
Remarks
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.
Declaration
// C#
public OracleTimeStamp GetOracleTimeStamp(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleTimeStamp
value of the column.
Exceptions
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
.
Remarks
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.
Declaration
// C#
public OracleTimeStampLTZ GetOracleTimeStampLTZ(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleTimeStampLTZ
value of the column.
Exceptions
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
.
Remarks
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.
Declaration
// C#
public OracleTimeStampTZ GetOracleTimeStampTZ(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleTimeStampTZ
value of the column.
Exceptions
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
.
Remarks
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.
Declaration
// C#
public OracleXmlType GetOracleXmlType(int index);
Parameters
index
The zero-based column index.
Return Value
The OracleXmlType
value of the column.
Exceptions
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
Remarks
IsDBNull
should be called to check for NULL
values before calling this method.
Requirements
This property can only be used with Oracle9i Release 2 (9.2) or later.
This method returns the specified column value as an ODP.NET type.
Declaration
// C#
public object GetOracleValue(int index);
Parameters
index
The zero-based column index.
Return Value
The value of the column as an ODP.NET type.
Exceptions
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.
Declaration
// C#
public int GetOracleValues(object[] values);
Parameters
values
An array of objects to hold the ODP.NET types as the column values.
Return Value
The number of ODP.NET types in the values
array.
Exceptions
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
Remarks
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.
Declaration
// C#
public int GetOrdinal(string name);
Parameters
name
The specified column name.
Return Value
The index of the column.
Implements
IDataRecord
Exceptions
InvalidOperationException
- The reader is closed.
IndexOutOfRangeException
- The column index is invalid.
Remarks
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
.
Declaration
// C# public DataTable GetSchemaTable();
Return Value
A DataTable
that contains the metadata of the result set.
Implements
IDataReader
Exceptions
InvalidOperationException
- The connection is closed or the reader is closed.
Remarks
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 5-44 OracleDataReader SchemaTable
Name | Name Type | Description |
---|---|---|
ColumnName |
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 or not 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 or not 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 . |
Example
This example creates and uses the SchemaTable
from the reader.
/* Database Setup, if you have not done so yet. connect scott/tiger@oracle CREATE TABLE empInfo ( empno NUMBER(4) PRIMARY KEY, empName VARCHAR2(20) NOT NULL, hiredate DATE, salary NUMBER(7,2), jobDescription Clob, byteCodes BLOB ); Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values (1,'KING','SOFTWARE ENGR', '5657'); Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values (2,'SCOTT','MANAGER', '5960'); commit; */ // C# using System; using System.Data; using Oracle.DataAccess.Client; using Oracle.DataAccess.Types; class GetSchemaTableSample { static void Main() { string constr = "User Id=scott;Password=tiger;Data Source=oracle"; OracleConnection con = new OracleConnection(constr); con.Open(); string cmdstr = "SELECT EMPNO,EMPNAME FROM EMPINFO where EMPNO = 1"; OracleCommand cmd = new OracleCommand(cmdstr, con); //get the reader OracleDataReader reader = cmd.ExecuteReader(); //get the schema table DataTable schemaTable = reader.GetSchemaTable(); //retrieve the first column info. DataRow row = schemaTable.Rows[0]; //print out the column info Console.WriteLine("Column name: " + row["COLUMNNAME"]); Console.WriteLine("Precision: " + row["NUMERICPRECISION"]); Console.WriteLine("Scale: " + row["NUMERICSCALE"]); reader.Close(); // Close the connection con.Close(); } }
This method returns the string
value of the specified column.
Declaration
// C#
public string GetString(int index);
Parameters
index
The zero-based column index.
Return Value
The string
value of the column.
Implements
IDataRecord
Exceptions
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
.
Remarks
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.
Declaration
// C#
public TimeSpan GetTimeSpan(int index);
Parameters
index
The zero-based column index.
Return Value
The TimeSpan
value of the column.
Implements
IDataRecord
Exceptions
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
.
Remarks
IsDBNull
should be called to check for NULL
values before calling this method.
This method returns the column value as a .NET type.
Declaration
// C#
public object GetValue(int index);
Parameters
index
The zero-based column index.
Return Value
The value of the column as a .NET type.
Implements
IDataRecord
Exceptions
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.
Remarks
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.
Declaration
// C#
public int GetValues(object[ ] values);
Parameters
values
An array of objects to hold the .NET types as the column values.
Return Value
The number of objects in the values
array.
Implements
IDataRecord
Exceptions
InvalidOperationException
- The connection is closed, the reader is closed, Read()
has not been called, or all rows have been read.
Remarks
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.
Declaration
// C#
public XmlReader GetXmlReader(int index);
Parameters
index
The zero-based column index.
Return Value
A .NET XmlTextReader
.
Exceptions
InvalidCastException
- The accessor method is invalid for this column type or the column value is NULL
.
Remarks
IsDBNull
should be called to check for NULL
values before calling this method.
This method indicates whether or not the column value is NULL
.
Declaration
// C#
public bool IsDBNull(int index);
Parameters
index
The zero-based column index.
Return Value
Returns true
if the column is a NULL
value; otherwise, returns false
.
Implements
IDataRecord
Exceptions
InvalidOperationException
- The reader is closed, Read()
has not been called, or all rows have been read.
IndexOutOfRangeException
- The column index is invalid.
Remarks
This method should be called to check for NULL
values before calling the other accessor methods.
Example
The code example for the OracleDataReader
class includes the IsDBNull
method. See "Example".
This method advances the data reader to the next result set.
Declaration
// C# public bool NextResult();
Return Value
Returns true
if another result set exists; otherwise, returns false
.
Implements
IDataReader
Exceptions
InvalidOperationException
- The connection is closed or the reader is closed.
Remarks
NextResult
is used when reading results from stored procedure execution that return more than one result set.
This method reads the next row in the result set.
Declaration
// C# public bool Read();
Return Value
Returns true
if another row exists; otherwise, returns false
.
Implements
IDataReader
Exceptions
InvalidOperationException
- The connection is closed or the reader is closed.
Remarks
The initial position of the data reader is before the first row. Therefore, the Read
method must be called to fetch the first row. The row that was just read is considered the current row. If the OracleDataReader
has no more rows to read, it returns false
.
Example
The code example for the OracleDataReader
class includes the Read
method. See "Example".