OracleDataReader 类

定义

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

继承

Object

MarshalByRefObject

OracleDataReader

继承

Object

MarshalByRefObject

DbDataReader

OracleDataReader

实现

IEnumerable IDataReader IDataRecord IDisposable

示例

以下示例创建一个 OracleConnection、一个 OracleCommand和一个 OracleDataReader。 该示例读取数据，并将其写出到控制台。 最后，该示例关闭 OracleDataReader，然后关闭 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

注解

若要创建对象 OracleDataReader，必须调用 ExecuteReader 对象的方法 OracleCommand ，而不是直接使用构造函数。

正在读取数据时对另一个进程或线程对结果集所做的更改可能对用户 OracleDataReader可见。

IsClosed 是 RecordsAffected 关闭后 OracleDataReader 可以调用的唯一属性。 在某些情况下，必须先呼叫 Close ，然后才能调用 RecordsAffected。

可以在任意给定时间打开多个 OracleDataReader 。

以下两个Visual Basic示例演示如何使用 OracleDataReader 检索 Oracle REF CURSOR。 这些示例使用 Oracle Scott/Tiger 架构中定义的表，并且需要以下 PL/SQL 包和包正文。 必须在服务器上创建这些示例才能使用示例。

在 Oracle 服务器上创建以下 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;
/

在 Oracle 服务器上创建以下 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;
/

本Visual Basic示例执行 PL/SQL 存储过程，该存储过程返回 REF CURSOR 参数，并将值读取为 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

本Visual Basic示例执行 PL/SQL 存储过程，该存储过程返回两个 REF CURSOR 参数，并使用 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

此 C# 示例创建 Oracle 表并使用数据加载它。 在运行后续示例之前，必须运行此示例，该示例演示如何使用 OracleDataReader 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();
   }
}

此 C# 示例使用一个 OracleDataReader 访问数据，并使用多个 OracleType 结构来显示数据。

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