使用 DataReader 检索数据Retrieve data using a DataReader

若要使用DataReader检索数据,请创建命令对象的实例,然后通过调用ExecuteReader来创建DataReader ,以从数据源中检索行。To retrieve data using a DataReader, create an instance of the Command object, and then create a DataReader by calling Command.ExecuteReader to retrieve rows from a data source. DataReader提供了一个未缓冲的数据流,该数据流允许过程逻辑有效地按顺序处理数据源中的结果。The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. 当检索大量数据时, DataReader是一个不错的选择,因为数据不会缓存在内存中。The DataReader is a good choice when you're retrieving large amounts of data because the data is not cached in memory.

下面的示例阐释了如何使用DataReaderreader其中表示有效的 datareader command并表示有效的命令对象。The following example illustrates using a DataReader, where reader represents a valid DataReader and command represents a valid Command object.

reader = command.ExecuteReader();  
reader = command.ExecuteReader()

使用DataReader方法可从查询结果中获取行。Use the DataReader.Read method to obtain a row from the query results. 通过向DataReader传递列的名称或序号,可以访问返回行的每一列。You can access each column of the returned row by passing the name or ordinal number of the column to the DataReader. 但是,为获得最佳性能, DataReader提供一系列方法,使你可以访问其本机数据类型(GetDateTimeGetDoubleGetGuidGetInt32等)中的列值。However, for best performance, the DataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on). 有关特定于数据访问接口的datareader的类型化访问器方法的OleDbDataReader列表SqlDataReader,请参阅和。For a list of typed accessor methods for data provider-specific DataReaders, see OleDbDataReader and SqlDataReader. 当您知道基础数据类型时,使用类型化访问器方法可减少检索列值时所需的类型转换量。Using the typed accessor methods when you know the underlying data type reduces the amount of type conversion required when retrieving the column value.

下面的示例循环访问DataReader对象并返回每行中的两列。The following example iterates through a DataReader object and returns two columns from each row.

static void HasRows(SqlConnection connection)
{
    using (connection)
    {
        SqlCommand command = new SqlCommand(
          "SELECT CategoryID, CategoryName FROM Categories;",
          connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
                    reader.GetString(1));
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}
Private Sub HasRows(ByVal connection As SqlConnection)
    Using connection
        Dim command As SqlCommand = New SqlCommand( _
          "SELECT CategoryID, CategoryName FROM Categories;", _
          connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine(reader.GetInt32(0) _
                  & vbTab & reader.GetString(1))
            Loop
        Else
            Console.WriteLine("No rows found.")
        End If

        reader.Close()
    End Using
End Sub

关闭 DataReaderClosing the DataReader

当完成使用DataReader对象时,请始终调用Close方法。Always call the Close method when you have finished using the DataReader object.

如果你的命令包含输出参数或返回值,则在DataReader关闭之前,这些值将不可用。If your Command contains output parameters or return values, those values are not available until the DataReader is closed.

datareader打开时,该datareader以独占方式使用连接While a DataReader is open, the Connection is in use exclusively by that DataReader. 不能对连接执行任何命令,包括创建另一个datareader,直到原始DataReader关闭为止。You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.

备注

不要对类的Finalize方法中的连接DataReader或任何其他托管对象调用CloseDispose操作。Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. 在终结器中,仅释放类直接拥有的非托管资源。In a finalizer, only release unmanaged resources that your class owns directly. 如果类不拥有任何非托管资源,则不要在类定义中包括Finalize方法。If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. 有关详细信息,请参阅垃圾回收For more information, see Garbage Collection.

使用 NextResult 检索多个结果集Retrieving multiple result sets using NextResult

如果DataReader返回多个结果集,请调用NextResult方法以按顺序循环访问结果集。If the DataReader returns multiple result sets, call the NextResult method to iterate through the result sets sequentially. 以下示例显示 SqlDataReader 如何使用 ExecuteReader 方法处理两个 SELECT 语句的结果。The following example shows the SqlDataReader processing the results of two SELECT statements using the ExecuteReader method.

static void RetrieveMultipleResults(SqlConnection connection)
{
    using (connection)
    {
        SqlCommand command = new SqlCommand(
          "SELECT CategoryID, CategoryName FROM dbo.Categories;" +
          "SELECT EmployeeID, LastName FROM dbo.Employees",
          connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        while (reader.HasRows)
        {
            Console.WriteLine("\t{0}\t{1}", reader.GetName(0),
                reader.GetName(1));

            while (reader.Read())
            {
                Console.WriteLine("\t{0}\t{1}", reader.GetInt32(0),
                    reader.GetString(1));
            }
            reader.NextResult();
        }
    }
}
Private Sub RetrieveMultipleResults(ByVal connection As SqlConnection)
    Using connection
        Dim command As SqlCommand = New SqlCommand( _
          "SELECT CategoryID, CategoryName FROM Categories;" & _
          "SELECT EmployeeID, LastName FROM Employees", connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        Do While reader.HasRows
            Console.WriteLine(vbTab & reader.GetName(0) _
              & vbTab & reader.GetName(1))

            Do While reader.Read()
                Console.WriteLine(vbTab & reader.GetInt32(0) _
                  & vbTab & reader.GetString(1))
            Loop

            reader.NextResult()
        Loop
    End Using
End Sub

从 DataReader 获取架构信息Getting schema information from the DataReader

DataReader打开时,可以使用GetSchemaTable方法检索有关当前结果集的架构信息。While a DataReader is open, you can retrieve schema information about the current result set using the GetSchemaTable method. GetSchemaTable返回一个DataTable用行和列填充的对象,其中包含当前结果集的架构信息。GetSchemaTable returns a DataTable object populated with rows and columns that contain the schema information for the current result set. 对于结果集的每一列, DataTable都包含一行。The DataTable contains one row for each column of the result set. 架构表中的每一列都映射到在结果集的行中返回的列的属性,其中ColumnName是属性的名称,列的值是属性的值。Each column of the schema table maps to a property of the columns returned in the rows of the result set, where the ColumnName is the name of the property and the value of the column is the value of the property. 下面的示例写出DataReader的架构信息。The following example writes out the schema information for DataReader.

static void GetSchemaInfo(SqlConnection connection)
{
    using (connection)
    {
        SqlCommand command = new SqlCommand(
          "SELECT CategoryID, CategoryName FROM Categories;",
          connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();
        DataTable schemaTable = reader.GetSchemaTable();

        foreach (DataRow row in schemaTable.Rows)
        {
            foreach (DataColumn column in schemaTable.Columns)
            {
                Console.WriteLine(String.Format("{0} = {1}",
                   column.ColumnName, row[column]));
            }
        }
    }
}
Private Sub GetSchemaInfo(ByVal connection As SqlConnection)
    Using connection
        Dim command As SqlCommand = New SqlCommand( _
          "SELECT CategoryID, CategoryName FROM Categories;", _
          connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()
        Dim schemaTable As DataTable = reader.GetSchemaTable()

        Dim row As DataRow
        Dim column As DataColumn

        For Each row In schemaTable.Rows
            For Each column In schemaTable.Columns
                Console.WriteLine(String.Format("{0} = {1}", _
                  column.ColumnName, row(column)))
            Next
            Console.WriteLine()
        Next
        reader.Close()
    End Using
End Sub

使用 OLE DB 章节Working with OLE DB chapters

可以使用OleDbDataReader检索分层行集或章节(OLE DB 类型DBTYPE_HCHAPTER、ADO 类型 adChapter)。Hierarchical rowsets, or chapters (OLE DB type DBTYPE_HCHAPTER, ADO type adChapter), can be retrieved using the OleDbDataReader. 当包含某一章节的查询作为datareader返回时,该章节将作为该datareader中的列返回,并作为datareader对象公开。When a query that includes a chapter is returned as a DataReader, the chapter is returned as a column in that DataReader and is exposed as a DataReader object.

ADO.NET数据集还可用于通过使用表之间的父子关系来表示分层行集。The ADO.NET DataSet can also be used to represent hierarchical rowsets by using parent-child relationships between tables. 有关详细信息,请参阅数据集、数据表和 dataviewFor more information, see DataSets, DataTables, and DataViews.

以下代码示例使用 MSDataShape 提供程序来为客户列表中的每个客户生成订单的章节列。The following code example uses the MSDataShape Provider to generate a chapter column of orders for each customer in a list of customers.

Using connection As OleDbConnection = New OleDbConnection(
    "Provider=MSDataShape;Data Provider=SQLOLEDB;" &
    "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

    Using custCMD As OleDbCommand = New OleDbCommand(
        "SHAPE {SELECT CustomerID, CompanyName FROM Customers} " &
        "APPEND ({SELECT CustomerID, OrderID FROM Orders} AS CustomerOrders " &
        "RELATE CustomerID TO CustomerID)", connection)

        connection.Open()

        Using custReader As OleDbDataReader = custCMD.ExecuteReader()

            Do While custReader.Read()
                Console.WriteLine("Orders for " & custReader.GetString(1))
                ' custReader.GetString(1) = CompanyName  

                Using orderReader As OleDbDataReader = custReader.GetValue(2)
                    ' custReader.GetValue(2) = Orders chapter as DataReader  

                    Do While orderReader.Read()
                        Console.WriteLine(vbTab & orderReader.GetInt32(1))
                        ' orderReader.GetInt32(1) = OrderID  
                    Loop
                    orderReader.Close()
                End Using
            Loop
            ' Make sure to always close readers and connections.  
            custReader.Close()
        End Using
    End Using
End Using
using (OleDbConnection connection = new OleDbConnection(
    "Provider=MSDataShape;Data Provider=SQLOLEDB;" +
    "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"))
{
    using (OleDbCommand custCMD = new OleDbCommand(
        "SHAPE {SELECT CustomerID, CompanyName FROM Customers} " +
        "APPEND ({SELECT CustomerID, OrderID FROM Orders} AS CustomerOrders " +
        "RELATE CustomerID TO CustomerID)", connection))
    {
        connection.Open();

        using (OleDbDataReader custReader = custCMD.ExecuteReader())
        {

            while (custReader.Read())
            {
                Console.WriteLine("Orders for " + custReader.GetString(1));
                // custReader.GetString(1) = CompanyName  

                using (OleDbDataReader orderReader = (OleDbDataReader)custReader.GetValue(2))
                {
                    // custReader.GetValue(2) = Orders chapter as DataReader  

                    while (orderReader.Read())
                        Console.WriteLine("\t" + orderReader.GetInt32(1));
                    // orderReader.GetInt32(1) = OrderID  
                    orderReader.Close();
                }
            }
            // Make sure to always close readers and connections.  
            custReader.Close();
        }
    }
}

用 Oracle REF cursor 返回结果Returning results with Oracle REF CURSORs

Oracle .NET Framework 数据提供程序支持使用 Oracle REF CURSOR 返回查询结果。The .NET Framework Data Provider for Oracle supports the use of Oracle REF CURSORs to return a query result. Oracle REF CURSOR 以 OracleDataReader 的形式返回。An Oracle REF CURSOR is returned as an OracleDataReader.

您可以OracleDataReader ExecuteReader使用方法检索表示 Oracle REF CURSOR 的对象。You can retrieve an OracleDataReader object that represents an Oracle REF CURSOR by using the ExecuteReader method. 还可以指定OracleCommand返回一个或多个 Oracle REF cursor 的, OracleDataAdapter作为用于填充DataSet的的SelectCommandYou can also specify an OracleCommand that returns one or more Oracle REF CURSORs as the SelectCommand for an OracleDataAdapter used to fill a DataSet.

若要访问从 Oracle 数据源返回的 REF CURSOR,请OracleCommand为查询创建,并将引用该引用光标的 output 参数添加Parameters到的集合OracleCommand中。To access a REF CURSOR returned from an Oracle data source, create an OracleCommand for your query and add an output parameter that references the REF CURSOR to the Parameters collection of your OracleCommand. 该参数的名称必须与查询中的 REF CURSOR 参数名称相匹配。The name of the parameter must match the name of the REF CURSOR parameter in your query. 将参数的类型设置为OracleType.CursorSet the type of the parameter to OracleType.Cursor. 的方法为 REF CURSOR OracleDataReader返回一个。 OracleCommand OracleCommand.ExecuteReader()The OracleCommand.ExecuteReader() method of your OracleCommand returns an OracleDataReader for the REF CURSOR.

OracleCommand如果返回多个 REF 游标,请添加多个输出参数。If your OracleCommand returns multiple REF CURSORS, add multiple output parameters. 可以通过调用OracleCommand.ExecuteReader()方法来访问不同的 REF cursor。You can access the different REF CURSORs by calling the OracleCommand.ExecuteReader() method. ExecuteReader()的调用将OracleDataReader返回引用第一个引用游标的。The call to ExecuteReader() returns an OracleDataReader referencing the first REF CURSOR. 然后,可以调用OracleDataReader.NextResult()方法来访问后续的 REF cursor。You can then call the OracleDataReader.NextResult() method to access subsequent REF CURSORs. 尽管OracleCommand.Parameters集合中的参数与 REF CURSOR 输出参数按名称匹配OracleDataReader ,但Parameters会按照它们添加到集合中的顺序来访问这些参数。Although the parameters in your OracleCommand.Parameters collection match the REF CURSOR output parameters by name, the OracleDataReader accesses them in the order in which they were added to the Parameters collection.

例如,请看下面这个 Oracle 包和包正文。For example, consider the following Oracle package and package body.

CREATE OR REPLACE PACKAGE CURSPKG AS   
  TYPE T_CURSOR IS REF CURSOR;   
  PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,   
    DEPTCURSOR OUT T_CURSOR);   
END CURSPKG;  
  
CREATE OR REPLACE PACKAGE BODY CURSPKG AS   
  PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,   
    DEPTCURSOR OUT T_CURSOR)   
  IS   
  BEGIN   
    OPEN EMPCURSOR FOR SELECT * FROM DEMO.EMPLOYEE;   
    OPEN DEPTCURSOR FOR SELECT * FROM DEMO.DEPARTMENT;   
  END OPEN_TWO_CURSORS;   
END CURSPKG;   

下面的代码创建一个OracleCommand ,它通过将类型OracleType.Cursor的两个参数添加到OracleCommand.Parameters集合中,从以前的 Oracle 包返回 REF cursor。The following code creates an OracleCommand that returns the REF CURSORs from the previous Oracle package by adding two parameters of type OracleType.Cursor to the OracleCommand.Parameters collection.

Dim cursCmd As OracleCommand = New OracleCommand("CURSPKG.OPEN_TWO_CURSORS", oraConn)  
cursCmd.Parameters.Add("EMPCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output  
cursCmd.Parameters.Add("DEPTCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output  
OracleCommand cursCmd = new OracleCommand("CURSPKG.OPEN_TWO_CURSORS", oraConn);  
cursCmd.Parameters.Add("EMPCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;  
cursCmd.Parameters.Add("DEPTCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;  

下面的代码使用Read()的和NextResult()方法OracleDataReader返回前一个命令的结果。The following code returns the results of the previous command using the Read() and NextResult() methods of the OracleDataReader. REF CURSOR 参数按顺序返回。The REF CURSOR parameters are returned in order.

oraConn.Open()  
  
Dim cursCmd As OracleCommand = New OracleCommand("CURSPKG.OPEN_TWO_CURSORS", oraConn)  
cursCmd.CommandType = CommandType.StoredProcedure  
cursCmd.Parameters.Add("EMPCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output  
cursCmd.Parameters.Add("DEPTCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output  
  
Dim reader As OracleDataReader = cursCmd.ExecuteReader()  
  
Console.WriteLine(vbCrLf & "Emp ID" & vbTab & "Name")  
  
Do While reader.Read()  
  Console.WriteLine("{0}" & vbTab & "{1}, {2}", reader.GetOracleNumber(0), reader.GetString(1), reader.GetString(2))  
Loop  
  
reader.NextResult()  
  
Console.WriteLine(vbCrLf & "Dept ID" & vbTab & "Name")  
  
Do While reader.Read()  
  Console.WriteLine("{0}" & vbTab & "{1}", reader.GetOracleNumber(0), reader.GetString(1))  
Loop  
' Make sure to always close readers and connections.  
reader.Close()  
oraConn.Close()  
oraConn.Open();  
  
OracleCommand cursCmd = new OracleCommand("CURSPKG.OPEN_TWO_CURSORS", oraConn);  
cursCmd.CommandType = CommandType.StoredProcedure;  
cursCmd.Parameters.Add("EMPCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;  
cursCmd.Parameters.Add("DEPTCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;  
  
OracleDataReader reader = cursCmd.ExecuteReader();  
  
Console.WriteLine("\nEmp ID\tName");  
  
while (reader.Read())  
  Console.WriteLine("{0}\t{1}, {2}", reader.GetOracleNumber(0), reader.GetString(1), reader.GetString(2));  
  
reader.NextResult();  
  
Console.WriteLine("\nDept ID\tName");  
  
while (reader.Read())  
  Console.WriteLine("{0}\t{1}", reader.GetOracleNumber(0), reader.GetString(1));  
// Make sure to always close readers and connections.  
reader.Close();  
oraConn.Close();  

下面的示例使用上一个命令, DataSet用 Oracle 包的结果填充。The following example uses the previous command to populate a DataSet with the results of the Oracle package.

Dim ds As DataSet = New DataSet()  
  
Dim adapter As OracleDataAdapter = New OracleDataAdapter(cursCmd)  
adapter.TableMappings.Add("Table", "Employees")  
adapter.TableMappings.Add("Table1", "Departments")  
  
adapter.Fill(ds)  
DataSet ds = new DataSet();  
  
OracleDataAdapter adapter = new OracleDataAdapter(cursCmd);  
adapter.TableMappings.Add("Table", "Employees");  
adapter.TableMappings.Add("Table1", "Departments");  
  
adapter.Fill(ds);  

备注

为了避免OverflowException,我们建议你还在将DataRow值存储在中之前,先处理从 Oracle NUMBER 类型到有效 .NET Framework 类型的任何转换。To avoid an OverflowException, we recommend that you also handle any conversion from the Oracle NUMBER type to a valid .NET Framework type before storing the value in a DataRow. 您可以使用FillError事件来确定是否发生了OverflowExceptionYou can use the FillError event to determine if an OverflowException has occurred. 有关FillError事件的详细信息,请参阅处理 DataAdapter 事件For more information on the FillError event, see Handling DataAdapter Events.

请参阅See also