In this article I will explain with an example, what is the difference between SqlDataReader and SqlDataAdapter in .Net.
 
 
SqlDataReader
The SqlDataReader as the name suggests is used to read data from the database.
The SqlDataReader uses connection-oriented architecture i.e. it requires an open connection to the Data Source while reading the data.
The data is available as long as the connection with the database exists. You need to open and close the connection explicitly.
It reads data in the forward-only direction. Once a record is read, then the next record will be read.
There is no way to go back and read the previous record.
The SqlDataReader class has some properties.
1. Connection - Gets the SqlConnection associated with the SqlDataReader.
2. HasRows - Gets a value that indicates whether the SqlDataReader contains one or more rows.
3. IsClosed - Retrieves a Boolean value that indicates whether the specified SqlDataReader instance has been closed.
4. RecordsAffected - Gets the number of rows changed, inserted, or deleted by execution of the Transact-SQL statement.
5. FieldCount - Gets the number of columns in the current row.
Note: For more details about SqlDataReader, please refer MSDN.
 
Example
In the below code, the records are fetched from the database Table using SqlDataReader and using WHILE Loop, the records are fetched one by one.
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
    using (SqlCommand cmd = new SqlCommand("SELECT Name, City FROM Persons", con))
    {
        cmd.CommandType = CommandType.Text;
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            string name = dr["Name"].ToString();
            string city = dr["City"].ToString();
            Response.Write("Name: " + name);
            Response.Write("City: " + city);
        }
        con.Close();
    }
}
 
For complete reference on SqlDataReader, please refer SqlDataReader Tutorial with example in C# and VB.Net.
 
Screenshot
Difference between SqlDataReader and SqlDataAdapter in .Net
 
 
SqlDataAdapter
The SqlDataAdapter works as a bridge between a DataSet or DataTable and SQL Server Database for retrieving and saving data.
The SqlDataAdapter uses disconnection-oriented architecture i.e. it does not requires an open connection to the Data Source while retrieving the data.
It is used to fill the DataSet or DataTable and update the data source as well.
It has a Fill method, which adds or refreshes rows in the DataSet or DataTable to match those in the Data Source.
The SqlDataAdapter class has following constructors.
1. SqlDataAdapter(): Initializes a new instance of the SqlDataAdapter class.
2. SqlDataAdapter(SqlCommand): Initializes a new instance of the SqlDataAdapter class with the specified SqlCommand as the SelectCommand property.
3. SqlDataAdapter(String, SqlConnection): Initializes a new instance of the SqlDataAdapter class with a SelectCommand and a SqlConnection object.
4. SqlDataAdapter(String, String): Initializes a new instance of the SqlDataAdapter class with the SelectCommand and a connection string.
Note: For more details about SqlDataAdapter, please refer MSDN.
 
Example
In this below code, the SqlDataAdapter object is initialized with the SqlCommand and using the Fill function, the DataTable is populated with the records from database.
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
    using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, ContactName, Country FROM Customers", con))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
        {
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
            }
        }
    }
}
 
For complete reference on SqlDataAdapter, please refer SqlDataAdapter Tutorial with example in C# and VB.Net.
 
Screenshot
Difference between SqlDataReader and SqlDataAdapter in .Net