Dot Net Bulk Insert

I have a project to get read CSV files and pushed those data to SQL table. CSV file contains more than 5000 records.

It is not good practice to insert record one by one SQL table due to it is a time-consuming process.

To optimize the process, Dot net has an ‘SQLBulkCopy’ feature to insert data at once to target table.

I like to share my experience how I used above feature.

1.0   I have read a CSV file and put them to object list.

2.0   For a bulk update, you need to convert object list to a data table.

Code for converting object list to the data table.

   public static DataTable ToDataTable<T>(List<T> items)
   {            DataTable dataTable = new DataTable(typeof(T).Name);
             //Get all the properties            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {                //Defining type of data column gives proper data table
                var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
                //Setting column names as Property names                dataTable.Columns.Add(prop.Name, type);            }            foreach (T item in items)
            {                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {                    //inserting property values to datatable rows                    values[i] = Props[i].GetValue(item, null);
                }                dataTable.Rows.Add(values); 

 }     

     //put a breakpoint here and check datatable 

return dataTable;

 }

2.1 You can call above method as below.

  DataTable dt = new DataTable("MyTable");
  dt = BaseDao.ToDataTable<T>(items);

3.0   After you need to specify the destination table. if source table and destination table’s column fields a are not match then you need to specify mapping columns.

4.0   Completed code is below.

protected override void BulkInsertData<T>(List<T> items)
 {  string connectionString = BaseDao.ConnectionString;
             DataTable dt = new DataTable("MyTable");
            dt = BaseDao.ToDataTable<T>(items);
  using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
  { 

bulkCopy.BulkCopyTimeout = 660;   

bulkCopy.DestinationTableName = "Workday_Employee";

// Set up the column mappings by name.                SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping("Employee_ID", "Workday_Id");bulkCopy.ColumnMappings.Add(mapID);    

SqlBulkCopyColumnMapping mapID1 = new SqlBulkCopyColumnMapping("User_Name", "Username");bulkCopy.ColumnMappings.Add(mapID1);     

bulkCopy.WriteToServer(dt);

      bulkCopy.Close();         

}

Summary