SQL Bulk Copy in ASP.NET

Sql bulk copy is used when large amounts of data need to insert into database. Let I want to export data from excel. Excel have multiple columns so it can be achieve by following steps.


Step 1: Convert Excel Data to Data Table.
Step 2: Map columns between Data Table & Database Table.
Step 3: Export by SQL bulk copy.

Here I am going to discuss example.

 public void BatchBulkCopy(DataTable dt, string DestinationTbl, int batchSize   
  , Dictionary<string, string> MapColumns, int BatchTimeout)  
    {  
      // Get the DataTable   
      DataTable dtInsertRows = dt;  
      SqlConnection conn = new SqlConnection();  
      conn.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnection"].ToString();  
      conn.Open();  
      try  
      {   
        using (SqlBulkCopy sbc = new SqlBulkCopy(conn))  
        {  
          sbc.DestinationTableName = DestinationTbl;  
          // Number of records to be processed in one go  
          sbc.BatchSize = batchSize;  
          sbc.BulkCopyTimeout = BatchTimeout;                  
          // Add your column mappings here            
          foreach (KeyValuePair item in MapColumns)  
          {  
            sbc.ColumnMappings.Add(item.Key, item.Value);  
          }  
          // Finally write to server  
          sbc.WriteToServer(dtInsertRows);          
        }  
      }  
      catch (Exception ex)  
      {  
      }  
      finally  
      {  
        conn.Close();  
      }  
    }  

Parameter Description

dt: It is data table to export data.
DestinationTbl: table name where data need to export.
batchSize: Number of rows that will export at a time.
MapColumns: It is a dictionary object Contains details for mapping columns.

BatchTimeout: Time out for sql bulk copy.

Export Data Example

 Dictionary<string,string> MappingColumn = new Dictionary<string,string>();  
 MappingColumn.Add("productname","Name"); // (Excel Column Name, Data Base Table Column)  
 MappingColumn.Add("productid","ProductId");   
 MappingColumn.Add("description","Description");   
 DataTable dt = ExportExcelToDataTable("~/File/product.xslt");  
 BatchBulkCopy(dt, "DataImportTbl", 500, MappingColumn, 100);