Share via


SqlDataReader Class

Definition

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 ref class SqlDataReader : System::Data::Common::DbDataReader, IDisposable, System::Data::Common::IDbColumnSchemaGenerator
public ref class SqlDataReader : System::Data::Common::DbDataReader
public class SqlDataReader : System.Data.Common.DbDataReader, IDisposable
public class SqlDataReader : System.Data.Common.DbDataReader, IDisposable, System.Data.Common.IDbColumnSchemaGenerator
public class SqlDataReader : System.Data.Common.DbDataReader
type SqlDataReader = class
    inherit DbDataReader
    interface IDataReader
    interface IDisposable
    interface IDataRecord
type SqlDataReader = class
    inherit DbDataReader
    interface IDataReader
    interface IDataRecord
    interface IDisposable
type SqlDataReader = class
    inherit DbDataReader
    interface IDbColumnSchemaGenerator
    interface IDisposable
type SqlDataReader = class
    inherit DbDataReader
    interface IDbColumnSchemaGenerator
    interface IDataReader
    interface IDataRecord
    interface IDisposable
type SqlDataReader = class
    inherit DbDataReader
    interface IDisposable
Public Class SqlDataReader
Inherits DbDataReader
Implements IDisposable
Public Class SqlDataReader
Inherits DbDataReader
Implements IDbColumnSchemaGenerator, IDisposable
Public Class SqlDataReader
Inherits DbDataReader
Inheritance
SqlDataReader
Implements

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.

using Microsoft.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]));
    }

}

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

Connection

Gets the SqlConnection associated with the SqlDataReader.

Depth

Gets a value that indicates the depth of nesting for the current row.

FieldCount

Gets the number of columns in the current row.

HasRows

Gets a value that indicates whether the SqlDataReader contains one or more rows.

IsClosed

Retrieves a Boolean value that indicates whether the specified SqlDataReader instance has been closed.

Item[Int32]

Gets the value of the specified column in its native format given the column ordinal.

Item[String]

Gets the value of the specified column in its native format given the column name.

RecordsAffected

Gets the number of rows changed, inserted, or deleted by execution of the Transact-SQL statement.

SensitivityClassification

Gets the SensitivityClassification information with the SqlDataReader.

VisibleFieldCount

Gets the number of fields in the SqlDataReader that are not hidden.

Methods

Close()

Closes the SqlDataReader object.

Dispose(Boolean)
GetBoolean(Int32)

Gets the value of the specified column as a Boolean.

GetByte(Int32)

Gets the value of the specified column as a byte.

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)

Gets the value of the specified column as a single character.

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()

Gets the read-only column schema collection.

GetDataTypeName(Int32)

Gets a string representing the data type of the specified column.

GetDateTime(Int32)

Gets the value of the specified column as a DateTime object.

GetDateTimeOffset(Int32)

Retrieves the value of the specified column as a DateTimeOffset object.

GetDecimal(Int32)

Gets the value of the specified column as a Decimal object.

GetDouble(Int32)

Gets the value of the specified column as a double-precision floating point number.

GetEnumerator()

Returns an IEnumerator that iterates through the SqlDataReader.

GetFieldType(Int32)

Gets the Type that is the data type of the object.

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.

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.

GetFloat(Int32)

Gets the value of the specified column as a single-precision floating point number.

GetGuid(Int32)

Gets the value of the specified column as a globally unique identifier (GUID).

GetInt16(Int32)

Gets the value of the specified column as a 16-bit signed integer.

GetInt32(Int32)

Gets the value of the specified column as a 32-bit signed integer.

GetInt64(Int32)

Gets the value of the specified column as a 64-bit signed integer.

GetName(Int32)

Gets the name of the specified column.

GetOrdinal(String)

Gets the column ordinal, given the name of the column.

GetProviderSpecificFieldType(Int32)

Gets an Object that is a representation of the underlying provider-specific field type.

GetProviderSpecificValue(Int32)

Gets an Object that is a representation of the underlying provider specific value.

GetProviderSpecificValues(Object[])

Gets an array of objects that are a representation of the underlying provider specific values.

GetSchemaTable()

Returns a DataTable that describes the column metadata of the SqlDataReader.

GetSqlBinary(Int32)

Gets the value of the specified column as a SqlBinary.

GetSqlBoolean(Int32)

Gets the value of the specified column as a SqlBoolean.

GetSqlByte(Int32)

Gets the value of the specified column as a SqlByte.

GetSqlBytes(Int32)

Gets the value of the specified column as SqlBytes.

GetSqlChars(Int32)

Gets the value of the specified column as SqlChars.

GetSqlDateTime(Int32)

Gets the value of the specified column as a SqlDateTime.

GetSqlDecimal(Int32)

Gets the value of the specified column as a SqlDecimal.

GetSqlDouble(Int32)

Gets the value of the specified column as a SqlDouble.

GetSqlGuid(Int32)

Gets the value of the specified column as a SqlGuid.

GetSqlInt16(Int32)

Gets the value of the specified column as a SqlInt16.

GetSqlInt32(Int32)

Gets the value of the specified column as a SqlInt32.

GetSqlInt64(Int32)

Gets the value of the specified column as a SqlInt64.

GetSqlMoney(Int32)

Gets the value of the specified column as a SqlMoney.

GetSqlSingle(Int32)

Gets the value of the specified column as a SqlSingle.

GetSqlString(Int32)

Gets the value of the specified column as a SqlString.

GetSqlValue(Int32)

Returns the data value in the specified column as a SQL Server type.

GetSqlValues(Object[])

Fills an array of Object that contains the values for all the columns in the record, expressed as SQL Server types.

GetSqlXml(Int32)

Gets the value of the specified column as an XML value.

GetStream(Int32)

Retrieves binary, image, varbinary, UDT, and variant data types as a Stream.

GetString(Int32)

Gets the value of the specified column as a string.

GetTextReader(Int32)

Retrieves Char, NChar, NText, NVarChar, text, varChar, and Variant data types as a TextReader.

GetTimeSpan(Int32)

Retrieves the value of the specified column as a TimeSpan object.

GetValue(Int32)

Gets the value of the specified column in its native format.

GetValues(Object[])

Populates an array of objects with the column values of the current row.

GetXmlReader(Int32)

Retrieves data of type XML as an XmlReader.

IsCommandBehavior(CommandBehavior)

Determines whether the specified CommandBehavior matches that of the SqlDataReader .

IsDBNull(Int32)

Gets a value that indicates whether the column contains non-existent or missing values.

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.

NextResult()

Advances the data reader to the next result, when reading the results of batch Transact-SQL statements.

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.

Read()

Advances the SqlDataReader to the next record.

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.

Explicit Interface Implementations

IDataRecord.GetData(Int32)

Returns an IDataReader for the specified column ordinal.

Applies to