forum

Home / DeveloperSection / Forums / SqlBulkCopy operation hang and not responding

Ask Question

SqlBulkCopy operation hang and not responding

marcel ethan 3888 23-Dec-2013

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?

c# c# 
Updated on 23-Dec-2013
marcel ethan

marcel ethan

Follow

Can you answer this question?

Answer

1 Answers

Pravesh Singh

Pravesh Singh

23-Dec-2013

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;
             }
            }
         }
      }
}

 

Liked By