how to loop through each row in datatable and insert data in mySQL

Question

I have this code that loops through each row in a datagridview but I don't know what is the equivalent code in using a datatable. I tried using datarow instead of DataGridViewRow but I don't know how to get the data in each row like I did in this code. So how will I get data in each row in datatable?

using (MySqlCommand cmd = new MySqlCommand(query_1, con))
{
foreach (DataGridViewRow dr in dataGridView2.Rows)
{
if (Convert.ToInt32(dr.Cells["quantity"].Value) > 0)
{
cmd.Parameters.AddWithValue("@product", dr.Cells["product_name"].Value);
cmd.Parameters.AddWithValue("@variant", dr.Cells["variant_name"].Value);
cmd.Parameters.AddWithValue("@size", dr.Cells["size"].Value);
cmd.Parameters.Add("@qty", MySqlDbType.Int32, 8).Value = dr.Cells["quantity"].Value;
cmd.Parameters.AddWithValue("@ID", txtboxID.Text);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
}

Steve · Accepted Answer · 2013-05-25 13:29:05Z

1

Use a syntax like this

dr.Field<string>("product_name");

Requires a reference to System.Data.DataSetExtensions.dll

using (MySqlCommand cmd = new MySqlCommand(query_1, con))
{
    cmd.Parameters.AddWithValue("@product", "");
    cmd.Parameters.AddWithValue("@variant", "");
    cmd.Parameters.AddWithValue("@size", 0);
    cmd.Parameters.Add("@qty", MySqlDbType.Int32, 8).Value = 0:
    cmd.Parameters.AddWithValue("@ID", txtboxID.Text);
    foreach (DataRow dr in yourTable.Rows)
    {
         if (dr.Field<int>("quantity") > 0)
         {
             cmd.Parameters["@product"].Value = dr.Field<string>("product_name");
             cmd.Parameters["@variant"].Value = dr.Field<string>("variant_name");
             cmd.Parameters["@size".Value = dr.Field<int>("size");
             cmd.Parameters["@qty"].Value = dr.Field<int>("quantity");
             cmd.ExecuteNonQuery();
             // no need to clear, reuse the same set of parameters
             //  cmd.Parameters.Clear();
          }
     }
}

edited May 25, 2013 at 13:29

answered May 25, 2013 at 13:21

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Harvey Over a year ago

Ahmm...is it okay if I do it like this cmd.Parameters.AddWithValue("@product", row["product_name"]);??

Steve Over a year ago

Yes, but you need to convert to the appropriate datatype, also look at the updated answer to get an idea on a change that will avoid to recreate the parameters at each loop

Harvey Over a year ago

Okay, I tried to add the reference System.Data.DataSetExtensions.dll but I can't find it in the list. Where I can find it?

Steve Over a year ago

Which Version of DotNet Framework are you using? However, right click on the References of your project and select Add Reference, click on Assembly and search for System.Data.DataSetExtensions. Then double click the file and confirm the dialog box

Harvey Over a year ago

I tried to run the application even without adding the reference you have said. It returns an error saying Specified cast is not valid?? Do you think that is the cause?

|

Collectives™ on Stack Overflow

how to loop through each row in datatable and insert data in mySQL

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related