How to insert data into SQL Server (from excel) faster

Question

I use this code to insert data from the excel sheet in to sql server table. Is there another way to make it fast because it takes a lot of time to insert records in it.

private void Button2_Click(object sender, EventArgs e)
    {

            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;

            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                cmd.CommandText = "INSERT INTO tabl(Name_Arabic, CIVILIDD , gender, NATIONALITY, TEL8, TEL7, TEL6, TEL5, TEL4, TEL3, TEL2, TEL1, Work_Adderss, Home_Address, Home_Address2, Name_eng, username, note, Governorate, City, Block, Street, Avenue, House, Floor, flat, address_note, status, confirmation,Irregularities,Irregularities_date)values" +
                "(N'" + dataGridView1.Rows[i].Cells[0].Value + "',N'" + dataGridView1.Rows[i].Cells[1].Value + "',N'" + dataGridView1.Rows[i].Cells[2].Value + "',N'" + dataGridView1.Rows[i].Cells[3].Value + "',N'" + dataGridView1.Rows[i].Cells[4].Value + "',N'" + dataGridView1.Rows[i].Cells[5].Value + "',N'" + dataGridView1.Rows[i].Cells[6].Value + "',N'" + dataGridView1.Rows[i].Cells[7].Value + "',N'" + dataGridView1.Rows[i].Cells[8].Value + "',N'" + dataGridView1.Rows[i].Cells[9].Value + "',N'" + dataGridView1.Rows[i].Cells[10].Value + "',N'" + dataGridView1.Rows[i].Cells[11].Value + "',N'" + dataGridView1.Rows[i].Cells[12].Value + "',N'" + dataGridView1.Rows[i].Cells[13].Value + "',N'" + dataGridView1.Rows[i].Cells[14].Value + "',N'" + dataGridView1.Rows[i].Cells[15].Value + "',N'" + dataGridView1.Rows[i].Cells[16].Value + "',N'" + dataGridView1.Rows[i].Cells[17].Value + "',N'" + dataGridView1.Rows[i].Cells[18].Value + "',N'" + dataGridView1.Rows[i].Cells[19].Value + "',N'" + dataGridView1.Rows[i].Cells[20].Value + "',N'" + dataGridView1.Rows[i].Cells[21].Value + "',N'" + dataGridView1.Rows[i].Cells[22].Value + "',N'" + dataGridView1.Rows[i].Cells[23].Value + "',N'" + dataGridView1.Rows[i].Cells[24].Value + "',N'" + dataGridView1.Rows[i].Cells[25].Value + "',N'" + dataGridView1.Rows[i].Cells[26].Value + "',N'" + dataGridView1.Rows[i].Cells[27].Value + "',N'" + dataGridView1.Rows[i].Cells[28].Value + "',N'" + dataGridView1.Rows[i].Cells[29].Value + "',N'" + dataGridView1.Rows[i].Cells[30].Value + "')";
                conn.Open();
                cmd.ExecuteNonQuery();

                conn.Close();
            }
            MessageBox.Show("saved");

    }

her im try to add the gridview data to the database and it is different from the duplicate answer

You should prepare all the statements (end each statement with a `;`) combine them into one string and only _then_ open a connection and execute a single query. Opening and closing the connection for each of your loop cycles takes a lot of time already. — Markus Deibel, Aug 14 '19 at 07:09

score 0 · Accepted Answer · answered Aug 14 '19 at 07:09

You should create fever but bigger bunches of data to be inserted with minimun amount of database transactions. Multiple rows with one-by-one insertions can be slow.

Bulk operations can be implemented for example following ways:

With SQL Bulk Insert operations provided by .NET framework
By using external library which is specialized on high-speed Bulk operations
By crafting sql stored procedure which takes array of data as parameter. More information about table-valued parameters can be found in https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine

Please notice also that your solution have bad SQL Injection vulnerability.

score -1 · Answer 2 · answered Aug 14 '19 at 07:17

One easy way to improve could look like

private void Button2_Click(object sender, EventArgs e)
{
    cmd.CommandType = CommandType.Text;
    cmd.Connection = conn;

    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
    {
        cmd.CommandText += // Append additional statements
        "INSERT INTO tabl (Name_Arabic, 
        // ...
        ) values" +
        "(N'" + dataGridView1.Rows[i].Cells[0].Value + 
             //..."');" // End each statement with ;
        ;
    } 

    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();

    MessageBox.Show("saved");
}

How to insert data into SQL Server (from excel) faster

2 Answers2