ADO.NET

The DataReader provides a read-only, forward-only stream of results from a database query or stored procedure and we can use a DataReader if it is desirable,and possible, to keep the connection to the database open while data is being processed.A DataReader is a lightweight object which provides read-only, forward-only data in a very fast and efficient way . DataReader is efficient than using a DataAdapter but it is limited and Data access with DataReader is read-only, i.e, you cannot make any changes (update) to data and forward-only, i.e we cannot go back to the previous record which was accessed.

DataReader requires the exclusive use of an active connection for the entire time if it is in existence and we instantiate a DataReader by calling a Command object's ExecuteReader command. When the DataReader is first returned it is positioned before the first record of the result set. To making the first record available we need to call the Read method and If a record is exist, the Read method moves the DataReader for next record and returns True and If a record is not available then the Read method returns False. we can use a While Loop to iterate through the records with the Read method.

using System;
using System.Data;
using System.Data.SqlClient;
public class empnamereader {
public static void Main() {
SqlConnection con = new SqlConnection(
"server=(local)\mycomputer;database=emp;trusted_connection=yes"
);
string s = "SELECT * FROM emp";
con.Open();
sqlcommand cmd=new sqlcommand(s, con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read()) {
Console.WriteLine(sdr["empname"] );
}
con.Close();
}
}

Limitations of the DataReader

There is not possible to sort, filter, or manipulate the data while using a DataReader, since it is read-only and forward-only.