Export (0) Print
Expand All
3 out of 3 rated this helpful - Rate this topic

OracleDataReader Class

Provides a way of reading a forward-only stream of data rows from a data source. This class cannot be inherited.

System.Object
  System.MarshalByRefObject
    System.Data.Common.DbDataReader
      System.Data.OracleClient.OracleDataReader

Namespace:  System.Data.OracleClient
Assembly:  System.Data.OracleClient (in System.Data.OracleClient.dll)
public sealed class OracleDataReader : DbDataReader

The OracleDataReader type exposes the following members.

  NameDescription
Public propertyDepthGets a value indicating the depth of nesting for the current row. (Overrides DbDataReader.Depth.)
Public propertyFieldCountGets the number of columns in the current row. (Overrides DbDataReader.FieldCount.)
Public propertyHasRowsGets a value indicating whether the OracleDataReader contains one or more rows. (Overrides DbDataReader.HasRows.)
Public propertyIsClosedIndicates whether the OracleDataReader is closed. (Overrides DbDataReader.IsClosed.)
Public propertyItem[Int32]Gets the value of the specified column in its native format given the column ordinal. (Overrides DbDataReader.Item.)
Public propertyItem[String]Gets the value of the specified column in its native format given the column name. (Overrides DbDataReader.Item.)
Public propertyRecordsAffectedGets the number of rows changed, inserted, or deleted by execution of the SQL statement. (Overrides DbDataReader.RecordsAffected.)
Public propertyVisibleFieldCountGets the number of fields in the DbDataReader that are not hidden. (Inherited from DbDataReader.)
Top
  NameDescription
Public methodCloseCloses the OracleDataReader object. (Overrides DbDataReader.Close().)
Public methodCreateObjRefCreates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object. (Inherited from MarshalByRefObject.)
Public methodDispose()Releases all resources used by the current instance of the DbDataReader class. (Inherited from DbDataReader.)
Public methodEquals(Object)Determines whether the specified object is equal to the current object. (Inherited from Object.)
Public methodGetBooleanGets the value of the specified column as a Boolean. (Overrides DbDataReader.GetBoolean(Int32).)
Public methodGetByteGets the value of the specified column as a byte. (Overrides DbDataReader.GetByte(Int32).)
Public methodGetBytesReads a stream of bytes from the specified column offset into the buffer as an array, starting at the given buffer offset. (Overrides DbDataReader.GetBytes(Int32, Int64, Byte[], Int32, Int32).)
Public methodGetCharGets the value of the specified column as a character. (Overrides DbDataReader.GetChar(Int32).)
Public methodGetCharsReads a stream of characters from the specified column offset into the buffer as an array, starting at the given buffer offset. (Overrides DbDataReader.GetChars(Int32, Int64, Char[], Int32, Int32).)
Public methodGetDataReturns a DbDataReader object for the requested column ordinal. (Inherited from DbDataReader.)
Public methodGetDataTypeNameGets the name of the source data type. (Overrides DbDataReader.GetDataTypeName(Int32).)
Public methodGetDateTimeGets the value of the specified column as a DateTime object. (Overrides DbDataReader.GetDateTime(Int32).)
Public methodGetDecimalGets the value of the specified column as a Decimal object. (Overrides DbDataReader.GetDecimal(Int32).)
Public methodGetDoubleGets the value of the specified column as a double-precision floating point number. (Overrides DbDataReader.GetDouble(Int32).)
Public methodGetEnumeratorReturns an IEnumerator that can be used to iterate through the rows in the data reader. (Overrides DbDataReader.GetEnumerator().)
Public methodGetFieldTypeGets the Type that is the data type of the object. (Overrides DbDataReader.GetFieldType(Int32).)
Public methodGetFieldValue<T>Synchronously gets the value of the specified column as a type. (Inherited from DbDataReader.)
Public methodGetFieldValueAsync<T>(Int32)Asynchronously gets the value of the specified column as a type. (Inherited from DbDataReader.)
Public methodGetFieldValueAsync<T>(Int32, CancellationToken)Asynchronously gets the value of the specified column as a type. (Inherited from DbDataReader.)
Public methodGetFloatGets the value of the specified column as a single-precision floating-point number. (Overrides DbDataReader.GetFloat(Int32).)
Public methodGetGuidGets the value of the specified column as a globally-unique identifier (GUID). (Overrides DbDataReader.GetGuid(Int32).)
Public methodGetHashCodeServes as the default hash function. (Inherited from Object.)
Public methodGetInt16Gets the value of the specified column as a 16-bit signed integer. (Overrides DbDataReader.GetInt16(Int32).)
Public methodGetInt32Gets the value of the specified column as a 32-bit signed integer. (Overrides DbDataReader.GetInt32(Int32).)
Public methodGetInt64Gets the value of the specified column as a 64-bit signed integer. (Overrides DbDataReader.GetInt64(Int32).)
Public methodGetLifetimeServiceRetrieves the current lifetime service object that controls the lifetime policy for this instance. (Inherited from MarshalByRefObject.)
Public methodGetNameGets the name of the specified column. (Overrides DbDataReader.GetName(Int32).)
Public methodGetOracleBFileGets the value of the specified column as an OracleBFile object.
Public methodGetOracleBinaryGets the value of the specified column as an OracleBinary object.
Public methodGetOracleDateTimeGets the value of the specified column as an OracleDateTime object.
Public methodGetOracleLobGets the value of the specified column as an OracleLob object.
Public methodGetOracleMonthSpanGets the value of the specified column as an OracleMonthSpan object.
Public methodGetOracleNumberGets the value of the specified column as an OracleNumber object.
Public methodGetOracleStringGets the value of the specified column as an OracleString object.
Public methodGetOracleTimeSpanGets the value of the specified column as an OracleTimeSpan object.
Public methodGetOracleValueGets the value of the column at the specified ordinal in its Oracle format.
Public methodGetOracleValuesGets all the attribute columns in the current row in Oracle format.
Public methodGetOrdinalGets the column ordinal, given the name of the column. (Overrides DbDataReader.GetOrdinal(String).)
Public methodGetProviderSpecificFieldTypeGets an Object that is a representation of the underlying provider specific field type. (Overrides DbDataReader.GetProviderSpecificFieldType(Int32).)
Public methodGetProviderSpecificValueGets an Object that is a representation of the underlying provider specific field type. (Overrides DbDataReader.GetProviderSpecificValue(Int32).)
Public methodGetProviderSpecificValuesGets an array of objects that are a representation of the underlying provider specific values. (Overrides DbDataReader.GetProviderSpecificValues(Object[]).)
Public methodGetSchemaTableReturns a DataTable that describes the column metadata of the OracleDataReader. (Overrides DbDataReader.GetSchemaTable().)
Public methodGetStreamRetrieves data as a Stream. (Inherited from DbDataReader.)
Public methodGetStringGets the value of the specified column as a string. (Overrides DbDataReader.GetString(Int32).)
Public methodGetTextReaderRetrieves data as a TextReader. (Inherited from DbDataReader.)
Public methodGetTimeSpanGets the value of the specified column as a System.TimeSpan.
Public methodGetTypeGets the Type of the current instance. (Inherited from Object.)
Public methodGetValueGets the value of the column at the specified ordinal in its native format. (Overrides DbDataReader.GetValue(Int32).)
Public methodGetValuesPopulates an array of objects with the column values of the current row. (Overrides DbDataReader.GetValues(Object[]).)
Public methodInitializeLifetimeServiceObtains a lifetime service object to control the lifetime policy for this instance. (Inherited from MarshalByRefObject.)
Public methodIsDBNullGets a value indicating whether the column contains non-existent or missing values. (Overrides DbDataReader.IsDBNull(Int32).)
Public methodIsDBNullAsync(Int32)An asynchronous version of IsDBNull, which gets a value that indicates whether the column contains non-existent or missing values. (Inherited from DbDataReader.)
Public methodIsDBNullAsync(Int32, CancellationToken)An asynchronous version of IsDBNull, which gets a value that indicates whether the column contains non-existent or missing values. Optionally, sends a notification that operations should be cancelled. (Inherited from DbDataReader.)
Public methodNextResultAdvances the OracleDataReader to the next result (Overrides DbDataReader.NextResult().)
Public methodNextResultAsync()An asynchronous version of NextResult, which advances the reader to the next result when reading the results of a batch of statements.Invokes NextResultAsync with CancellationToken.None. (Inherited from DbDataReader.)
Public methodNextResultAsync(CancellationToken)This is the asynchronous version of NextResult. Providers should override with an appropriate implementation. The cancellationToken may optionally be ignored.The default implementation invokes the synchronous NextResult method and returns a completed task, blocking the calling thread. The default implementation will return a cancelled task if passed an already cancelled cancellationToken. Exceptions thrown by NextResult will be communicated via the returned Task Exception property.Other methods and properties of the DbDataReader object should not be invoked while the returned Task is not yet completed. (Inherited from DbDataReader.)
Public methodReadAdvances the OracleDataReader to the next record. (Overrides DbDataReader.Read().)
Public methodReadAsync()An asynchronous version of Read, which advances the reader to the next record in a result set. This method invokes ReadAsync with CancellationToken.None. (Inherited from DbDataReader.)
Public methodReadAsync(CancellationToken)This is the asynchronous version of Read. Providers should override with an appropriate implementation. The cancellationToken may optionally be ignored.The default implementation invokes the synchronous Read method and returns a completed task, blocking the calling thread. The default implementation will return a cancelled task if passed an already cancelled cancellationToken. Exceptions thrown by Read will be communicated via the returned Task Exception property.Do not invoke other methods and properties of the DbDataReader object until the returned Task is complete. (Inherited from DbDataReader.)
Public methodToStringReturns a string that represents the current object. (Inherited from Object.)
Top
  NameDescription
Explicit interface implemetationPrivate methodIDataRecord.GetDataFor a description of this member, see IDataRecord.GetData. (Inherited from DbDataReader.)
Top

To create an OracleDataReader, you must call the ExecuteReader method of the OracleCommand object, rather than directly using a constructor.

Changes made to a resultset by another process or thread while data is being read may be visible to the user of the OracleDataReader.

IsClosed and RecordsAffected are the only properties that you can call after the OracleDataReader is closed. In some cases, you must call Close before you can call RecordsAffected.

More than one OracleDataReader can be open at any given time.

The following two Visual Basic examples demonstrate how to use an OracleDataReader to retrieve an Oracle REF CURSOR. These examples use tables that are defined in the Oracle Scott/Tiger schema, and require the following PL/SQL package and package body. You must create these on your server to use the examples.

Create the following Oracle package on the Oracle server.

CREATE OR REPLACE PACKAGE CURSPKG AS 
   TYPE T_CURSOR IS REF CURSOR; 
   PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER, 
      IO_CURSOR IN OUT T_CURSOR); 
   PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, 
      DEPTCURSOR OUT T_CURSOR);
END CURSPKG;
/

Create the following Oracle package body on the Oracle server.

CREATE OR REPLACE PACKAGE BODY CURSPKG AS 
    PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER, 
                               IO_CURSOR OUT T_CURSOR) 
    IS 
        V_CURSOR T_CURSOR; 
    BEGIN 
        IF N_EMPNO <> 0 THEN 
             OPEN V_CURSOR FOR 
             SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
                  FROM EMP, DEPT 
                  WHERE EMP.DEPTNO = DEPT.DEPTNO 
                        AND EMP.EMPNO = N_EMPNO; 
        ELSE 
             OPEN V_CURSOR FOR 
             SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
                  FROM EMP, DEPT 
                  WHERE EMP.DEPTNO = DEPT.DEPTNO; 
        END IF; 
        IO_CURSOR := V_CURSOR; 
    END OPEN_ONE_CURSOR; 
    PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, 
                                DEPTCURSOR OUT T_CURSOR) 
    IS 
        V_CURSOR1 T_CURSOR; 
        V_CURSOR2 T_CURSOR; 
    BEGIN 
        OPEN V_CURSOR1 FOR SELECT * FROM EMP; 
        OPEN V_CURSOR2 FOR SELECT * FROM DEPT; 
        EMPCURSOR  := V_CURSOR1; 
        DEPTCURSOR := V_CURSOR2; 
    END OPEN_TWO_CURSORS; 
END CURSPKG;
/

This Visual Basic example executes a PL/SQL stored procedure that returns a REF CURSOR parameter, and reads the value as an OracleDataReader.

Private Sub ReadOracleData(ByVal connectionString As String)
   Dim connection As New OracleConnection(connectionString)
   Dim command As New OracleCommand()
   Dim reader As OracleDataReader

   connection.Open()
   command.Connection = connection
   command.CommandText = "CURSPKG.OPEN_ONE_CURSOR"
   command.CommandType = CommandType.StoredProcedure
   command.Parameters.Add(New OracleParameter("N_EMPNO", OracleType.Number)).Value = 7369
   command.Parameters.Add(New OracleParameter("IO_CURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output

   reader = command.ExecuteReader()
   While (reader.Read())
      ' Do something with the values.
   End While
   reader.Close()
   connection.Close()
End Sub

This Visual Basic example executes a PL/SQL stored procedure that returns two REF CURSOR parameters, and reads the values using an OracleDataReader.

Private Sub ReadOracleData(ByVal connectionString As String)
   Dim dataSet As New DataSet()
   Dim connection As New OracleConnection(connectionString)
   Dim command As New OracleCommand()
   Dim reader As OracleDataReader

   connection.Open()
   command.Connection = connection
   command.CommandText = "CURSPKG.OPEN_TWO_CURSORS"
   command.CommandType = CommandType.StoredProcedure
   command.Parameters.Add(New OracleParameter("EMPCURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output
   command.Parameters.Add(New OracleParameter("DEPTCURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output

   reader = command.ExecuteReader(CommandBehavior.CloseConnection)
   While (reader.Read())
      ' Do something with the values.
   End While
   reader.NextResult()
   While (reader.Read())
        ' Do something with the values.
   End While
   reader.Close()
   connection.Close()
 End Sub

This C# example creates an Oracle table and loads it with data. You must run this example prior to running the subsequent example, which demonstrates using an OracleDataReader to access the data using OracleType structures.

public void Setup(string connectionString)
{
   OracleConnection connection = new OracleConnection(connectionString);
   try
   {
      connection.Open();
      OracleCommand command = connection.CreateCommand();
      command.CommandText ="CREATE TABLE OracleTypesTable (MyVarchar2 varchar2(3000),MyNumber number(28,4) PRIMARY KEY,MyDate date, MyRaw raw(255))";
      command.ExecuteNonQuery();
      command.CommandText ="INSERT INTO OracleTypesTable VALUES ('test', 2, to_date('2000-01-11 12:54:01','yyyy-mm-dd hh24:mi:ss'), '0001020304')";
      command.ExecuteNonQuery();
      command.CommandText="SELECT * FROM OracleTypesTable";
   }
   catch(Exception)
   {
   }
   finally
   {
      connection.Close();
   }
}

This C# example uses an OracleDataReader to access data, and uses several OracleType structures to display the data.

public void ReadOracleTypesExample(string connectionString)
{
   OracleConnection connection = new OracleConnection(connectionString);
   connection.Open();
   OracleCommand command = connection.CreateCommand();
   try
   {
      command.CommandText = "SELECT * FROM OracleTypesTable";
      OracleDataReader reader = command.ExecuteReader();
      reader.Read();
      //Using the Oracle specific getters for each type is faster than
      //using GetOracleValue.
      //First column, MyVarchar2, is a VARCHAR2 data type in Oracle Server
      //and maps to OracleString.
      OracleString oraclestring1 = reader.GetOracleString(0);
      Console.WriteLine("OracleString " + oraclestring1.ToString());

      //Second column, MyNumber, is a NUMBER data type in Oracle Server
      //and maps to OracleNumber.
      OracleNumber oraclenumber1 = reader.GetOracleNumber(1);
      Console.WriteLine("OracleNumber " + oraclenumber1.ToString());

      //Third column, MyDate, is a DATA data type in Oracle Server
      //and maps to OracleDateTime.
      OracleDateTime oracledatetime1 = reader.GetOracleDateTime(2);
      Console.WriteLine("OracleDateTime " + oracledatetime1.ToString());

      //Fourth column, MyRaw, is a RAW data type in Oracle Server and
      //maps to OracleBinary.
      OracleBinary oraclebinary1 = reader.GetOracleBinary(3);

      //Calling value on a null OracleBinary throws
      //OracleNullValueException; therefore, check for a null value.
      if (oraclebinary1.IsNull==false)
      {
         foreach(byte b in oraclebinary1.Value)
         {
            Console.WriteLine("byte " + b.ToString());
         }
      }
      reader.Close();
   }
   catch(Exception e)
   {
      Console.WriteLine(e.ToString());
   }
   finally
   {
      connection.Close();
   }
}

The following example creates an OracleConnection, an OracleCommand, and an OracleDataReader. The example reads through the data, writing it out to the console. Finally, the example closes the OracleDataReader, then the OracleConnection.

    public void ReadData(string connectionString)
    {
       string queryString = "SELECT EmpNo, EName FROM Emp";
       using (OracleConnection connection = new OracleConnection(connectionString))
       {
          OracleCommand command = new OracleCommand(queryString, connection);
          connection.Open();
          using(OracleDataReader reader = command.ExecuteReader())
          {
	         // Always call Read before accessing data. 
        	 while (reader.Read())
        	 {
            	Console.WriteLine(reader.GetInt32(0) + ", " + reader.GetString(1));
        	 }
          }
       }
    }

.NET Framework

Supported in: 4.5.1, 4.5, 4, 3.5, 3.0, 2.0, 1.1

Windows Phone 8.1, Windows Phone 8, Windows 8.1, Windows Server 2012 R2, Windows 8, Windows Server 2012, Windows 7, Windows Vista SP2, Windows Server 2008 (Server Core Role not supported), Windows Server 2008 R2 (Server Core Role supported with SP1 or later; Itanium not supported)

The .NET Framework does not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.