使用 DataReader 取出資料Retrieve data using a DataReader

若要使用DataReader來抓取資料,請建立command物件的實例,然後呼叫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.

下列範例說明如何使用DataReader,其中 reader 代表有效的 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. Read方法,從查詢結果取得資料列。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之具類型存取子方法的清單,請參閱 OleDbDataReaderSqlDataReaderFor 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.

注意

請勿在連接DataReader或您類別的Finalize方法中的任何其他 Managed 物件上呼叫CloseDisposeDo not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. 在完成項中,只需釋放類別直接擁有的 Unmanaged 資源。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

階層式資料列集或章節(OLE DB 類型DBTYPE_HCHAPTER,ADO 類型adChapter)可以使用來抓取 OleDbDataReaderHierarchical 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. 如需詳細資訊,請參閱dataset、datatable 和 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 資料指標傳回結果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 使用方法來抓取代表 ORACLE REF 資料指標的物件 ExecuteReaderYou can retrieve an OracleDataReader object that represents an Oracle REF CURSOR by using the ExecuteReader method. 您也可以指定 OracleCommand ,它會傳回一個或多個 ORACLE REF 資料SelectCommand指標做為 OracleDataAdapter 用來填滿之的 SelectCommand DataSetYou 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 為您的查詢建立,並將參考參考游標的輸出參數加入至的 Parameters 集合 OracleCommandTo 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. OracleCommand.ExecuteReader() 方法會 OracleCommand OracleDataReader 針對 REF 資料指標傳回。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. 您可以藉由呼叫方法來存取不同的 REF 資料指標 OracleCommand.ExecuteReader()You can access the different REF CURSORs by calling the OracleCommand.ExecuteReader() method. 對的呼叫會傳回 ExecuteReader() OracleDataReader 參考第一個 REF 資料指標的。The call to ExecuteReader() returns an OracleDataReader referencing the first REF CURSOR. 接著,您可以呼叫 OracleDataReader.NextResult() 方法來存取後續的 REF 資料指標。You can then call the OracleDataReader.NextResult() method to access subsequent REF CURSORs. 雖然集合中的參數 OracleCommand.Parameters 符合 REF CURSOR 輸出參數的名稱,但會依其 OracleDataReader 加入集合的順序來存取它們 ParametersAlthough 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 Package 和 Package 內容。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 ,它會將類型的兩個參數加入至集合,以傳回先前 Oracle 封裝的 REF 資料指標 OracleType.Cursor OracleCommand.ParametersThe 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() OracleDataReaderThe 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,建議您同時處理從 Oracle NUMBER 類型到有效 .NET Framework 類型的任何轉換,然後再將值儲存在中 DataRowTo 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