SqlDataReader SqlDataReader SqlDataReader SqlDataReader Class

定義

提供從 SQL Server 資料庫中讀取順向資料流資料列的方式。Provides a way of reading a forward-only stream of rows from a SQL Server database. 這個類別無法被繼承。This class cannot be inherited.

public ref class SqlDataReader : System::Data::Common::DbDataReader, IDisposable
public class SqlDataReader : System.Data.Common.DbDataReader, IDisposable
type SqlDataReader = class
    inherit DbDataReader
    interface IDataReader
    interface IDisposable
    interface IDataRecord
Public Class SqlDataReader
Inherits DbDataReader
Implements IDisposable
繼承
實作

範例

下列範例會建立SqlConnection,則SqlCommand,和SqlDataReaderThe following example creates a SqlConnection, a SqlCommand, and a SqlDataReader. 此範例會讀取資料,並將它寫出至主控台視窗。The example reads through the data, writing it out to the console window. 程式碼接著會關閉SqlDataReaderThe code then closes the SqlDataReader. SqlConnection結尾的自動關閉using程式碼區塊。The SqlConnection is closed automatically at the end of the using code block.

using System;
using System.Data;
using System.Data.SqlClient;


class Program
{
    static void Main()
    {
        string str = "Data Source=(local);Initial Catalog=Northwind;"
            + "Integrated Security=SSPI";
        ReadOrderData(str);
    }

    private static void ReadOrderData(string connectionString)
    {
        string queryString =
            "SELECT OrderID, CustomerID FROM dbo.Orders;";

        using (SqlConnection connection =
                   new SqlConnection(connectionString))
        {
            SqlCommand command =
                new SqlCommand(queryString, connection);
            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            // Call Read before accessing data.
            while (reader.Read())
            {
                ReadSingleRow((IDataRecord)reader);
            }

            // Call Close when done reading.
            reader.Close();
        }
    }

    private static void ReadSingleRow(IDataRecord record)
    {
        Console.WriteLine(String.Format("{0}, {1}", record[0], record[1]));
    }

}
Option Explicit On
Option Strict On

Imports System.Data
Imports System.Data.SqlClient

Module Module1

    Sub Main()
        Dim str As String = "Data Source=(local);Initial Catalog=Northwind;" _
       & "Integrated Security=SSPI;"
        ReadOrderData(str)
    End Sub

    Private Sub ReadOrderData(ByVal connectionString As String)
        Dim queryString As String = _
            "SELECT OrderID, CustomerID FROM dbo.Orders;"

        Using connection As New SqlConnection(connectionString)
            Dim command As New SqlCommand(queryString, connection)
            connection.Open()

            Dim reader As SqlDataReader = command.ExecuteReader()

            ' Call Read before accessing data.
            While reader.Read()
                ReadSingleRow(CType(reader, IDataRecord))
            End While

            ' Call Close when done reading.
            reader.Close()
        End Using
    End Sub

    Private Sub ReadSingleRow(ByVal record As IDataRecord)
       Console.WriteLine(String.Format("{0}, {1}", record(0), record(1)))

    End Sub

End Module

備註

若要建立SqlDataReader,您必須呼叫ExecuteReader方法SqlCommand物件,而不是直接使用建構函式。To create a SqlDataReader, you must call the ExecuteReader method of the SqlCommand object, instead of directly using a constructor.

雖然SqlDataReader正在使用中,相關聯SqlConnection忙於處理SqlDataReader,並可對任何其他作業SqlConnection以外關閉它。While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. 此情況下,直到Close方法的SqlDataReader呼叫。This is the case until the Close method of the SqlDataReader is called. 比方說,您無法擷取輸出參數,直到您呼叫之後CloseFor example, you cannot retrieve output parameters until after you call Close.

結果集,另一個處理序或執行緒正在讀取資料時所做的變更可能會顯示給使用者,是SqlDataReaderChanges made to a result set by another process or thread while data is being read may be visible to the user of the SqlDataReader. 不過,精確的行為取決於時間點。However, the precise behavior is timing dependent.

IsClosedRecordsAffected是您可以呼叫之後的唯一屬性SqlDataReader已關閉。IsClosed and RecordsAffected are the only properties that you can call after the SqlDataReader is closed. 雖然RecordsAffected可能會存取屬性雖然SqlDataReader存在,一定要呼叫Close傳回的值之前RecordsAffected以確保正確的傳回值。Although the RecordsAffected property may be accessed while the SqlDataReader exists, always call Close before returning the value of RecordsAffected to guarantee an accurate return value.

使用循序存取時 (CommandBehavior.SequentialAccess),則InvalidOperationException若系統將產生SqlDataReader位置進階和另一個讀取上一個資料行上嘗試執行作業。When using sequential access (CommandBehavior.SequentialAccess), an InvalidOperationException will be raised if the SqlDataReader position is advanced and another read operation is attempted on the previous column.

注意

為了達到最佳效能,SqlDataReader可避免建立不必要的物件,或進行不必要資料的複本。For optimal performance, SqlDataReader avoids creating unnecessary objects or making unnecessary copies of data. 因此,多個呼叫方法這類GetValue傳回相同物件的參考。Therefore, multiple calls to methods such as GetValue return a reference to the same object. 如果您要修改這類方法所傳回的物件的基礎值特別小心GetValueUse caution if you are modifying the underlying value of the objects returned by methods such as GetValue.

屬性

Connection Connection Connection Connection

取得與 SqlConnection 相關聯的 SqlDataReaderGets the SqlConnection associated with the SqlDataReader.

Depth Depth Depth Depth

取得值,表示目前資料列的巢狀深度。Gets a value that indicates the depth of nesting for the current row.

FieldCount FieldCount FieldCount FieldCount

取得目前資料列中的資料行數目。Gets the number of columns in the current row.

HasRows HasRows HasRows HasRows

取得值,指出 SqlDataReader 是否包含一個或多個資料列。Gets a value that indicates whether the SqlDataReader contains one or more rows.

IsClosed IsClosed IsClosed IsClosed

擷取布林值,指出指定的 SqlDataReader 執行個體是否已關閉。Retrieves a Boolean value that indicates whether the specified SqlDataReader instance has been closed.

Item[Int32] Item[Int32] Item[Int32] Item[Int32]

提供資料行序數,取得使用原生格式的指定資料行值。Gets the value of the specified column in its native format given the column ordinal.

Item[String] Item[String] Item[String] Item[String]

提供資料行名稱,取得使用原生格式的指定資料行值。Gets the value of the specified column in its native format given the column name.

RecordsAffected RecordsAffected RecordsAffected RecordsAffected

取得 Transact-SQL 陳述式的執行所變更、插入或刪除的資料列數目。Gets the number of rows changed, inserted, or deleted by execution of the Transact-SQL statement.

VisibleFieldCount VisibleFieldCount VisibleFieldCount VisibleFieldCount

取得 SqlDataReader 中沒有隱藏的欄位數。Gets the number of fields in the SqlDataReader that are not hidden.

方法

Close() Close() Close() Close()

關閉 SqlDataReader 物件。Closes the SqlDataReader object.

CreateObjRef(Type) CreateObjRef(Type) CreateObjRef(Type) CreateObjRef(Type)

建立包含所有相關資訊的物件,這些資訊是產生用來與遠端物件通訊的所需 Proxy。Creates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object.

(Inherited from MarshalByRefObject)
Dispose(Boolean) Dispose(Boolean) Dispose(Boolean) Dispose(Boolean)
Equals(Object) Equals(Object) Equals(Object) Equals(Object)

判斷指定的物件是否等於目前的物件。Determines whether the specified object is equal to the current object.

(Inherited from Object)
GetBoolean(Int32) GetBoolean(Int32) GetBoolean(Int32) GetBoolean(Int32)

取得指定之資料行的值做為布林值 (Boolean)。Gets the value of the specified column as a Boolean.

GetByte(Int32) GetByte(Int32) GetByte(Int32) GetByte(Int32)

取得指定資料行的值做為位元組。Gets the value of the specified column as a byte.

GetBytes(Int32, Int64, Byte[], Int32, Int32) GetBytes(Int32, Int64, Byte[], Int32, Int32) GetBytes(Int32, Int64, Byte[], Int32, Int32) GetBytes(Int32, Int64, Byte[], Int32, Int32)

從指定的資料行位移將字元的資料流讀入做為陣列的緩衝區,開始於指定的緩衝區位移。Reads a stream of bytes from the specified column offset into the buffer an array starting at the given buffer offset.

GetChar(Int32) GetChar(Int32) GetChar(Int32) GetChar(Int32)

取得指定的資料行值做為單一字元。Gets the value of the specified column as a single character.

GetChars(Int32, Int64, Char[], Int32, Int32) GetChars(Int32, Int64, Char[], Int32, Int32) GetChars(Int32, Int64, Char[], Int32, Int32) GetChars(Int32, Int64, Char[], Int32, Int32)

從指定的資料行位移將字元的資料流讀入做為陣列的緩衝區,開始於指定的緩衝區位移。Reads a stream of characters from the specified column offset into the buffer as an array starting at the given buffer offset.

GetColumnSchema() GetColumnSchema() GetColumnSchema() GetColumnSchema()
GetData(Int32) GetData(Int32) GetData(Int32) GetData(Int32)

傳回指定之資料行序數的 IDataReaderReturns an IDataReader for the specified column ordinal.

GetDataTypeName(Int32) GetDataTypeName(Int32) GetDataTypeName(Int32) GetDataTypeName(Int32)

取得字串,表示指定之資料行的資料型別。Gets a string representing the data type of the specified column.

GetDateTime(Int32) GetDateTime(Int32) GetDateTime(Int32) GetDateTime(Int32)

取得指定之資料行的值做為 DateTime 物件。Gets the value of the specified column as a DateTime object.

GetDateTimeOffset(Int32) GetDateTimeOffset(Int32) GetDateTimeOffset(Int32) GetDateTimeOffset(Int32)

擷取指定資料行的值做為 DateTimeOffset 物件。Retrieves the value of the specified column as a DateTimeOffset object.

GetDecimal(Int32) GetDecimal(Int32) GetDecimal(Int32) GetDecimal(Int32)

取得指定之資料行的值做為 Decimal 物件。Gets the value of the specified column as a Decimal object.

GetDouble(Int32) GetDouble(Int32) GetDouble(Int32) GetDouble(Int32)

取得指定資料行的值做為雙精確度浮點數。Gets the value of the specified column as a double-precision floating point number.

GetEnumerator() GetEnumerator() GetEnumerator() GetEnumerator()

傳回透過 IEnumerator 重複的 SqlDataReaderReturns an IEnumerator that iterates through the SqlDataReader.

GetFieldType(Int32) GetFieldType(Int32) GetFieldType(Int32) GetFieldType(Int32)

取得 Type,即是物件的資料型別。Gets the Type that is the data type of the object.

GetFieldValue<T>(Int32) GetFieldValue<T>(Int32) GetFieldValue<T>(Int32) GetFieldValue<T>(Int32)

同步取得指定之資料行的值做為型別。Synchronously gets the value of the specified column as a type. GetFieldValueAsync<T>(Int32, CancellationToken) 是這個方法的非同步版本。GetFieldValueAsync<T>(Int32, CancellationToken) is the asynchronous version of this method.

GetFieldValueAsync<T>(Int32, CancellationToken) GetFieldValueAsync<T>(Int32, CancellationToken) GetFieldValueAsync<T>(Int32, CancellationToken) GetFieldValueAsync<T>(Int32, CancellationToken)

非同步取得指定之資料行的值做為型別。Asynchronously gets the value of the specified column as a type. GetFieldValue<T>(Int32) 是這個方法的同步版本。GetFieldValue<T>(Int32) is the synchronous version of this method.

GetFloat(Int32) GetFloat(Int32) GetFloat(Int32) GetFloat(Int32)

取得指定資料行的值做為單精確度浮點數。Gets the value of the specified column as a single-precision floating point number.

GetGuid(Int32) GetGuid(Int32) GetGuid(Int32) GetGuid(Int32)

取得指定資料行的值做為全域唯一識別項 (GUID)。Gets the value of the specified column as a globally unique identifier (GUID).

GetHashCode() GetHashCode() GetHashCode() GetHashCode()

做為預設雜湊函式。Serves as the default hash function.

(Inherited from Object)
GetInt16(Int32) GetInt16(Int32) GetInt16(Int32) GetInt16(Int32)

取得指定資料行的值做為 16 位元帶正負號的整數。Gets the value of the specified column as a 16-bit signed integer.

GetInt32(Int32) GetInt32(Int32) GetInt32(Int32) GetInt32(Int32)

取得指定資料行的值作為 32 位元帶正負號的整數。Gets the value of the specified column as a 32-bit signed integer.

GetInt64(Int32) GetInt64(Int32) GetInt64(Int32) GetInt64(Int32)

取得指定資料行的值做為 64 位元帶正負號的整數 (Signed Integer)。Gets the value of the specified column as a 64-bit signed integer.

GetLifetimeService() GetLifetimeService() GetLifetimeService() GetLifetimeService()

擷取控制這個執行個體存留期 (Lifetime) 原則的目前存留期服務物件。Retrieves the current lifetime service object that controls the lifetime policy for this instance.

(Inherited from MarshalByRefObject)
GetName(Int32) GetName(Int32) GetName(Int32) GetName(Int32)

取得指定資料行的名稱。Gets the name of the specified column.

GetOrdinal(String) GetOrdinal(String) GetOrdinal(String) GetOrdinal(String)

提供資料行的名稱,取得資料行序數。Gets the column ordinal, given the name of the column.

GetProviderSpecificFieldType(Int32) GetProviderSpecificFieldType(Int32) GetProviderSpecificFieldType(Int32) GetProviderSpecificFieldType(Int32)

取得 Object,其為基礎提供者特定欄位型別的表示。Gets an Object that is a representation of the underlying provider-specific field type.

GetProviderSpecificValue(Int32) GetProviderSpecificValue(Int32) GetProviderSpecificValue(Int32) GetProviderSpecificValue(Int32)

取得 Object,其為基礎提供者特定值的表示。Gets an Object that is a representation of the underlying provider specific value.

GetProviderSpecificValues(Object[]) GetProviderSpecificValues(Object[]) GetProviderSpecificValues(Object[]) GetProviderSpecificValues(Object[])

取得物件的陣列,這些物件是基礎提供者特定值的表示。Gets an array of objects that are a representation of the underlying provider specific values.

GetSchemaTable() GetSchemaTable() GetSchemaTable() GetSchemaTable()

傳回 DataTable,以描述 SqlDataReader 的資料行中繼資料。Returns a DataTable that describes the column metadata of the SqlDataReader.

GetSqlBinary(Int32) GetSqlBinary(Int32) GetSqlBinary(Int32) GetSqlBinary(Int32)

取得指定的資料行值做為 SqlBinaryGets the value of the specified column as a SqlBinary.

GetSqlBoolean(Int32) GetSqlBoolean(Int32) GetSqlBoolean(Int32) GetSqlBoolean(Int32)

取得指定的資料行值做為 SqlBooleanGets the value of the specified column as a SqlBoolean.

GetSqlByte(Int32) GetSqlByte(Int32) GetSqlByte(Int32) GetSqlByte(Int32)

取得指定的資料行值做為 SqlByteGets the value of the specified column as a SqlByte.

GetSqlBytes(Int32) GetSqlBytes(Int32) GetSqlBytes(Int32) GetSqlBytes(Int32)

取得指定資料行的值做為 SqlBytesGets the value of the specified column as SqlBytes.

GetSqlChars(Int32) GetSqlChars(Int32) GetSqlChars(Int32) GetSqlChars(Int32)

取得指定資料行的值做為 SqlCharsGets the value of the specified column as SqlChars.

GetSqlDateTime(Int32) GetSqlDateTime(Int32) GetSqlDateTime(Int32) GetSqlDateTime(Int32)

取得指定的資料行值做為 SqlDateTimeGets the value of the specified column as a SqlDateTime.

GetSqlDecimal(Int32) GetSqlDecimal(Int32) GetSqlDecimal(Int32) GetSqlDecimal(Int32)

取得指定的資料行值做為 SqlDecimalGets the value of the specified column as a SqlDecimal.

GetSqlDouble(Int32) GetSqlDouble(Int32) GetSqlDouble(Int32) GetSqlDouble(Int32)

取得指定的資料行值做為 SqlDoubleGets the value of the specified column as a SqlDouble.

GetSqlGuid(Int32) GetSqlGuid(Int32) GetSqlGuid(Int32) GetSqlGuid(Int32)

取得指定的資料行值做為 SqlGuidGets the value of the specified column as a SqlGuid.

GetSqlInt16(Int32) GetSqlInt16(Int32) GetSqlInt16(Int32) GetSqlInt16(Int32)

取得指定的資料行值做為 SqlInt16Gets the value of the specified column as a SqlInt16.

GetSqlInt32(Int32) GetSqlInt32(Int32) GetSqlInt32(Int32) GetSqlInt32(Int32)

取得指定的資料行值做為 SqlInt32Gets the value of the specified column as a SqlInt32.

GetSqlInt64(Int32) GetSqlInt64(Int32) GetSqlInt64(Int32) GetSqlInt64(Int32)

取得指定的資料行值做為 SqlInt64Gets the value of the specified column as a SqlInt64.

GetSqlMoney(Int32) GetSqlMoney(Int32) GetSqlMoney(Int32) GetSqlMoney(Int32)

取得指定的資料行值做為 SqlMoneyGets the value of the specified column as a SqlMoney.

GetSqlSingle(Int32) GetSqlSingle(Int32) GetSqlSingle(Int32) GetSqlSingle(Int32)

取得指定的資料行值做為 SqlSingleGets the value of the specified column as a SqlSingle.

GetSqlString(Int32) GetSqlString(Int32) GetSqlString(Int32) GetSqlString(Int32)

取得指定的資料行值做為 SqlStringGets the value of the specified column as a SqlString.

GetSqlValue(Int32) GetSqlValue(Int32) GetSqlValue(Int32) GetSqlValue(Int32)

將指定之資料行內的資料值當做 SQL Server 型別傳回。Returns the data value in the specified column as a SQL Server type.

GetSqlValues(Object[]) GetSqlValues(Object[]) GetSqlValues(Object[]) GetSqlValues(Object[])

填滿 Object 陣列,其中包含記錄內所有資料行 (表示為 SQL Server 型別) 的值。Fills an array of Object that contains the values for all the columns in the record, expressed as SQL Server types.

GetSqlXml(Int32) GetSqlXml(Int32) GetSqlXml(Int32) GetSqlXml(Int32)

取得指定資料行的值做為 XML 值。Gets the value of the specified column as an XML value.

GetStream(Int32) GetStream(Int32) GetStream(Int32) GetStream(Int32)

擷取二進位、影像、varbinary、UDT 和 variant 資料型別做為StreamRetrieves binary, image, varbinary, UDT, and variant data types as a Stream.

GetString(Int32) GetString(Int32) GetString(Int32) GetString(Int32)

取得指定的資料行值做為字串。Gets the value of the specified column as a string.

GetTextReader(Int32) GetTextReader(Int32) GetTextReader(Int32) GetTextReader(Int32)

擷取 Char、NChar、 NText、 NVarChar、 文字、 varChar 及 Variant 資料型別做為TextReaderRetrieves Char, NChar, NText, NVarChar, text, varChar, and Variant data types as a TextReader.

GetTimeSpan(Int32) GetTimeSpan(Int32) GetTimeSpan(Int32) GetTimeSpan(Int32)

擷取指定資料行的值做為 TimeSpan 物件。Retrieves the value of the specified column as a TimeSpan object.

GetType() GetType() GetType() GetType()

取得目前執行個體的 TypeGets the Type of the current instance.

(Inherited from Object)
GetValue(Int32) GetValue(Int32) GetValue(Int32) GetValue(Int32)

取得使用原生格式的指定資料行值。Gets the value of the specified column in its native format.

GetValues(Object[]) GetValues(Object[]) GetValues(Object[]) GetValues(Object[])

使用目前資料列的資料行值填入物件陣列。Populates an array of objects with the column values of the current row.

GetXmlReader(Int32) GetXmlReader(Int32) GetXmlReader(Int32) GetXmlReader(Int32)

擷取 XML 型別的資料做為XmlReaderRetrieves data of type XML as an XmlReader.

InitializeLifetimeService() InitializeLifetimeService() InitializeLifetimeService() InitializeLifetimeService()

取得存留期服務物件,以控制這個執行個體的存留期原則。Obtains a lifetime service object to control the lifetime policy for this instance.

(Inherited from MarshalByRefObject)
IsCommandBehavior(CommandBehavior) IsCommandBehavior(CommandBehavior) IsCommandBehavior(CommandBehavior) IsCommandBehavior(CommandBehavior)

判斷指定的 CommandBehavior 是否與 SqlDataReader 的行為相符。Determines whether the specified CommandBehavior matches that of the SqlDataReader .

IsDBNull(Int32) IsDBNull(Int32) IsDBNull(Int32) IsDBNull(Int32)

取得值,指出資料行是否含有不存在或遺漏的值。Gets a value that indicates whether the column contains non-existent or missing values.

IsDBNullAsync(Int32, CancellationToken) IsDBNullAsync(Int32, CancellationToken) IsDBNullAsync(Int32, CancellationToken) IsDBNullAsync(Int32, CancellationToken)

IsDBNull(Int32) 的非同步版本,該版本會取得值,表示資料行是否包含不存在或遺漏的值。An asynchronous version of IsDBNull(Int32), which gets a value that indicates whether the column contains non-existent or missing values.

取消語彙基元可用於要求在命令逾時之前捨棄作業。The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. 例外狀況將經由傳回的 Task 物件回報。Exceptions will be reported via the returned Task object.

MemberwiseClone() MemberwiseClone() MemberwiseClone() MemberwiseClone()

建立目前 Object 的淺層複本 (Shallow Copy)。Creates a shallow copy of the current Object.

(Inherited from Object)
MemberwiseClone(Boolean) MemberwiseClone(Boolean) MemberwiseClone(Boolean) MemberwiseClone(Boolean)

建立目前 MarshalByRefObject 物件的淺層複本。Creates a shallow copy of the current MarshalByRefObject object.

(Inherited from MarshalByRefObject)
NextResult() NextResult() NextResult() NextResult()

當讀取批次 Transact-SQL 陳述式的結果時,將資料讀取器前移到下一個結果。Advances the data reader to the next result, when reading the results of batch Transact-SQL statements.

NextResultAsync(CancellationToken) NextResultAsync(CancellationToken) NextResultAsync(CancellationToken) NextResultAsync(CancellationToken)

NextResult() 的非同步版本,該版本會在讀取批次 Transact-SQLTransact-SQL 陳述式的結果時,將資料讀取器前移至下一個結果。An asynchronous version of NextResult(), which advances the data reader to the next result, when reading the results of batch Transact-SQLTransact-SQL statements.

取消語彙基元可用於要求在命令逾時之前捨棄作業。The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. 例外狀況將經由傳回的 Task 物件回報。Exceptions will be reported via the returned Task object.

Read() Read() Read() Read()

SqlDataReader 推進到下一筆記錄。Advances the SqlDataReader to the next record.

ReadAsync(CancellationToken) ReadAsync(CancellationToken) ReadAsync(CancellationToken) ReadAsync(CancellationToken)

非同步版本的 Read(),這個版本會將 SqlDataReader 前移至下一筆記錄。An asynchronous version of Read(), which advances the SqlDataReader to the next record.

取消語彙基元可用於要求在命令逾時之前捨棄作業。The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. 例外狀況將經由傳回的 Task 物件回報。Exceptions will be reported via the returned Task object.

ToString() ToString() ToString() ToString()

傳回代表目前物件的字串。Returns a string that represents the current object.

(Inherited from Object)

明確介面實作

IDataRecord.GetData(Int32) IDataRecord.GetData(Int32) IDataRecord.GetData(Int32) IDataRecord.GetData(Int32)

傳回指定之資料行序數的 IDataReaderReturns an IDataReader for the specified column ordinal.

IDisposable.Dispose() IDisposable.Dispose() IDisposable.Dispose() IDisposable.Dispose()

釋放資料讀取器使用的所有資源。Releases all resources that are used by the data reader.

IEnumerable.GetEnumerator() IEnumerable.GetEnumerator() IEnumerable.GetEnumerator() IEnumerable.GetEnumerator()

傳回可用來逐一查看項目集合的列舉值。Returns an enumerator that can be used to iterate through the item collection.

適用於

另請參閱