SqlBulkCopy – one major feature in the ADO.NET 2.0

hus — Fri, 09 Feb 2007 11:25:00 GMT

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;

}

Building and using a 3-tiered data architecture with ASP.NET 2.0

hus — Fri, 19 Jan 2007 03:08:00 GMT

In this set of tutorials you will learn how to build 3-tired data applications, you will get to know how to deal with the new features in ASP.NET plus using the new controls.
click here

Enjoy :)

.Net Gate : C#

SqlBulkCopy – one major feature in the ADO.NET 2.0

Building and using a 3-tiered data architecture with ASP.NET 2.0