Difference Between DataAdapter And DataReader In Asp.Net With Example

Introduction
In this article we will learn what is DataAdapter and DataReader. What is the difference between dataadapter and datareader. When do we use dataadapter and when to use datareader. Use data reader and data adapter in connected and disconnected mode.

Previous Updates
In previous articles we have learnt Showing what is connection pooling in c# . Bind multiple dropdown list using single method call using c#. Read Excel file in C#  And Display in Grid. Read Excel in Windows Application project. Chart With Database In Asp.Net Using C#.  Insert Only Numeric Values In Asp TextBox Using Regex . Get TextBox , Dropdown, CheckBox  control Values In Aspx.cs Page From User Control Using C#.


When we are working with ADO.Net we used DataReader DataSet , DataAdapter , DataTables to interact with database. But they all have their different meaning and use based on the logic. 

DataReader
DataReader as its name implies it is used to read the data. DataReader reads the data from database. DataReader gets the records from Stored Procedure , table returning SELECT query.
DataReader works only in forward direction means row read once cannot be read again because of this it is fast to fetching records.
Most used function of datareader is Read function which  reads the table row one by one and returns true or false if it is read.
Datareader always required a Open connection for executing the SQL commands. Once a connection closed then you will not been able to read the data from  datareader. Thats why it is used in connecte mode in SQL.

string conStr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; using (SqlConnection con = new SqlConnection(conStr)) {     using (SqlCommand cmd = new SqlCommand("SELECT EmpId, EmpName FROM Employee", con))     {         cmd.CommandType = CommandType.Text;         con.Open();         SqlDataReader dr = cmd.ExecuteReader();         while (dr.Read())         {             string name = dr["EmpID"].ToString();             string city = dr["EmpName"].ToString();              Response.Write("EmpID: " + EmpID);                 Response.Write("Name: " + EmpName);         }          con.Close();     } }

DataAdapter
DataAdapter is also a ADO.Net object. It is used to execute the SQL statements and populate the result of that SQL query in DataSet or in DataTable.
DataAdapter gets all the returned rows from Sql statement at once and then fill the data into DataSet or datatable. Because of this dataadapter is slow in comparision to datareader.
DataReader have Read function just like Dataadapter has Fill function which fills the returned rows from sql statement onto dataset or datatable.
DataAdapter will not reuire any open and close connection . Means DataAdapter can work in Disconnected mode.

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) {     using (SqlCommand cmd = new SqlCommand("SELECT EmpId, EmpName FROM Employee", con))     {         cmd.CommandType = CommandType.Text;         using (SqlDataAdapter adp = new SqlDataAdapter(cmd))         {             DataSet ds = new DataSet();              adp.Fill(ds);             foreach (DataRow row in ds.Tables[0].Rows)             {                 string EmpID = row["EmpID"].ToString();                string EmpName = row["EmpName"].ToString();                 Response.Write("EmpID: " + EmpID);                 Response.Write("Name: " + EmpName);             }         }     } }