OracleDataReader Class

Reference

Definition

Namespace:: System.Data.OracleClient

Assembly:: System.Data.OracleClient.dll

Important

Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.

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

public ref class OracleDataReader sealed : MarshalByRefObject, IDisposable, System::Collections::IEnumerable, System::Data::IDataReader

public ref class OracleDataReader sealed : System::Data::Common::DbDataReader

public sealed class OracleDataReader : MarshalByRefObject, IDisposable, System.Collections.IEnumerable, System.Data.IDataReader

public sealed class OracleDataReader : System.Data.Common.DbDataReader

type OracleDataReader = class
    inherit MarshalByRefObject
    interface IDataReader
    interface IDisposable
    interface IDataRecord
    interface IEnumerable

type OracleDataReader = class
    inherit DbDataReader

Public NotInheritable Class OracleDataReader
Inherits MarshalByRefObject
Implements IDataReader, IDisposable, IEnumerable

Public NotInheritable Class OracleDataReader
Inherits DbDataReader

Inheritance: Object

MarshalByRefObject
OracleDataReader

Inheritance: Object

MarshalByRefObject

DbDataReader
OracleDataReader

Implements: IEnumerable IDataReader IDataRecord IDisposable

Examples

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));
         }
      }
   }
}

Public Sub ReadData(ByVal connectionString As String)
    Dim queryString As String = "SELECT EmpNo, EName FROM Emp"
    Using connection As New OracleConnection(connectionString)
        Dim command As New OracleCommand(queryString, connection)
        connection.Open()
        Using reader As OracleDataReader = command.ExecuteReader()
            ' Always call Read before accessing data.
            While reader.Read()
                Console.WriteLine(reader.GetInt32(0).ToString() + ", " _
                   + reader.GetString(1))
            End While
        End Using
    End Using
End Sub

Remarks

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();  
   }  
}

Properties

Depth	Gets a value indicating the depth of nesting for the current row.
FieldCount	Gets the number of columns in the current row.
HasRows	Gets a value indicating whether the OracleDataReader contains one or more rows.
IsClosed	Indicates whether the OracleDataReader is closed.
Item[Int32]	Gets the value of the specified column in its native format given the column ordinal.
Item[String]	Gets the value of the specified column in its native format given the column name.
RecordsAffected	Gets the number of rows changed, inserted, or deleted by execution of the SQL statement.
VisibleFieldCount	Gets the number of fields in the DbDataReader that are not hidden. (Inherited from DbDataReader)

Methods

Close()	Closes the OracleDataReader object.
CloseAsync()	Asynchronously closes the DbDataReader object. (Inherited from DbDataReader)
CreateObjRef(Type)	Creates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object. (Inherited from MarshalByRefObject)
Dispose()	Releases the resources that are used by this object.
Dispose()	Releases all resources used by the current instance of the DbDataReader class. (Inherited from DbDataReader)
Dispose(Boolean)	Releases the unmanaged resources used by the DbDataReader and optionally releases the managed resources. (Inherited from DbDataReader)
DisposeAsync()	Asynchronously releases all resources used by the current instance of the DbDataReader class. (Inherited from DbDataReader)
Equals(Object)	Determines whether the specified object is equal to the current object. (Inherited from Object)
GetBoolean(Int32)	Gets the value of the specified column as a Boolean.
GetByte(Int32)	Gets the value of the specified column as a byte.
GetBytes(Int32, Int64, Byte[], Int32, Int32)	Reads a stream of bytes from the specified column offset into the buffer as an array, starting at the given buffer offset.
GetChar(Int32)	Gets the value of the specified column as a character.
GetChars(Int32, Int64, Char[], Int32, Int32)	Reads a stream of characters from the specified column offset into the buffer as an array, starting at the given buffer offset.
GetColumnSchemaAsync(CancellationToken)	This is the asynchronous version of GetColumnSchema(DbDataReader). Providers should override with an appropriate implementation. The `cancellationToken` can optionally be honored. The default implementation invokes the synchronous GetColumnSchema(DbDataReader) call and returns a completed task. The default implementation will return a cancelled task if passed an already cancelled `cancellationToken`. Exceptions thrown by GetColumnSchema(DbDataReader) will be communicated via the returned Task Exception property. (Inherited from DbDataReader)
GetData(Int32)	Returns an IDataReader for the specified column ordinal.
GetData(Int32)	Returns a nested data reader for the requested column. (Inherited from DbDataReader)
GetDataTypeName(Int32)	Gets the name of the source data type.
GetDateTime(Int32)	Gets the value of the specified column as a `DateTime` object.
GetDbDataReader(Int32)	Returns a DbDataReader object for the requested column ordinal that can be overridden with a provider-specific implementation. (Inherited from DbDataReader)
GetDecimal(Int32)	Gets the value of the specified column as a `Decimal` object.
GetDouble(Int32)	Gets the value of the specified column as a double-precision floating point number.
GetEnumerator()	Returns an IEnumerator that can be used to iterate through the rows in the data reader.
GetFieldType(Int32)	Gets the Type that is the data type of the object.
GetFieldValue<T>(Int32)	Gets the value of the specified column as the requested type. (Inherited from DbDataReader)
GetFieldValueAsync<T>(Int32, CancellationToken)	Asynchronously gets the value of the specified column as the requested type. (Inherited from DbDataReader)
GetFieldValueAsync<T>(Int32)	Asynchronously gets the value of the specified column as the requested type. (Inherited from DbDataReader)
GetFloat(Int32)	Gets the value of the specified column as a single-precision floating-point number.
GetGuid(Int32)	Gets the value of the specified column as a globally-unique identifier (GUID).
GetHashCode()	Serves as the default hash function. (Inherited from Object)
GetInt16(Int32)	Gets the value of the specified column as a 16-bit signed integer.
GetInt32(Int32)	Gets the value of the specified column as a 32-bit signed integer.
GetInt64(Int32)	Gets the value of the specified column as a 64-bit signed integer.
GetLifetimeService()	Obsolete. Retrieves the current lifetime service object that controls the lifetime policy for this instance. (Inherited from MarshalByRefObject)
GetName(Int32)	Gets the name of the specified column.
GetOracleBFile(Int32)	Gets the value of the specified column as an OracleBFile object.
GetOracleBinary(Int32)	Gets the value of the specified column as an OracleBinary object.
GetOracleDateTime(Int32)	Gets the value of the specified column as an OracleDateTime object.
GetOracleLob(Int32)	Gets the value of the specified column as an OracleLob object.
GetOracleMonthSpan(Int32)	Gets the value of the specified column as an OracleMonthSpan object.
GetOracleNumber(Int32)	Gets the value of the specified column as an OracleNumber object.
GetOracleString(Int32)	Gets the value of the specified column as an OracleString object.
GetOracleTimeSpan(Int32)	Gets the value of the specified column as an OracleTimeSpan object.
GetOracleValue(Int32)	Gets the value of the column at the specified ordinal in its Oracle format.
GetOracleValues(Object[])	Gets all the attribute columns in the current row in Oracle format.
GetOrdinal(String)	Gets the column ordinal, given the name of the column.
GetProviderSpecificFieldType(Int32)	Gets an `Object` that is a representation of the underlying provider specific field type.
GetProviderSpecificValue(Int32)	Gets an `Object` that is a representation of the underlying provider specific field type.
GetProviderSpecificValues(Object[])	Gets an array of objects that are a representation of the underlying provider specific values.
GetSchemaTable()	Returns a DataTable that describes the column metadata of the OracleDataReader.
GetSchemaTableAsync(CancellationToken)	This is the asynchronous version of GetSchemaTable(). Providers should override with an appropriate implementation. The `cancellationToken` can optionally be honored. The default implementation invokes the synchronous GetSchemaTable() call and returns a completed task. The default implementation will return a cancelled task if passed an already cancelled `cancellationToken`. Exceptions thrown by GetSchemaTable() will be communicated via the returned Task Exception property. (Inherited from DbDataReader)
GetStream(Int32)	Gets a stream to retrieve data from the specified column. (Inherited from DbDataReader)
GetString(Int32)	Gets the value of the specified column as a string.
GetTextReader(Int32)	Gets a text reader to retrieve data from the column. (Inherited from DbDataReader)
GetTimeSpan(Int32)	Gets the value of the specified column as a `System.TimeSpan`.
GetType()	Gets the Type of the current instance. (Inherited from Object)
GetValue(Int32)	Gets the value of the column at the specified ordinal in its native format.
GetValues(Object[])	Populates an array of objects with the column values of the current row.
InitializeLifetimeService()	Obsolete. Obtains a lifetime service object to control the lifetime policy for this instance. (Inherited from MarshalByRefObject)
IsDBNull(Int32)	Gets a value indicating whether the column contains non-existent or missing values.
IsDBNullAsync(Int32, CancellationToken)	Asynchronously gets a value that indicates whether the column contains non-existent or missing values. (Inherited from DbDataReader)
IsDBNullAsync(Int32)	Asynchronously gets a value that indicates whether the column contains non-existent or missing values. (Inherited from DbDataReader)
MemberwiseClone()	Creates a shallow copy of the current Object. (Inherited from Object)
MemberwiseClone(Boolean)	Creates a shallow copy of the current MarshalByRefObject object. (Inherited from MarshalByRefObject)
NextResult()	Advances the OracleDataReader to the next result.
NextResultAsync()	Asynchronously advances the reader to the next result when reading the results of a batch of statements. (Inherited from DbDataReader)
NextResultAsync(CancellationToken)	Asynchronously advances the reader to the next result when reading the results of a batch of statements. (Inherited from DbDataReader)
Read()	Advances the OracleDataReader to the next record.
ReadAsync()	Asynchronously advances the reader to the next record in a result set. (Inherited from DbDataReader)
ReadAsync(CancellationToken)	Asynchronously advances the reader to the next record in a result set. (Inherited from DbDataReader)
ToString()	Returns a string that represents the current object. (Inherited from Object)

Explicit Interface Implementations

IDataRecord.GetData(Int32)	For a description of this member, see GetData(Int32). (Inherited from DbDataReader)
IEnumerable.GetEnumerator()	Returns an enumerator that iterates through a collection.

Extension Methods

CanGetColumnSchema(DbDataReader)	Gets a value that indicates whether a DbDataReader can get a column schema.
GetColumnSchema(DbDataReader)	Gets the column schema (DbColumn collection) for a DbDataReader.
Cast<TResult>(IEnumerable)	Casts the elements of an IEnumerable to the specified type.
OfType<TResult>(IEnumerable)	Filters the elements of an IEnumerable based on a specified type.
AsParallel(IEnumerable)	Enables parallelization of a query.
AsQueryable(IEnumerable)	Converts an IEnumerable to an IQueryable.

Applies to

Partajați prin