How to access COM object within a BackgroundWorker?

Question

I have a WPF app and in the main form, user is allowed to select few files(Excel) and then click a button to do a data extraction and uploading them to a database. Things work fine.

Now I wanted to implement a busy indicator.

So what I have done is, declare a BackgroundWorker thread and do my database uploading (which takes time) as a background thread. The busy indicator is set accordingly when thread start and completes. The Issue is inside my upload process, I access Clipboard to print some messages. So I ran in to the following error which is obvious.

"Current thread must be set to single thread apartment (STA) mode before OLE calls can be made."

BackgroundWorker is by default MTA. So what is the best way to overcome this issue?

Code:

Public WithEvents BgWorker As BackgroundWorker = New BackgroundWorker()

 Private Sub MainWindow_Loaded() Handles Me.Loaded
    AddHandler BgWorker.DoWork, AddressOf ExtractData
    AddHandler BgWorker.RunWorkerCompleted, AddressOf BgWorker_RunWorkerCompleted        
End Sub

Private Sub btnExtract_Click(sender As Object, e As RoutedEventArgs)

    .....

    Try
        .....

       Me.busyIndicator.IsBusy = True
       BgWorker.RunWorkerAsync(Me.cmbFormats.SelectedItem.ToString.Trim())

    Catch ex As Exception
        Utility.Message.ErrorMessage(ex)
    End Try

End Sub

completed event:

Private Sub BgWorker_RunWorkerCompleted(ByVal sender As Object, ByVal e As RunWorkerCompletedEventArgs)
    busyIndicator.IsBusy = False
End Sub

DoWork:

Private Sub ExtractData(sender As Object, e As DoWorkEventArgs)
    Dim exformat As IExtractor = New FormatFactory().CreateInstance(e.Argument.ToString())
    If (exformat.FeedToDb(filename)) Then
        Utility.Message.SuccessMessage("Successfully Extracted to database")
    Else
    End If
 End Sub

Utility.Message.SuccessMessage :

 Public Shared Sub SuccessMessage(msg As String)
    Dim M As New Text.StringBuilder
    M.AppendLine()
    M.AppendLine(msg)
    M.AppendLine()
    Clipboard.Clear() 'problem with MTA
    Clipboard.SetText(M.ToString)
    MsgBox(M.ToString, MsgBoxStyle.Information, "FF IT")
End Sub

Answer 1

The thing is I have more of these calls inside other methods that are called from inside of FeedToDb

It is fairly difficult to get started on telling you what a horrendous idea this is. Trying to focus on the most severe issues:

• The point of using a BackgroundWorker is to run code that takes time to execute. It won't freeze your user interface, the user can (hopefully) do something else that's useful. If not with your program then he can, say, check his Facebook page or fire up Solitaire. But by forcing him to click on the OK button of a stream of message boxes, that is completely ruined. You turned your user into a slave of your program, he'll quickly tire of the treatment.

• MsgBox() displays a dialog. A dialog needs an owner, another window on which it is on top. MsgBox is convenience function, you don't have to be explicit about who is the owner. It sorts it out by itself, it picks the window that's currently active. But there's a big problem with that, you are displaying it on another thread. When MsgBox goes looking for an owner it will not find one. Windows are owned by a specific thread and your BackgroundWorker thread doesn't own any. So it will fall back to the desktop window as the owner. That's a problem if the user actually continues to interact with the other windows in your app, what is supposed to be possible when your use a worker thread. Now there's a fight between the message box window that's trying to display itself and the window that the user is working with. The message box window will lose. It will end up behind the foreground window. The user can never see it, has no idea it is even around. Looks to him your program froze, he has no idea how to unfreeze it, he'll never think of minimizing the window he's working with.

• The Clipboard.SetText() method call is already a strong hint that you know that you have a UI implementation problem. You already figured out that you forced your user (or yourself) to look through a peep-hole. Delivering important information that the user might want to preserve but not giving him any option to preserve it other than the one-time copy he can make to the clipboard. Doesn't actually work, he won't be able to press Ctrl+V quick enough. This always works much, much, better if you give him a chance to review the info later. A ListBox is a much better way to do this. Or a RichTextBox, pretty suitable to act as a way to display logging info.

Well, enough of that, you are probably looking for a Quick Fix. Take the sting out of that SuccessMessage() method, knowing that it is awkward and can't work correctly when used from a worker thread:

Public Shared Sub SuccessMessage(msg As String)
    If System.Threading.Thread.CurrentThread.GetApartmentState <> Threading.ApartmentState.STA Then Return
    '' etc...
End Sub