C# populating dataTable in Visual Studio 2015 from table in MySQL

Question

I am working on a program in Visual Studio 2015 that is pulling data from a table in MySQL. I am new to SQL so I may be going about this the wrong way all together. What I need is to basically mimic the table from MySQL into a C# application. I have created a new dataset called DataSet4 and within that it has a table called DataTable1. This table has 2 columns, ID and Name. Right now my code (shown below) only pulls the first row from the table. How would I add a foreach loop to this to pull all rows from mySQL? I hope I stated this clearly enough, if not I will do what I can to try and explain. There is a lot more to it than this but I feel like I can figure it out from there. I did try just setting up a dataset that pulls the table directly but that kept freezing my Visual Studio.

private void button7_Click(object sender, EventArgs e)
    {
        try
        {
            cnC03.Open();
            s = "SELECT * FROM c03.tbl_kit t;";
            mcd = new MySqlCommand(s, cnC03);
            mdr = mcd.ExecuteReader();


            if (mdr.Read())
            {
                DataRow newDataRow = dataSet4.Tables["DataTable1"].NewRow();
                newDataRow["ID"] = mdr.GetString("ID");
                newDataRow["Name"] = mdr.GetString("Name");

                dataSet4.Tables["DataTable1"].Rows.Add(newDataRow);
            }
            else
            {
                MessageBox.Show("No Data");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            mdr.Close();
            cnC03.Close();
        }
    }

New code:

private void button7_Click(object sender, EventArgs e)
    {
        try
        {
            s = "SELECT ID, Name FROM c03.tbl_kit";
            using (cnC03)
            using (MySqlCommand mcd2 = new MySqlCommand(s, cnC03))
            {
                cnC03.Open();
                using (MySqlDataReader mdr = mcd2.ExecuteReader())
                {
                    DataTable dt = new DataTable("DataTable1");
                    dt.Load(mdr);
                    dataSet4.Tables.Add(dt);


                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

Answer 1

If you need to load all data returned by your query in a DataTable then you could shorten your code a lot with

s = "SELECT ID, Name FROM c03.tbl_kit";
mcd = new MySqlCommand(s, cnC03);
mdr = mcd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(mdr);

See MSDN on DataTable.Load

Note that if you need only two fields then you should add the exact column's names required in output to the query text.

In your code I see a potential problem. You don't have a local variable for the MySqlConnection. This poses a big problem because a connection is a disposable object and should be created, used and disposed as soon as possible.

I would write something like this

private void button7_Click(object sender, EventArgs e)
{
    try
    {
        s = "SELECT ID, Name FROM c03.tbl_kit";
        using(MySqlConnection cnn = new MySqlConnection(connectionstring))
        using(MySqlCommand mcd = new MySqlCommand(s, cnn))
        {
            cnC03.Open();
            using(MySqlDataReader mdr = mcd.ExecuteReader())
            {
                DataTable dt = new DataTable("DataTable1");
                dt.Load(mdr);            
                //.... use your table ....
                //.... or add it to the global dataset
                //.... of course this means that you haven't done it manually before
                dataSet4.Tables.Add(dt);


            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

}