PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] Getting progress updates from SQLBulkCopy in a BackgroundWorker-VBForums
Results 1 to 38 of 38

Thread: [RESOLVED] Getting progress updates from SQLBulkCopy in a BackgroundWorker

  1. #1

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Resolved [RESOLVED] Getting progress updates from SQLBulkCopy in a BackgroundWorker

    I found this great sample below that demonstrated how to report progress of SQLBulyCopy. It works in this application but I am trying to run the SQLBulkCopy in a BackgroundWorker. I can't figure out how to adapt it to report to a TextBox on the parent WinForm. Do you have a suggestions?
    I was also wondering if SQLBulkCopy needs to be run in a BackgroundWorker to keep the UI responsive. IOW if it were run without the BackgroundWorker, would the form and controls still be responsive? I was wondering it it was already in an asynchronous process. Or whatever it's called when one uses the BackgroundWorker class.
    vb.net Code:
    1. Imports System.Data.SqlClient
    2.  
    3. Class Program
    4.     Private Shared Sub Main()
    5.         Dim connectionString As String = GetConnectionString()
    6.  
    7.         Using sourceConnection As SqlConnection = New SqlConnection(connectionString)
    8.             sourceConnection.Open()
    9.             Dim commandRowCount As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM " & "dbo.BulkCopyDemoMatchingColumns;", sourceConnection)
    10.             Dim countStart As Long = System.Convert.ToInt32(commandRowCount.ExecuteScalar())
    11.             Console.WriteLine("NotifyAfter Sample")
    12.             Console.WriteLine("Starting row count = {0}", countStart)
    13.             Dim commandSourceData As SqlCommand = New SqlCommand("SELECT ProductID, Name, " & "ProductNumber " & "FROM Production.Product;", sourceConnection)
    14.             Dim reader As SqlDataReader = commandSourceData.ExecuteReader()
    15.  
    16.             Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
    17.                 bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"
    18.                 AddHandler bulkCopy.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
    19.                 bulkCopy.NotifyAfter = 50
    20.  
    21.                 Try
    22.                     bulkCopy.WriteToServer(reader)
    23.                 Catch ex As Exception
    24.                     Console.WriteLine(ex.Message)
    25.                 Finally
    26.                     reader.Close()
    27.                 End Try
    28.             End Using
    29.  
    30.             Dim countEnd As Long = System.Convert.ToInt32(commandRowCount.ExecuteScalar())
    31.             Console.WriteLine("Ending row count = {0}", countEnd)
    32.             Console.WriteLine("{0} rows were added.", countEnd - countStart)
    33.             Console.WriteLine("Press Enter to finish.")
    34.             Console.ReadLine()
    35.         End Using
    36.     End Sub
    37.  
    38.     Private Shared Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
    39.         Console.WriteLine("Copied {0} so far...", e.RowsCopied)
    40.     End Sub
    41.  
    42.     Private Shared Function GetConnectionString() As String
    43.         Return "Data Source=(local); " & " Integrated Security=true;" & "Initial Catalog=AdventureWorks;"
    44.     End Function
    45. End Class

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,261

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    It would be the same... only instead of writing to the console, it would send to the text box... because I'm normally concerned with performance over the UI, I've never used that event... so I don't know how it reacts with the app... my guess is the the WriteToServer isn't async, but within the event call, you can do UI updates then do and .Invalidate on a control, or even the form to force a refresh. Now, if you off load the SBC to a BGW, then you'd have to do through the usual .Invoke hoops to do any kind of UI updates.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Hmm. I tried putting the OnSqlRowsCopied sub in the main form and that worked for the Console write. But right next to it I added a AppendText and nothing happened in the TextBox despite the messages appearing in the Immediate Window. And it wasn't that it was not refreshing, because at the end there are additional AppendText writes to the TextBox and they didn't change. These things I do not understand well at all.

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,261

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    did you make the textbox multiine? and did you make it big enough to actually display the lines of text? Usually for things like that I use a list box since textboxes can be tampered with by the user while listboxes can't be... but for testing purposes it doesn't matter. That said ..... hmmmm.....
    Try using textbox.invalidate, right after the append text... it could be a refresh issue... I'm not convinced it is, but that would rule that out (or rule it in if it is in fact the issue, either way, it's not going to hurt to try it.)


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Multiline = yes. I write other parts with the BackgroundWorker.ReportProgress using essentially the same command.
    Large enough = Yes.

    textbox.invalidate sounds interesting. I did try a refresh. I will try to post a sample later.

  6. #6
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,848

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Hi,

    don't know but if this is intended but showing a progress every 50 rows seems abit small
    Code:
     Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
                    bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"
                    AddHandler bulkCopy.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
                    bulkCopy.NotifyAfter = 50
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    ChrisE: You're right. That's not my code BTW. But I was testing with a small batch and used 100 for testing with 1000 rows. The production job is 2M rows and I have been using 1000 for that. Mine is embedded in a BackgroundWorker.

  8. #8
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,261

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Wait... you ARE using a BGW for this? Might want to show your current code then... since it's different from the code in post 1... If it's in the DoWork of a BGW, that might be why you're not seeing the updates in the Textbox, because that's in a separate thread... that's why I said if you used one, you'd have to make sure you used .Invoke to update the UI properly on the main UI thread...


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    I'll try to get that up soon. I have another problem with SQLBulkCopy crashing after about 6000 rows. I was hoping SQLBulkCopy would be easier...

  10. #10
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,261

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    If I had to guess, it would be that it must be something else then... I've done 10k+ rows with SQLBulkCopy before with no issues on dozens of projects... but without seeing the code and possibly sample data or the error message, it's hard to say what the problem is.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    TG: I'll start another post in a moment. I had to build a plan B, DataTable.WriteXML, and execute it to ensure I don't miss my deadline today. Then I can work on my other issues at leisure. I hope you will read my other post with the error message when I get it in a few minutes.

  12. #12

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    TG: (Off topic) It was a VarChar overflow. Someone put "CANADA" in a "State" column. They have a state width of 20 and I have 2. I had compared all the widths earlier but I changed my methodology and forgot to review all the widths. My mistake.

  13. #13
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,261

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Ah... those pesky users.... always trying to break our better mouse traps.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    No way. My fault. Stupid of me to forget to check the width compatibility again.

  15. #15

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    I accomplished some milestones and had some time this morning to come back to this opportunity for me to learn. Thank you guys for being patient.
    I lashed up a simple program to demonstrate the progress I have and where I am unable to continue. If you want to create a destination table to test this, execute this query "Create table Test (Test int)" and change my code to your Db instance and whatever connection string works for you.
    vb.net Code:
    1. Private Sub BwMain_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bwMain.DoWork
    2.     Using dtTest As New DataTable("Test")
    3.         bwMain.ReportProgress(0, "Starting.")
    4.         dtTest.Columns.Add("Test", GetType(Integer))
    5.         For intTest = 1 To 10000
    6.             Dim drTemp As DataRow = dtTest.NewRow
    7.             drTemp("Test") = intTest
    8.             dtTest.Rows.Add(drTemp)
    9.         Next
    10.         bwMain.ReportProgress(0, "Table created.")
    11.         Using bcMain = New SqlBulkCopy("Data Source=.\sqlexpress;Initial Catalog=sandbox;Integrated Security=True")
    12.             AddHandler bcMain.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
    13.             bcMain.NotifyAfter = 100
    14.             bcMain.BatchSize = 100
    15.             bcMain.DestinationTableName = "Test"
    16.             bcMain.ColumnMappings.Add("Test", "Test")
    17.             bcMain.WriteToServer(dtTest)
    18.         End Using
    19.         bwMain.ReportProgress(0, "Table sent.")
    20.     End Using
    21. End Sub
    22.  
    23. Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
    24.     Console.WriteLine("Copied " & e.RowsCopied)
    25.     'txtProgress.AppendText("Copied " & e.RowsCopied)
    26. End Sub
    Obviously the remarked line can't work due to a cross threading problem. But I wonder, am I going about this the wrong way? Should I be trying to use the BwMain_ProgressChanged? And why is it my sub for that, which looks just the same, can update the text box but this one can't?
    I don't understand enough about multi-thread. The BackgroundWorker has insulated me from all this. But it seems this time it's abnormal and I want to know how to make it report this progress back to the Progress text box.

  16. #16

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    bulk copy out of the BackgroundWorker and it looks like it was writing to the TextBox without locking up the UI. But it goes too fast. I'm going to try it on my main program where things go much slower due to remote server and much more data. If I can get updates to the form and it remains responsive, then I don't need to run it in a background worker. But it can't be asynchronous because the program waits. So I'm confused. Probably nothing, but I'll give it a try.

  17. #17

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    I changed my program above to use my Azure Db, made the test table 100 columns of VarChar(255), and created 100k rows of the letter "A" 255 wide in each column, to make the table a big fat pig. I changed it to upload and notify in chunks of 1000 rows. When the WriteToServer method kicked in, the UI stopped responding to user inputs. However the TextBox was being updated with each chunk sent.

  18. #18

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    I'm looking into WriteToServerAsync method now.

  19. #19

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    I changed the method to WriteToServerAsync but it does nothing. I think there's something about creating a task and using "Await", but I can't find any examples in VB.NET. I've tried to convert several examples from C# but none of them will compile for me.

  20. #20

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    If you want to create this table, you can use this:
    Code:
    Create table Test (T1 VarChar(255),T2 VarChar(255),T3 VarChar(255),T4 VarChar(255),T5 VarChar(255),T6 VarChar(255),T7 VarChar(255),T8 VarChar(255),T9 VarChar(255),T10 VarChar(255),T11 VarChar(255),T12 VarChar(255),T13 VarChar(255),T14 VarChar(255),T15 VarChar(255),T16 VarChar(255),T17 VarChar(255),T18 VarChar(255),T19 VarChar(255),T20 VarChar(255),T21 VarChar(255),T22 VarChar(255),T23 VarChar(255),T24 VarChar(255),T25 VarChar(255),T26 VarChar(255),T27 VarChar(255),T28 VarChar(255),T29 VarChar(255),T30 VarChar(255),T31 VarChar(255),T32 VarChar(255),T33 VarChar(255),T34 VarChar(255),T35 VarChar(255),T36 VarChar(255),T37 VarChar(255),T38 VarChar(255),T39 VarChar(255),T40 VarChar(255),T41 VarChar(255),T42 VarChar(255),T43 VarChar(255),T44 VarChar(255),T45 VarChar(255),T46 VarChar(255),T47 VarChar(255),T48 VarChar(255),T49 VarChar(255),T50 VarChar(255),T51 VarChar(255),T52 VarChar(255),T53 VarChar(255),T54 VarChar(255),T55 VarChar(255),T56 VarChar(255),T57 VarChar(255),T58 VarChar(255),T59 VarChar(255),T60 VarChar(255),T61 VarChar(255),T62 VarChar(255),T63 VarChar(255),T64 VarChar(255),T65 VarChar(255),T66 VarChar(255),T67 VarChar(255),T68 VarChar(255),T69 VarChar(255),T70 VarChar(255),T71 VarChar(255),T72 VarChar(255),T73 VarChar(255),T74 VarChar(255),T75 VarChar(255),T76 VarChar(255),T77 VarChar(255),T78 VarChar(255),T79 VarChar(255),T80 VarChar(255),T81 VarChar(255),T82 VarChar(255),T83 VarChar(255),T84 VarChar(255),T85 VarChar(255),T86 VarChar(255),T87 VarChar(255),T88 VarChar(255),T89 VarChar(255),T90 VarChar(255),T91 VarChar(255),T92 VarChar(255),T93 VarChar(255),T94 VarChar(255),T95 VarChar(255),T96 VarChar(255),T97 VarChar(255),T98 VarChar(255),T99 VarChar(255),T100 VarChar(255))

  21. #21
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,848

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Hi

    just tested puting the doWork stuff in a seperate Function and the Form didn't freeze

    see if it helps

    Code:
    Public Class Form1
    
        Private Function doStuff() As Boolean
            For y As Integer = 0 To 1000
                Dim x As Double = 0
                For z As Integer = 1 To 1000000
                    x = (z + x) / 1000
                Next
                BackgroundWorker1.ReportProgress(y / 10)
            Next
            Return True
        End Function
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Me.BackgroundWorker1.RunWorkerAsync()
        End Sub
    
        Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
            'put Calculation in other Thread
            Me.doStuff()
        End Sub
    
        Private Sub BackgroundWorker1_ProgressChanged1(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged
            Me.ProgressBar1.Value = e.ProgressPercentage
            Me.ToolStripProgressBar1.Value = Me.ProgressBar1.Value
            Me.ToolStripLabel1.Text = Me.ProgressBar1.Value.ToString & " %"
            Me.Label1.Text = Me.ToolStripLabel1.Text
        End Sub
    
        Private Sub BackgroundWorker1_RunWorkerCompleted1(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
            Me.ProgressBar1.Value = 0
            Me.ToolStripProgressBar1.Value = Me.ProgressBar1.Value
            Me.ToolStripLabel1.Text = "Done"
            Me.Label1.Text = Me.ToolStripLabel1.Text
        End Sub
    
        
        'this won't work, the Form will freeze
        'put calculation in other Thread
        'Private Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click
        '    For y As Integer = 0 To 1000
        '        Dim x As Double = 0
        '        For z As Integer = 1 To 1000000
        '            x = (z + x) / 1000
        '        Next
        '        Me.ToolStripProgressBar1.Value = y / 10
        '        Me.ToolStripLabel1.Text = Me.ToolStripProgressBar1.Value.ToString & " %"
        '        Me.ToolStrip1.Refresh()
        '        Me.ProgressBar2.Value = Me.ToolStripProgressBar1.Value
        '        Me.Label1.Text = Me.ToolStripProgressBar1.Value.ToString & " %"
        '        Me.Label1.Refresh()
        '    Next
        'End Sub
    
    End Class
    hth
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  22. #22

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Thank you ChrisE, but this doesn't handle the SqlBulkCopy.SqlRowsCopied event. When the event fires, I have it run the OnSqlRowsCopied subroutine but since it's on the BackgroundWorker thread, it can't update the TextBox in the form.

  23. #23
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,111

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Would using the ReportProgress method to pass the info you need to the TextBox work? That is how I update the UI when using a BGW. I use the integer parameter of ReportProgress method to control what "state" I'm in, which determines what control(s) I am updating on the UI. Then in ProgressChanged event, I Select Case on e.ProgressPercentage and handle as needed. For example:

    Code:
    Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
        'Console.WriteLine("Copied " & e.RowsCopied)
        bwMain.ReportProgress(BGW_State.S205_UpdRows, e.RowsCopied) 'BGW_State.S205_UpdRows is just Enum As Integer value that can't happen normally
    End Sub
    
    Private Sub bwMain_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles bwMain.ProgressChanged
    
        Select Case e.ProgressPercentage
            Case BGW_State.S205_UpdRows
                Dim cnt As Integer = CType(e.UserState, Integer) 'I am assuming RowsCopied is Integer type
                txtProgress.Text = cnt.ToString
                txtProgress.Refresh()
            Case ...
                'update some other control
        End Select
    
    End Sub

  24. #24

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Topshot I do the same things with other updates in the program, but this event is being raised by the SQLBulkCopy and I think must be handled by another sub. But I can't call ReportProgress from this other sub.

  25. #25
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,111

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Quote Originally Posted by cory_jackson View Post
    But I can't call ReportProgress from this other sub.
    Does it give an error? How/where are you declaring bwMain?

  26. #26

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Crossthreading error. bwMain is being declared in the form designer as a component.

  27. #27
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,111

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Hmmm. I've called ReportProgress from button Click event (UI thread) before. Obviously, the BulkCopy must be on its own thread.

    There's likely a better way, but how about updating a global variable with e.RowsCopied and a Timer that does txtProgress.Text = gblRows.ToString?

  28. #28

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    I believe BulkCopy is on the same thread as the background worker. When it copies X number of rows, defined by the program, it raises and event and passes to the event handler an object with the number of rows and some other things. But I don't know how to update a text box on thread A from thread B. That sub that runs is on thread B. That sub can not contain the BackgroundWorker ProgressChanged.

  29. #29
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,261

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    I said it before, I'll say it three more times: Invoke, invoke, invoke....

    https://medium.com/xster-tech/invoke...t-404d21498727


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  30. #30

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    But I don't know how to invoke! :-) Thanks for the link. I'll check it out after lunch.

  31. #31

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    I'm trying to understand. I read it, but it's clear as mud. I'll study my books more and see if I can understand this cross thread action. Then I'll see if I can implement it. Parts of what that page does with adding a handler and all looks similar to what was in my first example. SO I'm thinking you're suggesting that there is some part of that which needs to be changed. Something like adding the "Private Delegate Sub" part. I'll keep studying it. Thank you.

  32. #32

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    I learned that a a delegate is a variable type that represents a method and it's parameters to be passed. I know that Invoke is a method to inject a call back to the originating thread, in this case, the UI. Cool. But I don't understand the code on that page. There's a line declaring a sub but then another line below declaring another sub, but only one "End sub". This is confusing. I know that the example includes a condition to check if invoke needs to be used. But I don't understand how they connects to the Delegate Sub declaration. It's all very confusing to me. Nonetheless, I tried to adapt my program to this unfamiliar formatting, but it doesn't work.
    vb.net Code:
    1. Private Delegate Sub DoOSRC()
    2. Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
    3.     If Me.InvokeRequired Then
    4.         Me.Invoke(New DoOSRC(AddressOf OnSqlRowsCopied))
    5.     Else
    6.         txtProgress.AppendText("Copied " & e.RowsCopied & vbCrLf)
    7.     End If
    8. End Sub
    OnSqlRowsCopied has the red squiggly and the error description is "Severity Code Description Project File Line Suppression State
    Error BC31143 Method 'Private Sub OnSqlRowsCopied(sender As Object, e As SqlRowsCopiedEventArgs)' does not have a signature compatible with delegate 'Delegate Sub frmMain.DoOSRC()'." If I remove the parameters from the OnSqlRowsCopied the errors goes away. I know the delegate is a signature including these parameters so there's something wrong with having it that way and why the example works, but not in my code. But in the example they doesn't need the argument passed from the SqlRowsCopied. But I don't see how I can make it work with that. I tried several things but really I'm just guessing. It's not based on any understanding and I hate adding code I don't functionally understand.
    I don't want to just make this work, I want to understand. I have to jump off of this and work on something else now. I'll do some more studying later.

  33. #33

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    One last thing. These examples uses "InvokeRequired" to test if invocation is required. But I already know it's required. Can this be simplified to not check that? I mean just do the invoke method and not try the impossible "Else" part.

  34. #34

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    Now I have the code below. No Intellisense error, but when it gets to the txtProgress.Invoke it complains that the parameter count is mismatched. So I'm guessing I need to do something different than just the "AddressOf ReportUploadProgress" to pass the string parameter. But I don't get it. I' know I must be doing something stupid.
    Can't I just declare a delegate variable type at the address type of the TextBox AppendText method instead of all these subs? It just seems like it should be simpler than I have it.

    vb.net Code:
    1. Imports System.Data.SqlClient
    2.  
    3. Public Class frmMain
    4.     Private Sub BtnExecute_Click(sender As Object, e As EventArgs) Handles btnExecute.Click
    5.         bwMain.RunWorkerAsync()
    6.     End Sub
    7.  
    8.     Dim dtTest As New DataTable("Test")
    9.     Private Sub BwMain_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bwMain.DoWork
    10.         bwMain.ReportProgress(0, "Starting.")
    11.         For intCol = 1 To 100
    12.             dtTest.Columns.Add("T" & intCol, GetType(String))
    13.         Next
    14.         For intTest = 1 To 2000
    15.             Dim drTemp As DataRow = dtTest.NewRow
    16.             For intCol = 1 To 100
    17.                 drTemp("T" & intCol) = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
    18.             Next
    19.             dtTest.Rows.Add(drTemp)
    20.         Next
    21.         bwMain.ReportProgress(0, "Table created.")
    22.         Dim csbTest As New SqlConnectionStringBuilder With {
    23.             .DataSource = My.Settings.DataSource,
    24.             .InitialCatalog = My.Settings.InitialCatalog,
    25.             .UserID = My.Settings.User,
    26.             .Password = My.Settings.Password}
    27.         Using bcMain = New SqlBulkCopy(csbTest.ConnectionString)
    28.             AddHandler bcMain.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
    29.             bcMain.BulkCopyTimeout = 600
    30.             bcMain.NotifyAfter = 1000
    31.             bcMain.BatchSize = 1000
    32.             bcMain.DestinationTableName = "Test"
    33.             For intCol = 1 To 100
    34.                 bcMain.ColumnMappings.Add("T" & intCol, "T" & intCol)
    35.             Next
    36.             bcMain.WriteToServer(dtTest)
    37.         End Using
    38.     End Sub
    39.  
    40.     Private Sub BwMain_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles bwMain.ProgressChanged
    41.         txtProgress.AppendText(String.Concat(e.UserState, vbCrLf))
    42.     End Sub
    43.  
    44.     Private Sub BwMain_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwMain.RunWorkerCompleted
    45.         txtProgress.AppendText("Table sent." & vbCrLf)
    46.     End Sub
    47.  
    48.     Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
    49.         Dim strMessage As String = String.Concat("Copied: ", e.RowsCopied, vbCrLf)
    50.         Debug.WriteLine(strMessage)
    51.         ReportUploadProgress(strMessage)
    52.     End Sub
    53.  
    54.     Private Delegate Sub ReportUploadProgressDelegate(strMessage As String)
    55.     Private Sub ReportUploadProgress(strMessage As String)
    56.         If txtProgress.InvokeRequired Then
    57.             txtProgress.Invoke(New ReportUploadProgressDelegate(AddressOf ReportUploadProgress))
    58.         Else
    59.             txtProgress.AppendText(strMessage)
    60.         End If
    61.     End Sub
    62.  
    63. End Class

  35. #35

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    I found another article on Microsoft's site that looked promising. I tried to adapt that with the Invoke but got the cross thread exception again.
    vb.net Code:
    1. Imports System.Data.SqlClient
    2.  
    3. Public Class frmMain
    4.     Private Sub BtnExecute_Click(sender As Object, e As EventArgs) Handles btnExecute.Click
    5.         bwMain.RunWorkerAsync()
    6.     End Sub
    7.  
    8.     Dim dtTest As New DataTable("Test")
    9.     Private Sub BwMain_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bwMain.DoWork
    10.         bwMain.ReportProgress(0, "Starting.")
    11.         For intCol = 1 To 100
    12.             dtTest.Columns.Add("T" & intCol, GetType(String))
    13.         Next
    14.         For intTest = 1 To 2000
    15.             Dim drTemp As DataRow = dtTest.NewRow
    16.             For intCol = 1 To 100
    17.                 drTemp("T" & intCol) = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
    18.             Next
    19.             dtTest.Rows.Add(drTemp)
    20.         Next
    21.         bwMain.ReportProgress(0, "Table created.")
    22.         Dim csbTest As New SqlConnectionStringBuilder With {
    23.             .DataSource = My.Settings.DataSource,
    24.             .InitialCatalog = My.Settings.InitialCatalog,
    25.             .UserID = My.Settings.User,
    26.             .Password = My.Settings.Password}
    27.         Using bcMain = New SqlBulkCopy(csbTest.ConnectionString)
    28.             AddHandler bcMain.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
    29.             bcMain.BulkCopyTimeout = 600
    30.             bcMain.NotifyAfter = 1000
    31.             bcMain.BatchSize = 1000
    32.             bcMain.DestinationTableName = "Test"
    33.             For intCol = 1 To 100
    34.                 bcMain.ColumnMappings.Add("T" & intCol, "T" & intCol)
    35.             Next
    36.             bcMain.WriteToServer(dtTest)
    37.         End Using
    38.     End Sub
    39.  
    40.     Private Sub BwMain_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles bwMain.ProgressChanged
    41.         txtProgress.AppendText(String.Concat(e.UserState, vbCrLf))
    42.     End Sub
    43.  
    44.     Private Sub BwMain_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwMain.RunWorkerCompleted
    45.         txtProgress.AppendText("Table sent." & vbCrLf)
    46.     End Sub
    47.  
    48.     Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
    49.         Dim strMessage As String = String.Concat("Copied: ", e.RowsCopied, vbCrLf)
    50.         Debug.WriteLine(strMessage)
    51.         ReportUploadProgress(strMessage)
    52.     End Sub
    53.  
    54.     Private Delegate Sub ReportUploadProgressDelegate(strMessage As String)
    55.     Protected Sub ReportUploadProgress(strMessage As String)
    56.         Dim RUPD As ReportUploadProgressDelegate = AddressOf txtProgress.AppendText
    57.         RUPD.Invoke(strMessage)
    58.     End Sub
    59.  
    60. End Class

  36. #36

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    I changed the last part to what's below and it works. But I'm not sure why. I'll need to come back to this and study it more later. I do understand now that "Private Delegate Sub" is declaring a subroutine delegate variable, not starting a subroutine. This should help me figure the rest out. But I'm still confused. Seems like too many subs.
    vb.net Code:
    1. Private Delegate Sub ReportUploadProgressDelegate(strMessage As String)
    2. Protected Sub ReportUploadProgress(strMessage As String)
    3.     If txtProgress.InvokeRequired Then
    4.         txtProgress.Invoke(New ReportUploadProgressDelegate(AddressOf ReportUploadProgress), strMessage)
    5.     Else
    6.         txtProgress.AppendText(strMessage)
    7.     End If
    8. End Sub

  37. #37

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    At 0444 while laying awake, something clicked. I came out and tried it and got it down to a single sub. I don't think it's an improvement but it means I'm starting to understand slightly better. Replaced the last two subs with this one.
    vb.net Code:
    1. Private Delegate Sub OnSqlRowsCopiedDelegate(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
    2. Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
    3.     Dim strMessage As String = String.Concat("Copied: ", e.RowsCopied, vbCrLf)
    4.     If Me.InvokeRequired Then
    5.         Me.Invoke(New OnSqlRowsCopiedDelegate(AddressOf OnSqlRowsCopied), sender, e)
    6.     Else
    7.         txtProgress.AppendText(strMessage)
    8.     End If
    9. End Sub

  38. #38

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Getting progress updates from SQLBulkCopy in a BackgroundWorker

    It finally all gelled in my mind. I do want 2 subs, the first handles the event from the SQLBulkCopy object and a very small one to do the update and only have a string parameter to take back to the UI thread. I know I need to invoke, so there's no need to test if it's needed. I also understand now that the "Private Delegate Sub" is a declaration of a delegate variable type and isn't a sub despite it having "sub" in the declaration. It's a "Delegate sub". And it's kind of a template, address and parameters, for a subroutine to get the signatures right to reference in code. Like the declaration of the subroutine with the rest cut off. Makes total sense now. So when I invoke I'm passing the variable over to the UI thread and that points to where that code exists in memory so it can be executes, and give it the parameter value that the subroutine needs. In the example, ProgressAppend never runs on the second thread. This is cool and makes sense now. And now that this makes sense, the other examples make sense also. And I see how one can run the delegated sub once on the secondary thread to try, then send a delegate for that same sub over to the UI thread to run if direct access was not allowed.
    vb.net Code:
    1. Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
    2.     Dim strMessage As String = String.Concat("Copied: ", e.RowsCopied, vbCrLf)
    3.     Me.Invoke(New ProgressAppendDelegate(AddressOf ProgressAppend), strMessage)
    4. End Sub
    5.  
    6. Private Delegate Sub ProgressAppendDelegate(strMessage As String)
    7. Private Sub ProgressAppend(strMessage As String)
    8.     txtProgress.AppendText(strMessage)
    9. End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width