Connecting a MySQL table to a DataGridView control in C#

Question

I'm working on a project where I need to use datagridview in c# I want to do a test, when I do the search for a product by it's number, if the number is incorrect it will display an error, which works fine in my case, and if the number is correct it will display the information related to this product in the datagridview, which doesn't work, it gives me nothing when I type a correct number. here is the code, help me please

private MySqlDataAdapter mySqlDataAdapter;
private void button1_Click(object sender, EventArgs e)
{
    int n = Convert.ToInt32(t_ref.Text);

    string cs = "datasource=localhost;port=3306;database=stock;username=root;password=;";

    MySqlConnection con = new MySqlConnection(cs);
    try
    {
        con.Open();
    }
    catch (Exception)
    {
        MessageBox.Show("Erreur de connexion à la base de donnée !", "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);

    }

    string query = "select * from produits where reference = " + n + "; ";
    MySqlCommand cmd = new MySqlCommand(query, con);
    MySqlDataReader dr = cmd.ExecuteReader();

    if (dr.Read())
    {
        dg2.DataSource =dr;

    }
    else
    {
        MessageBox.Show("Aucun élément avec ce reférence a été trouvé !", "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
    }
}

this is a c# windows form application, the reference field type is a textbox, the reference type is int — Anis Dhaouefi, May 05 '16 at 21:52

score 0 · Accepted Answer · edited May 23 '17 at 12:07

Use an MySqlDataReader to fill a DataTable and use the DataTable as the DataSource

string query = "select * from produits where reference = @num";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.Add("@num", MySqlDbType.VarChar).Value =  n; 
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader())

if (dt.Rows.Count > 0)
{
    dg2.DataSource =dt;

}
else
{
    MessageBox.Show("Aucun élément avec ce reférence a été trouvé !", "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}

Notice also that a query text should always constructed using parameters and not concatenating string together. If you join together strings received by your user is too easy for a malicious user write something that could destroy your database or reveal confidential informations like username and passwords.

See this famous comic by XKCD

Anoterh important point is to have

 dg2.AutoGenerateColumns = true;

score 0 · Answer 2 · answered May 05 '16 at 22:00

you must add the Apostrophe '' in query like this :

string query = "select * from produits where reference = '" + n + "'; ";

But I advice you to use the Parameter to avoid sql injections, like this :

string query = "select * from produits where reference = @reference ";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.Add("@reference ", MySqlDbType.VarChar).Value = n;
MySqlDataReader dr = cmd.ExecuteReader();

Connecting a MySQL table to a DataGridView control in C#

2 Answers2