AspBucket offers ASP.NET, C#, VB, Jquery, CSS, Ajax, SQL tutorials. It is the best place for programmers to learn

Tuesday 27 October 2015

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();  
        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  
      catch (Exception ex)  
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)  
 DataTable dt = ExportExcelToDataTable("~/File/product.xslt");  
 BatchBulkCopy(dt, "DataImportTbl", 500, MappingColumn, 100);  


Post a Comment

  • Popular Posts
  • Comments