Sql Bulk Copy Operation in ADO.NET 2.0

Introduction Bulk copying of data from one data source to another data source is a new feature added to ADO.NET 2.0. Bulk copy classes provides the fastest way to transfer set of data from once source to the other. Each ADO.NET data provider provides bulk copy classes. For example, in SQL .NET data provider, the bulk copy operation is handled by SqlBulkCopy class, which is described in Figure 1. As you can see from Figure 1, data from a data source can be copied to one of the four types - DataReader, DataSet, DataTable, or XML. Figure 1. Bulk Copy operation in ADO.NET 2.0 Using bulk copy operation, you can transfer data between two tables on the same SQL Server, between two different SQL Servers, or even two different types of database servers. Filling Data from the Source The first step in copying bulk data from a data source to another is to fill data from the source database. This source data can be filled in a DataSet, DataTable, or a DataReader. // Select data from Products table cmd = new SqlCommand("SELECT * FROM Products", source); // Execute reader SqlDataReader reader = cmd.ExecuteReader(); Creating SqlBulkCopy Object In ADO.NET 2.0, each data provider has a bulk copy operations class, which provides bulk copy related functionality. For example, SQL data provider has SqlBulkCopy class. SqlBulkCopy class constructor takes a connection string or SqlConnection object as first parameter, which defines the destination data source. After creating the object, you need to set the DestinationTableName propety to the table, which you want to copy date to. // Create SqlBulkCopy SqlBulkCopy bulkData = new SqlBulkCopy(destination); // Set destination table name bulkData.DestinationTableName = "BulkDataTable"; Copying Data to the Destination The SqlBulkCopy class provides WriteToServer method which is used to write data from a DataReader, DataSet, or DataTable to the destination data source. bulkData.WriteToServer(reader); In this code, I fill data in a DataReader object from the source data source. You can even fill data in a DataSet and pass DataSet as the input parameter of WriteToServer method. You can also pass an XML object or fill data in a DataSet from XML document. Closing SqlBulkCopy Object The Close method of SqlBulkCopy closes the bulk copy operation. bulkData.Close(); Complete Source Code The following table lists the complete source code. See attached source code for more details: // Create source connection SqlConnection source = new SqlConnection(connectionString); // Create destination connection SqlConnection destination = new SqlConnection(connectionString); // Clean up destination table. Your destination database must have the // table which schema which you are copying data to. SqlCommand cmd = new SqlCommand("DELETE FROM BulkDataTable", destination); // Open source and destination connections. source.Open(); destination.Open(); cmd.ExecuteNonQuery(); // Select data from Products table cmd = new SqlCommand("SELECT * FROM Products", source); // Execute reader SqlDataReader reader = cmd.ExecuteReader(); // Create SqlBulkCopy SqlBulkCopy bulkData = new SqlBulkCopy(destination); // Set destination table name bulkData.DestinationTableName = "BulkDataTable"; // Write data bulkData.WriteToServer(reader); // Close objects bulkData.Close(); destination.Close(); source.Close(); Summary ADO.NET 2.0 has many new features and bulk copy is one of them. In this article, we discussed how we can take advantage of SqlBulkCopy class and its members when copying data from a source database to a destination database.