Bulk data copy using ADO.NET

BANGALORE, INDIA: 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.

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:

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();

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.

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.

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