How to add Bulk Table into Database using Asp.net

Question

I have a situation here where I need to upload an Excel sheet on which there may be records more than 50K. The problem is now im managing it using a DataTable. Im storing the entire records into a DataTable and im manupulating the Rows and Inserting it into the DB one by one.

I can see that its only inserting 50recrods in a Second and It will take hours to complete the entire process..

Is there any way that you can send the Entire DataSet or DataTable to the SQL so it will be much Faster than this??

Im using Web Application.

---

In Windows App there is option for Bulk update with DataAdapter. Like wise is there any method for ASP.NET

Answer 1

hope this you want

 public void BultInsert(DataTable dtSource)
            {
                System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(GetconnectionString());//add connectionstring here

                bcp.DestinationTableName = "DestinationTable";//give destination table name

                //bcp.ColumnMappings.Add("Column1", "Column1");//Map all columns

                bcp.ColumnMappings.Add("Column2", "Column2");

                bcp.ColumnMappings.Add("Column3", "Column3");            
    // and so on...., maap all source table with your destination table
                if (dtSource.Rows.Count > 0)
                {
                    bcp.WriteToServer(dtSource);
                }

            }