SQL Bulk Copy

Some times we faced situation when we need to insert single row for each millions of iterations. For example, we read from an Excel file which contains millions of students records and we want to add the details to the data base.  This can be done using the Entity Framework as follows,

                foreach (var student in students)
                {
                        using (var ctx = new dbContext())
                        {
                            ctx.Students.AddObject(student);
                            ctx.SaveChanges();
                        }              
                }

However this is inefficient. We can make use of SqlBulkCopy class to do the trick.

          string connString = "";
            // connect to SQL
            using (SqlConnection connection =  new SqlConnection(connString))
            {
                // make sure to enable triggers
                // more on triggers in next post
                SqlBulkCopy bulkCopy = new SqlBulkCopy(
                    connection,
                    SqlBulkCopyOptions.TableLock |
                    SqlBulkCopyOptions.FireTriggers |
                    SqlBulkCopyOptions.UseInternalTransaction,
                    null
                    );

                // set the destination table name
                bulkCopy.DestinationTableName = "tableName";
                connection.Open();

                // write the data in the "dataTable"
                bulkCopy.WriteToServer(dataTable);
                connection.Close();
            }
This would help to reduce the over head.