使用資料讀取器擷取資料

它 SqlDataReader 從 SQL 資料庫讀取一串只能向前的結果序列。 它不會將完整結果載入到 DataSet 中,因此適合在資料列抵達時處理。

本文範例假設已開啟且名為 connection 的 SqlConnection 目標為 AdventureWorks 範例資料庫。

C# 範例需要 using Microsoft.Data.SqlClient;。 使用 SqlDbType 或 CommandBehavior 的範例也需要 using System.Data;。

已讀取資料列

基本閱讀工作流程如下:

  1. 使用 ExecuteReader 或 ExecuteReaderAsync 執行一個指令。
  2. 解析當前結果的資料行序號。
  3. 每列都呼叫 Read 一次。
  4. 在呼叫型別 getter 前,先以 IsDBNull 檢查可為 null 的資料行。
  5. 在重複使用連線或讀取輸出參數前,先丟棄讀取器。

此範例快取欄位序數,使用型別的 getter,並檢查可為 Null 的 Color 欄位:

using var command = new SqlCommand("""
    SELECT ProductID, Name, Color
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
    ORDER BY ProductID;
    """, connection);
command.Parameters.Add("@subcategoryId", SqlDbType.Int).Value = 2;

using SqlDataReader reader = command.ExecuteReader();
int idOrdinal = reader.GetOrdinal("ProductID");
int nameOrdinal = reader.GetOrdinal("Name");
int colorOrdinal = reader.GetOrdinal("Color");

while (reader.Read())
{
    int id = reader.GetInt32(idOrdinal);
    string name = reader.GetString(nameOrdinal);
    string? color = reader.IsDBNull(colorOrdinal)
        ? null
        : reader.GetString(colorOrdinal);

    Console.WriteLine($"{id} | {name} | {color ?? "(no color)"}");
}

呼叫NULL某個SqlNullValueException值會拋出IsDBNull,所以先檢查GetString。

HasRows 報告目前結果是否包含列,但通常你可以直接呼叫 Read 。 若結果為空,第一個 Read 呼叫會回傳 false:

using var command = new SqlCommand("""
    SELECT ProductID
    FROM Production.Product
    WHERE ProductID = @productId;
    """, connection);
command.Parameters.Add("@productId", SqlDbType.Int).Value = -1;

using SqlDataReader reader = command.ExecuteReader();
Console.WriteLine($"HasRows: {reader.HasRows}");
Console.WriteLine($"First Read: {reader.Read()}");

對於較大的值,請使用 CommandBehavior.SequentialAccess 執行,並使用 GetStream、GetTextReader 或 GetXmlReader。 欲了解更多資訊,請參閱 SqlClient 串流支援。

關閉 DataReader

處理完成後立即釋放讀取器。 即使資料列處理拋出,await using 或 using 陳述式也會將其關閉。

輸出參數、輸入/輸出參數及儲存程序回傳值在讀取器關閉之前,都不算最終值:

using var command = new SqlCommand("""
    SELECT TOP (5) ProductID, Name
    FROM Production.Product
    ORDER BY ProductID;
    SET @count = @@ROWCOUNT;
    """, connection);

SqlParameter count = command.Parameters.Add("@count", SqlDbType.Int);
count.Direction = ParameterDirection.Output;

using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        Console.WriteLine(reader.GetString(1));
    }
}

Console.WriteLine($"Rows returned: {count.Value}");

若沒有多個主動結果集(MARS),開啟的讀取器會讓連線保持忙碌,導致該連線無法執行其他指令。 啟用 MARS 後,不同指令可能在同一個連線上有待處理結果,但它們的操作仍非執行緒安全。 欲了解更多資訊,請參閱 多個活躍結果集。

如果已傳入 CommandBehavior.CloseConnection 命令,關閉讀取器也會關閉連線。 否則,開啟連線的程式碼仍需負責釋放該連線。

讀取受影響的資料列數

RecordsAffected 回報命令中由 INSERT、UPDATE 和 DELETE 陳述式所變更的列數。 請在讀者關閉後再讀。 此範例會復原其更新:

using SqlTransaction transaction = connection.BeginTransaction();
using var command = new SqlCommand("""
    UPDATE Production.Product
    SET ModifiedDate = ModifiedDate
    WHERE ProductID IN (706, 707);

    SELECT ProductID, Name
    FROM Production.Product
    WHERE ProductID IN (706, 707)
    ORDER BY ProductID;
    """, connection, transaction);

SqlDataReader reader = command.ExecuteReader();
using (reader)
{
    while (reader.Read())
    {
        Console.WriteLine($"{reader.GetInt32(0)} | {reader.GetString(1)}");
    }
}

Console.WriteLine($"RecordsAffected: {reader.RecordsAffected}");
transaction.Rollback();

對於僅限 SELECT 的命令,-1 是 RecordsAffected。 SET NOCOUNT ON 不顯示計數,也使值保持在 -1。

使用 NextResult 檢索多個結果集

一個指令或儲存程序可以回傳多個結果。 完整處理目前結果,然後呼叫 NextResult 或 NextResultAsync:

using var command = new SqlCommand("""
    SELECT TOP (3) ProductID, Name
    FROM Production.Product
    ORDER BY ProductID;

    SELECT COUNT(*) AS ProductCount
    FROM Production.Product;
    """, connection);

using SqlDataReader reader = command.ExecuteReader();
int result = 1;

do
{
    Console.WriteLine($"Result {result++}: {reader.FieldCount} column(s)");
    while (reader.Read())
    {
        var values = new object[reader.FieldCount];
        reader.GetValues(values);
        Console.WriteLine(string.Join(" | ", values));
    }
}
while (reader.NextResult());

每個結果的欄位數量、名稱、序數和類型都會有所變動。 在 true 返回 NextResult 後重新計算快取的序數。

同一讀取器同一時間只能有一個非同步操作待處理。 在同一讀取器上開始另一個作業之前,請先等候 ReadAsync、NextResultAsync 或非同步欄位作業完成。

從 DataReader 取得結構描述資訊

呼叫 GetColumnSchema 以取得目前結果資料行的強型別描述。 當現有程式碼需要在 GetSchemaTable 中使用該結構描述時,呼叫 DataTable:

using var command = new SqlCommand("""
    SELECT ProductID, Name, Color
    FROM Production.Product;
    """, connection);

using SqlDataReader reader = command.ExecuteReader(
    CommandBehavior.SchemaOnly);

foreach (System.Data.Common.DbColumn column in reader.GetColumnSchema())
{
    Console.WriteLine(
        $"{column.ColumnOrdinal}: {column.ColumnName} " +
        $"({column.DataTypeName}), nullable={column.AllowDBNull}");
}

CommandBehavior.SchemaOnly 請求資料行中繼資料,但不含資料列。 結構描述資訊描述目前的結果集。 進入下一個結果後再次要求。 根據查詢與伺服器權限,部分基表元資料可能無法取得。