Bulk Data Copy using ADO.NET

ADO.NET (ActiveX Data Object) is a set of classes used for data access in
.NET. ADO allows .NET programmers to access a database without even

 knowing how the database has been implemented. It is a part of base
class library that is included with the Microsoft .NET Framework.

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.

In this article we would be implementing single bulk copy. To implement
single bulk copy we used SQL server 2005 with Visual Studio 2005. We would be
using C# as programming language.

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.