Difference between SqlDataReader and DataSet

Difference between SqlDataReader and DataSet

There are lot of Difference between SqlDataReader and DataSet:-

• DataReader is used to retrieve read-only (cannot update/manipulate data back to datasource) and forward-only (cannot read backward/random) data from a database. It provides the ability to expose the data from database while DataSet is a collection of in-memory tables.
• DataReader is like a forward only recordset. It fetches one row at a time so very less network cost compare to DataSet which fetches all the rows at a time i.e. it fetches all data from the datasource at a time to its memory area.
• As one row at a time is stored in memory in DataReader it increases application performance and reduces system overheads while there is more system overheads in DataSet as it fetches all the data from the datasource at a time in memory.
• DataReader fetches the records from database and stores in the network buffer and gives whenever requests. It releases the records as query executes and do not wait for the entire query to execute. Hence very fast as compare to the DataSet which releases the data after loading all the data in memory.
• As DataReader is forward only, we can’t fetch random records as we can’t move back and forth .While in DataSet we can move back and forth and fetch records randomly as per requirement.
• As DataReader can have data from a single table so no relationship can be maintained while relationship between multiple tables can be maintained in DataSet.
• DataReader fetches data from a single table while DataSet can fetch data from multiple tables.
• DataReader is read only so no transaction like insert, update and delete is possible while these transactions are possible in DataSet.
• When you need to navigate through the data multiple times then DataSet is better choice e.g. we can fill data in multiple controls But DataReader can only be read once so it can be bound to a single control and requires data to be retrieved for each control.
• DataSet is a bulky object that requires lot of memory space as compared to DataReader .
• DataSet can be serialized and represented in XML so easily passed around to other tiers but DataReader can't be serialized.
• DataReader is a connected architecture: The data is available as long as the connection with database exists while DataSet is a disconnected architecture that automatically opens the connection, fetches the data into memory and closes the connection when done.
• Since DataSet can be serialized it, can be used in wcf services and web service that will return retrieved data. But DataReader can’t be serialized so can’t be used in wcf services and web services.
• DataReader requires connection to be open and close manually in code while DataSet automatically handles it.
• DataReader will be the best choice where we need to show the data to the user which requires no manipulation while DataSet is best suited where there is possibility of manipulation on the data.

The example of SqlDataReader and DataSet:-

SqlDataReader Example:-

  

public void BindGridview()
{
using (SqlConnection con = new SqlConnection("Data Source=santosh-pc;Initial Catalog=dbSantosh; <br />Integrated Security=true"))
{
con.Open();
SqlCommand cmd = new SqlCommand("Select Name,Rollno,Branch FROM tblDemo", con);
SqlDataReader dr = cmd.ExecuteReader();
gvDetail.DataSource = dr;
gvDetail.DataBind();
con.Close();
}
}

DataSet Example:-

  

protected void BindGridview()
{
using (SqlConnection con = new SqlConnection("Data Source=santosh-pc;Initial Catalog=dbSantosh; <br />Integrated Security=true"))
con.Open();
SqlCommand cmd = new SqlCommand("Select Name,Rollno,Branch FROM tblDemo", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
gvDetail.DataSource = ds;
gvDetail.DataBind();
}