How to insert all datagridview data of same vendor in one row of sql database

Question

This code insert all the the rows of datagridview as different rows in database with same vendor name. Here is my code. database table where gridview data insert into different rows.

43 7869 Bouzia Pharamectuial(pvt) Ld 0000-00-00 00:00:00 0 0 1741 45 abc pending 36005

44 7869 Bouzia Pharamectuial(pvt) Ld 0000-00-00 00:00:00 0 0 1741 35 As pharma pending 36005

string StrQuery;
        connString = "[enter image description here][1]";
        try
        {
            conn = new MySqlConnection();
            conn.ConnectionString = connString;


            conn.Open();
            using (MySqlCommand cmd = new MySqlCommand())
                {


                    cmd.Connection = conn;
                for (int i = 0; i < invoice_insertion_grid_view.Rows.Count -1; i++)
                {
                    StrQuery = @"INSERT INTO purchasing Values('','" + bill_purchasing_invoice_textbox.Text + "','" + bill_purchasing_vendor_combobox.SelectedItem.ToString() + "','" + bill_purchasing_date_textbox.Value.Date + "','','','" + bill_purchasing_total_balance_textbox.Text + "','" + invoice_insertion_grid_view.Rows[i].Cells["quantity"].Value + "','" + invoice_insertion_grid_view.Rows[i].Cells["medicine_name"].Value + "','pending','" + purchasing_invoice_payable_box.Text + "')";

                    cmd.CommandText = StrQuery;
                    cmd.ExecuteNonQuery();



                }

                MessageBox.Show("Data is inserted successfully");
                cmd.Dispose();
                conn.Close();


            }




        }
        catch (MySql.Data.MySqlClient.MySqlException ex)
        {
            MessageBox.Show(ex.Message);
        }

I would create a DataTable using the DataAdapter method. the create an Xml file using DataTable.WriteXml(MemoryStream) which will give an xml string. Then save xml into database. — jdweng, May 29 '18 at 12:30
Well, you're inserting a record within a loop. So this is going to insert one record per iteration of the loop. What did you *want* to do? If you only want to insert a single record, don't perform the insert operation inside of a loop. — David, May 29 '18 at 12:32

score 1 · Answer 1 · answered May 29 '18 at 12:48

The code currently reads each row of the table and executes an associated INSERT query. That's N queries for an N sized table. If you want to coalesce all of the data into a single DB row, you'll need to create and execute a single INSERT statement, presumably with all of the rows concatenated into a single string. Something like:

var strData = "";
for (int i = 0; i < invoice_insertion_grid_view.Rows.Count -1; i++) {
    strData += // <this row's data>;
}
StrQuery = @"INSERT INTO purchasing Values(" + strData + ")";
cmd.CommandText = StrQuery;
cmd.ExecuteNonQuery();

However, I heavily question if this is what you want to do. In my career, there has been exactly once where putting multiple rows into a single string was the correct answer. Every other time, it was someone not understanding the power and utility of SQL and how relational databases work.

Meanwhile, while here, I'll point out some other details:

Fully take advantage of C#'s using statement. You do not need to manually dispose the db command or close the db connection.
Learn about -- and use -- prepared statements. Almost every time, when using string interpolation to build up a SQL statement (e.g., @"INSERT INTO ..." + someDataValue + " ..."), you're doing it wrong.

How to insert all datagridview data of same vendor in one row of sql database

1 Answers1