Asychronous ADO.NET , NOT

[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 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

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

 

[Shawn B.]

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

procedures ?

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

 

[William \(Bill\) Vaughn]

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.
__________________________________