DataReader and Dataset

• A DataReader works in a connected environment, whereas DataSet works in a disconnected environment.
• Dataset communicates with the dataadapter only whereas datareader communicates with the command object
• Dataset supports integeration with XML whereas Datareader doesn't support.
• We can create relations in dataset whereas we can't create relation in datareader.
• Dataset is scrollable whereas datareader is forward only record set.

DataReader :

A DataReader object represents a forward only, read only access to data from a source.

It will require you to open the connection through out and so it is not suitable for
interacting between different tiers.


Dataset:

DataSet is disconnected, in-memory representation of relational database.

It is well suited to communicate remotely as it is disconnected and changes
can be updated whenever it is required.

Fill gridview using Datareader:

SqlConnection con = new SqlConnection("server=.;database=northwind;integrated security=true");
SqlCommand com = new SqlCommand("select * from Categories", con);
SqlDataReader DR;
con.Open();
DR = com.ExecuteReader(CommandBehavior.CloseConnection);
DR.Read();
GridView1.DataSource = DR;
GridView1.DataBind();

Fill gridview using DataSet:

SqlConnection con = new SqlConnection("server=.;database=northwind;integrated security=true");
SqlDataAdapter DA = new SqlDataAdapter("select * from Categories", con);
DataSet DS = new DataSet();
DA.Fill(DS);
GridView1.DataSource = DS;
GridView1.DataBind();