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

DaveOnCSharp.com runs on the Thesis Theme


How smart is your Theme? How good is your support? Check out ThesisTheme for WordPress.

The Thesis Theme is one of the best Wordpress frameworks out there, especially if you understand software development, which since you're here you probably do. Thesis allows you to design post and page templates using a drag-and-drop system which gives you access to all the relevant page elements you would normally have to manually access through code. This is why understanding development techniques definitely helps, even though you don't need to write any code to get Thesis up and running.

So go check out the incredible features at DIYThemes and start using Thesis now!

12 comments… add one
  • Arkadi

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

    • You’re welcome. Thanks for the comment.

  • Robert

    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

    Thanks dave. Great work:)

  • fajar89

    thanks dave for this tutorial… great works

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

  • mafer

    Great! Simple and useful.

  • Maha

    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

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

    😀 😀

  • Patrick

    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

    thank u for great code

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

Leave a Comment