SqlBulkCopy RowsCopied event not updating TextBox

[cory_jackson]

I don't understand delegates, but I got some help a while back and was able to make it work. Now I have a new project and I did something to break it.
I have a Background Worker that has an object that's doing SqlBulkCopy. I have this line.

AddHandler bcMain.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf frmMain.OnSqlRowsCopied)

In my main form I have a sub for updating a TextBox.

Private Delegate Sub AppendResultsDelegate(strMessage As String)
Private Sub AppendResults(strLine As String)
    txtResults.AppendText(strLine & vbCrLf)
End Sub

This works for everything except the SqlBulkCopy reporting rows copied. The odd thing is when I run the debugger and break on the AppendText line, I can see it doing it, but it's not apparent on the main form. I've tried adding a Refresh() on the control and the form, but it doesn't work. What am I doing wrong?

[jmcilhinney]

This is probably an issue with default form instances. That first line of code appears to be referring to a default instance. If that code is executed on a secondary thread than that will not be the instance that you think it is, so the code is working but it's updating a TextBox in a different form. Can you show us all of the relevant code?

[.paul.]

Looks like a cross thread problem to me. You probably need to Invoke txtResults on your BGW thread...

EDIT: Actually, it's probably what jm said

[cory_jackson]

Sounds like what might be happening.
I don't know what would be relevant. I have a default main form frmMain with the text box. frmMain also has the sub I showed above to modify the text box. frmMain also has a sub for the background worker DoWork. In that sub I create an object objDbTools. Just a class of functions I need to changing the Db. It has a function BulkUploadSuccess. Here it is.

Public Function BulkUploadSuccess(bwMain As System.ComponentModel.BackgroundWorker,
                                  dtTemp As DataTable) As Boolean
    strException = ""
    Using bcMain = New SqlBulkCopy(objConn.ConnectionString)
        AddHandler bcMain.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf frmMain.OnSqlRowsCopied)
        bcMain.NotifyAfter = My.Settings.BatchSize
        bcMain.BatchSize = My.Settings.BatchSize
        bcMain.DestinationTableName = My.Settings.DestinationTable
        bcMain.BulkCopyTimeout = My.Settings.SQLCommandTimeout
        For Each column As DataColumn In dtTemp.Columns
            bcMain.ColumnMappings.Add(column.ColumnName, column.ColumnName)
        Next
        Try
            bcMain.WriteToServer(dtTemp)
        Catch ex As Exception
            strException = ex.Message
        End Try
    End Using
    If strException = "" Then
        Return True
    Else
        Return False
    End If
End Function

Is there something else I could show you?
Thanks for all your help.

[cory_jackson]

Thanks Paul. Sounds about right, but I don't know how to do that.
I really need to learn about these someday...

[cory_jackson]

Thanks Paul, "Invoke" got me to looking and I see what I failed to copy over from the working project. I was playing with some things and forgot to put somehting back.

Me.Invoke(New AppendResultsDelegate(AddressOf AppendResults), strMessage)

Thank you both! You guys are benevolent gods.

[jmcilhinney]

The DoWork event handler of the BackgroundWorker is executed on a secondary thread. If that BulkUploadSuccess method is called from that event handler then it is also executed on that secondary thread. That means that this line:

AddHandler bcMain.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf frmMain.OnSqlRowsCopied)

is referring to the default instance of frmMain that is specific to that thread, which is NOT the instance that you are looking at. Like I said, the code is working but it is updating a TextBox on a form that you are not looking at and have never actually shown. You need to refer to the actual instance of frmMain that you have displayed, NOT the default instance.

Probably your best bet would be for that BulkUploadSuccess to have an extra parameter by which to pass the event handler delegate:

Public Function BulkUploadSuccess(bwMain As System.ComponentModel.BackgroundWorker,
                                  dtTemp As DataTable,
                                  onSqlRowsCopied As SqlRowsCopiedEventHandler) As Boolean
    strException = ""
    Using bcMain = New SqlBulkCopy(objConn.ConnectionString)
        AddHandler bcMain.SqlRowsCopied, onSqlRowsCopied

You can then create the delegate in the form itself, so you know that you'll be referring to the correct instance, and pass it into the method.

By the way, why is that method named BulkUploadSuccess? That sounds more like the name of a variable that indicates whether the operation was successful or not. A method name should be more like BulkUpload or DoBulkUpload.

[cory_jackson]

That was all educational and about what I understood, more I don't understand the commands to implement it.

Many of my routines are fail or success values returned. I use this as my error handing back on where it was called from. So like here it's a condition to report success to the main form or exit the subroutine and report the failure and exceptions to the user and bail on the routine. Just my goofy error handing logic.

[jmcilhinney]

Many of my routines are fail or success values returned. I use this as my error handing back on where it was called from. So like here it's a condition to report success to the main form or exit the subroutine and report the failure and exceptions to the user and bail on the routine. Just my goofy error handing logic.

There's nothing wrong with a method returning a Boolean to indicate success or failure. The issue is the name. That is not an appropriate method name. Methods are actions and the name should describe the action. Your name indicates that all the method does is determine whether a bulk upload is successful, not that it actually performs the bulk upload.

[cory_jackson]

Good point. I'll think about that.
However the fact that it failed or succeeded means it was attempted. And "bulk upload" is the action.
Also to me it's part of a sentence. "If (the) bulk upload (is a) success, then... else...". So the method's name is clipped form that. It just keeps the logic in my head straight and I know the method is a function that returns a Boolean value without checking.
But its' a good point. Thank you.

[.paul.]

I’m not disputing anything JM has told you, but this is the correct way to invoke your TextBox on your secondary thread…

Private Delegate Sub AppendResultsDelegate(strMessage As String)
Private Sub AppendResults(strLine As String)
    If txtResults.InvokeRequired Then
        txtResults.Invoke(New AppendResultsDelegate(AddressOf AppendResults), strMessage)
    Else
        txtResults.AppendText(strLine & vbCrLf)
    End If
End Sub

[jmcilhinney]

I’m not disputing anything JM has told you, but this is the correct way to invoke your TextBox on your secondary thread…

Private Delegate Sub AppendResultsDelegate(strMessage As String)
Private Sub AppendResults(strLine As String)
    If txtResults.InvokeRequired Then
        txtResults.Invoke(New AppendResultsDelegate(AddressOf AppendResults), strMessage)
    Else
        txtResults.AppendText(strLine & vbCrLf)
    End If
End Sub

I would also suggest that there's no point declaring your own delegate these days. In this case, an Action(Of String) delegate would be appropriate. The Framework includes Action and Func delegates for Subs and Functions with zero to 16 parameters.

[cory_jackson]

Oh cool! Thank you.

[cory_jackson]

I'd like to learn how to do this.
Would you be willing to show my how I would do this in my case?

[.paul.]

I’d also like to see Action and Func delegates for this case…

[jmcilhinney]

I'd like to learn how to do this.
Would you be willing to show my how I would do this in my case?

Are you referring to the use of an Action delegate? When there are multiple posts, please quote what you are replying to so that we know. If that is the case then simply get rid of your own delegate declaration and use an Action(Of String) delegate instead. That's all there is to it.

[.paul.]

To Invoke without a delegate...

Private Sub AppendResults(ByVal strLine As String)
    If txtResults.InvokeRequired Then
        txtResults.Invoke(New Action(Of String)(AddressOf AppendResults), strLine)
    Else
        txtResults.AppendText(strLine & vbCrLf)
    End If
End Sub

[.paul.]

AddHandler bcMain.SqlRowsCopied, AddressOf bcMain_SqlRowsCopied

Private Sub bcMain_SqlRowsCopied(sender As Object, e As SqlRowsCopiedEventArgs)
    ' your code
End Sub

[cory_jackson]

I'm messing this up somehow. I just don't understand.
In my dbTools class I have this function.

Public Function BulkUploadSuccess(bwMain As System.ComponentModel.BackgroundWorker,
                                  dtTemp As DataTable) As Boolean
    strException = ""
    Using bcMain = New SqlBulkCopy(objConn.ConnectionString)
        AddHandler bcMain.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf frmMain.SqlRowsCopied)
        bcMain.NotifyAfter = My.Settings.BatchSize
        bcMain.BatchSize = My.Settings.BatchSize
        bcMain.DestinationTableName = My.Settings.DestinationTable
        bcMain.BulkCopyTimeout = My.Settings.SQLCommandTimeout
        For Each column As DataColumn In dtTemp.Columns
            bcMain.ColumnMappings.Add(column.ColumnName, column.ColumnName)
        Next
        Try
            bcMain.WriteToServer(dtTemp)
        Catch ex As Exception
            strException = ex.Message
        End Try
    End Using
    If strException = "" Then
        Return True
    Else
        Return False
    End If
End Function

Then in the main form I have this sub.

Public Sub OnSqlRowsCopied(ByVal sender As Object, ByVal e As SqlClient.SqlRowsCopiedEventArgs)
    AppendResults(String.Concat("Copied: ", e.RowsCopied))
End Sub

And finally

Private Sub AppendResults(strLine As String)
    If txtResults.InvokeRequired Then
        txtResults.Invoke(New Action(Of String)(AddressOf AppendResults), strLine)
    Else
        txtResults.AppendText(strLine & vbCrLf)
    End If
End Sub

What do I have wrong?

[jmcilhinney]

Where is that BulkUploadSuccess called? Is it in the DoWork event handler of the BackgroundWorker? If so then I've already explained what the problem is and what the solution is.

[cory_jackson]

OK, my mistake. I just don't understand how this works. Thanks. I tried to mimic what Paul posted and it didn't work.

It's a method in the dbTools object in DoWork.

[jmcilhinney]

There are potentially two issues at play here, both related to multithreading. Think it through.

As I have already stated, default instances are thread-specific. If you are calling that BulkUploadSuccess method on a secondary thread then the default instance of frmMain is specific to that thread and of no use to you. I have already told you what to do about that.

Also, if you are calling the AppendResults method on a secondary thread then you can't access controls directly there either. If the SqlRowsCopied event handler is raised on a secondary thread then that will be the case and that is why .paul. told you to do as he did.

[cory_jackson]

Thank you