Thursday, September 27, 2012

sqlbulkcopy with transaction



string connstring = _hsconnstring.Substring(1, (_myconnstring.Length - 1));
using (SqlConnection destinationConnection = new SqlConnection((connstring)))
{
    destinationConnection.Open();

    using (SqlTransaction transaction = destinationConnection.BeginTransaction())
                {

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection,SqlBulkCopyOptions.Default,transaction))
                    {
                        try
                        {
                            bulkCopy.DestinationTableName = "myfirsttable";
                            bulkCopy.WriteToServer(outTable1);
                           
                            bulkCopy.DestinationTableName = "mysecondtable";
                            bulkCopy.WriteToServer(outTable2);

                            bulkCopy.DestinationTableName = "mythirdtable";
                            bulkCopy.WriteToServer(outTable3);

                            bulkCopy.DestinationTableName = "myfouthtable";
                            bulkCopy.WriteToServer(outTable4);
                           
                            bulkCopy.DestinationTableName = "myfifthtable";
                            bulkCopy.WriteToServer(outTable5);
                            bulkCopy.Close();
                            transaction.Commit();

                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            Utilities.HandleError("Error at data bulk insert: ", ex);
                            throw ex;
                        }

                    }
                }

            }

No comments: