I am trying to figure out how to do multi-threaded and asynchronous programming in VB.NET. I am trying to write an application which has a list of .sql files (some stored procedures, some regular t-sql code). These reside in a directory and get read in and processed. The idea is that I have a list of files to process (in order). Some just load individual stored procedures, others run code, and later I execute some of the stored procedures. What I would like to be able to do is, process the items (in order) and be able to decide which scripts depend on others and wait for those processes to be done before continuing to run the next script. For example, I might load a stored procedure, execute it, execute a script that depends on that procedure (so I need to ensure it has completed before running the third script). Then, I can load 20 stored procedures (in any order). Afterwards, run some scripts in groups (for example, updating vendors won't affect General Ledger, but modules that depend on GL need to wait for GL to complete).
The problem lies with trying to get BeginExecuteNonQuery / EndExecuteNonQuery to work. I have tried various things:
Dim res As IAsyncResult = Command.BeginExecuteNonQuery()
While (res.IsCompleted = False)
Threading.Thread.Sleep(1000)
End While
Dim rowCount As Integer = Command.EndExecuteNonQuery(res)
and
Dim acb As System.AsyncCallback = New AsyncCallback(AddressOf DoUpdate)
asResult = Command.BeginExecuteNonQuery(acb, Command)
Private Sub DoUpdate(ByVal asResult As System.IAsyncResult)
Dim rowsAffected As Long
While asResult.IsCompleted = False
rowsAffected = Command.EndExecuteNonQuery(asResult)
End While
End Sub
No matter what I try, the second script gives me an error:
"the command execution cannot proceed due to a pending asynchronous operation already in progress"
I have tried using just Command.ExecuteNonQuery(), which works synchronously, but the long running SQL procedures make the application appear to hang and will not update my form (progress bars, etc).
So, I appear to be stuck unless someone can point me in the right direction.
FYI, using Visual Studio 2008 and SQL Server 2005.
The other part of the equation, which is probably causing the problem, is that I have ONE connection object. This is because I need to handle [inline] WithEvents [/inline] on the connection object in order to get the codes being returned (by RAISERROR) in order to update my progress bars.
If the solution is to use separate sql connection objects, how would I get the feedback from sql scripts? I cannot create 70 connection objects with WithEvents. I suppose I could create 5-10 and re-use them.
Just looking for the best solution.
Unsolved 
