Using SQL bulk copy

Using SQL bulk copy

First we add a connection string to web-config so that we can reuse it.

<connectionstrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer"
connectionString="Integrated Security=SSPI;
Initial Catalog=NorthWind;Server=(local)"
providerName="System.Data.SqlClient"/>
</connectionStrings>


Now we will demonstrate simple use of SqlBulkCopy class to copy from source table to destination table here both table have same structure though constraint on both may be different.
I am first creating such duplicate table for product in northwind database by running following query
Query:
select * into product_backup from products where 1=2
*** Please a point to see this newly created table(product_backup) is empty

Now to page load add following code
Code-1:
/*reads connection string from web.config*/
string connStr = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;

/*create a sqlconnetion object for source table*/
SqlConnection sourceConnection = new SqlConnection(connStr);
SqlCommand myCommand =new SqlCommand("SELECT * FROM Products", sourceConnection);
sourceConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader();

SqlConnection destinationConnection = new SqlConnection(connStr);

destinationConnection.Open();
SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString);

/*After BatchSize is reached the rows in the batch are
* sent to the server for insert*/
bulkCopy.BatchSize = 500;

/*calls OnSqlRowsCopied after copying of each 10 rows*/
bulkCopy.NotifyAfter = 10;
/*specifies event handler to be called when NotifyAfter amount of row are copied*/
bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

/*specify destination table name*/
bulkCopy.DestinationTableName = "product_backup";

/*calls the actual writing*/
bulkCopy.WriteToServer(reader);

bulkCopy.Close();
destinationConnection.Close();
sourceConnection.Close();


Further into class add event handler code bellow

Code-2:

private void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
/*will abort the operation of bulk copy*/
//e.Abort = true;

Response.Write(e.RowsCopied.ToString()+",");
}

Explination:
Run above code and trace ececution using breakpoint.you will see
that after each 10 rows are inserted the event “OnSqlRowsCopied”
get fired.Inside this command you can abort it.
1)bulkCopy.BatchSize signifies that this much number of rows
inserted as single transcation.it’s default value is 0.i.e.
that each WriteToServer operation is a single batch.
2)bulkCopy.NotifyAfter this signifies that after batch of this
much rows get inserted OnSqlRowsCopied event get fired
3)bulkCopy.WriteToServer this method Copies all rows from a source
table(product) to a destination table(product_backup) specified by the
DestinationTableName property of the SqlBulkCopy object.
This method has many overload, I am here only using one that takes data-reader as input parameter.

The class SqlBulkCopy is sealed class i.e. one can’t inherits this class.

Now after running this code you can see that the records from product table are being copied to product_backup.To see run query as following.
Query:select * from product_backup

You can try this twice thrice just for that truncate destination table Using following query.
Query: truncate table product_backup

If you doesn’t truncate the destination table then duplicate entries get added except the primary key column.