SQL Bulk Copy with C#.Net

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.

using System.Data.SqlClient;

public static void CopyData(DataTable sourceTable, SqlConnection destConnection, SqlTransaction destTrans, string destTableName)
  {
          // new method: SQLBulkCopy:
          using (SqlBulkCopy s = new SqlBulkCopy(destConnection, SqlBulkCopyOptions.Default, destTrans))
            {
                s.DestinationTableName = destTableName;
                s.NotifyAfter = 40000;
                //s.SqlRowsCopied += new SqlRowsCopiedEventHandler(s_SqlRowsCopied);
                s.WriteToServer(sourceTable);
                s.Close();
            }
  }

Calling the above function

         SqlConnection destinationConnection= new SqlConnection(sourceConnectionString);
        
         destinationConnection.Open();
         destinationTransaction =              destinationConnection.BeginTransaction(IsolationLevel.ReadUncommitted);


         DataTable SourceDataTable= new  DataTable();

         CopyData(SourceDataTable, destinationConnection, destinationTransaction, "DestinationTableName");

         destinationTransaction.Commit();

         destinationConnection.Close();