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 the 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.

for (int ind = 0; ind <= 1000; ind++)
            {
                SqlConnection sourceconnection = new SqlConnection("sourceconnectionstring");
                sourceconnection.Open();
                SqlCommand sCommand = new SqlCommand();
                sCommand.CommandText = "Insert into SourceTable(column1,column2,column3)value(value1,value2,value3)";
                sCommand.CommandType = CommandType.Text;
                sCommand.Connection = sourceconnection;
                sCommand.ExecuteNonQuery();
                sourceconnection.Close();
            }

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 a very time and system resources 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 as 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.

1. Connect to the source server to get the data. The source could be various data platforms such as Access, Excel, SQL. You must get the source data in your code wrapping it in a DataTable, or any DataReader class that implements IDataReader. Here we use SQL as source server.
   

   //Code for source server
               DataTable SourceTable=new DataTable();
               SqlConnection sourceconnection = new SqlConnection("sourceconnectionstring");
               sourceconnection.Open();            SqlCommand sCommand = new SqlCommand();
               sCommand.CommandText = "Select * from SourceTable";
               sCommand.CommandType = CommandType.Text;
               sCommand.Connection = sourceconnection;
               SqlDataAdapter sAdapter = new SqlDataAdapter();
               sAdapter.SelectCommand = sCommand;
               sAdapter.Fill(SourceTable);
               sourceconnection.Close();

   In the preceding code SourceTable is the name of the table from which the data is to be copied.
   
   
2. Connect to the Destination Server: The destination server is your SQL Database.
   

   SqlConnection targetconnection = new SqlConnection();
               targetconnection.Open();

3. Create an object of the SqlBulkCopy class:
   

   SqlBulkCopy bcopy =new SqlBulkCopy(destinationConnection);

4. Set the destination table name. The destination table name is the name of the target table in which you want to insert your data:
   

   bcopy.DestinationTableName=”TargetTableName”;

5. Map the Source Table and Destination Table: Before committing the Bulk copy operation you should ensure that the column data types of both the source table and the destination table match or you can match them manually by calling the bulk copy match function.
   

     SqlBulkCopy bcopy = new SqlBulkCopy(sourceconnection);
               SqlBulkCopyColumnMapping mapping=new SqlBulkCopyColumnMapping("SourceColumnName","TargetColumnName");
   bcopy.ColumnMappings.Add(mapping);

6. Call the WriteToServer() method as in the following:
   

   bcopy.WriteToServer(SourceTable);

7. Close the Target server connection:
   

   targetconnection.Close();

Summary

In this illustration we came to learn how to insert a bunch of data using a SQL Bulk copy operation into a SQL database.