none
Using the BackgroundWorker to transfer data between databases RRS feed

  • Question

  • I need to copy the data from an old database to a new one.
    There are three tables.

    I have the code for doing that but I'd like to show a progress bar to indicate that something is happening.
    Pseudo Code: 
    For each record in old Table1
         Write to new table1
         Data1Transfered = Data1Transfered + 1
         worker.ReportProgress(Data1Transfered)
    Next
    For each record in old Table2
         Write to new table2     Data2Transfered = Data2Transfered + 1
         worker.ReportProgress(Data2Transfered)
    Next
    For each record in old Table3
         Write to new table3     Data3Transfered = Data3Transfered + 1
         worker.ReportProgress(Data3Transfered)
    Next

    Then I have a label to display the count of records as they are transferred.

    Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged      
            Label1.Text = e.ProgressPercentage.ToString
        End Sub

    Which is ok, but they all go into label1. I'd like to use three labels, one for each table.
    Do I need to use three background workers?
    Also, as table3 depends on values being present in table1 & 2 it is important that the finish before populating table3 starts.
    Also (Lots of Alsos) I only seem to have the choice of e.ProgressPercentage even though I only want a count.

    Clearly this is the first time I've tried to use the background worker.

    Any help appreciated.

    Andy

    Saturday, December 21, 2019 2:32 PM

Answers

  • Hi

    Here is one way to do it. This makes some assumptions - such as the 3 tables have different column types, table3 has one column produced from a column on table1 and a column on table2.

    This is a stand alone example.

    There are two distinct processes: first one uses DataTable.Copy, and second uses the BGW. See code for each.

    It used the Label1 to indicate progress and has a 'dummy' Sleep to simulate code execution between counts and to let the Label text be seen. A ProgressBar could be made to follow those values too/instead. However, both the Lable and any ProgressBar would only be of use if the Table row count is quite large, otherwise it is allover before anything can be seen. The BGW has Progress and Completed events enabled.

    Anyway, this code may help.

    ' Form1 with DataGridView1
    ' Button1, Button2, Label1
    ' and BackGroundWorker1
    Option Strict On
    Option Explicit On
    Public Class Form1
      Dim OldTable1, OldTable2, OldTable3 As New DataTable
      Dim NewTable1, NewTable2, NewTable3 As New DataTable
      Dim ShowTable As Integer = 0
      Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
        ' here you would load old data into
        ' new tables, but I use dummy data
        With OldTable1
          .Columns.Add("One1", GetType(Integer))
          .Columns.Add("Two1", GetType(String))
          .Columns.Add("Three1", GetType(Double))
          .Columns.Add("Four1", GetType(Decimal))

          .Rows.Add(1, "String1", 6.54321, 11.1234D)
          .Rows.Add(2, "String2", 2.23456, 12.34565D)
          .Rows.Add(3, "String3", 3.23456, 13.3456D)
          .Rows.Add(4, "String4", 4.23456, 14.3456D)
        End With
        With OldTable2
          .Columns.Add("One2", GetType(String))
          .Columns.Add("Two2", GetType(Integer))
          .Columns.Add("Three2", GetType(Double))
          .Columns.Add("Four2", GetType(String))

          .Rows.Add("String21", 21, 26.54321, "211.1234")
          .Rows.Add("String22", 22, 22.23456, "212.34565")
          .Rows.Add("String23", 23, 23.23456, "213.3456")
          .Rows.Add("String24", 24, 24.23456, "214.3456")

        End With
        With OldTable3
          .Columns.Add("One3", GetType(String))
          .Columns.Add("Two3", GetType(Decimal))
          .Columns.Add("Three3", GetType(Double))
          .Columns.Add("Four3", GetType(Integer))

          ' simulate special values for column2
          .Rows.Add("String31", 31, CDbl(OldTable1.Rows(0).Item("Three1")) * CDbl(OldTable2.Rows(0).Item("Two2")), 311)
          .Rows.Add("String32", 32, CDbl(OldTable1.Rows(1).Item("Three1")) * CDbl(OldTable2.Rows(1).Item("Two2")), 312)
          .Rows.Add("String33", 33, CDbl(OldTable1.Rows(2).Item("Three1")) * CDbl(OldTable2.Rows(2).Item("Two2")), 313)
          .Rows.Add("String34", 34, CDbl(OldTable1.Rows(3).Item("Three1")) * CDbl(OldTable2.Rows(3).Item("Two2")), 314)
        End With

        ' a straightforward way to do it
        NewTable1 = OldTable1.Copy()
        NewTable2 = OldTable2.Copy()
        NewTable3 = OldTable3.Copy()

      End Sub
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' an alternative way to do it which
        ' could allow for other data manipulation
        ' to be done
        Button1.Visible = False
        BackgroundWorker1.RunWorkerAsync()
      End Sub
      Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        ShowTable += 1
        If ShowTable > 2 Then ShowTable = 0
        Select Case ShowTable
          Case 1
            DataGridView1.DataSource = NewTable1
          Case 2
            DataGridView1.DataSource = NewTable2
          Case Else
            DataGridView1.DataSource = NewTable3
        End Select
      End Sub



      Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        ' copy OldTable1 to NewTable1
        Dim counter As Integer = 10
        For Each i As Object In OldTable1.Columns
          NewTable1.Columns.Add(DirectCast(i, DataColumn).ColumnName, DirectCast(i, DataColumn).DataType)
        Next
        For Each i As Object In OldTable1.Rows
          NewTable1.Rows.Add(DirectCast(i, DataRow).ItemArray)
          counter += 1
          BackgroundWorker1.ReportProgress(counter)
          Threading.Thread.Sleep(500)
        Next

        ' copy OldTable2 to NewTable2
        counter = 20
        For Each i As Object In OldTable2.Columns
          NewTable2.Columns.Add(DirectCast(i, DataColumn).ColumnName, DirectCast(i, DataColumn).DataType)
        Next
        For Each i As Object In OldTable2.Rows
          NewTable2.Rows.Add(DirectCast(i, DataRow).ItemArray)
          counter += 1
          BackgroundWorker1.ReportProgress(counter)
          Threading.Thread.Sleep(500)
        Next

        ' copy OldTable3 to NewTable3
        counter = 30
        For Each i As Object In OldTable3.Columns
          NewTable3.Columns.Add(DirectCast(i, DataColumn).ColumnName, DirectCast(i, DataColumn).DataType)
        Next
        For Each i As Object In OldTable3.Rows
          NewTable3.Rows.Add(DirectCast(i, DataRow).ItemArray)
          counter += 1
          BackgroundWorker1.ReportProgress(counter)
          Threading.Thread.Sleep(500)
        Next

      End Sub
      Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged
        Select Case e.ProgressPercentage
          Case < 20
            Invoke(Sub() Label1.Text = "Table1 Row " & (e.ProgressPercentage - 10).ToString)
          Case < 30
            Invoke(Sub() Label1.Text = "Table2 Row " & (e.ProgressPercentage - 20).ToString)
          Case Else
            Invoke(Sub() Label1.Text = "Table3 Row " & (e.ProgressPercentage - 30).ToString)
        End Select

        Invoke(Sub() Application.DoEvents())
        ' simulate pause to see label value
      End Sub
      Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        Button2.Visible = True
        Button2.PerformClick()
      End Sub
    End Class


    Regards Les, Livingston, Scotland



    • Edited by leshay Saturday, December 21, 2019 4:44 PM
    • Marked as answer by AndyNakamura Saturday, December 21, 2019 9:17 PM
    Saturday, December 21, 2019 4:19 PM

All replies

  • Sorry I should have said the pseudo code is in the backgroundWorker_DoWork Sub
    Saturday, December 21, 2019 2:34 PM
  • Hi

    At the point where you are ready to do the copy, do all three 'old' tables exist and are populated? If so, then all the rest is reasonably straightforward.



    Regards Les, Livingston, Scotland

    Saturday, December 21, 2019 3:23 PM
  • Hi

    Here is one way to do it. This makes some assumptions - such as the 3 tables have different column types, table3 has one column produced from a column on table1 and a column on table2.

    This is a stand alone example.

    There are two distinct processes: first one uses DataTable.Copy, and second uses the BGW. See code for each.

    It used the Label1 to indicate progress and has a 'dummy' Sleep to simulate code execution between counts and to let the Label text be seen. A ProgressBar could be made to follow those values too/instead. However, both the Lable and any ProgressBar would only be of use if the Table row count is quite large, otherwise it is allover before anything can be seen. The BGW has Progress and Completed events enabled.

    Anyway, this code may help.

    ' Form1 with DataGridView1
    ' Button1, Button2, Label1
    ' and BackGroundWorker1
    Option Strict On
    Option Explicit On
    Public Class Form1
      Dim OldTable1, OldTable2, OldTable3 As New DataTable
      Dim NewTable1, NewTable2, NewTable3 As New DataTable
      Dim ShowTable As Integer = 0
      Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
        ' here you would load old data into
        ' new tables, but I use dummy data
        With OldTable1
          .Columns.Add("One1", GetType(Integer))
          .Columns.Add("Two1", GetType(String))
          .Columns.Add("Three1", GetType(Double))
          .Columns.Add("Four1", GetType(Decimal))

          .Rows.Add(1, "String1", 6.54321, 11.1234D)
          .Rows.Add(2, "String2", 2.23456, 12.34565D)
          .Rows.Add(3, "String3", 3.23456, 13.3456D)
          .Rows.Add(4, "String4", 4.23456, 14.3456D)
        End With
        With OldTable2
          .Columns.Add("One2", GetType(String))
          .Columns.Add("Two2", GetType(Integer))
          .Columns.Add("Three2", GetType(Double))
          .Columns.Add("Four2", GetType(String))

          .Rows.Add("String21", 21, 26.54321, "211.1234")
          .Rows.Add("String22", 22, 22.23456, "212.34565")
          .Rows.Add("String23", 23, 23.23456, "213.3456")
          .Rows.Add("String24", 24, 24.23456, "214.3456")

        End With
        With OldTable3
          .Columns.Add("One3", GetType(String))
          .Columns.Add("Two3", GetType(Decimal))
          .Columns.Add("Three3", GetType(Double))
          .Columns.Add("Four3", GetType(Integer))

          ' simulate special values for column2
          .Rows.Add("String31", 31, CDbl(OldTable1.Rows(0).Item("Three1")) * CDbl(OldTable2.Rows(0).Item("Two2")), 311)
          .Rows.Add("String32", 32, CDbl(OldTable1.Rows(1).Item("Three1")) * CDbl(OldTable2.Rows(1).Item("Two2")), 312)
          .Rows.Add("String33", 33, CDbl(OldTable1.Rows(2).Item("Three1")) * CDbl(OldTable2.Rows(2).Item("Two2")), 313)
          .Rows.Add("String34", 34, CDbl(OldTable1.Rows(3).Item("Three1")) * CDbl(OldTable2.Rows(3).Item("Two2")), 314)
        End With

        ' a straightforward way to do it
        NewTable1 = OldTable1.Copy()
        NewTable2 = OldTable2.Copy()
        NewTable3 = OldTable3.Copy()

      End Sub
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' an alternative way to do it which
        ' could allow for other data manipulation
        ' to be done
        Button1.Visible = False
        BackgroundWorker1.RunWorkerAsync()
      End Sub
      Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        ShowTable += 1
        If ShowTable > 2 Then ShowTable = 0
        Select Case ShowTable
          Case 1
            DataGridView1.DataSource = NewTable1
          Case 2
            DataGridView1.DataSource = NewTable2
          Case Else
            DataGridView1.DataSource = NewTable3
        End Select
      End Sub



      Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        ' copy OldTable1 to NewTable1
        Dim counter As Integer = 10
        For Each i As Object In OldTable1.Columns
          NewTable1.Columns.Add(DirectCast(i, DataColumn).ColumnName, DirectCast(i, DataColumn).DataType)
        Next
        For Each i As Object In OldTable1.Rows
          NewTable1.Rows.Add(DirectCast(i, DataRow).ItemArray)
          counter += 1
          BackgroundWorker1.ReportProgress(counter)
          Threading.Thread.Sleep(500)
        Next

        ' copy OldTable2 to NewTable2
        counter = 20
        For Each i As Object In OldTable2.Columns
          NewTable2.Columns.Add(DirectCast(i, DataColumn).ColumnName, DirectCast(i, DataColumn).DataType)
        Next
        For Each i As Object In OldTable2.Rows
          NewTable2.Rows.Add(DirectCast(i, DataRow).ItemArray)
          counter += 1
          BackgroundWorker1.ReportProgress(counter)
          Threading.Thread.Sleep(500)
        Next

        ' copy OldTable3 to NewTable3
        counter = 30
        For Each i As Object In OldTable3.Columns
          NewTable3.Columns.Add(DirectCast(i, DataColumn).ColumnName, DirectCast(i, DataColumn).DataType)
        Next
        For Each i As Object In OldTable3.Rows
          NewTable3.Rows.Add(DirectCast(i, DataRow).ItemArray)
          counter += 1
          BackgroundWorker1.ReportProgress(counter)
          Threading.Thread.Sleep(500)
        Next

      End Sub
      Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged
        Select Case e.ProgressPercentage
          Case < 20
            Invoke(Sub() Label1.Text = "Table1 Row " & (e.ProgressPercentage - 10).ToString)
          Case < 30
            Invoke(Sub() Label1.Text = "Table2 Row " & (e.ProgressPercentage - 20).ToString)
          Case Else
            Invoke(Sub() Label1.Text = "Table3 Row " & (e.ProgressPercentage - 30).ToString)
        End Select

        Invoke(Sub() Application.DoEvents())
        ' simulate pause to see label value
      End Sub
      Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        Button2.Visible = True
        Button2.PerformClick()
      End Sub
    End Class


    Regards Les, Livingston, Scotland



    • Edited by leshay Saturday, December 21, 2019 4:44 PM
    • Marked as answer by AndyNakamura Saturday, December 21, 2019 9:17 PM
    Saturday, December 21, 2019 4:19 PM
  • Hi Les, thanks for that.

    I've used your select case idea.
    I set an integer variable to either 1, 2 or 3 depending on which table is being processed.
    Then

    Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged          
            Select Case intProc
                Case Is = 1
                    Label1.Text = e.ProgressPercentage.ToString
                Case Is = 2
                    Label2.Text = e.ProgressPercentage.ToString
                Case Is = 3
                    Label3.Text = e.ProgressPercentage.ToString
            End Select
           
        End Sub

    Thanks Les

    Andy

    Saturday, December 21, 2019 9:17 PM
  • Hi

    Did you check out the 'first' method used - the DataTable.Copy method?


    Regards Les, Livingston, Scotland


    • Edited by leshay Saturday, December 21, 2019 9:27 PM
    Saturday, December 21, 2019 9:27 PM