SqlBulkCopy – one major feature in the ADO.NET 2.0
There was in the past lots of approaches to transfer data from one table to another those methods deferred in its complexity or in the performance impact behind applying it,
We face this scenario everyday in our real life projects and even in our small code snippets that support us in the data migration process while project deployments and so on…
Taking the above scenarios in our minds the ADO.NET 2.0 introduced the SqlBulkCopy class which work as a schema mapping between two data sources and starts transferring data from a source data table to another.
This operation takes the following steps:-
1- We define the data source and the targeted destination
2- Then we start mapping the data attributes – columns – between the source and the destination tables.
3- After that we start configuring the BatchSize – the number of rows to be copy in each attempt to copy- and the BulkCopyTimeOut.
4- We also have a SqlRowsCopied event which get fired whenever the number we define in the NotifyAfter property got reached.
Let’s go to the enjoyable part which is CODE J
protected void MoveData(DataSet dsSource)
{
using (SqlConnection connDest = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString()))
{
using (SqlBulkCopy bulkCopyobj = new SqlBulkCopy(connDest))
{
//Start mapping columns
bulkCopyobj.ColumnMappings.Add("SourceID", "DestID");
//configure the SqlBulkCopy Properties
bulkCopyobj.BatchSize = 90;
bulkCopyobj.BulkCopyTimeout = 15;
bulkCopyobj.NotifyAfter = 250;
bulkCopyobj.DestinationTableName = "DestinationTable";
bulkCopyobj.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopyobj_SqlRowsCopied);
//start moving data
bulkCopyobj.WriteToServer(dsSource.Tables[0]);
//close the bulk copy operation
bulkCopyobj.Close();
}
}
}
void bulkCopyobj_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
Console.Write(string.Format("{0} Rows copied....",e.RowsCopied.ToString()) );
}
protected DataSet GetData()
{
using (SqlConnection connSource = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionSource"].ToString()))
{
SqlDataAdapter da = new SqlDataAdapter("GetDataFromSource", connSource);
DataSet ds = new DataSet();
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds);
return ds;
}
}