Home > DeveloperSection > Forums > SqlBulkCopy operation hang and not responding
marcel ethan
marcel ethan

Total Post:105

Points:735
Posted on    December-23-2013 2:47 AM

 C# C# 
Ratings:


 1 Reply(s)
 2029  View(s)
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?



Pravesh Singh

Total Post:411

Points:2881
Posted on    December-23-2013 5:07 AM

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

Don't want to miss updates? Please click the below button!

Follow MindStick