Bulk data copy using ADO.NET

The simplest and fastest way of copying a large amount of data from different sources to an SQL Server table can be accomplished with the help of Bulk Copy Operation, a new feature in ADO.NET version 2.0.

Here different sources of data are those from which data can be loaded into 'DataTable' instance or read with 'IDataReader' instance.

To accomplish bulk copy one has to use 'SqlBulkCopy' class that is present in 'Sytem.Data.SqlClient' namespace in .NET framework. Now using this 'SqlBulkCopy' class one can perform single bulk copy, multiple bulk copy and bulk copy with a transaction.

Implementation
Before implementing bulk copy we should be ready with source and destination of data. In this example we would be using SQL Server as both source and destination. We would copy data from 'Source' table and paste it into 'Destination' residing on same server.

We will start by creating a connection to the SQL Server that is acting as a source of data. The following code snippet shows how this is done:

using System.Data.SqlClient;
string connectionString = "Data Source=.;Initial Catalog=NTL;User ID=sa;Password=P@ssw0rd";
SqlConnection sourceConnection = new SqlConnection(connectionString);
sourceConnection.Open();

Next step is to run commands to get data into a DataReader, following is the code to accomplish the same:

We created a source table 'Table_1' using SQL Server Manager in 'sandeep' database that contains some values to be copied

SqlCommand commandSourceData = new SqlCommand("SELECT * FROM Source;", sourceConnection);
SqlDataReader reader = commandSourceData.ExecuteReader();
Connect to a destination table of data using:
SqlConnection destinationConnection = new SqlConnection(connectionString);
destinationConnection.Open();

Destination table 'Table_2' will be required to fill the data from 'Table_1' using bulk copy

Point to be noted here is that we are using same 'connectionString' as we are copying data to same server. We now have to copy data using bulk copy feature:

SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection);
bulkCopy.DestinationTableName = "Destination";
bulkCopy.WriteToServer(reader);
reader.Close();

In the end we closed 'reader' and then we can close our connections to the SQL server.In this way data from one table was copied to other in fastest possible way.It is also possible to use single instance of 'SqlBulkCopy' for performing multiple bulk copy operation. This technique is more efficient than using separate 'SqlBulkCopy' instances for each operations.

One more technique is to perform bulk copy in a transaction. Using this technique one can perform multiple bulk copy operation along with other database operations i.e. update, delete etc and as one is using transaction it canbe easily commited or rolled back.

A Windows form was created using Visual Studio 2005 with a single button 'bulkcopy', as one clicks on this button data is copied and a message is shown to user

Features
Besides bulk copy, ADO.NET version 2.0 has plenty of new features, here we are listing some of them:
(a) Multiple Active Results Sets(MARS)-allows application to have multiple 'SqlDataReader' open on a connection where each instance of 'SqlDataReader' is started from a separate command.

To verify our results we went back to SQL Server Manager to check contents of 'Table_2' and found that valus of 'Table_1' were present

(b) Batch processing-another feature included to enhance application performance is batch processing in which updates to databse from 'Dataset' are done in batches.
(c) Data tracing-an interesting feature is built-in data tracing supported by .NET data providers.
All these features of ADO.NET version 2.0 make it a better and efficient tool for data access.