Bulk copy using Enterprise Library in C#

SqlBulkCopy is use to upload the data very efficiently from application to database in one shot. The best example could be of excel data; where we got the requirement to upload all the excel data into database table. One could think to hit the database row by row and storing each row into table, but this approach is totally wrong. This will lay down the performance of your application as well as your database. The best way is to do by using Bulk copy. This functionality is only limited to Sql database table.

Below is the sample code, which depicts the usage of SqlBulkCopy with Enterprise Library.

 int InsertRecord(ref SqlDatabase db, DbTransaction transaction) {
     //here you can enter your insertion code
     // db.ExecuteNonQuery("command", transaction);
     return 1;
 }
 public void InsertDataInBulk(DataTable dtData) {
     SqlDatabase db = null;
     DbTransaction transaction = null;
     try {
         int recordId = 0;
         DataTable dt = dtData;

         //remove any empty rows
         using(dt = dt.AsEnumerable().Where(row = > !row.ItemArray.All(field = > object.ReferenceEquals(field, DBNull.Value) | field.Equals(string.Empty))).CopyToDataTable()) {

             db = (SqlDatabase) DatabaseFactory.CreateDatabase();

             // insert all the records in bulk into the database.

             using(DbConnection connection = db.CreateConnection()) {
                 connection.Open();

                 // open transaction only in the case where you have to do multiple database operation
                 using(transaction = connection.BeginTransaction()) {

                     //insert the file record into filelog table
                     recordId = InsertRecord(ref db, transaction);
                     if (recordId > 0) {


                         using(SqlBulkCopy bulkCopy = new SqlBulkCopy((SqlConnection) connection, SqlBulkCopyOptions.Default, (SqlTransaction) transaction)) {
                             //if you're unsure of the column sequence in source and destination table then it's better to map the columns

                             SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping("ProductID", "ProdID");
                             bulkCopy.ColumnMappings.Add(mapID);

                             SqlBulkCopyColumnMapping mapName = new SqlBulkCopyColumnMapping("Name", "ProdName");
                             bulkCopy.ColumnMappings.Add(mapName);

                             SqlBulkCopyColumnMapping mapMumber = new SqlBulkCopyColumnMapping("ProductNumber", "ProdNum");
                             bulkCopy.ColumnMappings.Add(mapMumber);

                             bulkCopy.DestinationTableName = "YourTableName";
                             bulkCopy.WriteToServer(dt);
                         }
                         transaction.Commit();

                     } else transaction.Rollback();
                 }

             }

         }

     } catch (Exception ex) {
         if (transaction != null) transaction.Rollback();
         //Log your error

     } finally {
         db = null;
     }
 }

Bulkcopy can also be used by using SqlConnection. But the above example is other way around.