In this article I will explain with an example, what is the difference between MySqlDataReader and MySqlDataAdapter in Windows Forms (WinForms) Application using C# and VB.Net.
 
 

MySqlDataReader

The MySqlDataReader as the name suggests is used to read data from the MySQL database.
The MySqlDataReader 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 MySqlDataReader class has some properties.
1. Connection - Gets the MySqlConnection associated with the MySqlDataReader.
2. HasRows - Gets a value that indicates whether the MySqlDataReader contains one or more rows.
3. IsClosed - Retrieves a Boolean value that indicates whether the specified MySqlDataReader 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 MySqlDataReader, please refer MySQL Documentation.
 

Example

In the below code, the records are fetched from the MySQL database using MySqlDataReader and then, a WHILE loop is executed and the records are fetched one by one.
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string sql = "SELECT Name, Country FROM Customers";
using (MySqlConnection con = new MySqlConnection(constr))
{
    using (MySqlCommand cmd = new MySqlCommand(sql, con))
    {
        con.Open();
        using (MySqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                string name = dr["Name"].ToString();
                string country = dr["Country"].ToString();
                Response.Write("Name: " + name);
                Response.Write("Country: " + country);
            }
        }
        con.Close();
    }
}
 
For complete reference on MySqlDataReader, please refer MySqlDataReader Tutorial with example in C# and VB.Net.
 
 

Screenshot

Difference between MySqlDataReader and MySqlDataAdapter in .Net
 
 

MySqlDataAdapter

The MySqlDataAdapter works as a bridge between a DataSet or DataTable and MySQL database for retrieving and saving data.
The MySqlDataAdapter 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 MySqlDataAdapter class has following constructors.
1. MySqlDataAdapter (): Initializes a new instance of the MySqlDataAdapter class.
2. MySqlDataAdapter (MySqlCommand): Initializes a new instance of the MySqlDataAdapter class with the specified MySqlCommand as the SelectCommand property.
3. MySqlDataAdapter (String, MySqlConnection): Initializes a new instance of the MySqlDataAdapter class with a SelectCommand and a MySqlConnection object.
4. MySqlDataAdapter (String, String): Initializes a new instance of the MySqlDataAdapter class with the SelectCommand and a connection string.
Note: For more details about MySqlDataAdapter, please refer MySQL Documentation.
 

Example

In this below code, the MySqlDataAdapter object is initialized with the MySqlCommand and using the Fill function, the DataTable is populated with the records from database.
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string sql = "SELECT CustomerId, Name, Country FROM Customers";
 
using (MySqlConnection con = new MySqlConnection(constr))
{
    using (MySqlDataAdapter sda = new MySqlDataAdapter(sql, con))
    {
        using (DataTable dt = new DataTable())
        {
            sda.Fill(dt);
            dataGridView1.DataSource = dt;
        }
    }
}
 
For complete reference on MySqlDataAdapter, please refer MySqlDataAdapter Tutorial with example in C# and VB.Net.
 
 

Screenshot

Difference between MySqlDataReader and MySqlDataAdapter in .Net