ADO.NET

BCP (Bulk Copy) and DTS (Data Transformation Services) both work much faster than a row by row SQL statement insert would be. In .NET Framework if we wado.net to implement the functionality of copying over vast amounts of data between databases then the DataSet or data adapter would have been a very poor option because filling the DataSet, sending it across, changing all row states, iterating over each single row, and executing one SQL command per row would take so long time .

.NET 2.0, has introduced a new class specifically for this purpose: the SqlBulkCopy class. , SqlBulkCopy copies data from one table to another over two different open SqlConnections. The two different open SqlConnections can also point to the same database if that is what you prefer, or they may point to different databases.

There is following step using BCP:

1. Start a new Console application, and name it SqlBulkCopy.
2. The aim of this application is to fast copying of data between two tables of identical structure. So first step is to create two tables of identical structure. You can easily achieve this with the following SQL statement:

Create Table student as Select * from student  where 1 = 2

3. we will need two different SqlConnections: one for the data reader that the SqlBulkCopy will read from, and one for SqlBulkCopy itself. So let’s start by creating the data reader that will read from the Student table. This can be seen in the following code:

   using (SqlConnection sqlconn =
       new SqlConnection(connectionString))
   {
   SqlCommand sqlcomm = sqlconn.CreateCommand();
   sqlcomm.CommandText = "Select * from student";
   sqlconn.Open();
   SqlDataReader dr = sqlcomm.ExecuteReader();
   }
   // Dispose is called on firstConnection
   

4. The second part of the application uses the SqlBulkCopy object instance to insert data read from the created data reader into the StudentCopy table.

   using (SqlConnection sqlconn =
      new SqlConnection(connectionString))
   {
   SqlCommand sqlcomm = sqlconn.CreateCommand();
   sqlcomm.CommandText = "Select * from Student";
   sqlconn.Open();
   SqlDataReader dr = sqlcomm.ExecuteReader();
   using (SqlConnection secondConnection =
   new SqlConnection(connectionString))
   {
   SqlBulkCopy bc = new SqlBulkCopy(secondConnection);
   bc.DestinationTableName = "StudentCopy";
   bc.WriteToServer(dr);
   bc.Close();
   dr.Close();
   } // Dispose is called on sqlconn
   }
   

5. That’s it. Compile and run the application to copy rows from one table to another in a screamingly fast manner. Connect to a larger table and write up an equivalent application leveraging DataSets and data adapters, and notice the time it takes in comparison with SqlBulkCopy. You can see that SqlBulkCopy is hundreds to thousands of times faster.