Difference between DataSet and DataReader

Introduction

In this post I will try to explain the difference between DataSet and DataReader. My previous post Difference between stored procedure and user defined functions.

DataSet

DataSet is a type of disconnected architecture which means sql live database connections is not required while fetch the data from database. DataSet contains the collection of multiple DataTables. We are able to keep the tables, views values from database in DataSet. Also we can fetch the XML values and store in DataSet. We can use DataAdapter as a mediator between DataSet and SQL DataBase tables.

public DataSet GetData()
{
    SqlConnection conString = new SqlConnection("DataBase Connection");
    conString.Open();
    SqlCommand cmdQuery = new SqlCommand("SQL Statement", conString);
    SqlDataAdapter sda = new SqlDataAdapter(cmdQuery);
    DataSet dsData = new DataSet();
    sda.Fill(dsData);
    return dsData;
}

DataReader

DataReader is also used to fetch the data from SQL database. When compared to DataSet, DataReader is a connection oriented architecture. Also it is a read/forward only approach while fetching the data from SQL database. We can use DataReader for fast fetching the data from SQL database. DataReader will loop one by one record and return the data. Here we will see a sample for binding the GridView using DataReader.

Protected void BindDataGrid()
{
    SqlConnection conString = new SqlConnection("DataBase Connection");
    conString.Open();
    SqlCommand cmdQuery = new SqlCommand("SQL Statement", conString);
    SqlDataReader sdrData = cmdQuery.ExecuteReader();
    gvEmployees.DataSource = sdrData;
    gvEmployees.DataBind();
    conString.Close();
}

Output
In this post i tried to explain the difference between SQL Stored procedure and functions. My previous post Difference between stored procedure and user defined functions.