[RESOLVED] Getting progress updates from SQLBulkCopy in a BackgroundWorker

[cory_jackson]

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.

Imports System.Data.SqlClient
 
Class Program
    Private Shared Sub Main()
        Dim connectionString As String = GetConnectionString()
 
        Using sourceConnection As SqlConnection = New SqlConnection(connectionString)
            sourceConnection.Open()
            Dim commandRowCount As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM " & "dbo.BulkCopyDemoMatchingColumns;", sourceConnection)
            Dim countStart As Long = System.Convert.ToInt32(commandRowCount.ExecuteScalar())
            Console.WriteLine("NotifyAfter Sample")
            Console.WriteLine("Starting row count = {0}", countStart)
            Dim commandSourceData As SqlCommand = New SqlCommand("SELECT ProductID, Name, " & "ProductNumber " & "FROM Production.Product;", sourceConnection)
            Dim reader As SqlDataReader = commandSourceData.ExecuteReader()
 
            Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
                bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"
                AddHandler bulkCopy.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
                bulkCopy.NotifyAfter = 50
 
                Try
                    bulkCopy.WriteToServer(reader)
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                Finally
                    reader.Close()
                End Try
            End Using
 
            Dim countEnd As Long = System.Convert.ToInt32(commandRowCount.ExecuteScalar())
            Console.WriteLine("Ending row count = {0}", countEnd)
            Console.WriteLine("{0} rows were added.", countEnd - countStart)
            Console.WriteLine("Press Enter to finish.")
            Console.ReadLine()
        End Using
    End Sub
 
    Private Shared Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
        Console.WriteLine("Copied {0} so far...", e.RowsCopied)
    End Sub
 
    Private Shared Function GetConnectionString() As String
        Return "Data Source=(local); " & " Integrated Security=true;" & "Initial Catalog=AdventureWorks;"
    End Function
End Class

[techgnome]

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

[cory_jackson]

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.

[techgnome]

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

[cory_jackson]

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.

[ChrisE]

Hi,

don't know but if this is intended but showing a progress every 50 rows seems abit small

 Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
                bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"
                AddHandler bulkCopy.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
                bulkCopy.NotifyAfter = 50

[cory_jackson]

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.

[techgnome]

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

[cory_jackson]

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...

[techgnome]

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

[cory_jackson]

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.

[cory_jackson]

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.

[techgnome]

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

-tg

[cory_jackson]

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

[cory_jackson]

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.

Private Sub BwMain_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bwMain.DoWork
    Using dtTest As New DataTable("Test")
        bwMain.ReportProgress(0, "Starting.")
        dtTest.Columns.Add("Test", GetType(Integer))
        For intTest = 1 To 10000
            Dim drTemp As DataRow = dtTest.NewRow
            drTemp("Test") = intTest
            dtTest.Rows.Add(drTemp)
        Next
        bwMain.ReportProgress(0, "Table created.")
        Using bcMain = New SqlBulkCopy("Data Source=.\sqlexpress;Initial Catalog=sandbox;Integrated Security=True")
            AddHandler bcMain.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
            bcMain.NotifyAfter = 100
            bcMain.BatchSize = 100
            bcMain.DestinationTableName = "Test"
            bcMain.ColumnMappings.Add("Test", "Test")
            bcMain.WriteToServer(dtTest)
        End Using
        bwMain.ReportProgress(0, "Table sent.")
    End Using
End Sub
 
Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
    Console.WriteLine("Copied " & e.RowsCopied)
    'txtProgress.AppendText("Copied " & e.RowsCopied)
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.

[cory_jackson]

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.

[cory_jackson]

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.

[cory_jackson]

I'm looking into WriteToServerAsync method now.

[cory_jackson]

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.

[cory_jackson]

If you want to create this table, you can use this:

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))

[ChrisE]

Hi

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

see if it helps

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

[cory_jackson]

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.

[topshot]

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:

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

[cory_jackson]

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.

[topshot]

But I can't call ReportProgress from this other sub.

Does it give an error? How/where are you declaring bwMain?

[cory_jackson]

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

[topshot]

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?

[cory_jackson]

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.

[techgnome]

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

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


-tg

[cory_jackson]

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

[cory_jackson]

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.

[cory_jackson]

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.

Private Delegate Sub DoOSRC()
Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
    If Me.InvokeRequired Then
        Me.Invoke(New DoOSRC(AddressOf OnSqlRowsCopied))
    Else
        txtProgress.AppendText("Copied " & e.RowsCopied & vbCrLf)
    End If
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.

[cory_jackson]

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.

[cory_jackson]

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.

Imports System.Data.SqlClient
 
Public Class frmMain
    Private Sub BtnExecute_Click(sender As Object, e As EventArgs) Handles btnExecute.Click
        bwMain.RunWorkerAsync()
    End Sub
 
    Dim dtTest As New DataTable("Test")
    Private Sub BwMain_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bwMain.DoWork
        bwMain.ReportProgress(0, "Starting.")
        For intCol = 1 To 100
            dtTest.Columns.Add("T" & intCol, GetType(String))
        Next
        For intTest = 1 To 2000
            Dim drTemp As DataRow = dtTest.NewRow
            For intCol = 1 To 100
                drTemp("T" & intCol) = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
            Next
            dtTest.Rows.Add(drTemp)
        Next
        bwMain.ReportProgress(0, "Table created.")
        Dim csbTest As New SqlConnectionStringBuilder With {
            .DataSource = My.Settings.DataSource,
            .InitialCatalog = My.Settings.InitialCatalog,
            .UserID = My.Settings.User,
            .Password = My.Settings.Password}
        Using bcMain = New SqlBulkCopy(csbTest.ConnectionString)
            AddHandler bcMain.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
            bcMain.BulkCopyTimeout = 600
            bcMain.NotifyAfter = 1000
            bcMain.BatchSize = 1000
            bcMain.DestinationTableName = "Test"
            For intCol = 1 To 100
                bcMain.ColumnMappings.Add("T" & intCol, "T" & intCol)
            Next
            bcMain.WriteToServer(dtTest)
        End Using
    End Sub
 
    Private Sub BwMain_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles bwMain.ProgressChanged
        txtProgress.AppendText(String.Concat(e.UserState, vbCrLf))
    End Sub
 
    Private Sub BwMain_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwMain.RunWorkerCompleted
        txtProgress.AppendText("Table sent." & vbCrLf)
    End Sub
 
    Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
        Dim strMessage As String = String.Concat("Copied: ", e.RowsCopied, vbCrLf)
        Debug.WriteLine(strMessage)
        ReportUploadProgress(strMessage)
    End Sub
 
    Private Delegate Sub ReportUploadProgressDelegate(strMessage As String)
    Private Sub ReportUploadProgress(strMessage As String)
        If txtProgress.InvokeRequired Then
            txtProgress.Invoke(New ReportUploadProgressDelegate(AddressOf ReportUploadProgress))
        Else
            txtProgress.AppendText(strMessage)
        End If
    End Sub
 
End Class

[cory_jackson]

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.

Imports System.Data.SqlClient
 
Public Class frmMain
    Private Sub BtnExecute_Click(sender As Object, e As EventArgs) Handles btnExecute.Click
        bwMain.RunWorkerAsync()
    End Sub
 
    Dim dtTest As New DataTable("Test")
    Private Sub BwMain_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bwMain.DoWork
        bwMain.ReportProgress(0, "Starting.")
        For intCol = 1 To 100
            dtTest.Columns.Add("T" & intCol, GetType(String))
        Next
        For intTest = 1 To 2000
            Dim drTemp As DataRow = dtTest.NewRow
            For intCol = 1 To 100
                drTemp("T" & intCol) = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
            Next
            dtTest.Rows.Add(drTemp)
        Next
        bwMain.ReportProgress(0, "Table created.")
        Dim csbTest As New SqlConnectionStringBuilder With {
            .DataSource = My.Settings.DataSource,
            .InitialCatalog = My.Settings.InitialCatalog,
            .UserID = My.Settings.User,
            .Password = My.Settings.Password}
        Using bcMain = New SqlBulkCopy(csbTest.ConnectionString)
            AddHandler bcMain.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
            bcMain.BulkCopyTimeout = 600
            bcMain.NotifyAfter = 1000
            bcMain.BatchSize = 1000
            bcMain.DestinationTableName = "Test"
            For intCol = 1 To 100
                bcMain.ColumnMappings.Add("T" & intCol, "T" & intCol)
            Next
            bcMain.WriteToServer(dtTest)
        End Using
    End Sub
 
    Private Sub BwMain_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles bwMain.ProgressChanged
        txtProgress.AppendText(String.Concat(e.UserState, vbCrLf))
    End Sub
 
    Private Sub BwMain_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwMain.RunWorkerCompleted
        txtProgress.AppendText("Table sent." & vbCrLf)
    End Sub
 
    Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
        Dim strMessage As String = String.Concat("Copied: ", e.RowsCopied, vbCrLf)
        Debug.WriteLine(strMessage)
        ReportUploadProgress(strMessage)
    End Sub
 
    Private Delegate Sub ReportUploadProgressDelegate(strMessage As String)
    Protected Sub ReportUploadProgress(strMessage As String)
        Dim RUPD As ReportUploadProgressDelegate = AddressOf txtProgress.AppendText
        RUPD.Invoke(strMessage)
    End Sub
 
End Class

[cory_jackson]

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.

Private Delegate Sub ReportUploadProgressDelegate(strMessage As String)
Protected Sub ReportUploadProgress(strMessage As String)
    If txtProgress.InvokeRequired Then
        txtProgress.Invoke(New ReportUploadProgressDelegate(AddressOf ReportUploadProgress), strMessage)
    Else
        txtProgress.AppendText(strMessage)
    End If
End Sub

[cory_jackson]

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.

Private Delegate Sub OnSqlRowsCopiedDelegate(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
    Dim strMessage As String = String.Concat("Copied: ", e.RowsCopied, vbCrLf)
    If Me.InvokeRequired Then
        Me.Invoke(New OnSqlRowsCopiedDelegate(AddressOf OnSqlRowsCopied), sender, e)
    Else
        txtProgress.AppendText(strMessage)
    End If
End Sub

[cory_jackson]

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.

Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlRowsCopiedEventArgs)
    Dim strMessage As String = String.Concat("Copied: ", e.RowsCopied, vbCrLf)
    Me.Invoke(New ProgressAppendDelegate(AddressOf ProgressAppend), strMessage)
End Sub
 
Private Delegate Sub ProgressAppendDelegate(strMessage As String)
Private Sub ProgressAppend(strMessage As String)
    txtProgress.AppendText(strMessage)
End Sub