Stored Procedure not done, but IAsyncResult.IsCompleted is "true"

Question

I have what I thought would be a simple program for end-users to run a stored procedure, which can take quite some time to run. I thought I'd able to use an asynchronous connection, call Begin/EndExecuteNonQuery, and have a timer going to let the user know it's still running. So far, so good.

If a certain parameter is true the Stored Procedure being called will EXEC another stored procedure at the very end. It appears that, when this secondary call happens, IsCompleted becomes "true" even though it's not and the program then blocks on EndExecuteNonQuery until the second SP finishes. While I don't know for sure, I imagine this is because the immediate SQL process completes (since there's nothing more for it to do, the EXEC call is the last line) but a sub-process keeps the call going.

How can I tell if the SP is completely done? I get the idea that I'm not completely understanding IAsyncResult.IsCompleted; my impression is that it becomes true when the call is completely done, but the following statement from that link makes me think otherwise:

When this property is true, you can assume it is safe to discard any resources you allocate for use by the asynchronous operation.

So it may be working as intended, in that I can safely discard resources, even though the full SP isn't actually done. If that's the case, is there alternative for knowing when it's completely done? I've considered just using a normal ExecuteNonQuery in another thread (BackgroundWorker?), but I'd like to be sure I'm not missing something with BeginXxx/EndXxx first.

I'm also open to a way to discard the SqlCommand so the program can exit without waiting on the SP, as that first SP is the only thing of real importance to the end user.

Using .Net 4, MSSQL 2008 R2.

Relevant code:

conn = new SqlConnection(@"Server=SERVERSQL;Initial Catalog=MyDatabase;User ID=MyUser;Asynchronous Processing=true");
SqlCommand cmd = new SqlCommand("OuterProcedure", conn);
cmd.CommandType = CommandType.StoredProcedure;
//[...]
IAsyncResult asyncObj = cmd.BeginExecuteNonQuery();
int count = 0;
int finalLine = Console.CursorTop;
while (!asyncObj.IsCompleted) {
    Console.SetCursorPosition(0, finalLine);
    ++count;
    Console.WriteLine("Time taken (m:s): {0}:{1}", Math.Floor((double)count / 60), (count % 60).ToString("D2"));
    System.Threading.Thread.Sleep(1000);
}
Console.WriteLine("Complete");
int results = cmd.EndExecuteNonQuery(asyncObj);

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

If you use BeginExecuteNonQuery your code does not wait for the query to execute before continuing... It will mark as the Async call is complete.

One alternative would be, as you said, to employ a separate BackGroundWorker to execute the stored procedure, if you want it to be executed in a background thread.

Refer this

edited May 23 '17 at 12:06

Community

1
1

answered Mar 29 '15 at 03:17

Saagar Elias Jacky

2,684
2
14
28

I thought about that; I've tried making the SP store the return value of the EXEC, then `SELECT @result`, but this doesn't change anything. Would using a static value in place of `@result` work? – Kodithic Mar 29 '15 at 03:27
@Tukaro check this out : http://stackoverflow.com/questions/14506871/how-to-execute-a-stored-procedure-inside-a-select-query – Saagar Elias Jacky Mar 29 '15 at 03:29
If you can make the second stored procedure a Function, you can call it inside a SELECT statement and will the final statement in your stored procedure. – Saagar Elias Jacky Mar 29 '15 at 03:32
Ah, understood. No, there's no realistic way of making it a function. It does a bunch of number crunching/comparisons, then stores the results to a table. Taking the time to force it into a function would be far, far more trouble than throwing the C# stuff into another thread. – Kodithic Mar 29 '15 at 03:35

Stored Procedure not done, but IAsyncResult.IsCompleted is "true"

1 Answers1