Performing Bulk Copy in ADO.NET

Introduction

This article explains how to insert a large amount of data using the SqlBulkCopy class. Suppose you are in a situation where you need to insert more than a thousand items of data into a SQL table. What will you do in the situation? You will normally thin insert data one by one in a loop, but doing it that way will take a lot of time. Because your code will execute a thousand times for opening the connection, inserting the data, and closing the connection, as in the following example.

ADO.NET provides a better solution named bulk copy operation for this kind of problem. It provides a class name SqlBulkCopy for a bulk copy operation. The bulk copy operation in .Net is a very fast way to copy a large amount of data somewhere to SQL Server. The reason for that is the Bulkcopy SQL Server mechanism. Inserting all data row by row, one after the other is very time and system resource consuming. But the bulk copy mechanism processes all the data at once. So the data insertion becomes very fast. The simplest approach to do a SQL Server bulk copy operation is to do a single bulk copy operation against a database. By default, a bulk copy operation is an isolated operation. The copy operation occurs in a not-transacted way with no opportunity for rolling it back. To do the bulk copy operation, you need to use the following procedure.