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.