Saving million rows into database

Question

i am using the below code to insert data into database using datagridview but found out that it is not a good approach to use for loop as my data is very huge (million rows)

 if (dataGridView1.Rows.Count > 1)
        {

            for (int i = 0; i <= dataGridView1.Rows.Count - 1; i++)
            {

                string col1 = dataGridView1.Rows[i].Cells[0].Value.ToString();

                string col2 = dataGridView1.Rows[i].Cells[1].Value.ToString();

                string col3 = dataGridView1.Rows[i].Cells[2].Value.ToString();

                string col4 = dataGridView1.Rows[i].Cells[3].Value.ToString();

                string col5 = dataGridView1.Rows[i].Cells[4].Value.ToString();

                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectstring"].ConnectionString))
                {

                    string insert = "INSERT INTO InvoiceItems(SNo,Quantity,Rate,Description,Total) VALUES (@SNo,@Quantity,@Rate,@Description,@Total)";

                    con.Open();

                    SqlCommand cmd = new SqlCommand(insert, con);
                   // so on 

Please suggest me a faster and memory efficient approach which can save data much quicker into database

Answer 1

Load the DataGridView with a DataTable than use (if SQL-Server) SqlBulkCopy, see example.