How to use SQLBulkcopy C#
In this article I am going to explain about how to use C# SQLBulkcopy object. SQLbulkcopy object is very useful to transfer data from one datasource to other datasource. There are many ways to do it like use ssis packages, writing insert script or use SQLbulkcopy objects. This is the fastest and scalable way of doing it.
Transferring data from one datasource to other datasource is usually required thing especially if you need some data from test region to development servers or transferring data from old version to new version of database.
SQLBulkcopy class introduced with Visual Studio 2005. This includes in System.Data.SQLClient namespace.
The constructor of SQLBulkcopy can be used like
SqlBulkCopy productsCopy = new SqlBulkCopy(SqlConnection connnection);
It takes destination source' connection.
SqlBulkCopy productsCopy = new SqlBulkCopy(String SqlConnection)
It takes connection string for destination source.
SqlBulkCopy productsCopy = new SqlBulkCopy(connstring, SqlBulkCopyOptions.Default);
It takes connection string to connect destination source and SQLBulkCopyOptions which help to take care of existing constraints or null values.
Now we will write code to perform copy
private static void ProductsCopy()
{
string strConnection =
@"Server=localhost;Database=Northwind;Trusted_Connection=true";
// get the source data
using (SqlConnection srcConnection = new SqlConnection(strConnection))
{
SqlCommand srcCommand = new SqlCommand("SELECT * FROM Products");
srcCommand.Connection = srcConnection;
srcConnection.Open();
SqlDataReader reader = srcCommand.ExecuteReader();
// open the destination data
using (SqlConnection destConnection = new SqlConnection(strConnection))
{
// open the connection
destConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnection.ConnectionString))
{
bulkCopy.BatchSize = 500;
bulkCopy.DestinationTableName = "Products_New";
bulkCopy.WriteToServer(reader);
}
}
reader.Close();
}
}
The data which has read by reader will transferred to the destination Products_new table by SQLBulkcopy WriteServer method.
Other than this we can Use Transactions in sqlBulkcopy constructor.
sqlTransaction Trans =new SqlTransaction()
SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnection.ConnectionString,Trans)