Export (0) Print
Expand All

OracleDataReader Class

Note: This namespace, class, or member is supported only in version 1.1 of the .NET Framework.

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

For a list of all members of this type, see OracleDataReader Members.

System.Object
   System.MarshalByRefObject
      System.Data.OracleClient.OracleDataReader

[Visual Basic]
NotInheritable Public Class OracleDataReader
   Inherits MarshalByRefObject
   Implements IDataReader, IDisposable, IDataRecord, IEnumerable
[C#]
public sealed class OracleDataReader : MarshalByRefObject,
   IDataReader, IDisposable, IDataRecord, IEnumerable
[C++]
public __gc __sealed class OracleDataReader : public
   MarshalByRefObject, IDataReader, IDisposable, IDataRecord,
   IEnumerable
[JScript]
public class OracleDataReader extends MarshalByRefObject implements
   IDataReader, IDisposable, IDataRecord, IEnumerable

Thread Safety

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

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 IN OUT T_CURSOR)
    IS 
        V_CURSOR T_CURSOR; 
    BEGIN s 
        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.

[Visual Basic] 
Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim connString As New String("Data Source=Oracle8i;Integrated Security=yes")
    Dim conn As New OracleConnection(connString)
    conn.Open()
    Dim cmd As New OracleCommand()
    cmd.Connection = conn
    cmd.CommandText = "CURSPKG.OPEN_ONE_CURSOR"
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New OracleParameter("N_EMPNO", OracleType.Number)).Value = 7369
    cmd.Parameters.Add(New OracleParameter("IO_CURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output
    Dim rdr As OracleDataReader
    rdr = cmd.ExecuteReader()
    While (rdr.Read())
        REM do something with the values
    End While
    rdr.Close()
    conn.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.

[Visual Basic] 
Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim connString As New String("Data Source=Oracle8i;Integrated Security=yes")
    Dim ds As New DataSet()
    Dim conn As New OracleConnection(connString)
    Dim cmd As New OracleCommand()
    conn.Open()
    cmd.Connection = conn
    cmd.CommandText = "CURSPKG.OPEN_TWO_CURSORS"
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New OracleParameter("EMPCURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output
    cmd.Parameters.Add(New OracleParameter("DEPTCURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output
    Dim rdr As OracleDataReader
    rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    While (rdr.Read())
        REM do something with the values from the EMP table 
    End While
    rdr.NextResult()
    While (rdr.Read())
        REM do something with the values from the DEPT table 
    End While
    rdr.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.

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

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

[C#] 
public void ReadOracleTypesExample(string connectionString)
   {
   OracleConnection myConnection = new OracleConnection(connectionString);
   myConnection.Open();
   OracleCommand myCommand = myConnection.CreateCommand();
   try
      {
      myCommand.CommandText = "SELECT * from OracleTypesTable";
      OracleDataReader oracledatareader1 = myCommand.ExecuteReader();
      oracledatareader1.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 = oracledatareader1.GetOracleString(0);
      Console.WriteLine("OracleString " + oraclestring1.ToString());
      //Second column, MyNumber, is a NUMBER data type in Oracle Server
      //and maps to OracleNumber.
      OracleNumber oraclenumber1 = oracledatareader1.GetOracleNumber(1);
      Console.WriteLine("OracleNumber " + oraclenumber1.ToString());
      //Third column, MyDate, is a DATA data type in Oracle Server
      //and maps to OracleDateTime.
      OracleDateTime oracledatetime1 = oracledatareader1.GetOracleDateTime(2);
      Console.WriteLine("OracleDateTime " + oracledatetime1.ToString());
      //Fourth column, MyRaw, is a RAW data type in Oracle Server and
      //maps to OracleBinary.
      OracleBinary oraclebinary1 = oracledatareader1.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());
         }
      }
      oracledatareader1.Close();
   }
   catch(Exception e)
   {
       Console.WriteLine(e.ToString());
   }
   finally
   {
       myConnection.Close();
   }
}

Example

[Visual Basic, C#, C++] 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.

[Visual Basic] 
Public Sub ReadMyData(myConnString As String)
    Dim mySelectQuery As String = "SELECT EmpNo, EName FROM Emp"
    Dim myConnection As New OracleConnection(myConnString)
    Dim myCommand As New OracleCommand(mySelectQuery, myConnection)
    myConnection.Open()
    Dim myReader As OracleDataReader
    myReader = myCommand.ExecuteReader()
    ' Always call Read before accessing data.
    While myReader.Read()
        Console.WriteLine(myReader.GetInt32(0).ToString() + ", " _
           + myReader.GetString(1))
    End While
    ' always call Close when done reading.
    myReader.Close()
    ' Close the connection when done with it.
    myConnection.Close()
End Sub

[C#] 
public void ReadMyData(string myConnString) {
   string mySelectQuery = "SELECT EmpNo, EName FROM Emp";
   OracleConnection myConnection = new OracleConnection(myConnString);
   OracleCommand myCommand = new OracleCommand(mySelectQuery,myConnection);
   myConnection.Open();
   OracleDataReader myReader;
   myReader = myCommand.ExecuteReader();
   // Always call Read before accessing data.
   while (myReader.Read()) {
      Console.WriteLine(myReader.GetInt32(0) + ", " + myReader.GetString(1));
   }
   // always call Close when done reading.
   myReader.Close();
   // Close the connection when done with it.
   myConnection.Close();
}

[C++] 
public:
 void ReadMyData(String* myConnString) {
    String* mySelectQuery = S"SELECT EmpNo, EName FROM Emp";
    OracleConnection* myConnection = new OracleConnection(myConnString);
    OracleCommand* myCommand = new OracleCommand(mySelectQuery,myConnection);
    myConnection->Open();
    OracleDataReader* myReader;
    myReader = myCommand->ExecuteReader();
    // Always call Read before accessing data.
    while (myReader->Read()) {
       Console::WriteLine(S"{0}, {1}", __box(myReader->GetInt32(0)), myReader->GetString(1));
    }
    // always call Close when done reading.
    myReader->Close();
    // Close the connection when done with it.
    myConnection->Close();
 }

[JScript] No example is available for JScript. To view a Visual Basic, C#, or C++ example, click the Language Filter button Language Filter in the upper-left corner of the page.

Requirements

Namespace: System.Data.OracleClient

Platforms: Windows 98, Windows NT 4.0, Windows Millennium Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family

Assembly: System.Data.Oracleclient (in System.Data.Oracleclient.dll)

See Also

OracleDataReader Members | System.Data.OracleClient Namespace

Show:
© 2014 Microsoft