OracleDataReader 类
提供从数据源读取数据行的只进流的方法。无法继承此类。
**命名空间:**System.Data.OracleClient
**程序集:**System.Data.OracleClient(在 system.data.oracleclient.dll 中)
语法
声明
Public NotInheritable Class OracleDataReader
Inherits DbDataReader
用法
Dim instance As OracleDataReader
public sealed class OracleDataReader : DbDataReader
public ref class OracleDataReader sealed : public DbDataReader
public final class OracleDataReader extends DbDataReader
public final class OracleDataReader extends DbDataReader
备注
若要创建 OracleDataReader,必须调用 OracleCommand 对象的 ExecuteReader 方法,而不能直接使用构造函数。
OracleDataReader 的用户可能会看到在读取数据时另一进程或线程对结果集所做的更改。
当 OracleDataReader 关闭后,只能调用 IsClosed 和 RecordsAffected 属性。有些情况下,必须先调用 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 示例执行返回 REF CURSOR 参数的 PL/SQL 存储过程,并将值作为 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 示例执行返回两个 REF CURSOR 参数的 PL/SQL 存储过程,并使用 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();
}
}
示例
下面的示例创建一个 OracleConnection、一个 OracleCommand 和一个 OracleDataReader。该示例读取全部数据,并将这些数据写到控制台。最后,该示例先关闭 OracleDataReader,然后关闭 OracleConnection。
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()
Dim reader As OracleDataReader
reader = command.ExecuteReader()
' Always call Read before accessing data.
While reader.Read()
Console.WriteLine(reader.GetInt32(0).ToString() + ", " _
+ reader.GetString(1))
End While
' always call Close when done reading.
reader.Close()
End Using
End Sub
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();
OracleDataReader reader;
reader = command.ExecuteReader();
// Always call Read before accessing data.
while (reader.Read())
{
Console.WriteLine(reader.GetInt32(0) + ", " + reader.GetString(1));
}
// always call Close when done reading.
reader.Close();
}
}
继承层次结构
System.Object
System.MarshalByRefObject
System.Data.Common.DbDataReader
System.Data.OracleClient.OracleDataReader
线程安全
此类型的任何公共静态(Visual Basic 中的 Shared)成员都是线程安全的,但不保证所有实例成员都是线程安全的。
平台
Windows 98、Windows 2000 SP4、Windows Millennium Edition、Windows Server 2003、Windows XP Media Center Edition、Windows XP Professional x64 Edition、Windows XP SP2、Windows XP Starter Edition
.NET Framework 并不是对每个平台的所有版本都提供支持。有关受支持版本的列表,请参见系统要求。
版本信息
.NET Framework
受以下版本支持:2.0、1.1