Retrieving Data From a MySQL Database

In this article I am going to show you how to programmatically retrieve data from a MySQL database using the MySqlDataAdapter and the MySqlDataReader classes. Both these classes are available once you install the MySQL Connector for .NET which can be downloaded from here: MySQL Connectors.

For this example we need a database with some test data. MySQL has an sql script which creates a set of tables with world country information. I will be using these tables for this example so I suggest you download this script and run it on your MySQL database. Once done, you will have added the following three tables to your database.

Connecting to a MySQL Database

To retrieve data from a MySQL database you must obviously connect to the database first. If you do not know how to do this check out my article called Connecting to a MySQL Database Programmatically.

Retrieving Data Using MySqlDataAdapter

In the below code we are retrieving all the data in the country table and populating a DataTable using the MySqlDataAdapter. Then we are binding the DataTable instance to a DataGridView on the form.

private void btnPopulateGrid_Click(object sender, EventArgs e)
{
    MySqlConnection conn = null;
    MySqlCommand cmd = null;
    DataTable dataTable = new DataTable();

    try
    {
        string sql = "SELECT * FROM country ORDER BY name ASC";

        conn = new MySqlConnection(Properties.Settings.Default.ConnectionString);

        cmd = new MySqlCommand(sql, conn);

        conn.Open();

        using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
        {
            da.Fill(dataTable);
        }

        dataGridView.DataSource = dataTable;
        dataGridView.DataMember = dataTable.TableName;
    }
    catch (Exception ex)
    {
        MessageBox.Show(string.Format("An error occurred {0}", ex.Message), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        if (conn != null) conn.Close();
    }
}

When the code is run, it will populate the DataGridView as shown below.

Retrieving Data Using MySqlDataReader

With the MySqlDataReader we have a little more work to do than we did with the MySqlDataAdapter, but there is also an advantage to using it, which is speed. The DataReader retrieves data faster than the DataAdapter because it is a read-only, forward-only stream of data. So if you just want to read data from the database and display it somewhere in your application, I would suggest you go with the DataReader. It is true that you have to code more to use it but it is worth the extra effort.

The below code is selecting specific data for the country Malta using the DataReader.

private void btnPopulateLabels_Click(object sender, EventArgs e)
{
    MySqlConnection conn = null;
    MySqlCommand cmd = null;
    MySqlDataReader reader = null;

    try
    {
        string sql = "SELECT name, continent, region, surfacearea FROM country WHERE code = 'MLT'";

        conn = new MySqlConnection(Properties.Settings.Default.ConnectionString);
        cmd = new MySqlCommand(sql, conn);

        conn.Open();

        reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            lblCountryCode.Text = "MLT";
            lblName.Text = reader.GetString("name");
            lblContinent.Text = reader.GetString("continent");
            lblRegion.Text = reader.GetString("region");
            lblSurfaceArea.Text = string.Format("{0:0.00}", reader.GetFloat("surfacearea"));
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(string.Format("An error occurred {0}", ex.Message), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        if (reader != null) reader.Close();
        if (conn != null) conn.Close();
    }
}

The most significant part of this code is the while loop. This code will keep iterating until the DataReader finishes reading the data stream. It retrieves records one by one so within each iteration you have access to the fields you selected.

If your sql query returned no records, the Read() method of the DataReader will return false, therefore the code within the while loop will not execute.

When the above code is run, it will populate the form below:

I hope you found this article useful. Stay tuned for more soon.

Dave