SQLBULKCOPY OPERATION HANG AND NOT RESPONDING

marcel ethan

Total Post:104

Points:728
Posted by  marcel ethan
C# 
C#
 2846  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 Apr-04-2018 12:47:46 AM

Answer

NEWSLETTER

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