How to save DataGridView multiple row data in to MySQL database in c#

Question

I have multiple row data in a single column. I need to save all data in to a MySQL DB. But it's only saving selected rows data only in DataGridView.

Below is my sample code.

private void button1_Click(object sender, EventArgs e)
{
    string price = dataGridView1[3, dataGridView1.CurrentCell.RowIndex].Value.ToString();

    string Query = "INSERT INTO db1.table1 (price) VALUES ('"+ price +"');";


    MySqlConnection myConn = new MySqlConnection(MySQLConn);
    MySqlCommand MySQLcmd = new MySqlCommand(Query, myConn);
    MySqlDataReader myReader;
    try
    {
        myConn.Open();
        myReader = MySQLcmd.ExecuteReader();
        while (myReader.Read())
        {

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

Appreciate any help Thank you!

Do you want to save all the rows in your gridview? – Hary Sep 20 '18 at 10:56 — Hary, Sep 20 '18 at 10:56

score 1 · Answer 1 · answered Sep 20 '18 at 11:00

1

One way is to use Foreach loop to get all rows value one by one in DataGridView and then insert them.

foreach (DataGridViewRow row in dataGridView1.Rows)                   
{ 
    string constring = "Connection String";
    using (MySqlConnection con = new MySqlConnection(constring))
    {
        using (MySqlCommand cmd = new MySqlCommand("INSERT INTO db1.table1 (price) VALUES (@price", con))
        {
            cmd.Parameters.AddWithValue("@price", row.Cells["ColumnName"].Value);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

answered Sep 20 '18 at 11:00

SH7

732
7
20

When you use `using`, it will automatically close the connection for you and it is not suggested to open multiple connections inside the loop. In this case, just looping parameters with the new value and executing it should work. – Hary Sep 20 '18 at 11:33
Ya . you are right. Once the Connection is Open just looping should work. – SH7 Sep 20 '18 at 11:53
Thank you both of you. pls look at my reply – Sep 20 '18 at 12:51

Hary · Answer 2 · 2018-09-20T11:09:21.023

If you want to save all rows from your Gridview then loop through it and pick up the column value to save.

Also if you want to save/update to the database, you should use ExecuteNonQuery. Finally, dispose of the objects you're creating and the reason for using.

using (MySqlConnection myConn = new MySqlConnection(MySQLConn))
{
    myConn.Open();

    MySqlCommand MySQLcmd = new MySqlCommand(Query, myConn);
    MySqlParameter priceParameter = new MySqlParameter("@price");
    MySQLcmd.Parameters.Add(priceParameter);

    foreach (DataGridViewRow row in dataGridView1.Rows)
    {
       var price = row.Cells["PRICE_COLUMN_NAME"].Value;

       MySQLcmd.Parameters["@price"].Value = price;

       MySQLcmd.ExecuteNonQuery();
     }
}

score 0 · Answer 3 · answered Sep 20 '18 at 12:48

Dear mbharanidharan88 and user3501749, Thanks for quick support. With your sup I fond a new code. Below is my full working code (for me).

private void button1_Click(object sender, EventArgs e)
    {
       try
        {
            string MySQLConn = "datasource=localhost;port=3306;username=root;password=root;";
            MySqlConnection myConn = new MySqlConnection(MySQLConn);
            myConn.Open();

            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                string price = dataGridView1.Rows[i].Cells[3].Value.ToString();
                string Query = "INSERT INTO db1.table1 (price) VALUES ('"+ price + "');";
                MySqlCommand MySQLcmd = new MySqlCommand(Query, myConn);
                MySQLcmd.ExecuteNonQuery();

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

If anything wrong let me know

And try putting Connection String in .config file and read it to the code on runtime. — SH7, Sep 20 '18 at 12:55
thanks. if I do not close connection. will it cause the memory? or what else? — , Sep 20 '18 at 13:11
When I close connection i get error, "Connection must be valid an open". So I'm using, `using` as you guys before discussed. — , Sep 20 '18 at 13:26

How to save DataGridView multiple row data in to MySQL database in c#

3 Answers3