Asychronous ADO.NET , NOT

  • Thread starter Thread starter David Rigler
  • Start date Start date
D

David Rigler

SQLconn = new SqlConnection(dbConnectionString); has async=true
SQLconn.Open();
//
SQLcmd = new SqlCommand(SPName, SQLconn);
SQLcmd.CommandType = CommandType.StoredProcedure;
SQLcmd.CommandTimeout = 0;
// And start executing
IAsyncResult iaR = SQLcmd.BeginExecuteReader();
waitHandles[SQLEVENT] = iaR.AsyncWaitHandle;
int index = WaitHandle.WaitAny(waitHandles);


With this code the WaitHandle.WaitAny returns IMMEDIATELY with the
waitHandles[SQLEVENT] signaled. iaR.IsCompleted is also true?

The code then spends 5 minutes (its a long procedure) waiting for
SQLcmd.EndExecuteReader(iaR) to complete

How come its not waiting on the WaitHandle


thanks


dave
 
David said:
SQLconn = new SqlConnection(dbConnectionString); has async=true
SQLconn.Open();
//
SQLcmd = new SqlCommand(SPName, SQLconn);
SQLcmd.CommandType = CommandType.StoredProcedure;
SQLcmd.CommandTimeout = 0;
// And start executing
IAsyncResult iaR = SQLcmd.BeginExecuteReader();
waitHandles[SQLEVENT] = iaR.AsyncWaitHandle;
int index = WaitHandle.WaitAny(waitHandles);


With this code the WaitHandle.WaitAny returns IMMEDIATELY with the
waitHandles[SQLEVENT] signaled. iaR.IsCompleted is also true?

The code then spends 5 minutes (its a long procedure) waiting for
SQLcmd.EndExecuteReader(iaR) to complete

How come its not waiting on the WaitHandle


thanks


dave
Click to expand...
I've found that this behaviour is down to using RAISERROR in the stored
procedure.

So whats the accepted pattern for getting status from long running
stored procedures ?


dave
 
So whats the accepted pattern for getting status from long running stored
procedures ?
Click to expand...

One technique we use is to have another table to tracks progress. Inside
the stored procedure, we periodically update the progress table. From
within our code, we periodically check the progress-status table and act
upon the results.


Thanks,
Shawn
 
Ah, one issue here might be that only the EXECUTE portion of the query is
async. The part that actually returns the rows is synchronous. I show how to
setup a BackgroundWorker thread to handle the rowset fetch in my workshop
(VSLive Vegas).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top