VB.NET

VB.NET RSS

Multithreaded / asynch SQL

Please support our VB.NET advertiser: Intel Parallel Studio Home

•

Join Date: Dec 2008

Posts: 1

Reputation: investor5555 is an unknown quantity at this point

Solved Threads: 0

investor5555 investor5555 is offline

Offline

Newbie Poster

Multithreaded / asynch SQL

Dec 20th, 2008

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:

 Help with Code Tags 
 vb Syntax (Toggle Plain Text) 
Dim res As IAsyncResult = Command.BeginExecuteNonQuery()
While (res.IsCompleted = False)
  Threading.Thread.Sleep(1000)
 End While
Dim rowCount As Integer = Command.EndExecuteNonQuery(res)
Dim res As IAsyncResult = Command.BeginExecuteNonQuery()
While (res.IsCompleted = False)
  Threading.Thread.Sleep(1000)
 End While
Dim rowCount As Integer = Command.EndExecuteNonQuery(res)

and

 Help with Code Tags 
 vb Syntax (Toggle Plain Text) 
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
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.

Last edited by investor5555; Dec 20th, 2008 at 1:30 am. Reason: Add more info and code tags