CODEWITHSUNDEEP
c#sqldatagridviewprogress-barbackgroundworker
Łukasz Motyczka
Łukasz Motyczka

Reputation: 1199

How can I make all this code work under backgroundworker

Right now I have this code:

private void button1_Click(object sender, EventArgs e)  
{
  OpenFileDialog openFileDialog1 = new OpenFileDialog();

    var dialogResult = openFileDialog1.ShowDialog();
    string sWybranyPlik;
    if (dialogResult  == DialogResult.OK)
    {
      sWybranyPlik = openFileDialog1.FileName;

      try
      {


        System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + sWybranyPlik + "';Extended Properties=Excel 8.0;"); 

        System.Data.OleDb.OleDbDataAdapter OleDBAdapter = new System.Data.OleDb.OleDbDataAdapter("select * from [Tabelle1$]", ExcelConnection);

        DtSet.Clear();


        //wypełnianie datagridview
        OleDBAdapter.Fill(DtSet.Tables[0]);
        dataGridView1.DataSource = DtSet.Tables[0];


        //kasowanie starych danych w bazie danych
        string deletesql = @"DELETE FROM TabelaProdukty";
        System.Data.SqlServerCe.SqlCeCommand comDelete = new System.Data.SqlServerCe.SqlCeCommand(deletesql, sqlconnection);
        sqlconnection.Open();
        comDelete.ExecuteNonQuery();
        sqlconnection.Close();


        backgroundWorker1.RunWorkerAsync(); 
        progressform.ShowDialog();



        ExcelConnection.Close();
      }

      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
      }
    }
}

And in _DoWork:

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{

  //wprowadzanie nowych danych z datagridview do bazy danych
  string strQuery = @"INSERT INTO TabelaProdukty VALUES (@VD, @ItemCode, @Item, @Qty, @Ppcur, @StandardPrice, @CeMarked, @Description, @Description2, @Edma)";
  sqlconnection.Open();
  using (System.Data.SqlServerCe.SqlCeCommand comm = new System.Data.SqlServerCe.SqlCeCommand(strQuery, sqlconnection))
  {
    comm.Parameters.Add("@VD", SqlDbType.NVarChar);
    comm.Parameters.Add("@ItemCode", SqlDbType.NVarChar);
    comm.Parameters.Add("@Item", SqlDbType.NVarChar);
    comm.Parameters.Add("@Qty", SqlDbType.NVarChar);
    comm.Parameters.Add("@Ppcur", SqlDbType.NVarChar);
    comm.Parameters.Add("@StandardPrice", SqlDbType.NVarChar);
    comm.Parameters.Add("@CeMarked", SqlDbType.NVarChar);
    comm.Parameters.Add("@Description", SqlDbType.NVarChar);
    comm.Parameters.Add("@Description2", SqlDbType.NVarChar);
    comm.Parameters.Add("@Edma", SqlDbType.NVarChar);

    for (int i = 0; i < dataGridView1.Rows.Count; i++)
    {

      comm.Parameters["@VD"].Value = dataGridView1.Rows[i].Cells["VD"].Value.ToString().Trim();
      comm.Parameters["@ItemCode"].Value = dataGridView1.Rows[i].Cells["ItemCode"].Value.ToString().Trim();
      comm.Parameters["@Item"].Value = dataGridView1.Rows[i].Cells["ITEM"].Value.ToString().Trim();
      comm.Parameters["@Qty"].Value = dataGridView1.Rows[i].Cells["QUANTITY"].Value.ToString().Trim();
      comm.Parameters["@Ppcur"].Value = dataGridView1.Rows[i].Cells["PPCUR"].Value.ToString().Trim();
      comm.Parameters["@StandardPrice"].Value = dataGridView1.Rows[i].Cells["STANDARD_SELL_PRICE"].Value.ToString().Trim();
      comm.Parameters["@CeMarked"].Value = dataGridView1.Rows[i].Cells["CE-MARKED"].Value.ToString().Trim();
      comm.Parameters["@Description"].Value = dataGridView1.Rows[i].Cells["ITEM_DESCRIPTION"].Value.ToString().Trim();
      comm.Parameters["@Description2"].Value = dataGridView1.Rows[i].Cells["ITEM_DESCRIPTION2"].Value.ToString().Trim();
      comm.Parameters["@Edma"].Value = dataGridView1.Rows[i].Cells["EDMA"].Value.ToString().Trim();


      comm.ExecuteNonQuery();

      backgroundWorker1.ReportProgress(i);

      if (backgroundWorker1.CancellationPending)
      {
        e.Cancel = true;
        break;
      }

    }
    sqlconnection.Close();


  }
}

As you can see this code lets me pick a excel file with OpenFileDialog, clears DataSet, fills a dataset and datagridview with data, clears SQL table nad then it opens a form with a progress bar showing a long process of copying a data from datagridview to sql databse (around 150000 rows in 12 columns. At this point it works ok. The problem I have is that everything before opening progressform takes time too, so I would like to put it all under backgroundworker and in progressform show the status reports like "Old data is deleted" or table "Loading new data", and then run progressbar for the dgv->sql part. Unfortunately by putting this code under _DoWork I get different errors depending on how much code do I copy there (all of button1_Click or only "try" part).

Do you have any ideas how it should look?

Best regards,

EDIT:

Additional methods:

private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
{

  progressform.progressBar1.Value = e.ProgressPercentage;
}

private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
  if (e.Cancelled)
  {
    MessageBox.Show("Operacja przerwana! Cennik nie został wprowadzony w całości", "Uwaga!", MessageBoxButtons.OK, MessageBoxIcon.Warning);


  }
  else
  {

    progressform.label1.Text = "Zakończono pomyślnie! :)";
    progressform.button1.Enabled = true;
  }
}

Upvotes: 0

Views: 791

Answers (1)

Victor Mukherjee
Victor Mukherjee

Reputation: 11095

You need to use the reportProgress event of backgroundworker.

Save the filename from openfiledialog in a string at instance level, i.e. outside the methods. Now shift the code for populating dataset completely in the backgroundworker.dowork eventhandler. After populating dataset call backgroundworker.ReportProgress. This will trigger backgroundworker.ProgressChanged event. Populate your datagridview within this method since this method will be invoked on the thread where the backgroundworker was instantiated. Now send the rest of your ADO connection codes in the dowork method. this should be enough.

**EDIT**

class YourClass
    {
        string filePath;
        DataSet dataset;
        private static void Do_Work(object sender, DoWorkEventArgs e)
        {
            //populate dataset
            //call worker.ReportProgress to trigger worker.ProgressChanged 
            //rest of time consuming ADO.Net Codes and other codes you wrote in your DoWork method
        }

        private static void Progress_Changed(object sender, ProgressChangedEventArgs e)
        {
            //this method is invoked in the thread where background worker is instantiated.
            //do all UI stuff here like populating datagridview from dataset
        }
    }

Upvotes: 1

Related Questions