Retrieving Data From a MySQL Database

Databases

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.

Schema Diagram

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.

MySqlDataAdapter

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:

MySqlDataReader

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

Dave

14 comments… add one
  • Arkadi Link Reply

    I wanted to thank you for this great code samples,
    they helped me a lot.
    Keep on with you great work!

  • Robert Link Reply

    It’s great job ! Helps me a lot too. Keep rock on it. I would love waiting for more of C# and MySql.

    Best regards,
    Robert

  • Dana Link Reply

    Thanks dave. Great work:)

  • fajar89 Link Reply

    thanks dave for this tutorial… great works

  • U have explained it very well. Thanks for sharing. Keep it up.

  • mafer Link Reply

    Great! Simple and useful.

  • Maha Link Reply

    Sir I want to retrieve videos whose Path saved in sql Database and videos saved in a folder………….I am able to retrieve a specific video but i want to retrieve all videos on clicking their name saved in database …………
    waiting for your reply
    Thanks In advance

  • Aris Link Reply

    ohhh thanks a lot mate after 2 days i figure out how the MySqlDataReader Works.

    😀 😀

  • Patrick Link Reply

    sir how about using combobox to search in MySql Database…on my project i am using 3 combobox to search for scholarship, category and class for example under scholarship is athlete, then under category is basketball and under class is class A 100% percent

  • suhana Link Reply

    thank u for great code

  • its old article but it works perfectly, thanks dave.

  • VRA Link Reply

    Excelente, gracias.

  • Thanks dude…this works…

Leave a Comment

Cancel reply