SQLBULKCOPY OPERATION HANG AND NOT RESPONDING

marcel ethan

Total Post:105

Points:735
Posted by  marcel ethan
C# 
C#
 2536  View(s)
Ratings:
Rate this:

I am using SqlBulkCopy to copy several tables to database. However, most of the tables are successfully copied to database except one table with 6000++ rows of data. When I run the function, it just simply hang there and not responding.

Below is my code:

using (SqlConnection destinationConnection = Login.GetConnection())

{

    destinationConnection.Open();

    using (SqlTransaction transaction = destinationConnection.BeginTransaction(IsolationLevel.ReadCommited))

    {

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.KeepIdentity, transaction))

        {

            bulkCopy.DestinationTableName = "dbo." + tableName;

            try

            {

                bulkCopy.WriteToServer(dt);

                transaction.Commit();

                bulkCopySuccess = true;

            }

            catch (Exception ex)

            {

                transaction.Rollback();

                MessageBox.Show(ex.Message, ex.GetType().ToString());

                bulkCopySuccess = false;

            }

        }

    }

}

What is the possible problem? Is something wrong with my code?

  1. Pravesh Singh

    Post:412

    Points:2888
    Re: SqlBulkCopy operation hang and not responding

    Hi Marcel,

     

    Problem : if you are running the BulkCopy code from the main thread, it will wait/not respond untill unleess the whole operation gets completed.

     

    so if the user wants to perform some operations on the UI it will be completly unresponsive and hangs.

     

    Solution : you could use the BackgroundWorker Component to perform the operations on background without hanging the UI.

     

    Try This:

     

    private void button1_Click(object sender, EventArgs e)

        {

            BackgroundWorker backgroundWorker = new BackgroundWorker();

            backgroundWorker.DoWork += new DoWorkEventHandler(backgroundWorker_DoWork);

            backgroundWorker.RunWorkerAsync();

        }

      private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)

        {

            DoBulkCopy();

        }

      private void DoBulkCopy()

      {

         using (SqlConnection destinationConnection = Login.GetConnection())

         {

            destinationConnection.Open();

            using (SqlTransaction transaction = destinationConnection.BeginTransaction(IsolationLevel.ReadCommited))

            {

              using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.KeepIdentity, transaction))

               {

                 bulkCopy.DestinationTableName = "dbo." + tableName;                           

                 try

                 {

                   bulkCopy.WriteToServer(dt);

                   transaction.Commit();

                   bulkCopySuccess = true;

                 }

                 catch (Exception ex)

                 {

                  transaction.Rollback();

                  MessageBox.Show(ex.Message, ex.GetType().ToString());

                  bulkCopySuccess = false;

                 }

                }

             }

          }

    }

     

      Modified On Aug-11-2016 05:32:39 AM

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!