Fastest way to get data from database is using SqlDatareader

As you know the difference between DATASET and DATA READER, most developers use DATASET with DATA ADAPTER to get large amount of data, but this is not efficient work to do. If number of users access data from database simultaniously Dataset and Data Table gives burden on server which may get slowdown. So to avoid this we need to use DATA READER.

Example :

SqlCommand cmd = new SqlCommand("select * from tablename",con);

con.Open();
SqlDataReader dr = cmd.ExecuteReader();
GridView1.DataSource = dr;
Gridview1.DataBind();
con.Close();

This is the way we use Data reader to assign data to gridview.
and one more way is using " IDataReader ".

Suppose you have a class file to access data, and you can't return datareader because if you once close datareader it losts data so we use IDatareader.

The following is a class file or DAL (Data Access Layer)

public class Class1

{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ToString());
public IDataReader getRecs()

{
SqlCommand cmd = new SqlCommand("select * from myfaq",con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}

Here " getRes() " is a method returns object of type " IDataReader " and dr is the " SqlDataReader ".
to close connection after fetching all records we use " CommandBehaviour " class in " ExecuteReader " as shown above.
And the code in page is...

protected void Page_Load(object sender, EventArgs e)

{
Class1 cs = new Class1();     // class object
GridView1.DataSource = cs.getRecs();          // calling getRecs() method
GridView1.DataBind();
}

This is how we optimize the performance of data accessing from database using SqlDataReader.