Performing Bulk Copy Operations on Database

Vikash
Posted by in ADO.NET category on for Beginner level | Points: 250 | Views : 7522 red flag

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.
Page copy protected against web site content infringement by Copyscape

About the Author

Vikash
Full Name: vikash pathak
Member Level: Starter
Member Status: Member
Member Since: 6/18/2012 7:46:35 AM
Country: India
Regards, Vikash Pathak
http://www.dotnetfunda.com/profile/vikash.aspx
1+ yrs exp in .Net & MCP(70-526) paper completed.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)