In this article I will explain with an example, how to use MySqlDataReader in Windows Forms (WinForms) Application with C# and VB.Net.
For illustration purposes, this article will use DataGridView to display the data populated from database using MySqlDataReader in C# and VB.Net.
 
 

What is MySqlDataReader?

The MySqlDataReader class 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 MSDN.
 
 

Database

I have made use of the following table Customers with the schema as follows.
MySqlDataReader Tutorial with example in C# and VB.Net
 
I have already inserted few records in the table.
MySqlDataReader Tutorial with example in C# and VB.Net
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 

Form Design

The Form consists of following control:
DataGridView – For displaying data.
MySqlDataReader Tutorial with example in C# and VB.Net
 
 

Namespaces

You will need to import the following namespaces.
C#
using System.Configuration;
using MySql.Data.MySqlClient;
 
VB.Net
Imports System.Configuration
Imports System.Data.SqlClient
 
 

Public Class

The Public class consists of following properties.
C#
public class Customer
{
    public string CustomerId { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}
 
VB.Net
Public Class Customer
    Public Property CustomerId As String
    Public Property Name As String
    Public Property Country As String
End Class
 
 

Implementing MySqlDataReader in C# and VB.Net

Inside the Form Load event handler, first the connection string is read from App.Config file.
Note: For more details on how to read connection string from App.Config file, please refer my article .Net 4.5: Read (Get) Connection String from App.Config file using C# and VB.Net.
 
Then, an object of MySqlCommand class is created and the SELECT query is passed to it as parameter and the Generic List collection of Customer class is created.
The connection is opened and an object of MySqlDataReader is created and the ExecuteReader function is executed.
Then, a WHILE loop is executed and fetched records are added to the Generic List collection of Customer class.
Finally, the Generic List collection of Customer class object is assigned to the DataSource property of DataGridView and the DataGridView is populated.
Note: Here Generic List collection is used, but MySqlDataReader can be used to populate DataSet or DataTable.
 
C#
private void Form1_Load(object sender, EventArgs e)
{
    List<Customer> customer = new List<Customer>();
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string sql = "SELECT CustomerId, Name, Country FROM Customers";
 
    using (MySqlConnection con = new MySqlConnection(constr))
    {
        using (MySqlCommand cmd = new MySqlCommand(sql, con))
        {
            con.Open();
            using (MySqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    customer.Add(new Customer
                    {
                        CustomeId = Convert.ToInt32(sdr["CustomerId"]),
                        Name = sdr["Name"].ToString(),
                        Country = sdr["Country"].ToString()
                    });
                }
            }
            con.Close();
            dataGridView1.DataSource = customer;
        }
    }
}
 
VB.Net
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim customer As List(Of Customer) = New List(Of Customer)()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim sql As String = "SELECT CustomerId, Name, Country FROM Customers"
 
    Using con As MySqlConnection = New MySqlConnection(constr)
        Using cmd As MySqlCommand = New MySqlCommand(sql, con)
            con.Open()
            Using sdr As MySqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    customer.Add(New Customer With {
                        .CustomeId = Convert.ToInt32(sdr("CustomerId")),
                        .Name = sdr("Name").ToString(),
                        .Country = sdr("Country").ToString()
                    })
                End While
            End Using
            con.Close()
            dataGridView1.DataSource = customer
        End Using
    End Using
End Sub
 
 

Screenshot

MySqlDataReader Tutorial with example in C# and VB.Net
 
 

Downloads

Download Code