This article will give you the overview of how to copy data from one table to another by using ado.net sqlbulkcopy classes.
Introduction
There
can be situation when we need to copy large amount of data from one location to
another. For example, you have created a
backup copy of a table. Accidently, the records
of the original table get deleted. For this, we need to copy all the
records from the backup table to the original table.Most
of the database server provides a means to copy data to one database to
another, either by a GUI such as SQL Server’s Enterprise Manager, or through a
command-line tool, such as SQL Server’s Bulk Copy Program - bcp.exe.Objective
By this article you will come to know, how to write code in c# and use SqlBilkCopy classes provided by .Net framework to perform bulk copy operation on databases.
Using the code
To
implement this back up, we can also write our own program for bulk copy of data
by using the bulk copy classes provided by various .NET data provider. In SQL
Server, the SqlBulkCopy class is provided to write bulk data only to SQL Server
based database tables.
The
data is copied from the data source to the destination table. The destination
table is specified by the DestinationTableName property, by using the
WriteToServer() method of the SqlBulkCopy class. The following figure shows the
various parameters that can be passed to the WriteToServer() method to copy
data in bulk.
SqlBulkCopy can be used to perform following task:
1. 1.
Execute single Bulk Copy operation
2. 2.
Execute multiple bulk copy operations
Executing Single Bulk Copy Operation
It is used to perform a single operation against a
database. The following step has to be follow for this operation.
1.
Connect to the source server to get the data to
be copied and fetch the data from database.
2.
Create a connection to the destination server
3.
Create a SqlBulkCopy object using destination
connection.
4.
Set the DestinationTableName property to the
name of the target table.
5.
Call the WriteToServer() Method.
The complete code
has been given below for this operation.
protected void btnsinglebulkcopy_Click(object sender, EventArgs e)
{
//The following table copy data from the Patient table to the Patient_backup table.
using (SqlConnection sourceconnection = new SqlConnection(con))
{
sourceconnection.Open();
SqlCommand cmd = new SqlCommand("Select * from Patient", sourceconnection);
SqlDataReader dr = cmd.ExecuteReader();
using (SqlConnection destinationconnection = new SqlConnection(con))
{
destinationconnection.Open();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(destinationconnection))
{
bulkcopy.DestinationTableName = "Patients_backup";
try
{
bulkcopy.WriteToServer(dr);
}
catch (Exception ex)
{
lblerror.Text = ex.Message.ToString();
}
finally
{
dr.Close();
}
}
}
}
}
Note – You need to
ensure that structure of the table ‘Patient_backup’,has been created. Otherwise
you will get an error message ‘Cannot access Destination Table Patient_backUp’.
Executing Multiple
Bulk Copy Operation
A multiple bulk copy
command is executed to perform multiple operations against a database. Multiple
bulk copy operation can be done by using a single instance of SqlBulkCopy Class.
For executing Multiple
BulkCopy Operation, I have used Adventureworks database. You can download this database
from www.codeplex.com
website. In this database, I have created
two table ‘dbo.BulkCopyDemoOrderTable1
’, ‘dbo.BulkCopyDemoOrderTable1’ that
has following structure.
CREATE TABLE [dbo].[BulkCopyDemoOrderTable1](
[SalesOrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[OrderDate] [datetime] NOT NULL CONSTRAINT
[DF_SalesOrderHeader_OrderDate] DEFAULT (getdate()),
[AccountNumber] [dbo].[AccountNumber]
NULL,
CONSTRAINT
[PK_SalesOrderHeader_SalesOrderID] PRIMARY KEY CLUSTERED
(
[SalesOrderID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[BulkCopyDemoOrderTable2](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
CONSTRAINT
[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY
KEY CLUSTERED
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
‘dbo.BulkCopyDemoOrderTable1’
table contains 3 columns and data in
this table is copied from Sales.SalesOrderHeader’ table and ‘dbo.BulkCopyDemoOrderTable1’
table contains data copied by joining two table like following-
Select Sales.SalesOrderDetail.SalesOrderID,SalesOrderDetailID,OrderQty,ProductID,UnitPrice from Sales.SalesOrderDetail
INNER JOIN
Sales.SalesOrderHeader on
Sales.SalesOrderDetail.SalesOrderID=Sales.SalesOrderHeader.SalesOrderID where
AccountNumber='10-4020-000034';
All operation has been defined in button click event. Following coding section contains complete code and their working has been defined by commented(//) line.
protected void btnmultiplebulkcopy_Click(object sender, EventArgs e)
{
//All operation are defined under this connection
using (SqlConnection connection = new SqlConnection(con))
{
connection.Open();
//If destinations tables already contains records, because of that,they need to be emptied first.
SqlCommand deleteHeader = new SqlCommand("delete from dbo.BulkCopyDemoOrderTable1;", connection);
deleteHeader.ExecuteNonQuery();
SqlCommand deleteDetail = new SqlCommand("delete from dbo.BulkCopyDemoOrderTable2;", connection);
deleteDetail.ExecuteNonQuery();
//First of all, i am counting the number of row from both the destination table and storing them in two variable.
SqlCommand countRowHeader=new SqlCommand("select count(*) from dbo.BulkCopyDemoOrderTable1;",connection);
long countStartHeader = Convert.ToInt32(countRowHeader.ExecuteScalar());
Response.Write(string.Format("Starting row count for Table1 table = {0}. <br/>", countStartHeader));
SqlCommand rowCount = new SqlCommand("select count(*) from dbo.BulkCopyDemoOrderTable2;", connection);
long countStartDetail = Convert.ToInt32(rowCount.ExecuteScalar());
Response.Write(string.Format("Starting row count for Table2 table = {0}. <br/>", countStartDetail));
//Now retrieve the data for 'dbo.BulkCopyDemoOrderTable1' table
SqlCommand headerData = new SqlCommand("Select SalesOrderID,OrderDate,AccountNumber from Sales.SalesOrderHeader where AccountNumber=@accountnumber;", connection);
SqlParameter parameterAccount = new SqlParameter();
parameterAccount.ParameterName = "@accountnumber";
parameterAccount.SqlDbType = SqlDbType.NVarChar;
parameterAccount.Direction = ParameterDirection.Input;
parameterAccount.Value = "10-4020-000034";
headerData.Parameters.Add(parameterAccount);
SqlDataReader readerHeader = headerData.ExecuteReader();
//Retrieve data for newly created dbo.BulkCopyDemoOrderTable2 table in another connection
using (SqlConnection connection2 = new SqlConnection(con))
{
connection2.Open();
SqlCommand sourceDetailData = new SqlCommand("select Sales.SalesOrderDetail.SalesOrderID,SalesOrderDetailID,OrderQty,ProductID,UnitPrice from " +
"Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader on Sales.SalesOrderDetail.SalesOrderID=Sales.SalesOrderHeader.SalesOrderID where" +
" AccountNumber=@accountnumber;", connection2);
SqlParameter accountDetail = new SqlParameter();
accountDetail.ParameterName = "@accountnumber";
accountDetail.SqlDbType = SqlDbType.NVarChar;
accountDetail.Direction = ParameterDirection.Input;
accountDetail.Value = "10-4020-000034";
sourceDetailData.Parameters.Add(accountDetail);
SqlDataReader readerDetail = sourceDetailData.ExecuteReader();
//Now BulkCopy operation start here,that first map the columns and then write the data fetched in readerHeader
//and readerDetail SqlDataReader object to destination tables
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(con))
{
//Assign the first dbo.BulkCopyDemoOrderTable1 name created in the database.
bulkcopy.DestinationTableName = "dbo.BulkCopyDemoOrderTable1";
//then make sure that columns are mapped correctly
bulkcopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID");
bulkcopy.ColumnMappings.Add("OrderDate", "OrderDate");
bulkcopy.ColumnMappings.Add("AccountNumber", "AccountNumber");
try
{
bulkcopy.WriteToServer(readerHeader);
}
catch (Exception ex)
{
lblerror.Text = ex.Message.ToString();
}
finally
{
readerHeader.Close();
}
//Assign the second dbo.BulkCopyDemoOrderTable2 name created in the database.
bulkcopy.DestinationTableName = "dbo.BulkCopyDemoOrderTable2";
//Here clear the columnMappings Collection
bulkcopy.ColumnMappings.Clear();
bulkcopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID");
bulkcopy.ColumnMappings.Add("SalesOrderDetailID", "SalesOrderDetailID");
bulkcopy.ColumnMappings.Add("OrderQty", "OrderQty");
bulkcopy.ColumnMappings.Add("ProductID", "ProductID");
bulkcopy.ColumnMappings.Add("UnitPrice", "UnitPrice");
try
{
bulkcopy.WriteToServer(readerDetail);
}
catch (Exception ex)
{
lblerror.Text = ex.Message.ToString();
}
finally
{
readerDetail.Close();
}
}
//Count the number of rows that were added to tdestination tables
long countEndHeader = Convert.ToInt32(countRowHeader.ExecuteScalar());
Response.Write(string.Format("{0} rows were added to the Table1 table. <br/>", countEndHeader - countStartHeader));
long countEndDetail = Convert.ToInt32(rowCount.ExecuteScalar());
Response.Write(string.Format("{0} rows were added to the Table2 table. <br/>", countEndDetail - countStartDetail));
}
}
}
The output of the above code has been given below.
Hope this article was useful. Feel free to write comment or feedback.