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.
– Renatas M., Commented Dec 5, 2011 at 7:18
Select Statement is needed because it will be fetching the last row ID and increment by 1 with the regular exp. — Aaraadhana
– Aaraadhana, Commented Dec 5, 2011 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
– Aaraadhana, Commented Dec 5, 2011 at 19:12
you need to get id before loop and increment it in each iteration. Something like drowk["ATTENDENCE"] = atid + i; — Renatas M.
– Renatas M., Commented Dec 5, 2011 at 21:05
the ID is auto_incremented so its happening on the Database itself... — Aaraadhana
– Aaraadhana, Commented Dec 6, 2011 at 17:34

Community · Accepted Answer · 2017-05-23 11:48:47Z

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, 2017 at 11:48

CommunityBot

11 silver badge

answered Dec 5, 2011 at 6:49

Adam

16.2k7 gold badges68 silver badges112 bronze badges

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

9 Comments

Aaraadhana Over a year ago

Sir can't this rows be stored in the Datatable temp. and later updated to the Database...

Aaraadhana Over a year ago

Sir the Select Statement is needed because i will be fetching the last row ID and increment by 1 with the regular exp.

Adam Over a year ago

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 Over a year ago

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

Aaraadhana Over a year ago

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.

|

Elias Hossain · Accepted Answer · 2011-12-05 09:31:10Z

0

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
        }
    }
}

edited Dec 5, 2011 at 9:31

answered Dec 5, 2011 at 9:17

Elias Hossain

4,4991 gold badge23 silver badges33 bronze badges

1 Comment

Aaraadhana Over a year ago

SqlBulkCopy is not Supported in MySQl

Collectives™ on Stack Overflow

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

2 Answers 2

9 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related