OracleDataReader Classe

Definição

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

Herança

Object

MarshalByRefObject

OracleDataReader

Herança

Object

MarshalByRefObject

DbDataReader

OracleDataReader

Implementações

IEnumerable IDataReader IDataRecord IDisposable

Exemplos

O exemplo seguinte cria um OracleConnection, um OracleCommand, e um OracleDataReader. O exemplo lê os dados, escrevendo-os para a consola. Finalmente, o exemplo fecha o OracleDataReader, depois o 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

Observações

Para criar um OracleDataReader, deve chamar o ExecuteReader método do OracleCommand objeto, em vez de usar diretamente um construtor.

Alterações feitas a um conjunto de resultados por outro processo ou thread enquanto os dados estão a ser lidos podem ser visíveis para o utilizador do OracleDataReader.

IsClosed e RecordsAffected são as únicas propriedades que pode chamar depois de o OracleDataReader fechar. Em alguns casos, tem de ligar Close antes de poder ligar RecordsAffected.

Mais do que um OracleDataReader pode estar aberto ao mesmo tempo.

Os dois exemplos Visual Basic seguintes demonstram como usar um OracleDataReader para recuperar um Oráculo REF CURSOR. Estes exemplos utilizam tabelas definidas no esquema Oracle Scott/Tiger e requerem o seguinte pacote PL/SQL e o corpo do pacote. Tem de criar estes no seu servidor para usar os exemplos.

Crie o seguinte pacote Oracle no servidor Oracle.

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;
/

Crie o seguinte corpo de pacote Oracle no servidor Oracle.

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;
/

Este Visual Basic exemplo executa um procedimento armazenado PL/SQL que devolve um parâmetro REF CURSOR e lê o valor como um 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

Este Visual Basic exemplo executa um procedimento armazenado PL/SQL que devolve dois parâmetros REF CURSOR e lê os valores usando um 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

Este exemplo de C# cria uma tabela Oracle e carrega-a com dados. Deve executar este exemplo antes de executar o exemplo seguinte, que demonstra o uso de um OracleDataReader para aceder aos dados usando estruturas OracleType.

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

Este exemplo de C# usa um OracleDataReader para aceder a dados e utiliza várias OracleType estruturas para exibir os dados.

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