SQL Table Bulk Copy in C#
As a developer, sometimes we require to copy the specific production table data to Dev or QA environment instead of backup and restore the total database. We can use SqlBulkCopy class of the System.Data.SqlClient namespace. In this article we discuss about how to copy the table data from source to destination using SqlBulkCopy class.
Open Microsoft Visual Studio 2013 => Create new Windows Application and name it as “SQLBulkCopyExample”
First we have to fetch the table data from source (here production) by using either SqlDataReader or SqlDataAdapter as shown below.
//Create Source Connection and fetch table data to SqlDataReader
SqlConnection sqlSourceCon = new SqlConnection("Data Source=ProductionServer;Initial Catalog=TestDB;User ID=sa;Password=xxxx");
SqlCommand cmd = new SqlCommand("SELECT * FROM Employee", sqlSourceCon);
sqlSourceCon.Open();
SqlDataReader rdr = cmd.ExecuteReader();
Then create connection for destination database (here for Dev or QA) and load table from source as shown below by using SQLBulkCopy class of System.Data.Client namespace.
//create destination connection and load data from source using SqlBulkCopy
SqlConnection sqlDestiCon = new SqlConnection("Data Source=DevServer;Initial Catalog=TestDB;User ID=sa;Password=xxxx");
SqlBulkCopy sqlBCopy = new SqlBulkCopy(sqlDestiCon);
sqlBCopy.DestinationTableName = "Employee";
sqlBCopy.WriteToServer(sqlReader);
As shown above SQLBulkCopy copies the data from source to destination Employee table. The total code is provided below.
using System;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace SQLBulkCopyExample
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//Create Source Connection and fetch table data to SqlDataReader
SqlConnection sqlSourceCon = new SqlConnection("Data Source=ProductionServer;Initial Catalog=TestDB;User ID=sa;Password=xxxx");
SqlCommand cmd = new SqlCommand("SELECT * FROM Employee", sqlSourceCon);
sqlSourceCon.Open();
SqlDataReader sqlReader = cmd.ExecuteReader();
//create destination connection and load data from source using SqlBulkCopy
SqlConnection sqlDestiCon = new SqlConnection("Data Source=DevServer;Initial Catalog=TestDB;User ID=sa;Password=xxxx");
SqlBulkCopy sqlBCopy = new SqlBulkCopy(sqlDestiCon);
sqlBCopy.DestinationTableName = "Employee";
sqlBCopy.WriteToServer(sqlReader);
//close all connection
sqlBCopy.Close();
sqlReader.Close();
sqlSourceCon.Close();
}
}
}
By default SqlBulkCopy will not check primary key and foreign key relationship while copying the data from source to destination. We enable the checking primary key & foreign key relationship with the SqlBulkCopyOptions.CheckConstraints option as shown below.
SqlBulkCopy sqlBCopy = new SqlBulkCopy("Data Source=DevServer;Initial Catalog=TestDB;User ID=sa;Password=xxxx", SqlBulkCopyOptions.CheckConstraints);
sqlBCopy.DestinationTableName = "Employee";
sqlBCopy.WriteToServer(sqlReader);
