CODEWITHSUNDEEP
c#sqlasp.netsql-serverwindows
Asbar Ali
Asbar Ali

Reputation: 995

How to increase the efficiency for saving multiple rows in .NET windows application?

In my .NET Windows application, I need to insert around 10 millions data per time.However, it takes more than 5 minuites to save into SQL server according to my inefficient code.Are there any best way minimize the time taken to save those data?

private void saveAllDataInGrid()
    {
        int rowCount = dataGridView1.RowCount;

        String str = "server=DESKTOP-TDV8JQ7;database=ExcelFileApp;Integrated Security=SSPI";
        SqlConnection con = new SqlConnection(str);
        con.Open();
        for (int count = 0; count < rowCount; count++)
        {
            try
            {
                String query = "insert into TemMainTable values (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23)";
                SqlCommand cmd = new SqlCommand(query, con);

                cmd.Parameters.AddWithValue("@p1", dataGridView1.Rows[count].Cells[0].Value.ToString());
                cmd.Parameters.AddWithValue("@p2", dataGridView1.Rows[count].Cells[1].Value.ToString());
                cmd.Parameters.AddWithValue("@p3", dataGridView1.Rows[count].Cells[2].Value.ToString());
                cmd.Parameters.AddWithValue("@p4", dataGridView1.Rows[count].Cells[3].Value.ToString());
                cmd.Parameters.AddWithValue("@p5", dataGridView1.Rows[count].Cells[4].Value.ToString());
                cmd.Parameters.AddWithValue("@p6", dataGridView1.Rows[count].Cells[5].Value.ToString());
                cmd.Parameters.AddWithValue("@p7", dataGridView1.Rows[count].Cells[6].Value.ToString());
                cmd.Parameters.AddWithValue("@p8", dataGridView1.Rows[count].Cells[7].Value.ToString());
                cmd.Parameters.AddWithValue("@p9", dataGridView1.Rows[count].Cells[8].Value.ToString());
                cmd.Parameters.AddWithValue("@p10", dataGridView1.Rows[count].Cells[9].Value.ToString());
                cmd.Parameters.AddWithValue("@p11", dataGridView1.Rows[count].Cells[10].Value.ToString());
                cmd.Parameters.AddWithValue("@p12", dataGridView1.Rows[count].Cells[10].Value.ToString());
                cmd.Parameters.AddWithValue("@p13", dataGridView1.Rows[count].Cells[12].Value.ToString());
                cmd.Parameters.AddWithValue("@p14", dataGridView1.Rows[count].Cells[13].Value.ToString());
                cmd.Parameters.AddWithValue("@p15", dataGridView1.Rows[count].Cells[14].Value.ToString());
                cmd.Parameters.AddWithValue("@p16", dataGridView1.Rows[count].Cells[15].Value.ToString());
                cmd.Parameters.AddWithValue("@p17", dataGridView1.Rows[count].Cells[16].Value.ToString());
                cmd.Parameters.AddWithValue("@p18", dataGridView1.Rows[count].Cells[17].Value.ToString());
                cmd.Parameters.AddWithValue("@p19", dataGridView1.Rows[count].Cells[18].Value.ToString());
                cmd.Parameters.AddWithValue("@p20", dataGridView1.Rows[count].Cells[19].Value.ToString());
                cmd.Parameters.AddWithValue("@p21", dataGridView1.Rows[count].Cells[20].Value.ToString());
                cmd.Parameters.AddWithValue("@p22", dataGridView1.Rows[count].Cells[21].Value.ToString());
                cmd.Parameters.AddWithValue("@p23", dataGridView1.Rows[count].Cells[22].Value.ToString());

                int i = cmd.ExecuteNonQuery();

                lblDataSaved.Text = "No of rows Saved : " + rowCount;
            }
            catch (Exception es)
            {
                MessageBox.Show(es.Message);
            }
        }
        con.Close();
        MessageBox.Show("Successfully Saved");
    }

Thanks!!

Upvotes: 2

Views: 357

Answers (1)

Gaurav P
Gaurav P

Reputation: 1107

You can use User-Defined Table Types for bulk insert. You need to create a User-Defined Table Types in the database SQL Server Management Studio >> Programability >> User-Defined Table Types

The SQL Script Syntax will be like this to create the User-Defined Table Types

CREATE TYPE [dbo].[TableList] AS TABLE(
    [SampleColumn1] [bigint] NOT NULL,
    [SampleColumn2] [nvarchar](100) NOT NULL    
)

The Sample SQL Insert Query will be as below - 

INSERT  INTO SampleTable
        ( SampleColumn1 ,
          SampleColumn2

        )
        SELECT  cusTbl.SampleColumn1 ,
                cusTbl.SampleColumn2
        FROM    @TableList AS cusTbl;

In C# Code - the general Syntax to insert is as below - 

SqlCommand cmd = new SqlCommand(query, con);    
var tvparam = cmd.Parameters.AddWithValue("@TableList", tableList);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam.TypeName = "dbo.TableList" 
cmd.Connection.Open();
cmd.ExecuteNonQuery();

Note: You had used DataGridView in your Windows Application. So you can easily pass the DataTable which you had used to bind the DataGridView. Take care that the DataTable have same ColumnName and DataType which is declared in SQL Server Database User-Defined Table Types

I need to insert around 10 millions data per time.However, it takes more than 5 minutes Definitely time taken to insert will reduce - compared to sending rows one by one to SQL Server. More Reading Here and in this Blog

Upvotes: 5

Related Questions