Export (0) Print
Expand All

SqlDataReader Class

Provides a way of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited.

Namespace:  System.Data.SqlClient
Assembly:  System.Data (in System.Data.dll)

public class SqlDataReader : DbDataReader, 
	IDataReader, IDisposable, IDataRecord

The SqlDataReader type exposes the following members.

  NameDescription
Protected propertyConnectionGets the SqlConnection associated with the SqlDataReader.
Public propertyDepthGets a value that indicates the depth of nesting for the current row. (Overrides DbDataReader.Depth.)
Public propertyFieldCountGets the number of columns in the current row. (Overrides DbDataReader.FieldCount.)
Public propertyHasRowsGets a value that indicates whether the SqlDataReader contains one or more rows. (Overrides DbDataReader.HasRows.)
Public propertyIsClosedRetrieves a Boolean value that indicates whether the specified SqlDataReader instance has been closed. (Overrides DbDataReader.IsClosed.)
Public propertyItem[Int32]Gets the value of the specified column in its native format given the column ordinal. (Overrides DbDataReader.Item.)
Public propertyItem[String]Gets the value of the specified column in its native format given the column name. (Overrides DbDataReader.Item.)
Public propertyRecordsAffectedGets the number of rows changed, inserted, or deleted by execution of the Transact-SQL statement. (Overrides DbDataReader.RecordsAffected.)
Public propertyVisibleFieldCountGets the number of fields in the SqlDataReader that are not hidden. (Overrides DbDataReader.VisibleFieldCount.)
Top

  NameDescription
Public methodCloseCloses the SqlDataReader object. (Overrides DbDataReader.Close().)
Public methodCreateObjRefCreates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object. (Inherited from MarshalByRefObject.)
Public methodDispose()Releases all resources used by the current instance of the DbDataReader class. (Inherited from DbDataReader.)
Protected methodDispose(Boolean)Releases the managed resources used by the DbDataReader and optionally releases the unmanaged resources. (Inherited from DbDataReader.)
Public methodEquals(Object)Determines whether the specified object is equal to the current object. (Inherited from Object.)
Protected methodFinalizeAllows an object to try to free resources and perform other cleanup operations before it is reclaimed by garbage collection. (Inherited from Object.)
Public methodGetBooleanGets the value of the specified column as a Boolean. (Overrides DbDataReader.GetBoolean(Int32).)
Public methodGetByteGets the value of the specified column as a byte. (Overrides DbDataReader.GetByte(Int32).)
Public methodGetBytesReads a stream of bytes from the specified column offset into the buffer an array starting at the given buffer offset. (Overrides DbDataReader.GetBytes(Int32, Int64, Byte[], Int32, Int32).)
Public methodGetCharGets the value of the specified column as a single character. (Overrides DbDataReader.GetChar(Int32).)
Public methodGetCharsReads a stream of characters from the specified column offset into the buffer as an array starting at the given buffer offset. (Overrides DbDataReader.GetChars(Int32, Int64, Char[], Int32, Int32).)
Public methodGetDataReturns a DbDataReader object for the requested column ordinal. (Inherited from DbDataReader.)
Public methodGetDataTypeNameGets a string representing the data type of the specified column. (Overrides DbDataReader.GetDataTypeName(Int32).)
Public methodGetDateTimeGets the value of the specified column as a DateTime object. (Overrides DbDataReader.GetDateTime(Int32).)
Public methodGetDateTimeOffsetRetrieves the value of the specified column as a DateTimeOffset object.
Protected methodGetDbDataReaderReturns a DbDataReader object for the requested column ordinal that can be overridden with a provider-specific implementation. (Inherited from DbDataReader.)
Public methodGetDecimalGets the value of the specified column as a Decimal object. (Overrides DbDataReader.GetDecimal(Int32).)
Public methodGetDoubleGets the value of the specified column as a double-precision floating point number. (Overrides DbDataReader.GetDouble(Int32).)
Public methodGetEnumeratorReturns an IEnumerator that iterates through the SqlDataReader. (Overrides DbDataReader.GetEnumerator().)
Public methodGetFieldTypeGets the Type that is the data type of the object. (Overrides DbDataReader.GetFieldType(Int32).)
Public methodGetFieldValue<T>Synchronously gets the value of the specified column as a type. GetFieldValueAsync is the asynchronous version of this method. (Overrides DbDataReader.GetFieldValue<T>(Int32).)
Public methodGetFieldValueAsync<T>(Int32)Asynchronously gets the value of the specified column as a type. (Inherited from DbDataReader.)
Public methodGetFieldValueAsync<T>(Int32, CancellationToken)Asynchronously gets the value of the specified column as a type. GetFieldValue<T> is the synchronous version of this method. (Overrides DbDataReader.GetFieldValueAsync<T>(Int32, CancellationToken).)
Public methodGetFloatGets the value of the specified column as a single-precision floating point number. (Overrides DbDataReader.GetFloat(Int32).)
Public methodGetGuidGets the value of the specified column as a globally unique identifier (GUID). (Overrides DbDataReader.GetGuid(Int32).)
Public methodGetHashCodeServes as the default hash function. (Inherited from Object.)
Public methodGetInt16Gets the value of the specified column as a 16-bit signed integer. (Overrides DbDataReader.GetInt16(Int32).)
Public methodGetInt32Gets the value of the specified column as a 32-bit signed integer. (Overrides DbDataReader.GetInt32(Int32).)
Public methodGetInt64Gets the value of the specified column as a 64-bit signed integer. (Overrides DbDataReader.GetInt64(Int32).)
Public methodGetLifetimeServiceRetrieves the current lifetime service object that controls the lifetime policy for this instance. (Inherited from MarshalByRefObject.)
Public methodGetNameGets the name of the specified column. (Overrides DbDataReader.GetName(Int32).)
Public methodGetOrdinalGets the column ordinal, given the name of the column. (Overrides DbDataReader.GetOrdinal(String).)
Public methodGetProviderSpecificFieldTypeGets an Object that is a representation of the underlying provider-specific field type. (Overrides DbDataReader.GetProviderSpecificFieldType(Int32).)
Public methodGetProviderSpecificValueGets an Object that is a representation of the underlying provider specific value. (Overrides DbDataReader.GetProviderSpecificValue(Int32).)
Public methodGetProviderSpecificValuesGets an array of objects that are a representation of the underlying provider specific values. (Overrides DbDataReader.GetProviderSpecificValues(Object[]).)
Public methodGetSchemaTableReturns a DataTable that describes the column metadata of the SqlDataReader. (Overrides DbDataReader.GetSchemaTable().)
Public methodGetSqlBinaryGets the value of the specified column as a SqlBinary.
Public methodGetSqlBooleanGets the value of the specified column as a SqlBoolean.
Public methodGetSqlByteGets the value of the specified column as a SqlByte.
Public methodGetSqlBytesGets the value of the specified column as SqlBytes.
Public methodGetSqlCharsGets the value of the specified column as SqlChars.
Public methodGetSqlDateTimeGets the value of the specified column as a SqlDateTime.
Public methodGetSqlDecimalGets the value of the specified column as a SqlDecimal.
Public methodGetSqlDoubleGets the value of the specified column as a SqlDouble.
Public methodGetSqlGuidGets the value of the specified column as a SqlGuid.
Public methodGetSqlInt16Gets the value of the specified column as a SqlInt16.
Public methodGetSqlInt32Gets the value of the specified column as a SqlInt32.
Public methodGetSqlInt64Gets the value of the specified column as a SqlInt64.
Public methodGetSqlMoneyGets the value of the specified column as a SqlMoney.
Public methodGetSqlSingleGets the value of the specified column as a SqlSingle.
Public methodGetSqlStringGets the value of the specified column as a SqlString.
Public methodGetSqlValueReturns the data value in the specified column as a SQL Server type.
Public methodGetSqlValuesFills an array of Object that contains the values for all the columns in the record, expressed as SQL Server types.
Public methodGetSqlXmlGets the value of the specified column as an XML value.
Public methodGetStreamRetrieves binary, image, varbinary, UDT, and variant data types as a Stream. (Overrides DbDataReader.GetStream(Int32).)
Public methodGetStringGets the value of the specified column as a string. (Overrides DbDataReader.GetString(Int32).)
Public methodGetTextReaderRetrieves Char, NChar, NText, NVarChar, text, varChar, and Variant data types as a TextReader. (Overrides DbDataReader.GetTextReader(Int32).)
Public methodGetTimeSpanRetrieves the value of the specified column as a TimeSpan object.
Public methodGetTypeGets the Type of the current instance. (Inherited from Object.)
Public methodGetValueGets the value of the specified column in its native format. (Overrides DbDataReader.GetValue(Int32).)
Public methodGetValuesPopulates an array of objects with the column values of the current row. (Overrides DbDataReader.GetValues(Object[]).)
Public methodGetXmlReaderRetrieves data of type XML as an XmlReader.
Public methodInitializeLifetimeServiceObtains a lifetime service object to control the lifetime policy for this instance. (Inherited from MarshalByRefObject.)
Protected methodIsCommandBehaviorDetermines whether the specified CommandBehavior matches that of the SqlDataReader .
Public methodIsDBNullGets a value that indicates whether the column contains non-existent or missing values. (Overrides DbDataReader.IsDBNull(Int32).)
Public methodIsDBNullAsync(Int32)An asynchronous version of IsDBNull, which gets a value that indicates whether the column contains non-existent or missing values. (Inherited from DbDataReader.)
Public methodIsDBNullAsync(Int32, CancellationToken)An asynchronous version of IsDBNull, 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. Exceptions will be reported via the returned Task object. (Overrides DbDataReader.IsDBNullAsync(Int32, CancellationToken).)
Protected methodMemberwiseClone()Creates a shallow copy of the current Object. (Inherited from Object.)
Protected methodMemberwiseClone(Boolean)Creates a shallow copy of the current MarshalByRefObject object. (Inherited from MarshalByRefObject.)
Public methodNextResultAdvances the data reader to the next result, when reading the results of batch Transact-SQL statements. (Overrides DbDataReader.NextResult().)
Public methodNextResultAsync()An asynchronous version of NextResult, which advances the reader to the next result when reading the results of a batch of statements.Invokes NextResultAsync with CancellationToken.None. (Inherited from DbDataReader.)
Public methodNextResultAsync(CancellationToken)An asynchronous version of NextResult, which advances the data reader to the next result, when reading the results of batch Transact-SQL statements.The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. Exceptions will be reported via the returned Task object. (Overrides DbDataReader.NextResultAsync(CancellationToken).)
Public methodReadAdvances the SqlDataReader to the next record. (Overrides DbDataReader.Read().)
Public methodReadAsync()An asynchronous version of Read, which advances the reader to the next record in a result set. This method invokes ReadAsync with CancellationToken.None. (Inherited from DbDataReader.)
Public methodReadAsync(CancellationToken)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. Exceptions will be reported via the returned Task object. (Overrides DbDataReader.ReadAsync(CancellationToken).)
Public methodToStringReturns a string that represents the current object. (Inherited from Object.)
Top

  NameDescription
Explicit interface implemetationPrivate methodIDataRecord.GetDataReturns an IDataReader for the specified column ordinal.
Top

To create a SqlDataReader, you must call the ExecuteReader method of the SqlCommand object, instead of directly using a constructor.

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. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

Changes 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.

IsClosed and RecordsAffected are the only properties that you can call after the SqlDataReader is closed. 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.

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.

NoteNote

For optimal performance, SqlDataReader avoids creating unnecessary objects or making unnecessary copies of data. Therefore, multiple calls to methods such as GetValue return a reference to the same object. Use caution if you are modifying the underlying value of the objects returned by methods such as GetValue.

The following example creates a SqlConnection, a SqlCommand, and a SqlDataReader. The example reads through the data, writing it out to the console window. The code then closes the SqlDataReader. 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]));
    }

}

.NET Framework

Supported in: 4.5.3, 4, 3.5, 3.0, 2.0, 1.1, 1.0

.NET Framework Client Profile

Supported in: 4, 3.5 SP1

Windows 8.1, Windows Server 2012 R2, Windows 8, Windows Server 2012, Windows 7, Windows Vista SP2, Windows Server 2008 (Server Core Role not supported), Windows Server 2008 R2 (Server Core Role supported with SP1 or later; Itanium not supported)

The .NET Framework does not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.
Show:
© 2014 Microsoft