How to use DataReader in c#

SqlDataReader provides a way to reading a forward only (read only) stream of rows from a SQL Server Database. SqlDataReader class have no direct constructor. ADO.NET developers need to call the SqlCommand object's ExecuteReader method to create a SqlDataReader. When SqlDataReader is being used, its associated SqlConnection cannot perform any other operation without closing it. SqlDataReader Close() method close the SqlDataReader object. We should always call the Close() method after finished using DataReader object.

DataReader Read() method use to obtain a row from the results of the query. We can access each column of the returned row by passing the name or ordinal reference of the column to the DataReader. DataReader can retrieve multiple result sets. DataReader also can retrieve schema information about the current result set using GetSchemaTable method.

SqlDataReader Connection property get the associated SqlConnection. Depth property get a value that indicate depth of nesting for the current row. FieldCount property get the numbers of columns in the current row. HasRows property value indicate whether the DataReader contains one or more rows. IsClosed property provide a Boolean value that indicate specified SqlDataReader instance has been closed or not. Item[32] provide the value of specified column in its native format by column ordinal. Item[String] provide the specified column in its native format by column name. RecordsAffected property provide the number of rows changed, inserted or deleted by execution of the SQL statement.

There are many useful methods exists in SqlDataReader class such as Close(), Dispose(), Finalize, GetBoolean, GetByte, GetChar, GetData, GetDateTime, GetDouble, GetEnumerator, GetDecimal, GetFloat, GetGuid, GetInt32, GetName, GetOrdinal, GetSqlDateTime, GetSqlDecimal, GetSqlString, GetXmlReader, IsCommandBehavior, IsDBNull, NextResult, NextResultAsync(), Read, ReadAsync(), ToString etc. GetString method get the value of the specified column as a string. GetTimeSpan method retrieve specified column value as a TimeSpan object.

The following c# example source code describe you more about DataReader.

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>

<!DOCTYPE html>

<script runat="server">
    protected void Page_Load(object sender, System.EventArgs e) {
        if (!Page.IsPostBack) {
            SqlConnection MyConnection;
            SqlCommand MyCommand;
            SqlDataReader MyReader;

            MyConnection = new SqlConnection();
            MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString;
            MyCommand = new SqlCommand();
            MyCommand.CommandText = "SELECT TOP 10 * From PRODUCTS";
            MyCommand.CommandType = CommandType.Text;
            MyCommand.Connection = MyConnection;
            MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);

            GridView1.DataSource = MyReader;


<html xmlns="">
<head runat="server">
    <title>DataReader example: how to use DataReader in</title>
    <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server">
More c# examples