Insert several rows in a gridView without using a 'for' loop

Question

I am creating a Attendance System and using grid view to insert the data. There may be many rows on the grid. All things are going well and data are also entering well. But I am using a for loop to check each row. This make the performance quite slow when the number of rows increases. And also the round trips increases with the growing number of rows.

Can anyone provide a better solution for this?

I have modify my CODE according to u all.....but now a problem has arise it is only inserting the last row of the grid multiple times......Other than this the Code is fine.

MySqlDataAdapter myworkdatta = myworkdatta = new MySqlDataAdapter("SELECT CID,EID,TID,ATTENDENCE FROM EMPLOYEEATT ORDER BY AID DESC LIMIT 1", conn);
                                    DataSet myworkdsatt = new DataSet();
                                    myworkdatta.Fill(myworkdsatt, "EMPLOYEEATT");

                                    int i;
                                    for (i = 0; i < emplist_gv.Rows.Count; i++)
                                    {
                                        string tid = emplist_gv.Rows[i].Cells[6].Value.ToString();
                                        string eid = emplist_gv.Rows[i].Cells[0].Value.ToString();
                                        string atid = emplist_gv.Rows[i].Cells[7].Value.ToString();

                                        MySqlCommand cmdwk = new MySqlCommand("INSERT INTO EMPLOYEEATT (CID,EID,TID,ATTENDENCE) VALUES (@cid,@eid,@tid,@attendence)", conn);
                                        MySqlParameter spcidatt = new MySqlParameter("@cid", calid);
                                        MySqlParameter speid = new MySqlParameter("@eid", eid);
                                        MySqlParameter sptid = new MySqlParameter("@tid", tid);
                                        MySqlParameter spattendence = new MySqlParameter("@attendence", atid);

                                        cmdwk.Parameters.Add(spcidatt);
                                        cmdwk.Parameters.Add(speid);
                                        cmdwk.Parameters.Add(sptid);
                                        cmdwk.Parameters.Add(spattendence);

                                        myworkdatta.InsertCommand = cmdwk;

                                        DataRow drowk = myworkdsatt.Tables["EMPLOYEEATT"].NewRow();
                                        drowk["CID"] = calid;
                                        drowk["EID"] = eid;
                                        drowk["TID"] = tid;
                                        drowk["ATTENDENCE"] = atid;

                                        myworkdsatt.Tables["EMPLOYEEATT"].Rows.Add(drowk);

                                    }

                                    myworkdatta.Update(myworkdsatt, "EMPLOYEEATT");

Move selects before loop, make changes of table inside loop and update table after loop. Selecting 2 times and inserting data during one iteration of loop is not good idea. — Renatas M., Dec 05 '11 at 07:18
Select Statement is needed because it will be fetching the last row ID and increment by 1 with the regular exp. — Aaraadhana, Dec 05 '11 at 14:31
I have modify my CODE according to u .....but now a problem has arise it is only inserting the last row of the grid multiple times......Other than this the Code is fine. — Aaraadhana, Dec 05 '11 at 19:12
you need to get id before loop and increment it in each iteration. Something like `drowk["ATTENDENCE"] = atid + i;` — Renatas M., Dec 05 '11 at 21:05
the ID is auto_incremented so its happening on the Database itself... — Aaraadhana, Dec 06 '11 at 17:34
.NewRow() and .Rows.Add only Create new Rows in DataRows and add the values to the Dataset....we need to Update to make changes in the Database. The ID will be automatically generated....I can not increment it outside the loop....I followed your exact step... — Aaraadhana, Dec 06 '11 at 17:42
at the beggining you select last row id lets say its 5. In loop you dont insert and select row id. as you said its autoincrement so in first iteration id will be 5+1 so why execute select again? — Renatas M., Dec 06 '11 at 19:15
Sir i am executing Select once only as you can see in my Code...The Select statement in before the loop. Ya it will get the last row ID. As you can see in my code i am looping through the grid view rows and storing it in DataRows with the last row from the Select Statement. The Dataset contains all the new row its fine no problem. But when m updating the DataSet to the Database which you can see i have done after the end of looping statement only the last value of the DataSet is getting inserted multiple times.With your idea it has reduce the Hits to the Database but this problem has arised... — Aaraadhana, Dec 07 '11 at 07:41
Yeah i see the problem. `myworkdatta.InsertCommand = cmdwk;` this thing...when you rise update method, it will execute last insert command for all records. If you move update statement inside loop it reduces performance significantly? — Renatas M., Dec 07 '11 at 08:02

score 1 · Accepted Answer · edited May 23 '17 at 11:48

1

Considering your 2 select SQL statement doesn't seem to contain anything relevant to the the specific row you can take that out of the loop and just use its values easy enough.

Because you need to do an insert on each row, which I don't understand why, then it seems hard to remove the database hits there.

If you are doing a bulk insert you could look at bulk inserts for MySql: MySql Bulk insert

edited May 23 '17 at 11:48

Community

1
1

answered Dec 05 '11 at 06:49

Adam

16,089
6
66
109

Sir can't this rows be stored in the Datatable temp. and later updated to the Database... – Aaraadhana Dec 05 '11 at 07:13
Sir the Select Statement is needed because i will be fetching the last row ID and increment by 1 with the regular exp. – Aaraadhana Dec 05 '11 at 07:16
You could store it in a file and run a batch process in the background if you wanted. It couldn't be stored in memory or anywhere so there will still be some disk writing going on. Probably a lot faster than DB writing. – Adam Dec 05 '11 at 07:34
With getting the last row you can use mysql_insert_id() in the same insert statement and get the ID back with the insert. As for incrementing the ID by 1, if you are doing this manually it should be done in the database by setting the primary key as an identity column – Adam Dec 05 '11 at 07:35
I have modify my CODE according to u .....but now a problem has arise it is only inserting the last row of the grid multiple times......Other than this the Code is fine. – Aaraadhana Dec 05 '11 at 19:12
I am not sure if the .NewRow() and .Rows.Add actually update your database. If they dont and you need to call the .Update to do this, put the Update back in the loop. – Adam Dec 06 '11 at 08:43
No sir .NewRow() and .Rows.Add only Create new Rows in DataRows and add the values to the Dataset....we need to Update to make changes in the Database. Sir if i put back the Update inside the loop the round trip will increase and it will be same what i have done earliar....The more rows the more Hits in the Database – Aaraadhana Dec 06 '11 at 17:40
But it needs to send those inserts anyway, there is no way to get around that. You could do a bulk upload but I am not sure how to in MySql. Moving the select out the loop has already halved your database hits. Other than that, if you dont need realtime updates the only other way would be writing it all to a file and doing a background upload. But from the description of your project that doesn't sound like it would be a good idea. – Adam Dec 08 '11 at 02:32
Another idea would be to use AJAX or a timer depending on if you are using ASP.NET or WPF/WinForms and as the data is entered it keeps uploading and saving the rows as they move on to the next one. Then changing the row to readonly controls as it is saved. This way there is no long wait at the end. On the other hand this is quite a bit more code to write for your application. – Adam Dec 08 '11 at 02:33

Elias Hossain · Answer 2 · 2011-12-05T09:31:10.607

You can use SqlBulkCopy, it's easy to use. Basically just provide it with a data table (or data reader) and it will copy the rows from that source to your destination table.

Shortly, the code block would look like:

DataTable dataTableInGridView = (DataTable)emplist_gv.DataSource;
using (SqlConnection connection =
            new SqlConnection(connectionString))
{
    using (SqlBulkCopy bulkCopy =
                    new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName =
            "dbo.BulkCopyDemoMatchingColumns";
        try
        {
            // Write from the source to the destination.
            bulkCopy.WriteToServer(dataTableInGridView);
        }
        catch (Exception ex)
        {
            // Handle exception
        }
    }
}

SqlBulkCopy is not Supported in MySQl – Aaraadhana Dec 05 '11 at 19:13 — Aaraadhana, Dec 05 '11 at 19:13

Insert several rows in a gridView without using a 'for' loop

2 Answers2