Working around UI threading Issues.

[JaedenRuiner]

Code:

inputEdit as Textbox
workThread as BackgroundWorker
Button1 as Button

sub button1_click()
  _myobj = New MyObject(...params...)
  workthread.runworkerasync(_myobj)
end sub

sub workthread_dowork(e as args)
obj as myobject = e.argument
try
  if obj.OpenFile(inputedit.text) then
    obj.execute
  else
     e.cancel = ture
catch ex as exception
  msgbox(ex.message)
end try
e.result = obj
end sub

Now, I believe, and though I could be wrong I doubt it, that there are lot crazy excessive thread protections going on that are unnecessary, basically trying to protect against cross-threaded assignments, but when it comes to reading data they are quite counterproductive. In lower-level languages (C, C++, Delphi) the threads don't protect themselves, it is up to the programmer to do it. THis I dont' mind, because when an Item is read only, the address is the address and the code doesn't care. But right now it does, because i'm using VB.net.

Now, I'm permitted the argument object passed into the WorkThread to process, which is boon to say the least, and I already found that global fields (stored in My.Application) are inaccessible from the workthread. Dunno why they are set to nothing, but they are. They are assigned once at start up and never assigned again, yet somehow in the thread they are nothing, so I have to store them in the constructor of MyObject() in order to use them in the worker thread.

However, the funny part is the InputEdit.Text line in the DoWork() event of the BackgroundWorker.
currently I'm testing some thing and this is my step by step process:

1. I run the application
2. browse for the file
3. click Button1
4. The MyObject executes with an error - This is good because i knew it would. I'm testing that my db transaction is actually doing the commit/rollback. At least so far it is rolling back.
5. Click Button1 again - basically attempting to alter the execution so it won't have an error this time and I can see if it commits.

Error - Wrong guess...this time when it reaches the InputEdit.Text line in the DoWork() even I get a Cross Threading Error that causes an Invoke Exception that terminates the application.

Why? It worked the first time I clicked button1, but the second time is when it complains about accessing the UI across multiple threads...

The data is static and from the time I click button1 to the time it reads inputedit.text in the workthread, it is impossible for the data in InputEdit.Text to change. i thought of using Invoke but that doesn't wait for the return of the invoked method. I need to access the UI for readonly purposes from across Thread lines. How do I do this?

Thanks

 

[JohnH]

Why not pass the data the worker need through the state parameter of runworkerasync method?

 

[JaedenRuiner]

The issue ran deeper than that.

I'm not exactly sure, but i'm thinking I might need to set up a criticalsection around some of the data. the TextBox issue was resolved rather easily by creating a private field in the Form class and setting it in the Button Click method.

the issue is not really cross-threaded access, but that the application (vb) thinks it's an issue. Currently I have two forms. One is an interactive administration of the DB, with individual pages for the different tables of importance, along with a full SQL parsing window for running direct sql commands and multi-command scripts. the other is the main form that is only a edit box, browse button, run button, progress bar, and a small menu and status bar. The Idea is the Admin form is available for examining the data and possibly some updates/manual alterations when necessary. the main program is non-interactive. It's grab a file and process it according to simple rules. When I start that process, (via the run button on the main form) all other forms go disabled/invisible, and the main form only the run button (now a cancel button) is active. So NOTHING else is accessing or manipulating any of the data, only the workerthread.
the main thread (or the VCL thread as it was called in Delphi) is only active for running the paints of the progress bar and updating the visual responses of the current process.
Somewhere in there, something is taking some extra time and is not completing before i try to run it a second time. If i wait long enough I never get the invoke exception.

sometimes the AdHoc query causes the glitch, complaining that it can't open a connection, or more accurately it can't start a Transaction with the jet.oledb server or what not. Other times it complains that it can't even create a connection, and the whole thing bombs. I am fairly certain its a threading issue, but since nothing else is possibly active interfering with the data it shouldn't be. *shrug*

as for passing the data, I'm doing that. i have three objects that I access normally through My.Application.PropertyName, where propertyname is the specific object. Those objects are the...heart of my application. When I tried stepping through the thread the first time it failed, I found that within the worker thread My.Application.Preferences returned "Nothing", even though I know it was set, and when the thread exited, the Preferences were no longer nothing, so I passed all three references to those objects into the constructor of the object I pass to the worker thread. the issue is you only get one object to pass, and for now that is the main object that is the "worker" of the worker-thread.

Thanks

 

[JohnH]

State parameter is Object type so you can create and pass a class/structure containing all necessary info fields. That means any external data the worker might need can be passed through the state parameter as a single object upon thread start. Gathering various info from here and there and other threads during the async work can only be a source for possible synchronization problems and bad OOP design, no matter how organized you claim to be.

 

[JaedenRuiner]

I'm aware that it is an object. That's what I'm doing:
Worker Object

public class MyObject
  _prefs as MyPreferencesClass
  _user as MyUserNameStruct
  _db as MyDatabaseWrapper

public sub new(prefs as MyPreferencesClass, user as MyUserNameStruct, db as MyDatabaseWrapper)
 _prefs = prefs
 _user = user
 _db = db
end sub

public function OpenFile(name as string) as boolean
  'sets internal class fields from the xl file to create the adhoc query
  'and retrive Database Column Names.  
  'ie: Name, Addr, Name, becomes Name, Addr, Name1
end function

public function Execute() as Boolean
'executes my Async Work against the database controlled by the
' _db field.  
end function
end class

ApplicationEvents

 sub Onstartup()
   _rexprefs = new MyPreferencesClass()
   _rexprefs.loadsettings(filename)
   _rexdb = new MyDatabaseWrapper
   _user = GetCurrentUser()
 end sub

MainForm

private _obj as MyObject()
private _filename as string
sub Button_Click
  _obj = new MyObject(My.Application.Preferences, My.Application.UserName, My.Application.RexDB)
  _filename = InputEdit.Text
  WorkThread.RunWorkerAsync(_obj)
end sub

sub WorkThread_DoWork()
  dim obj as MyObject = e.Argument
  try
   if Obj.OpenFile(_filename) then
      obj.Execute()
   end if
  catch
  end try
  e.result = obj
end sub

So all my data should now be encapsulated by the MyObject instance that is being held and run within the async object. It was working pretty flawlessly until I installed some "Transaction" capabilities into my parser, and suddenly i'm getting these

Cannot start a transaction for OLE DB Provider "Microsoft.Jet.OleDb.4.0" for linked server "(null)"

but it is always on the second or third time running the thread. The thread fails, exits and appears to be completed. But somehow something in the backend there is getting mixed up.
I'm using the State object for the Execution, Progress, and Completion of the Thread, and all of it "should" be encapsulated within the MyObject instance. The only question in my mind is do i need to actually create the instances of those objects "within" the thread...but that would be counter productive, and self defeating. Multiple Threads "can" use the same data, they just have to protect it. I want to basically PASS the handles of those objects to the thread until the thread is done with them and ONLY allow them back when the thread has completed. *shrug*
As for the current errors, I don't know if there is something that gives the Database issues during the async threaded operation, that perhaps when I create the first transaction, it doesn't completely go away even though I commit/rollback, and then future attempts to connect and use the database (even for adhoc queries) is attempting to use or create a transaction because it is expecting one to be there.

Still playing with it. I would gather that some of my issue is with the whole garbage collector. I have always preferred the "Class.Create(), Class.Free", or the "GetMem(x), Freemem(X)" methodology, where I (the programmer) am responsible for freeing and allocating memory, and when I free it, it is gone, before the next statement executes. The collector can keep things in memory and I have noticed a bit of a delay that can affect not only performance but also the desired intent of the application because an object that should have been toasted is not gone yet. But, eh, i'm learning. I'll figure out how to whip vb into submission (*cheezygrin*) eventually.

Thanks

 

[JohnH]

i thought of using Invoke but that doesn't wait for the return of the invoked method

?? I just recalled there was some mention of Invoke, but I read too fast past it to notice that you this mixed up. You're probably thinking about BeginInvoke, and while this is a asynchronous call you wait for the call to complete with EndInvoke blocking call. For example you have this delegate and method:

Delegate Function GetTextHandler() As String

Function GetText() As String
    Return Me.RichTextBox1.Text
End Function

In worker you can call it both synchronous and asynchronous like this:

e.Result = Me.Invoke(New GetTextHandler(AddressOf GetText))

Dim ia As IAsyncResult = Me.BeginInvoke(New GetTextHandler(AddressOf GetText))       
'do something in the mean time
e.Result = Me.EndInvoke(ia)

IAsyncResult.AsyncWaitHandle also allows for other synchronization purposes.

I don't know the error you posted but "server "(null)"" could perhaps mean something happened to the connection(string)?

 

[JaedenRuiner]

hrm.. I'm noticing that this is drifting somewhat, and may be necessary to move it to a more appropriate forum...

Well, that Begin/End Invoke will definitely be helpful in the future, but I'm beginning to think that though threads may be the catalyst for the problem, they probably aren't the whole thing. I've solve the vast majority of my issues, by making sure all database connections are instantiated within thread boundaries. So to expand upon the code above:

Function Execute()
try   
  _db.Connect()  
  _db.FillDataSet()
  DoStuff()
finally
  _db.Disconnect() '<- this calls to my connection manager and 
           'shuts down ALL connections that exist to the db.
end try
end function

This basically helps me to assure that even though the Object instances are transferred to the current thread for processing, all database interactions are start, maintained, and terminated all on the creating thread. That should be more "thread-safe".

I even went through and any object that creates and maintains a database connection, I cleaned them up and designed Dispose() and Finalize() destructors for them, so that I am positive that each one is releasing their connections. For a moment there, my manager was maintaining up to 16 different connections because they weren't being disposed of properly.

I don't know the error you posted but "server "(null)"" could perhaps mean something happened to the connection(string)?

Actually, no, i thought that too for some time, but it's SqlServer's...uneducated way of dealing with AdHoc query connections. The Set up is pretty basic, and I'm using OPENROWSET() as it is the easiest for my needs with Excel. It works on Inserts and Selects, basically means I can SqlBulkCopy From an Excel File into a Table, and then ExecuteNonQuery() out to a new excel file created via COM Interop. The issue with the error is not the (Null) because it gives that Everytime there is an error with the AdHoc query.

ExcelAdHoc = "OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database={0}', [{1}$])"

usage:
Strings.Format(ExcelAdHoc, FileName, SheetName)

What is happening now, is that when I initially "Load" the file (the above function in previous post referred to as OpenFile(filename)) is not actually "Loaded". It's opened via Interop, where I grab all the Sheet names (renaming them as necessary because AdHoc does not like any Non-AlphaNumeric, thus, "_", "-", and " " are out) and then I save and close the file in order to "open" it instead via SqlServer. I then simply Create a temporary Select Statement from that:

Select * 
from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Db\092809.xls', [Sheet1$])

The short cut here, is that I'm using my SqlParser, which upon defining the "Source" (From statement) it automatically attempts to load the schema, or column information.

            ta = New SqlDataAdapter(Parser.Connection.CreateCommand)
            ta.SelectCommand.Transaction = Parser.Transaction
            ta.SelectCommand.CommandText = SqlParser.SQL_Select.FmtStr(SqlParser.SQL_All, _info.Name)
            tbl = New DataTable("SchemaFill")
            _schema.Clear()
            ta.FillSchema(tbl, SchemaType.Source)
            If (tbl IsNot Nothing) AndAlso (tbl.Columns.Count > 0) Then
               For i As Integer = 0 To tbl.Columns.Count - 1
                  If tbl.Columns(i).ColumnName.Trim.IsValid Then
                     _schema.Add(tbl.Columns(i).ColumnName, _
                      Type:=FlufConsts.GetDBType(tbl.Columns(i).DataType), _
                      Length:=tbl.Columns(i).MaxLength)
                  End If
               Next
            End If

From there I can grab all the "Actual" column names.

The Threading Issue at this point, is simply, that When an Error occurs during processing, the MyObject kicks out, with exception information ready for the WorkThread_Completed() event, which determines if the Attempt succeeded, failed, or was canceled, and displays the appropriate information, via a msgbox (also logging the error the log file as per the appconfig stuff).

Sometimes these errors are not expected (hence the process of debugging, ), and with the ability to make minor alterations on the fly, I re-execute the command that would take me backinto the tread - basically restarting the execution of the workerthread and worker MyObject from scratch, but without closing down the current debugging session and restarting the app, so the app has never closed after the initial error.

That's when I get that 'Cannot start a transaction for OLE DB Provider "Microsoft.Jet.OleDb.4.0"' error. However, if i let the program sit for a while. (basically the amount of time it took me to write this post), and I click the RunBtn again (never having Closed the application) it works fine. There is some delay from either the Thread, the Database, or both that causes a hiccup in the AdHoc connection. *shrug* But at this point, the issue may be more the Database and Database Threading, which means it might be appropriate to move this thread over to the DataAccess or Sql Server Forums.

Thanks