SqlDataReader Class

Definition

Namespace:: System.Data.SqlClient

Assembly:: System.Data.SqlClient.dll

Assembly:: System.Data.dll

Source:: System.Data.SqlClient.notsupported.cs

Important

Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.

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

For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.

public ref class SqlDataReader : System::Data::Common::DbDataReader, IDisposable

public ref class SqlDataReader sealed : MarshalByRefObject, IDisposable, System::Collections::IEnumerable, System::Data::IDataReader

public class SqlDataReader : System.Data.Common.DbDataReader, IDisposable

public sealed class SqlDataReader : MarshalByRefObject, IDisposable, System.Collections.IEnumerable, System.Data.IDataReader

type SqlDataReader = class
    inherit DbDataReader
    interface IDisposable

type SqlDataReader = class
    inherit MarshalByRefObject
    interface IEnumerable
    interface IDataReader
    interface IDisposable
    interface IDataRecord

type SqlDataReader = class
    inherit DbDataReader
    interface IDataReader
    interface IDisposable
    interface IDataRecord

Public Class SqlDataReader
Inherits DbDataReader
Implements IDisposable

Public NotInheritable Class SqlDataReader
Inherits MarshalByRefObject
Implements IDataReader, IDisposable, IEnumerable

Inheritance: Object

DbDataReader
SqlDataReader

Inheritance: Object

MarshalByRefObject

DbDataReader
SqlDataReader

Inheritance: Object

MarshalByRefObject
SqlDataReader

Implements: IDisposable IEnumerable IDataReader IDataRecord

Examples

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.

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 dataRecord)
{
    Console.WriteLine(String.Format("{0}, {1}", dataRecord[0], dataRecord[1]));
}

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

Remarks

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.

Note

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.

Properties

Name	Description
Connection	Gets the SqlConnection associated with the SqlDataReader. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
Depth	Gets a value that indicates the depth of nesting for the current row. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
FieldCount	Gets the number of columns in the current row. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
HasRows	Gets a value that indicates whether the SqlDataReader contains one or more rows. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
IsClosed	Retrieves a Boolean value that indicates whether the specified SqlDataReader instance has been closed. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
Item[Int32]	Gets the value of the specified column in its native format given the column ordinal. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
Item[String]	Gets the value of the specified column in its native format given the column name. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
RecordsAffected	Gets the number of rows changed, inserted, or deleted by execution of the Transact-SQL statement. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
VisibleFieldCount	Gets the number of fields in the SqlDataReader that are not hidden. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.

Methods

Name	Description
Close()	Closes the SqlDataReader object. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
CreateObjRef(Type)	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()	Releases all resources used by the current instance of the DbDataReader class. (Inherited from DbDataReader)
Dispose(Boolean)	Releases the unmanaged resources used by the DbDataReader and optionally releases the managed resources. (Inherited from DbDataReader)
Equals(Object)	Determines whether the specified object is equal to the current object. (Inherited from Object)
GetBoolean(Int32)	Gets the value of the specified column as a Boolean. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetByte(Int32)	Gets the value of the specified column as a byte. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
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. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetChar(Int32)	Gets the value of the specified column as a single character. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
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. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetData(Int32)	Returns an IDataReader for the specified column ordinal. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetData(Int32)	Returns a nested data reader for the requested column. (Inherited from DbDataReader)
GetDataTypeName(Int32)	Gets a string representing the data type of the specified column. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetDateTime(Int32)	Gets the value of the specified column as a DateTime object. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetDateTimeOffset(Int32)	Retrieves the value of the specified column as a DateTimeOffset object. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetDbDataReader(Int32)	Returns a DbDataReader object for the requested column ordinal that can be overridden with a provider-specific implementation. (Inherited from DbDataReader)
GetDecimal(Int32)	Gets the value of the specified column as a Decimal object. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetDouble(Int32)	Gets the value of the specified column as a double-precision floating point number. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetEnumerator()	Returns an IEnumerator that iterates through the SqlDataReader. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetFieldType(Int32)	Gets the Type that is the data type of the object. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetFieldValue<T>(Int32)	Synchronously gets the value of the specified column as a type. GetFieldValueAsync<T>(Int32, CancellationToken) is the asynchronous version of this method. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetFieldValueAsync<T>(Int32, CancellationToken)	Asynchronously gets the value of the specified column as a type. GetFieldValue<T>(Int32) is the synchronous version of this method. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetFieldValueAsync<T>(Int32)	Asynchronously gets the value of the specified column as the requested type. (Inherited from DbDataReader)
GetFloat(Int32)	Gets the value of the specified column as a single-precision floating point number. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetGuid(Int32)	Gets the value of the specified column as a globally unique identifier (GUID). For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetHashCode()	Serves as the default hash function. (Inherited from Object)
GetInt16(Int32)	Gets the value of the specified column as a 16-bit signed integer. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetInt32(Int32)	Gets the value of the specified column as a 32-bit signed integer. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetInt64(Int32)	Gets the value of the specified column as a 64-bit signed integer. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetLifetimeService()	Obsolete. Retrieves the current lifetime service object that controls the lifetime policy for this instance. (Inherited from MarshalByRefObject)
GetName(Int32)	Gets the name of the specified column. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetOrdinal(String)	Gets the column ordinal, given the name of the column. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetProviderSpecificFieldType(Int32)	Gets an `Object` that is a representation of the underlying provider-specific field type. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetProviderSpecificValue(Int32)	Gets an `Object` that is a representation of the underlying provider specific value. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetProviderSpecificValues(Object[])	Gets an array of objects that are a representation of the underlying provider specific values. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSchemaTable()	Returns a DataTable that describes the column metadata of the SqlDataReader. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlBinary(Int32)	Gets the value of the specified column as a SqlBinary. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlBoolean(Int32)	Gets the value of the specified column as a SqlBoolean. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlByte(Int32)	Gets the value of the specified column as a SqlByte. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlBytes(Int32)	Gets the value of the specified column as SqlBytes. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlChars(Int32)	Gets the value of the specified column as SqlChars. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlDateTime(Int32)	Gets the value of the specified column as a SqlDateTime. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlDecimal(Int32)	Gets the value of the specified column as a SqlDecimal. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlDouble(Int32)	Gets the value of the specified column as a SqlDouble. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlGuid(Int32)	Gets the value of the specified column as a SqlGuid. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlInt16(Int32)	Gets the value of the specified column as a SqlInt16. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlInt32(Int32)	Gets the value of the specified column as a SqlInt32. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlInt64(Int32)	Gets the value of the specified column as a SqlInt64. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlMoney(Int32)	Gets the value of the specified column as a SqlMoney. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlSingle(Int32)	Gets the value of the specified column as a SqlSingle. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlString(Int32)	Gets the value of the specified column as a SqlString. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlValue(Int32)	Returns the data value in the specified column as a SQL Server type. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlValues(Object[])	Fills an array of Object that contains the values for all the columns in the record, expressed as SQL Server types. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetSqlXml(Int32)	Gets the value of the specified column as an XML value. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetStream(Int32)	Retrieves binary, image, varbinary, UDT, and variant data types as a Stream. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetString(Int32)	Gets the value of the specified column as a string. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetTextReader(Int32)	Retrieves Char, NChar, NText, NVarChar, text, varChar, and Variant data types as a TextReader. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetTimeSpan(Int32)	Retrieves the value of the specified column as a TimeSpan object. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetType()	Gets the Type of the current instance. (Inherited from Object)
GetValue(Int32)	Gets the value of the specified column in its native format. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetValues(Object[])	Populates an array of objects with the column values of the current row. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
GetXmlReader(Int32)	Retrieves data of type XML as an XmlReader. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
InitializeLifetimeService()	Obsolete. Obtains a lifetime service object to control the lifetime policy for this instance. (Inherited from MarshalByRefObject)
IsCommandBehavior(CommandBehavior)	Determines whether the specified CommandBehavior matches that of the SqlDataReader . For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
IsDBNull(Int32)	Gets a value that indicates whether the column contains non-existent or missing values. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
IsDBNullAsync(Int32, CancellationToken)	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. Exceptions will be reported via the returned Task object. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
IsDBNullAsync(Int32)	Asynchronously gets a value that indicates whether the column contains non-existent or missing values. (Inherited from DbDataReader)
MemberwiseClone()	Creates a shallow copy of the current Object. (Inherited from Object)
MemberwiseClone(Boolean)	Creates a shallow copy of the current MarshalByRefObject object. (Inherited from MarshalByRefObject)
NextResult()	Advances the data reader to the next result, when reading the results of batch Transact-SQL statements. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
NextResultAsync()	Asynchronously advances the reader to the next result when reading the results of a batch of statements. (Inherited from DbDataReader)
NextResultAsync(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. <p>For new development, use <a href="/sql/connect/ado-net/introduction-microsoft-data-sqlclient-namespace">Microsoft.Data.SqlClient</a>. This page documents System.Data.SqlClient.</p>`
Read()	Advances the SqlDataReader to the next record. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
ReadAsync()	Asynchronously advances the reader to the next record in a result set. (Inherited from DbDataReader)
ReadAsync(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. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
ToString()	Returns a string that represents the current object. (Inherited from Object)

Explicit Interface Implementations

Name	Description
IDataReader.Close()	For a description of this member, see Close(). (Inherited from DbDataReader)
IDataReader.GetSchemaTable()	For a description of this member, see GetSchemaTable(). (Inherited from DbDataReader)
IDataRecord.GetData(Int32)	Returns an IDataReader for the specified column ordinal. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
IDataRecord.GetData(Int32)	For a description of this member, see GetData(Int32). (Inherited from DbDataReader)
IDisposable.Dispose()	Releases all resources that are used by the data reader. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.
IEnumerable.GetEnumerator()	Returns an enumerator that can be used to iterate through the item collection. For new development, use Microsoft.Data.SqlClient. This page documents System.Data.SqlClient.

Extension Methods

Name	Description
AsParallel(IEnumerable)	Enables parallelization of a query.
AsQueryable(IEnumerable)	Converts an IEnumerable to an IQueryable.
CanGetColumnSchema(DbDataReader)	Gets a value that indicates whether a DbDataReader can get a column schema.
Cast<TResult>(IEnumerable)	Casts the elements of an IEnumerable to the specified type.
GetColumnSchema(DbDataReader)	Gets the column schema (DbColumn collection) for a DbDataReader.
OfType<TResult>(IEnumerable)	Filters the elements of an IEnumerable based on a specified type.

Applies to

Feedback

Was this page helpful?